Sync data from PostgreSQL

Tips

Livesync vs live migration: The source PostgreSQL connector (livesync) keeps a Tiger Cloud service continuously synchronized with a source PostgreSQL database. It is designed for ongoing replication, not a one-time move. For a one-time database migration with minimal downtime, use live migration instead. The two features use different Docker images and are not interchangeable.

You use the source PostgreSQL connector in Tiger Cloud to synchronize all data or specific tables from a PostgreSQL database to your service in real time. You run the connector continuously, turning PostgreSQL into the primary with your Tiger Cloud service as a logical replica, so you can leverage Tiger Cloud‘s real-time analytics on your replica data without impacting the primary.

The connector uses the established PostgreSQL logical replication protocol, so you get compatibility, familiarity, and a broad knowledge base when adopting it. This is used for data synchronization, not one-off migration.

Use cases include:

• Copy existing data from a PostgreSQL instance to a Tiger Cloud service: up to ~150 GB/hr (recommended minimum 4 CPU/16 GB on both source and target); copy publication tables in parallel (very large tables use a single connection); foreign key validation is disabled during sync so you can sync a table without its referenced tables; and you can track progress via pg_stat_progress_copy on the source.
• Synchronize real-time changes from the source to your service.
• Add and remove tables on demand using the PUBLICATION interface.
• Enable hypertables, columnstore, and continuous aggregates on your logical replica.

Prerequisites

Best practice is to use an Ubuntu EC2 instance hosted in the same region as your Tiger Cloud service to move data. That is, the machine you run the commands on to move your data from your source database to your target Tiger Cloud service.

Before you move your data:

• Create a target Tiger Cloud service.

  Each Tiger Cloud service has a single PostgreSQL instance that supports the most popular extensions. Tiger Cloud services do not support tablespaces, and there is no superuser associated with a service. Best practice is to create a Tiger Cloud service with at least 8 CPUs for a smoother experience. A higher-spec instance can significantly reduce the overall migration window.

• To ensure that maintenance does not run while migration is in progress, best practice is to adjust the maintenance window.

In addition:

• Find your Tiger Cloud service connection details.
• Install PostgreSQL client tools on your sync machine.
• Make sure the source PostgreSQL instance and the target Tiger Cloud service have the same extensions installed. The connector does not create extensions on the target. If a table uses types from an extension, create that extension on the target service before syncing the table.

Limitations

• The source PostgreSQL instance must be accessible from the Internet. Services behind a firewall or VPC are not yet supported.
• Indexes, primary keys, unique constraints, and sequences are not migrated. Create the indexes you need on the target service for your query patterns.
• Using TimescaleDB as the source has limited support (no continuous aggregates).
• The source must run PostgreSQL 13 or later.
• Schema changes must be coordinated: make compatible changes on the Tiger Cloud service first, then on the source.
• There is WAL volume growth on the source during large table copy.
• Continuous aggregate invalidation: The connector uses session_replication_role=replica during copy, so table triggers (including those that invalidate continuous aggregates) do not fire. continuous aggregates on the target do not auto-refresh for data inserted during the sync. This applies only to data below the aggregate’s materialization watermark. If the continuous aggregate exists on the source, add it to the connector publication. If it exists only on the target, manually refresh with the force option of refresh_continuous_aggregate.

Avoid connection strings that route through poolers like PgBouncer; the connector needs a direct connection to the database.

Set your connection string

Set the connection information for the source database on your sync machine. See Tune your source database on the connector reference for the exact connection string and source configuration.

Tune your source database

Configure the source for logical replication and create a connector user with the right permissions. Choose your source type:

From AWS RDS/Aurora

Updating parameters on a PostgreSQL instance will cause an outage. Choose a time that will cause the least issues to tune this database.

