The timescaledb-backup tool

The timescaledb-backup tool is designed to help you manage backing up and restoring your TimescaleDB database. It resolves some of the common issues you might come across when using the standard PostgreSQL dump and restore method. The timescaledb-backup tool uses pg_dump and pg_restore in the background, and additionally:

• Automatically tracks the installed version of TimescaleDB, and ensure that the correct version is restored.
• Runs all pre- and post-restore jobs at the correct times during the restore process.
• Enables parallel restores by sequencing catalog and data restores during the restore process.

For more information about the timescaledb-backup tool, check out the source code in our github repository.

Install timescaledb-backup

If you have installed the timescaledb-tools package, you already have the timescaledb-backup tool. If you want to install it separately, you can use go get, or download a binary from our release page.

Procedure: Installing timescaledb-backup

1. Ensure you have installed these packages on the machine that you want to backup:
   • pg_dump
   • pg_dumpall
   • pg_restore

2. Install timescaledb-backup:

   $ go get github.com/timescale/timescaledb-backup/

Back up your database

You can perform a backup using the ts-dump command at the command prompt. For example, to backup a database named exampledb:

ts-dump --db-URI=postgresql://[email protected]/exampledb --dump-dir=˜/backups/exampledb/

The two main parameters you need to specify are:

• --db-URI: the location of the database that you want to back up. It should be in Postgres URI format, which is postgresql://[user[:password]@][host][:port][,...][/dbname][?param1=value1&...]. You can specify parameters in the URI as required, including passwords.
• --dump-dir: the path to where the backup is stored. Make sure that the path to the directory exists, but the directory itself does not. The directory is created by the script.

When your backup is created, the dump directory is created. The dump directory contains:

• Subdirectory that contains the dump itself
• An .so JSON file with the TimescaleDB version information
• Any sql files generated by the script

tip

Make sure that you retain all the files created by the script in the dump directory. They are required when you restore, so that the tool knows which version of PostgreSQL to use, and can restore your database correctly.

Optional parameters for backing up

In addition to the two required parameters, you can specify these optional parameters when you use the ts-dump command:

Restoring your database from backup

You can restore your database from backup using the ts-restore command at the command prompt:

Procedure: Restoring your database from backup

1. Create a new, empty, database in the location you want to restore to. In this example, the new database is called newdb.

2. Restore the database named exampledb to the new location, from the directory where the backup is stored:

   ts-restore --db-URI=postgresql://[email protected]/newdb --dump-dir=˜/backups/exampledb/

The two main parameters you need to specify are:

• --db-URI: the location of the new database that you want to restore to. It should already exist, but does not need to be set up. It should be in Postgres URI format, which is postgresql://[user[:password]@][host][:port][,...][/dbname][?param1=value1&...]. You can specify parameters in the URI as required, including passwords.
• --dump-dir: the path to where the backup is stored.

By default, the roles.sql and tablespaces.sql files are in the backup directory, and you do not need to restore them manually. However, if you want to run them before you restore, you can use these commands:

psql -d <db-URI> -f <dump-dir>/roles.sql  
psql -d <db-URI> -f <dump-dir>/tablespaces.sql

If you are restoring multiple databases on the same postgres instance, you only need to restore the roles.sql and tablespaces.sql files once. If you attempt to restore them multiple times, the script shows errors, but it does not damage the database or the backup.

Optional parameters for restoring

In addition to the two required parameters, you can specify these optional parameters when you use the ts-restore command:

Using timescaledb-backup to upgrade

In some instances, you can use the timescaledb-backup tool to upgrade your installed version. For example, if you are running a PostgreSQL 11 cluster on port 5432, and a PostgreSQL 12 cluster on port 5433, this command runs the dump on port 5432 in verbose mode with two workers:

ts-dump --db-URI=postgresql://postgres:[email protected]:5432/tsdb --dump-dir=˜/dumps/dump1 --verbose --jobs=2

You can then restore to port 5433:

ts-restore --db-URI=postgresql://postgres:[email protected]:5433/tsdb --dump-dir=˜/dumps/dump1 --verbose --jobs=2