Timescale's platform automatically manages the underlying disk volume. Due to
platform limitations, it is only possible to resize the disk once every six
hours. Depending on the rate at which you're able to copy data, you may be
affected by this restriction. Affected instances are unable to accept new data
and error with:
FATAL: terminating connection due to administrator command.
If you intend on migrating more than 400 GB of data to Timescale, open a support request requesting the required storage to be pre-allocated in your Timescale instance.
pg_dump starts, it takes an
ACCESS SHARE lock on all tables which it
dumps. This ensures that tables aren't dropped before
pg_dump is able to drop
them. A side effect of this is that any query which tries to take an
ACCESS EXCLUSIVE lock on a table is be blocked by the
ACCESS SHARE lock.
A number of timescale-internal processes require taking
locks to ensure consistency of the data. The following is a non-exhaustive list
of potentially affected operations:
- compress/decompress/recompress chunk
- continuous aggregate refresh (before 2.12)
- create hypertable with foreign keys, truncate hypertable
- enable compression on hypertable
- drop chunks
The most likely impact of the above is that background jobs for retention
policies, compression policies, and continuous aggregate refresh policies are
blocked for the duration of the
pg_dump command. This may have unintended
consequences for your database performance.
When using the
pg_dump directory format, it is possible to use concurrency to
use multiple connections to the source database to dump data. This speeds up
the dump process. Due to the fact that there are multiple connections, it is
pg_dump to end up in a deadlock situation. When it detects a
deadlock it aborts the dump.
In principle, any query which takes an
ACCESS EXCLUSIVE lock on a table
causes such a deadlock. As mentioned above, some common operations which take
ACCESS EXCLUSIVE lock are:
- retention policies
- compression policies
- continuous aggregate refresh policies
If you would like to use concurrency nonetheless, turn off all background jobs
in the source database before running
pg_dump, and turn them on once the dump
is complete. If the dump procedure takes longer than the continuous aggregate
refresh policy's window, you must manually refresh the continuous aggregate in
the correct time range. For more information, consult the
refresh policies documentation.
To turn off the jobs:
SELECT public.alter_job(id::integer, scheduled=>false)FROM _timescaledb_config.bgw_jobWHERE id >= 1000;
To turn on the jobs:
SELECT public.alter_job(id::integer, scheduled=>true)FROM _timescaledb_config.bgw_jobWHERE id >= 1000;
If the directory format is used for pg_dump and pg_restore, concurrency can be
employed to speed up the process. Unfortunately, loading the tables in the
timescaledb_catalog schema concurrently causes errors. Furthermore, the
tsdbadmin user does not have sufficient privileges to turn off triggers in
this schema. To get around this limitation, load this schema serially, and then
load the rest of the database concurrently.
# first, serially load JUST the _timescaledb_catalogpg_restore -d "$TARGET" \--format=directory \--schema='_timescaledb_catalog' \--exit-on-error \--no-tablespaces \--no-owner \--no-privileges \dump# next, concurrently load everything EXCEPT the _timescaledb_catalogpg_restore -d "$TARGET" \--format=directory \--jobs=8 \--exclude-schema='_timescaledb_catalog' \--exit-on-error \--disable-triggers \--no-tablespaces \--no-owner \--no-privileges \dump
_timescaledb_config.bgw_jobs table is used to manage background jobs.
This includes both user-defined actions, compression policies, retention
policies, and continuous aggregate refresh policies. On Timescale, this table
has a trigger which ensures that no database user can create or modify jobs
owned by another database user. This trigger can provide an obstacle for migrations.
--no-owner flag is used with pg_dump and pg_restore, all objects in
the target database are owned by the user that ran pg_restore, likely
If all the background jobs in the source database were owned by a user of the
same name as the user running the restore (again likely
_timescaledb_config.bgw_jobs table should work.
If the background jobs in the source were owned by the
postgres user, they
are be automatically changed to be owned by the
tsdbadmin user. In this case,
one just needs to verify that the jobs do not make use of privileges that the
tsdbadmin user does not possess.
If background jobs are owned by one or more users other than the user employed
in restoring, then there could be issues. To work around this issue, do not dump
this table with
pg_dump. Provide either
--exclude-table='_timescaledb_config.bgw_job' to pg_dump to skip this table.
Then, use psql and the COPY command to dump and restore this table with modified
values for the
# dump the _timescaledb_config.bgw_job table to a csv file replacing the owner# values with tsdbadminpsql -d "$SOURCE" -X -v ON_ERROR_STOP=1 --echo-errors -f - <<'EOF'begin;select string_agg( case attnamewhen 'owner' then $$'tsdbadmin' as "owner"$$else format('%I', attname)end, ', ') as colsfrom pg_namespace ninner join pg_class con (n.nspname = '_timescaledb_config'and n.oid = c.relnamespaceand c.relname = 'bgw_job')inner join pg_attribute aon (c.oid = a.attrelid and a.attnum > 0)\gsetcopy(select :colsfrom _timescaledb_config.bgw_jobwhere id >= 1000) to stdout with (format csv, header true)\g bgw_job.csvrollback;\qEOF# copy the csv file into the target's _timescaledb_config.bgw_jobpsql -X -d "$TARGET" -v ON_ERROR_STOP=1 --echo-errors \-c "\copy _timescaledb_config.bgw_job from 'bgw_job.csv' with (format csv, header match)"
Once the table has been loaded and the restore completed, you may then use SQL to adjust the ownership of the jobs and/or the associated stored procedures and functions as you wish.
There are a vast number of PostgreSQL extensions available in the wild. Timescale supports many of the most popular extensions, but not all extensions. Before migrating, check that the extensions you are using are supported on Timescale. Consult the list of supported extensions.
When self-hosting, the timescaledb extension may be installed in an arbitrary
schema. Timescale only supports installing the timescaledb extension in the
public schema. How to go about resolving this depends heavily on the
particular details of the source schema and the migration approach chosen.
Timescale does not support using custom tablespaces. Providing the
--no-tablespaces flag to pg_dump and pg_restore when dumping/restoring the
schema results in all objects being in the default tablespace as desired.
While PostgreSQL clusters can contain many databases, Timescale instances are limited to a single database. When migrating a cluster with multiple databases to Timescale, one can either migrate each source database to a separate Timescale instance or "merge" source databases to target schemas.
The tsdbadmin database user is the most powerful available on Timescale, but it is not a true superuser. Review your application for use of superuser privileged operations and mitigate before migrating.
Found an issue on this page?Report an issue!