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:

source /home/gpadmin/.bashrc

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.