Dual-write and backfill from PostgreSQL database

This document provides detailed step-by-step instructions to migrate data using the dual-write and backfill migration method from a source database which is using PostgreSQL to Timescale.

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.

In detail, the migration process consists of the following steps:

1. Set up a target database instance in Timescale.
2. Modify the application to write to the target database.
3. Migrate schema and relational data from source to target.
4. Start the application in dual-write mode.
5. Determine the completion point T.
6. Backfill time-series data from source to target.
7. Validate that all data is present in target database.
8. Validate that target database can handle production load.
9. Switch application to treat target database as primary (potentially continuing to write into source database, as a backup).

Tip

If you get stuck, you can get help by either opening a support request, or take your issue to the #migration channel in the community slack, where the developers of this migration method are there to help.

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

Create a database service in Timescale.

If you intend on migrating more than 400 GB, 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].

How exactly to do this is dependent on the language that your application is written in, and on how exactly your ingestion and application function. In the simplest case, you simply execute two inserts in parallel. In the general case, you must think about how to handle the failure to write to either the source or target database, and what mechanism you want to or can build to recover from such a failure.

Should your time-series data have foreign-key references into a plain table, you must ensure that your application correctly maintains the foreign key relations. If the referenced column is a *SERIAL type, the same row inserted into the source and target may not obtain the same autogenerated id. If this happens, the data backfilled from the source to the target is internally inconsistent. In the best case it causes a foreign key violation, in the worst case, the foreign key constraint is maintained, but the data references the wrong foreign key. To avoid these issues, best practice is to follow live migration.

You may also want to execute the same read queries on the source and target database to evaluate the correctness and performance of the results which the queries deliver. Bear in mind that the target database spends a certain amount of time without all data being present, so you should expect that the results are not the same for some period (potentially a number of days).

You would probably like to convert some of your large tables which contain time-series data into hypertables. This step consists of identifying those tables, excluding their data from the database dump, copying the database schema and tables, and setting up the time-series tables as hypertables. The data is backfilled into these hypertables in a subsequent step.

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

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.

Ideal candidates for hypertables are large tables containing time-series data. This is usually data with some form of timestamp value (TIMESTAMPTZ, TIMESTAMP, BIGINT, INT etc.) as the primary dimension, and some other measurement values.

pg_dump -d "$SOURCE" \
  --format=plain \
  --quote-all-identifiers \
  --no-tablespaces \
  --no-owner \
  --no-privileges \
  --exclude-table-data=<table name or pattern> \
  --file=dump.sql

Copy

• --exclude-table-data is used to exclude all data from hypertable candidates. You can either specify a table pattern, or specify --exclude-table-data multiple times, once for each table to be converted.

• --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.

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

Copy

For each table which should be converted to a hypertable in the target database, execute:

SELECT create_hypertable('<table name>', by_range('<time column name>'));

Copy

Note

The by_range dimension builder is an addition to TimescaleDB 2.13. For simpler cases, like this one, you can also create the hypertable using the old syntax:

SELECT create_hypertable('<table name>', '<time column name>');

Copy

For more information about the options which you can pass to create_hypertable, consult the create_table API reference. For more information about hypertables in general, consult the hypertable documentation.

You may also wish to consider taking advantage of some of Timescale's killer features, such as:

• retention policies to automatically drop unneeded data
• tiered storage to automatically move data to Timescale's low-cost bottomless object storage tier
• compression to reduce the size of your hypertables
• continuous aggregates to write blisteringly fast aggregate queries on your data

With the target database set up, your application can now be started in dual-write mode.

After dual-writes have been executing for a while, the target hypertable contains data in three time ranges: missing writes, late-arriving data, and the "consistency" range

Expand image

If the application is made up of multiple writers, and these writers did not all simultaneously start writing into the target hypertable, there is a period of time in which not all writes have made it into the target hypertable. This period starts when the first writer begins dual-writing, and ends when the last writer begins dual-writing.

Some applications have late-arriving data: measurements which have a timestamp in the past, but which weren't written yet (for example from devices which had intermittent connectivity issues). The window of late-arriving data is between the present moment, and the maximum lateness.

The consistency range is the range in which there are no missing writes, and in which all data has arrived, that is between the end of the missing writes range and the beginning of the late-arriving data range.

The length of these ranges is defined by the properties of the application, there is no one-size-fits-all way to determine what they are.

The completion point T is an arbitrarily chosen time in the consistency range. It is the point in time to which data can safely be backfilled, ensuring that there is no data loss.

