Logical backups with pg_dump and pg_restore

You can backup and restore an entire database or individual hypertables using the native PostgreSQL pg_dump and pg_restore commands. This works even for compressed hypertables, without having to decompress the chunks before you begin.

Upgrades between different versions of TimescaleDB can be done in place; you don’t need to backup and restore your data. See the upgrading instructions.

warning

If you are using this pg_dump backup method regularly, make sure you keep track of which versions of PostgreSQL and TimescaleDB you are running. For more information, see “Troubleshooting version mismatches” in this section.

Back up your entire database

You can perform a backup using the pg_dump command at the command prompt. For example, to backup a database named tsdb:

pg_dump -Fc -f tsdb.bak tsdb

To backup a database named tsdb hosted on a remote server:

pg_dump -h <REMOTE_HOST> -p 55555 -U tsdbadmin -Fc -f tsdb.bak tsdb

You might see some errors when running pg_dump. To learn if they can be safely ignored, see the troubleshooting section.

warning

Do not use the pg_dump command to backup individual hypertables. Dumps created using this method lack the necessary information to correctly restore the hypertable from backup.

Restore your entire database from backup

When you need to restore data from a backup, you can use psql to create a new database and restore the data.

Restoring an entire database from backup

1. In psql, create a new database to restore to, and connect to it:

   CREATE DATABASE tsdb;
   \c tsdb
   CREATE EXTENSION IF NOT EXISTS timescaledb;

2. Run timescaledb_pre_restore to put your database in the right state for restoring:

   SELECT timescaledb_pre_restore();

3. Restore the database:

   \! pg_restore -Fc -d tsdb tsdb.bak

4. Run timescaledb_post_restore to return your database to normal operations:

   SELECT timescaledb_post_restore();

warning

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

Back up individual hypertables

The pg_dump command 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.

warning

Do not use the pg_dump command to backup individual hypertables. Dumps created using this method lack the necessary information to correctly restore the hypertable from backup.

You can backup individual hypertables by backing up the entire database, and then excluding the tables you do not want to backup. You can also use this method to backup individual plain tables that are not hypertables.

Backing up individual hypertables

1. At the command prompt, back up the hypertable schema:

   pg_dump -s -d old_db --table conditions -N _timescaledb_internal | \
   grep -v _timescaledb_internal > schema.sql

2. Backup the hypertable data to a CSV file:

   psql -d old_db \
   -c "\COPY (SELECT * FROM conditions) TO data.csv DELIMITER ',' CSV"

Restoring individual hypertables from backup

1. At the command prompt, restore the schema:

   psql -d new_db < schema.sql

2. Recreate the hypertables:

   psql -d new_db -c "SELECT create_hypertable('conditions', 'time')"

3. Restore the data:

   psql -d new_db -c "\COPY conditions FROM data.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 parallel importer instead.

When you create the new hypertable with the create_hypertable command, you do not need to use the same parameters as existed in the old 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.