1. Tune the Write Ahead Log (WAL) on the RDS/Aurora PostgreSQL source database

   1. In RDS console, select the RDS instance to migrate.

   2. Click Configuration, scroll down and note the DB instance parameter group, then click Parameter Groups.

      

   3. Click Create parameter group, fill in the form with the following values, then click Create:

      • Parameter group name, whatever suits your fancy.
      • Description, knock yourself out with this one.
      • Engine type, {C.PG}
      • Parameter group family, the same as DB instance parameter group in your Configuration.

   4. In Parameter groups, select the parameter group you created, then click Edit.

   5. Update the following parameters, then click Save changes:

      • rds.logical_replication set to 1: record the information needed for logical decoding.
      • wal_sender_timeout set to 0: disable the timeout for the sender process.

   6. In RDS, navigate back to your databases, select the RDS instance to migrate, and click Modify.

   7. Scroll down to Database options, select your new parameter group, and click Continue.

   8. Click Apply immediately or choose a maintenance window, then click Modify DB instance.

   Changing parameters will cause an outage. Wait for the database instance to reboot before continuing. After it comes back up, verify that the new settings are in effect on your database.

2. Create a user for the source PostgreSQL connector and assign permissions

   1. Create <pg connector username>:

      psql $SOURCE -c "CREATE USER <pg connector username> PASSWORD '<password>'"

      You can use an existing user. However, you must ensure that the user has the following permissions.

   2. Grant permissions to create a replication slot:

      psql $SOURCE -c "GRANT rds_replication TO <pg connector username>"

   3. Grant permissions to create a publication:

      psql $SOURCE -c "GRANT CREATE ON DATABASE <database name> TO <pg connector username>"

   4. Assign the user permissions on the source database:

      psql $SOURCE <<EOF
      GRANT USAGE ON SCHEMA "public" TO <pg connector username>;
      GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO <pg connector username>;
      ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO <pg connector username>;
      EOF

      If the tables you are syncing are not in the public schema, grant the user permissions for each schema you are syncing:

      psql $SOURCE <<EOF
      GRANT USAGE ON SCHEMA <schema> TO <pg connector username>;
      GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO <pg connector username>;
      ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT SELECT ON TABLES TO <pg connector username>;
      EOF

   5. On each table you want to sync, make <pg connector username> the owner:

      psql $SOURCE -c 'ALTER TABLE <table name> OWNER TO <pg connector username>;'

      You can skip this step if the replicating user is already the owner of the tables.

3. Enable replication DELETE and UPDATE operations

   Replica identity assists data replication by identifying the rows being modified. Your options are that each table and hypertable in the source database should either have:

   • A primary key: data replication defaults to the primary key of the table being replicated. Nothing to do.
   • A viable unique index: each table has a unique, non-partial, non-deferrable index that includes only columns marked as NOT NULL. If a UNIQUE index does not exist, create one to assist the migration. You can delete it after migration. For each table, set REPLICA IDENTITY to the viable unique index:

     psql -X -d $SOURCE -c ‘ALTER TABLE <table name> REPLICA IDENTITY USING INDEX <_index_name>‘

   • No primary key or viable unique index: use brute force. For each table, set REPLICA IDENTITY to FULL:

     psql -X -d $SOURCE -c ‘ALTER TABLE <table_name> REPLICA IDENTITY FULL’

     For each UPDATE or DELETE statement, PostgreSQL reads the whole table to find all matching rows. This results in significantly slower replication. If you are expecting a large number of UPDATE or DELETE operations on the table, best practice is to not use FULL.

From PostgreSQL

1. Tune the Write Ahead Log (WAL) on the PostgreSQL source database

   psql $SOURCE <<EOF
   ALTER SYSTEM SET wal_level='logical';
   ALTER SYSTEM SET max_wal_senders=10;
   ALTER SYSTEM SET wal_sender_timeout=0;
   EOF

   • GUC “wal_level” as “logical”
   • GUC “max_wal_senders” as 10
   • GUC “wal_sender_timeout” as 0

   This will require a restart of the PostgreSQL source database.

