Logical backup with `pg_dump` and `pg_restore`

You backup 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. For more information, see Versions are mismatched when dumping and restoring a database.

This page shows you how to:

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.

In terminal:

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>
```
Backup your database
```
pg_dump -d "$SOURCE" \
  -Fc -f <db_name>.bak
```
You may see some errors while pg_dump is running. See Troubleshooting self-hosted TimescaleDB to check if they can be safely ignored.
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.

In Terminal:

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>

Backup the database schema and individual tables
1. Back up the hypertable schema:
```
pg_dump -s -d $SOURCE --table <table-name>  > schema.sql
```
2. Backup 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"
```
Restore the schema to the target database
```
psql -d $TARGET < schema.sql
```
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 backup 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 backup 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.

Logical backup with pg_dump and pg_restore

Prerequisites

Back up and restore an entire database

Back up and restore individual hypertables

Related Content

Logical backup with `pg_dump` and `pg_restore`