The completion point should be expressed as the type of the time column of the hypertables to be backfilled. For instance, if you're using a TIMESTAMPTZ time column, then the completion point may be 2023-08-10T12:00:00.00Z. If you're using a BIGINT column it may be 1695036737000.

If you are using a mix of types for the time columns of your hypertables, you must determine the completion point for each type individually, and backfill each set of hypertables with the same type independently from those of other types.

Dump the data from your source database on a per-table basis into CSV format, and restore those CSVs into the target database using the timescaledb-parallel-copy tool.

Determine the window of data that to be copied from the source database to the target. Depending on the volume of data in the source table, it may be sensible to split the source table into multiple chunks of data to move independently. In the following steps, this time range is called <start> and <end>.

Usually the time column is of type timestamp with time zone, so the values of <start> and <end> must be something like 2023-08-01T00:00:00Z. If the time column is not a timestamp with time zone then the values of <start> and <end> must be the correct type for the column.

If you intend to copy all historic data from the source table, then the value of <start> can be '-infinity', and the <end> value is the value of the completion point T that you determined.

The dual-write process may have already written data into the target database in the time range that you want to move. In this case, the dual-written data must be removed. This can be achieved with a DELETE statement, as follows:

psql $TARGET -c "DELETE FROM <hypertable> WHERE time >= <start> AND time < <end>);"

Copy

Important

The BETWEEN operator is inclusive of both the start and end ranges, so it is not recommended to use it.

Compression policies must be turned off for the target hypertable while data is being backfilled. This prevents the compression policy from compressing chunks which are only half full.

In the following command, replace <hypertable> with the fully qualified table name of the target hypertable, for example public.metrics:

psql -d $TARGET -f -v hypertable=<hypertable> - <<'EOF'
SELECT public.alter_job(j.id, scheduled=>false)
FROM _timescaledb_config.bgw_job j
JOIN _timescaledb_catalog.hypertable h ON h.id = j.hypertable_id
WHERE j.proc_schema IN ('_timescaledb_internal', '_timescaledb_functions')
  AND j.proc_name = 'policy_compression'
  AND j.id >= 1000
  AND format('%I.%I', h.schema_name, h.table_name)::text::regclass = :'hypertable'::text::regclass;
EOF

Copy

Execute the following command, replacing <source table> and <hypertable> with the fully qualified names of the source table and target hypertable respectively:

psql $SOURCE -f - <<EOF
  \copy ( \
      SELECT * FROM <source table> WHERE time >= <start> AND time < <end> \
    ) TO stdout WITH (format CSV);" | timescaledb-parallel-copy \
  --connection $TARGET \
  --table <hypertable> \
  --log-batches \
  --batch-size=1000 \
  --workers=4
EOF

Copy

The above command is not transactional. If there is a connection issue, or some other issue which causes it to stop copying, the partially copied rows must be removed from the target (using the instructions in step 6b above), and then the copy can be restarted.

In the following command, replace <hypertable> with the fully qualified table name of the target hypertable, for example public.metrics:

psql -d $TARGET -f -v hypertable=<hypertable> - <<'EOF'
SELECT public.alter_job(j.id, scheduled=>true)
FROM _timescaledb_config.bgw_job j
JOIN _timescaledb_catalog.hypertable h ON h.id = j.hypertable_id
WHERE j.proc_schema IN ('_timescaledb_internal', '_timescaledb_functions')
  AND j.proc_name = 'policy_compression'
  AND j.id >= 1000
  AND format('%I.%I', h.schema_name, h.table_name)::text::regclass = :'hypertable'::text::regclass;
EOF

Copy

Now that all data has been backfilled, and the application is writing data to both databases, the contents of both databases should be the same. How exactly this should best be validated is dependent on your application.

If you are reading from both databases in parallel for every production query, you could consider adding an application-level validation that both databases are returning the same data.

Another option is to compare the number of rows in the source and target tables, although this reads all data in the table which may have an impact on your production workload.

Another option is to run ANALYZE on both the source and target tables and then look at the reltuples column of the pg_class table. This is not exact, but doesn't require reading all rows from the table. Note: for hypertables, the reltuples value belongs to the chunk table, so you must take the sum of reltuples for all chunks belonging to the hypertable. If the chunk is compressed in one database, but not the other, then this check cannot be used.

Now that dual-writes have been in place for a while, the target database should be holding up to production write traffic. Now would be the right time to determine if the target database can serve all production traffic (both reads and writes). How exactly this is done is application-specific and up to you to determine.

Once you've validated that all the data is present, and that the target database can handle the production workload, the final step is to switch to the target database as your primary. You may want to continue writing to the source database for a period, until you are certain that the target database is holding up to all production traffic.