2. Create a user for the connector and assign permissions

   1. Create <pg connector username>:

      psql $SOURCE -c "CREATE USER <pg connector username> PASSWORD '<password>'"

      You can use an existing user. However, you must ensure that the user has the following permissions.

   2. Grant permissions to create a replication slot:

      psql $SOURCE -c "ALTER ROLE <pg connector username> REPLICATION"

   3. Grant permissions to create a publication:

      psql $SOURCE -c "GRANT CREATE ON DATABASE <database name> TO <pg connector username>"

   4. Assign the user permissions on the source database:

      psql $SOURCE <<EOF
      GRANT USAGE ON SCHEMA "public" TO <pg connector username>;
      GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO <pg connector username>;
      ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO <pg connector username>;
      EOF

      If the tables you are syncing are not in the public schema, grant the user permissions for each schema you are syncing:

      psql $SOURCE <<EOF
      GRANT USAGE ON SCHEMA <schema> TO <pg connector username>;
      GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO <pg connector username>;
      ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT SELECT ON TABLES TO <pg connector username>;
      EOF

   5. On each table you want to sync, make <pg connector username> the owner:

      psql $SOURCE -c 'ALTER TABLE <table name> OWNER TO <pg connector username>;'

      You can skip this step if the replicating user is already the owner of the tables.

3. Enable replication DELETE and UPDATE operations

   Replica identity assists data replication by identifying the rows being modified. Your options are that each table and hypertable in the source database should either have:

   • A primary key: data replication defaults to the primary key of the table being replicated. Nothing to do.
   • A viable unique index: each table has a unique, non-partial, non-deferrable index that includes only columns marked as NOT NULL. If a UNIQUE index does not exist, create one to assist the migration. You can delete it after migration. For each table, set REPLICA IDENTITY to the viable unique index:

     psql -X -d $SOURCE -c ‘ALTER TABLE <table name> REPLICA IDENTITY USING INDEX <_index_name>‘

   • No primary key or viable unique index: use brute force. For each table, set REPLICA IDENTITY to FULL:

     psql -X -d $SOURCE -c ‘ALTER TABLE <table_name> REPLICA IDENTITY FULL’

     For each UPDATE or DELETE statement, PostgreSQL reads the whole table to find all matching rows. This results in significantly slower replication. If you are expecting a large number of UPDATE or DELETE operations on the table, best practice is to not use FULL.

Synchronize data to your service

Choose your target: sync to Tiger Cloud using Tiger Console, or to a self-hosted TimescaleDB instance using the terminal and the live-sync Docker image.

Tiger Cloud

To sync from your PostgreSQL database to a Tiger Cloud service using Tiger Console:

1. Connect to your Tiger Cloud service

   Open Tiger Console and select the service you want to sync live data into.
2. Connect the source database and the target service

   • Click Connectors → PostgreSQL.
   • Set the connector name (pencil icon).
   • Check Set wal_level to logical and Update your credentials, then click Continue.
   • Enter your database credentials or PostgreSQL connection string and click Connect to database. Tiger Console connects to the source and retrieves schema information.
3. Choose tables and optimize for hypertables

   • In the Select table dropdown, choose the tables to sync.
   • Click Select tables +. Tiger Console checks the schema and, when possible, suggests the time dimension column for a hypertable.
   • Click Create Connector. Tiger Console starts the source PostgreSQL connector and shows progress.
4. Monitor synchronization

   • Click Connectors to see Connector data flow: Status and amount of data replicated.
   • For per-table progress: Connectors → Source connectors → select your connector.
5. Manage the connector

   • Edit: Connectors → Source connectors → select the connector. You can rename it, add or remove tables.
   • Pause: same table → three-dot menu → Pause.
   • Delete: three-dot menu → Delete (pause the connector first).

You’re now syncing the selected tables from your PostgreSQL instance to your Tiger Cloud service in real time.

Docker / Terminal

Sync from a PostgreSQL source using the timescale/live-sync Docker image. The connector runs on a machine that can reach both the source and target databases. The TARGET can be either a Tiger Cloud service or a self-hosted TimescaleDB instance — only the connection string changes.

1. Prerequisites for self-hosted target

   • A self-hosted TimescaleDB instance (target) and a PostgreSQL source (for example, self-hosted, AWS RDS, or Aurora).
   • Docker and PostgreSQL client tools (psql, pg_dump, pg_restore, vacuumdb) on the sync machine.
   • The source and target must have the same extensions installed; create any needed extensions on the target before syncing.
   • The <user> in the SOURCE connection string must have the replication role (to create a replication slot).
