Migrate using pg_dump and pg_restore

To easily migrate from self-hosted PostgreSQL or TimescaleDB to Timescale, you use native PostgreSQL pg_dump and pg_restore. If you are migrating from self-hosted TimescaleDB, this works for compressed hypertables without having to decompress data before you begin.

Before you migrate to Timescale, be aware that each Timescale instance has a single database, does not support tablespaces or all available extensions. Also there is no superuser associated with a Timescale instance.

This page shows you how to:

• Migrate from TimescaleDB
• Migrate from PostgreSQL

For minimal downtime, the pg_dump and psql commands should be run from a machine with a low-latency, high-throughput link to the database that they are connected to. As Timescale instances run in the Amazon cloud, use an AWS EC2 instance in the same region as your Timescale instance.

Before you begin, ensure that you have:

• Installed the PostgreSQL client libraries on the machine that you will perform the migration from. You will need pg_dump and psql.
• Created a database service in Timescale.
• Checked that all PostgreSQL extensions you use are available on Timescale. For more information, see the list of compatible extensions.
• Checked that the version of PostgreSQL in your target database is greater than or equal to that of the source database.

The following instructions show you how to move your data from self-hosted TimescaleDB to a Timescale instance using pg_dump and psql. To avoid data loss, you should take applications that connect to the database offline. The duration of the migration is proportional to the amount of data stored in your database.

We do not recommend using this migration method to migrate more than 100 GB of data, primarily because of the amount of downtime that it implies for your application, instead use the live migration low-downtime migration solution. Should you nonetheless wish to migrate more than 400 GB of data with this method, open a support request to ensure that enough disk is pre-provisioned on your Timescale instance.

You can open a support request directly from the Timescale console, or by email to [email protected].

Note

In the context of migrations, your existing production database is referred to as the "source" database, while the new Timescale database that you intend to migrate your data to is referred to as the "target" database.

Before you migrate, ensure that you're running the exact same version of Timescale on both your target and source databases. That is, the major,minor, and patch version must all be the same. For more information, see the upgrade instructions for self-hosted TimescaleDB.

Note

For the sake of convenience, connection strings to the source and target databases are referred to as $SOURCE and $TARGET throughout this guide. This can be set in your shell, for example:

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

Copy

