Logical backup with pg_dump and pg_restore

You back up and restore each self-hosted PostgreSQL database with TimescaleDB enabled using the native PostgreSQL pg_dump and pg_restore commands. This also works for compressed hypertables, you don’t have to decompress the chunks before you begin.

If you are using pg_dump to backup regularly, make sure you keep track of the versions of PostgreSQL and TimescaleDB you are running.

This page shows you how to:

• Back up and restore an entire database
• Back up and restore individual hypertables

You can also upgrade between different versions of TimescaleDB.

Prerequisites

• A source database to backup from, and a target database to restore to.
• Install the psql and pg_dump PostgreSQL client tools on your migration machine.

Back up and restore an entire database

You backup and restore an entire database using pg_dump and psql.

1. Set your connection strings

   These variables hold the connection information for the source database to backup from and the target database to restore to:

   export SOURCE=postgres://<user>:<password>@<source host>:<source port>/<db_name>
   export TARGET=postgres://<user>:<password>@<source host>:<source port>

2. Back up your database

   pg_dump -d "$SOURCE" \
     -Fc -f <db_name>.bak

   You may see some errors while pg_dump is running. These can usually be safely ignored.

3. Restore your database from the backup

   1. Connect to your target database:

      psql -d "$TARGET"

   2. Create a new database and enable TimescaleDB:

      CREATE DATABASE <restoration database>;
      \c <restoration database>
      CREATE EXTENSION IF NOT EXISTS timescaledb;

   3. Put your database in the right state for restoring:

      SELECT timescaledb_pre_restore();

   4. Restore the database:

      pg_restore -Fc -d <restoration database> <db_name>.bak

   5. Return your database to normal operations:

      SELECT timescaledb_post_restore();

      Do not use pg_restore with the -j option. This option does not correctly restore the TimescaleDB catalogs.

Back up and restore individual hypertables

pg_dump provides flags that allow you to specify tables or schemas to back up. However, using these flags means that the dump lacks necessary information that TimescaleDB requires to understand the relationship between them. Even if you explicitly specify both the hypertable and all of its constituent chunks, the dump would still not contain all the information it needs to recreate the hypertable on restore.

To backup individual hypertables, backup the database schema, then backup only the tables you need. You also use this method to backup individual plain tables.

1. Set your connection strings

   These variables hold the connection information for the source database to backup from and the target database to restore to:

   export SOURCE=postgres://<user>:<password>@<source host>:<source port>/<db_name>
   export TARGET=postgres://<user>:<password>@<source host>:<source port>/<db_name>

2. Back up the database schema and individual tables

   1. Back up the hypertable schema:

      pg_dump -s -d $SOURCE --table <table-name>  > schema.sql

   2. Back up hypertable data to a CSV file. For each hypertable to backup:

      psql -d $SOURCE \
      -c "\COPY (SELECT * FROM <table-name>) TO <table-name>.csv DELIMITER ',' CSV"

3. Restore the schema to the target database

   psql -d $TARGET < schema.sql

4. Restore hypertables from the backup

   For each hypertable to backup:

   1. Recreate the hypertable:

      psql -d $TARGET -c "SELECT create_hypertable(<table-name>, <partition>)"

      When you create the new hypertable, you do not need to use the same parameters as existed in the source database. This can provide a good opportunity for you to re-organize your hypertables if you need to. For example, you can change the partitioning key, the number of partitions, or the chunk interval sizes.

   2. Restore the data:

      psql -d $TARGET -c "\COPY <table-name> FROM <table-name>.csv CSV"

      The standard COPY command in PostgreSQL is single threaded. If you have a lot of data, you can speed up the copy using the timescaledb-parallel-copy.

Best practice is to back up and restore a database at a time. However, if you have superuser access to PostgreSQL instance with TimescaleDB installed, you can use pg_dumpall to back up all PostgreSQL databases in a cluster, including global objects that are common to all databases, namely database roles, tablespaces, and privilege grants. You restore the PostgreSQL instance using psql. For more information, see the PostgreSQL documentation.