This chapter provides information on backing up and restoring databases in HAWQ system.

As an administrator, you will need to back up and restore your database. HAWQ provides three utilities to help you back up your data:

gpfdist

PXF

pg_dump

gpfdist and PXF are parallel loading and unloading tools that provide the best performance. You can use pg_dump, a non-parallel utility inherited from PostgreSQL.

In addition, in some situations you should back up your raw data from ETL processes.

This section describes these three utilities, as well as raw data backup, to help you decide what fits your needs.

About gpfdist and PXF

You can perform a parallel backup in HAWQ using gpfdist or PXF to unload all data to external tables. Backup files can reside on a local file system or HDFS. To recover tables, you can load data back from external tables to the database.

Performing a Parallel Backup

Check the database size to ensure that the file system has enough space to save the backed up files.

Use the pg_dump utility to dump the schema of the target database.

Create a writable external table for each table to back up to that database.

Load table data into the newly created external tables.

Note: Put the insert statements in a single transaction to prevent problems if you perform any update operations during the backup.

Restoring from a Backup

Create a database to recover to.

Recreate the schema from the schema file (created during the pg_dump process).

Create a readable external table for each table in the database.

Load data from the external table to the actual table.

Run the ANALYZE command once loading is complete. This ensures that the query planner generates optimal plan based on up-to-date table statistics.

gpfdist doesn’t support generating compressed files, while PXF supports compression (you can specify a compression codec used in Hadoop such as org.apache.hadoop.io.compress.GzipCodec).

Both gpfdist and PXF have fast loading performance, but gpfdist is much faster than PXF.

About pg_dump and pg_restore

HAWQ supports the PostgreSQL backup and restore utilities, pg_dump and pg_restore. The pg_dump utility creates a single, large dump file in the master host containing the data from all active segments. The pg_restore utility restores a HAWQ database from the archive created by pg_dump. In most cases, this is probably not practical, as there is most likely not enough disk space in the master host for creating a single backup file of an entire distributed database. HAWQ supports these utilities in case you are migrating data from PostgreSQL to HAWQ.

To create a backup archive for database mydb:

$ pg_dump -Ft -f mydb.tar mydb

To create a compressed backup using custom format and compression level 3:

$ pg_dump -Fc -Z3 -f mydb.dump mydb

To restore from an archive using pg_restore:

$ pg_restore -d new_db mydb.dump

About Backing Up Raw Data

Parallel backup using gpfdist or PXF works fine in most cases. There are a couple of situations where you cannot perform parallel backup and restore operations:

Performing periodically incremental backups.

Dumping a large data volume to external tables - this process takes a long time.

In such situations, you can back up raw data generated during ETL processes and reload it into HAWQ. This provides the flexibility to choose where you store backup files.

Selecting a Backup Strategy/Utility

The table below summaries the differences between the four approaches we discussed above.

gpfdist

PXF

pg_dump

Raw Data Backup

Parallel

Yes

Yes

No

No

Incremental Backup

No

No

No

Yes

Backup Location

Local FS

HDFS

Local FS

Local FS, HDFS

Format

Text, CSV

Text, CSV, Custom

Text, Tar, Custom

Depends on format of row data

Compression

No

Yes

Only support custom format

Optional

Scalability

Good

Good

—

Good

Performance

Fast loading, Fast unloading

Fast loading, Normal unloading

—

Fast (Just file copy)

Estimating Space Requirements

Before you back up your database, ensure that you have enough space to store backup files. This section describes how to get the database size and estimate space requirements.

If tables in your database are compressed, this query shows the compressed size of the database.

Estimate the total size of the backup files.

If your database tables and backup files are both compressed, you can use the value sodddatsize as an estimate value.

If your database tables are compressed and backup files are not, you need to multiply sodddatsize by the compression ratio. Although this depends on the compression algorithms, you can use an empirical value such as 300%.

If your back files are compressed and database tables are not, you need to divide sodddatsize by the compression ratio.

Get space requirement.

If you use HDFS with PXF, the space requirement is size_of_backup_files * replication_factor.

If you use gpfdist, the space requirement for each gpfdist instance is size_of_backup_files / num_gpfdist_instances since table data will be evenly distributed to all gpfdist instances.

Using gpfdist

This section discusses gpfdist and shows an example of how to backup and restore HAWQ database.

gpfdist is HAWQ’s parallel file distribution program. It is used by readable external tables and hawq load to serve external table files to all HAWQ segments in parallel. It is used by writable external tables to accept output streams from HAWQ segments in parallel and write them out to a file.

To use gpfdist, start the gpfdist server program on the host where you want to store backup files. You can start multiple gpfdist instances on the same host or on different hosts. For each gpfdist instance, you specify a directory from which gpfdist will serve files for readable external tables or create output files for writable external tables. For example, if you have a dedicated machine for backup with two disks, you can start two gpfdist instances, each using one disk:

You can also run gpfdist instances on each segment host. During backup, table data will be evenly distributed to all gpfdist instances specified in the LOCATION clause in the CREATE EXTERNAL TABLE definition.

Example

This example of using gpfdist backs up and restores a 1TB tpch database. To do so, start two gpfdist instances on the backup host sdw1 with two 1TB disks (One disk mounts at /data1, another disk mounts at /data2).

Using gpfdist to Back Up the tpch Database

Create backup locations and start the gpfdist instances.

In this example, issuing the first command creates two folders on two different disks with the same postfix backup/tpch_20140627. These folders are labeled as backups of the tpch database on 2014-06-27. In the next two commands, the example shows two gpfdist instances, one using port 8080, and another using port 8081:

The sample shows two tables in the tpch database: orders and line item. The sample shows that two corresponding external tables are created. Specify a location or each gpfdist instance in the LOCATION clause. This sample uses the CSV text format here, but you can also choose other delimited text formats. For more information, see the CREATE EXTERNAL TABLE SQL command.

Troubleshooting gpfdist

Keep in mind that gpfdist is accessed at runtime by the segment instances. Therefore, you must ensure that the HAWQ segment hosts have network access to gpfdist. Since the gpfdist program is a web server, to test connectivity you can run the following command from each host in your HAWQ array (segments and master):

$ wget http://gpfdist_hostname:port/filename

Also, make sure that your CREATE EXTERNAL TABLE definition has the correct host name, port, and file names for gpfdist. The file names and paths specified should be relative to the directory where gpfdist is serving files (the directory path used when you started the gpfdist program). See “Defining External Tables - Examples”.

Using PXF

HAWQ Extension Framework (PXF) is an extensible framework that allows HAWQ to query external system data. The details of how to install and use PXF can be found in Using PXF with Unmanaged Data.

Using PXF to Back Up the tpch Database

Create a folder on HDFS for this backup:

master_host$ hdfs dfs -mkdir -p /backup/tpch-2014-06-27

Dump the database schema using pg_dump and store the schema file in a backup folder:

Here, all backup files for the orders table go in the /backup/tpch-2014-06-27/orders folder, all backup files for the lineitem table go in /backup/tpch-2014-06-27/lineitem folder. We use snappy compression to save disk space.

(Optional) Change the HDFS file replication factor for the backup folder. HDFS replicates each block into three blocks by default for reliability. You can decrease this number for your backup files if you need to:

master_host$ hdfs dfs -setrep 2 /backup/tpch-2014-06-27

Note: This only changes the replication factor for existing files; new files will still use the default replication factor.