How to backup database in Greenplum?
Post date: Nov 07, 2012 9:30:45 PM
How do I back up just the schema for all of my databases?
The pg_dumpall utility extracts all databases in a Greenplum Database system to a single script file. It will generate the DDL for all objects, including global objects such as resource queues and roles.
$ pg_dumpall --gp-syntax --schema-only > db_dump.sql
How do I back up just the schema for one of my databases?
The gp_dump utility dumps the contents of a database into SQL script files, which can then be used to restore the database schema and user data at a later time using gp_restore. The following command will create a schema dump of the template1 database:
$ gp_dump -s template1
How do I back up just the data in my database?
The following command will create a data dump of the test database:
$ gp_dump -a test
How do I clone a database?
The following command will create a full copy of your database into a new database:
# CREATE DATABASE new_dbname TEMPLATE old_dbname;
How do I schedule the backup of my entire database?
The gpcrondump utiity is a wrapper utility for gp_dump, which can be called directly or from a crontab entry. The following example is a Linux crontab entry that runs a backup of the test database (and global objects) nightly at one past midnight:
01 0 * * * gpadmin gpcrondump -x test -c -g -G -a -q >> gp_test.log
To back up multiple databases, you will need multiple gpcrondump entries in crontab. You will need to source your .bashrc file, .profile or greenplum_path.sh file to ensure you have the correct environment variables set.