Dump the roles from the source database (only necessary if you're using roles other than the default postgres role in your database):

pg_dumpall -d "$SOURCE" \
  -l $DB_NAME \
  --quote-all-identifiers \
  --roles-only \
  --file=roles.sql

Copy

Important

Some providers like Managed Service for TimescaleDB (MST) and AWS RDS don't support role password dumps. If dumping the passwords results in the error:

pg_dumpall: error: query failed: ERROR:  permission denied for table pg_authid

Copy

Execute the command adding the --no-role-passwords flag. After restoring the roles into the target database, manually set passwords with ALTER ROLE name WITH PASSWORD '<YOUR_PASSOWRD>';

Timescale services do not support roles with superuser access. If your SQL dump includes roles that have such permissions, you'll need to modify the file to be compliant with the security model.

You can use the following sed command to remove unsupported statements and permissions from your roles.sql file:

sed -i -E \
-e '/CREATE ROLE "postgres";/d' \
-e '/ALTER ROLE "postgres"/d' \
-e 's/(NO)*SUPERUSER//g' \
-e 's/(NO)*REPLICATION//g' \
-e 's/(NO)*BYPASSRLS//g' \
-e 's/GRANTED BY "[^"]*"//g' \
roles.sql

Copy

Note

This command works only with the GNU implementation of sed (sometimes referred to as gsed). For the BSD implementation (the default on macOS), you need to add an extra argument to change the -i flag to -i ''.

To check the sed version, you can use the command sed --version. While the GNU version explicitly identifies itself as GNU, the BSD version of sed generally doesn't provide a straightforward --version flag and simply outputs an "illegal option" error.

A brief explanation of this script is:

• CREATE ROLE "postgres"; and ALTER ROLE "postgres": These statements are removed because they require superuser access, which is not supported by Timescale.

• (NO)SUPERUSER | (NO)REPLICATION | (NO)BYPASSRLS: These are permissions that require superuser access.

• GRANTED BY role_specification: The GRANTED BY clause can also have permissions that require superuser access and should therefore be removed. Note: Per the TimescaleDB documentation, the GRANTOR in the GRANTED BY clause must be the current user, and this clause mainly serves the purpose of SQL compatibility. Therefore, it's safe to remove it.

Dump the source database schema and data:

pg_dump -d "$SOURCE" \
  --format=plain \
  --quote-all-identifiers \
  --no-tablespaces \
  --no-owner \
  --no-privileges \
  --file=dump.sql

Copy

Check the run time, a long-running pg_dump can cause various issues

Note

It is possible to dump using multiple connections to the source database. This may dramatically reduce the time taken to dump the source database. For more information, see dumping with concurrency and restoring with concurrency.

The following is a brief explanation of the flags used:

• --no-tablespaces is required because Timescale does not support tablespaces other than the default. This is a known limitation.

• --no-owner is required because Timescale's tsdbadmin user is not a superuser and cannot assign ownership in all cases. This flag means that everything is owned by the user used to connect to the target, regardless of ownership in the source. This is a known limitation.

• --no-privileges is required because Timescale's tsdbadmin user is not a superuser and cannot assign privileges in all cases. This flag means that privileges assigned to other users must be reassigned in the target database as a manual clean-up task. This is a known limitation.

It is very important that the version of the TimescaleDB extension is the same in the source and target databases. This requires upgrading the TimescaleDB extension in the source database before migrating.

You can determine the version of TimescaleDB in the target database with the following command:

psql $TARGET -c "SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';"

Copy

To update the TimescaleDB extension in your source database, first ensure that the desired version is installed from your package repository. Then you can upgrade the extension with the following query:

psql $SOURCE -c "ALTER EXTENSION timescaledb UPDATE TO '<version here>';"

Copy

For more information and guidance, consult the Upgrade TimescaleDB page.

The following command loads the dumped data into the target database:

psql $TARGET -v ON_ERROR_STOP=1 --echo-errors \
    -f roles.sql \
    -c "SELECT timescaledb_pre_restore();" \
    -f dump.sql \
    -c "SELECT timescaledb_post_restore();"

Copy

It uses timescaledb_pre_restore and timescaledb_post_restore to put your database in the right state for restoring.

Verify that the data has been successfully restored by connecting to the target database by querying the restored data.

Once you have verified that the data is present, and returns the results that you expect, you can reconfigure your application to use the target database and then restart the application.

The following instructions show you how to move your data from self-hosted PostgreSQL to a Timescale instance using pg_dump and psql. To avoid data loss, you should take applications that connect to the database offline. The duration of the migration is proportional to the amount of data stored in your database.

This migration method only moves the data. It does not enable Timescale features like hypertables, data compression or retention. You must manually enable these after the migration, which also requires taking your application offline.

We do not recommend using this migration method to migrate more than 100 GB of data, primarily because of the amount of downtime that it implies for your application, instead use the live migration low-downtime migration solution. Should you nonetheless wish to migrate more than 400 GB of data with this method, open a support request to ensure that enough disk is pre-provisioned on your Timescale instance.

You can open a support request directly from the Timescale console, or by email to [email protected].

Note

In the context of migrations, your existing production database is referred to as the "source" database, while the new Timescale database that you intend to migrate your data to is referred to as the "target" database.

Note

For the sake of convenience, connection strings to the source and target databases are referred to as $SOURCE and $TARGET throughout this guide. This can be set in your shell, for example:

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

Copy

Dump the roles from the source database (only necessary if you're using roles other than the default postgres role in your database):

pg_dumpall -d "$SOURCE" \
  -l $DB_NAME \
  --quote-all-identifiers \
  --roles-only \
  --file=roles.sql

Copy

Important

Some providers like Managed Service for TimescaleDB (MST) and AWS RDS don't support role password dumps. If dumping the passwords results in the error:

pg_dumpall: error: query failed: ERROR:  permission denied for table pg_authid

Copy

Execute the command adding the --no-role-passwords flag. After restoring the roles into the target database, manually set passwords with ALTER ROLE name WITH PASSWORD '<YOUR_PASSOWRD>';

Timescale services do not support roles with superuser access. If your SQL dump includes roles that have such permissions, you'll need to modify the file to be compliant with the security model.

You can use the following sed command to remove unsupported statements and permissions from your roles.sql file:

sed -i -E \
-e '/CREATE ROLE "postgres";/d' \
-e '/ALTER ROLE "postgres"/d' \
-e 's/(NO)*SUPERUSER//g' \
-e 's/(NO)*REPLICATION//g' \
-e 's/(NO)*BYPASSRLS//g' \
-e 's/GRANTED BY "[^"]*"//g' \
roles.sql

Copy

Note

This command works only with the GNU implementation of sed (sometimes referred to as gsed). For the BSD implementation (the default on macOS), you need to add an extra argument to change the -i flag to -i ''.

To check the sed version, you can use the command sed --version. While the GNU version explicitly identifies itself as GNU, the BSD version of sed generally doesn't provide a straightforward --version flag and simply outputs an "illegal option" error.

A brief explanation of this script is:

• CREATE ROLE "postgres"; and ALTER ROLE "postgres": These statements are removed because they require superuser access, which is not supported by Timescale.

• (NO)SUPERUSER | (NO)REPLICATION | (NO)BYPASSRLS: These are permissions that require superuser access.

• GRANTED BY role_specification: The GRANTED BY clause can also have permissions that require superuser access and should therefore be removed. Note: Per the TimescaleDB documentation, the GRANTOR in the GRANTED BY clause must be the current user, and this clause mainly serves the purpose of SQL compatibility. Therefore, it's safe to remove it.

Dump the source database schema and data:

pg_dump -d "$SOURCE" \
  --format=plain \
  --quote-all-identifiers \
  --no-tablespaces \
  --no-owner \
  --no-privileges \
  --file=dump.sql

Copy

Check the run time, a long-running pg_dump can cause various issues

Note

It is possible to dump using multiple connections to the source database. This may dramatically reduce the time taken to dump the source database. For more information, see dumping with concurrency and restoring with concurrency.

The following is a brief explanation of the flags used:

• --no-tablespaces is required because Timescale does not support tablespaces other than the default. This is a known limitation.

• --no-owner is required because Timescale's tsdbadmin user is not a superuser and cannot assign ownership in all cases. This flag means that everything is owned by the user used to connect to the target, regardless of ownership in the source. This is a known limitation.

• --no-privileges is required because Timescale's tsdbadmin user is not a superuser and cannot assign privileges in all cases. This flag means that privileges assigned to other users must be reassigned in the target database as a manual clean-up task. This is a known limitation.

Load the dumped roles and data into the target database:

psql $TARGET -v ON_ERROR_STOP=1 --echo-errors \
    -f roles.sql \
    -f dump.sql

Copy

Update the table statistics by running ANALYZE on all data:

psql $TARGET -c "ANALYZE;"

Copy

Verify that the data has been successfully restored by connecting to the target database and querying the restored data.

Once you have verified that the data is present, and returns the results that you expect, you can reconfigure your application to use the target database and then start your application.