--- title: Sync data from PostgreSQL | Tiger Data Docs description: Sync PostgreSQL tables to Tiger Cloud in real time using the source PostgreSQL connector (livesync) --- Early access Note The source PostgreSQL connector is not yet supported for production use. 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](/docs/migrate/live-migration/index.md) 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](https://www.postgresql.org/docs/current/logical-replication.html), 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](https://www.postgresql.org/docs/current/sql-createpublication.html). - **Enable** [hypertables](/docs/learn/hypertables/understand-hypertables/index.md), [columnstore](/docs/learn/columnar-storage/understand-hypercore/index.md), and [continuous aggregates](/docs/learn/continuous-aggregates/index.md) on your logical replica. ![Connectors overview in Tiger Console](/docs/_astro/tiger-console-connector-overview.DOnSC8st_1D7nU8.webp) ## Prerequisites Best practice is to use an [Ubuntu EC2 instance](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EC2_GetStarted.html#ec2-launch-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](/docs/get-started/quickstart/create-service/index.md). Each Tiger Cloud service has a single PostgreSQL instance that supports the [most popular extensions](/docs/deploy/tiger-cloud/tiger-cloud-aws/tiger-cloud-extensions/index.md). 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](/docs/deploy/tiger-cloud/tiger-cloud-aws/upgrades#define-your-maintenance-window/index.md). In addition: - Find your Tiger Cloud service [connection details](/docs/integrate/find-connection-details/index.md). - Install [PostgreSQL client tools](/docs/integrate/query-administration/psql/index.md) 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](/docs/reference/timescaledb/continuous-aggregates/refresh_continuous_aggregate/index.md). 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](/docs/integrate/connectors/source/sync-from-postgres/index.md) 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](#tab-panel-662) - [From PostgreSQL](#tab-panel-663) 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](https://console.aws.amazon.com/rds/home#databases:), select the RDS instance to migrate. 2. Click `Configuration`, scroll down and note the `DB instance parameter group`, then click `Parameter Groups`. ![RDS parameter groups in the AWS console](/docs/_astro/awsrds-parameter-groups.CiXrFBVV_1Diea.webp) 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`, `PostgreSQL`\ • `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](https://console.aws.amazon.com/rds/home#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 ``: Terminal window ``` psql $SOURCE -c "CREATE USER 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: Terminal window ``` psql $SOURCE -c "GRANT rds_replication TO " ``` 3. Grant permissions to create a publication: Terminal window ``` psql $SOURCE -c "GRANT CREATE ON DATABASE TO " ``` 4. Assign the user permissions on the source database: Terminal window ``` psql $SOURCE <; GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO ; ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO ; EOF ``` If the tables you are syncing are not in the `public` schema, grant the user permissions for each schema you are syncing: Terminal window ``` psql $SOURCE < TO ; GRANT SELECT ON ALL TABLES IN SCHEMA TO ; ALTER DEFAULT PRIVILEGES IN SCHEMA GRANT SELECT ON TABLES TO ; EOF ``` 5. On each table you want to sync, make `` the owner: Terminal window ``` psql $SOURCE -c 'ALTER TABLE OWNER TO ;' ``` 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
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 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`. 1) **Tune the Write Ahead Log (WAL) on the PostgreSQL source database** ``` psql $SOURCE <`: ``` psql $SOURCE -c "CREATE USER 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 REPLICATION" ``` 3. Grant permissions to create a publication: ``` psql $SOURCE -c "GRANT CREATE ON DATABASE TO " ``` 4. Assign the user permissions on the source database: ``` psql $SOURCE <; GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO ; ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO ; 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 < TO ; GRANT SELECT ON ALL TABLES IN SCHEMA TO ; ALTER DEFAULT PRIVILEGES IN SCHEMA GRANT SELECT ON TABLES TO ; EOF ``` 5. On each table you want to sync, make `` the owner: ``` psql $SOURCE -c 'ALTER TABLE
OWNER TO ;' ``` 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
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 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](#tab-panel-660) - [Docker / Terminal](#tab-panel-661) To sync from your PostgreSQL database to a Tiger Cloud service using [Tiger Console](https://console.cloud.tigerdata.com/dashboard/services): 1. **Connect to your Tiger Cloud service** Open [Tiger Console](https://console.cloud.tigerdata.com/dashboard/services) 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. 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](https://docs.docker.com/engine/install/) and [PostgreSQL client tools](/docs/integrate/query-administration/psql/index.md) (`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 `` 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: Terminal window ``` export SOURCE="postgres://:@:/" export TARGET="postgres://:@:/" ``` `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** Terminal window ``` 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** Terminal window ``` 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](/docs/learn/hypertables/understand-hypertables/index.md) for better performance: Terminal window ``` psql -X -d $TARGET -c "SELECT public.create_hypertable('public.
', by_range('', ''::interval));" ``` Example: `time` column, 1-day chunks: Terminal window ``` 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 FOR TABLE , ; ``` - To add tables later: `ALTER PUBLICATION ADD TABLE ;` - To use a partitioned table: `ALTER PUBLICATION SET (publish_via_partition_root = true);` 2. **Run the live-sync connector** Run the connector as a long-lived container (for example, daemon). Replace ``, ``, and optionally ``: Terminal window ``` docker run -d --rm --name livesync timescale/live-sync:v0.11.2 run \ --publication \ --subscription \ --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 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: Terminal window ``` docker run --rm timescale/live-sync:v0.11.2 run \ --publication \ --subscription \ --source $SOURCE \ --target $TARGET \ --drop ``` `--drop` removes: - The replication slot (`live_sync_`) 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](/docs/migrate/live-migration/index.md) guide. For connector capabilities and technical details, see the [source PostgreSQL connector reference](/docs/integrate/connectors/source/sync-from-postgres/index.md).