I have to work with the Greenplum database as we will replace the oracle data warehouse with this monster.
I need to learn every aspects and I think I will share everything about this, because it's very difficult to find good scripts, solutions for this product.
One of my first issue was how to export metadata from development database to refresh QA.
The free GPAdmin III is not good enough for mass refresh, so we need to use the server side utilities.
That tool is the pg_dump as the greenplum is more or less compatible with postgres.
how to export the metadata of the database :
set|grep PG
PGDATABASE=edw_dev
pg_dump -s -f outputfile.sql
how to export the metadata of a specific schema
pg_dump -s -f outputfile.sql -n 'schema_name'
The import is just the execution of the sql file:
psql -e -a -f outputfile.sql -o import_log.txt
sometimes it's not enough because the output is not good to read the
command
execution time
error messages
that's why i use this to get the result like oracle sqlplus spool function :
psql -e -a -f outputfile.sql -o import_log.out 2>&1 | tee import_log.out > import_log.txt
As the pg_dump exports the metadata sql I always had to do a manual sort to get just the functions, just views, etc. Here is an example how to get JUST to functions :
pg_dump -Fc -v -s database_name -f temp.dump -n 'schema_name'
pg_restore -l temp.dump | grep FUNCTION > functionlist
pg_restore -L functionlist temp.dump > yourfunctions.sql
to verify yourself :
cat yourfunctions.sql | grep FUNCTION | grep CREATE | wc -l