2. Set your connection strings

   On your sync machine, set the source and target connection strings:

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

   TARGET is the connection string to your Tiger Cloud service or self-hosted TimescaleDB instance. Use your Tiger Cloud service host and port if syncing to Tiger Cloud. Avoid connection strings that route through poolers (for example, PgBouncer); use a direct connection.

Migrate the table schema to your self-hosted target

1. Export schema from the source

   pg_dump $SOURCE \
     --no-privileges --no-owner --no-publications --no-subscriptions \
     --no-table-access-method --no-tablespaces --schema-only \
     --file=schema.sql

2. Apply the schema on the target

   psql $TARGET -f schema.sql

Convert time-series tables to hypertables (optional)

On the target, convert tables that hold time-series or event data into hypertables for better performance:

psql -X -d $TARGET -c "SELECT public.create_hypertable('public.<table>', by_range('<time_column>', '<chunk_interval>'::interval));"

Example: time column, 1-day chunks:

psql -X -d $TARGET -c "SELECT public.create_hypertable('public.metrics', by_range('time', '1 day'::interval));"

1. Specify the tables to synchronize

   On the source database, create a publication for the tables you want to sync:

   CREATE PUBLICATION <publication_name> FOR TABLE <table_name>, <table_name>;

   • To add tables later: ALTER PUBLICATION <publication_name> ADD TABLE <table_name>;
   • To use a partitioned table: ALTER PUBLICATION <publication_name> SET (publish_via_partition_root = true);
2. Run the live-sync connector

   Run the connector as a long-lived container (for example, daemon). Replace <publication_name>, <subscription_name>, and optionally <table_map>:

   docker run -d --rm --name livesync timescale/live-sync:v0.11.2 run \
     --publication <publication_name> \
     --subscription <subscription_name> \
     --source $SOURCE \
     --target $TARGET

   • --publication: name of the publication on the source.
   • --subscription: name for the subscription on the target (identifies this sync).
   • --source / --target: connection strings. Target is your self-hosted TimescaleDB.
   • Optional --table-map: for example, '{"source":{"schema":"public","table":"x"},"target":{"schema":"public","table":"y"}}' to map source tables to different target names or schemas.
   • Optional --copy-data false: skip initial copy and only replicate changes after the slot is created.
3. Monitor and manage

   • Logs: docker logs -f livesync
   • Progress on target: psql $TARGET -c "SELECT * FROM _ts_live_sync.subscription_rel"
   • Replication lag on source: query pg_replication_slots for live_sync_% slots and compare pg_current_wal_flush_lsn() with confirmed_flush_lsn.
   • Add/remove tables: ALTER PUBLICATION <publication_name> ADD TABLE ... or DROP TABLE ... on the source; add tables to the publication as needed.
   • Stop: docker stop livesync
   • Update statistics after initial sync: vacuumdb --analyze --verbose --dbname=$TARGET
4. Clean up when done (--drop)

   When you are permanently done syncing (for example, you have fully cut over to Tiger Cloud, or you want to restart the sync from scratch), run the connector with --drop to remove the replication slot from the source and all subscription state from the target:

   docker run --rm timescale/live-sync:v0.11.2 run \
     --publication <publication_name> \
     --subscription <subscription_name> \
     --source $SOURCE \
     --target $TARGET \
     --drop

   --drop removes:

   • The replication slot (live_sync_<subscription_name>) on the source database.
   • The subscription and internal tracking tables (_ts_live_sync.*) on the target database.

   Warning

   Run --drop only when you intend to permanently stop or fully restart the sync. Dropping the replication slot while the source is still active causes WAL to be retained until the slot is recreated, which can grow disk usage on the source.

For more detail on the terminal flow (for example, resetting sequences, partitioned tables), see the live migration guide.

For connector capabilities and technical details, see the source PostgreSQL connector reference.