---
title: Sync data from PostgreSQL | Tiger Data Docs
description: Sync PostgreSQL tables to Tiger Cloud in real time using the source PostgreSQL connector
---

Tips

**Source PostgreSQL connector vs. Livesync replication:** The source PostgreSQL connector provides **continuous ongoing replication** where PostgreSQL stays the primary and Tiger Cloud acts as a logical replica. For a **one-time migration** to Tiger Cloud with a cutover at the end, see [Livesync replication](/migrate/livesync-replication/index.md) instead. Both features share the same underlying technology, but the workflows and end states differ.

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

![Connectors overview in Tiger Console](/_astro/tiger-console-connector-overview.C3brL-kO_Z1bOtoE.webp)

The source PostgreSQL connector in Tiger Cloud leverages the well-established PostgreSQL logical replication protocol. By relying on this protocol, Tiger Cloud ensures compatibility, familiarity, and a broader knowledge base, making it easier for you to adopt the connector and integrate your data.

You use the source PostgreSQL connector for data synchronization, rather than migration. This includes:

- Copy existing data from a PostgreSQL instance to a Tiger Cloud service:

  - Copy data at up to 150 GB/hr.

    You need at least a 4 CPU/16 GB source database, and a 4 CPU/16 GB target service.

  - Copy the publication tables in parallel.

    Large individual tables still use a single connection, except PostgreSQL declarative partitioned tables published with `publish_via_partition_root = false` (the PostgreSQL default), which are copied leaf partition by leaf partition in parallel since v0.23.0.

  - Forget foreign key relationships.

    The connector disables foreign key validation during the sync. For example, if a `metrics` table refers to the `id` column on the `tags` table, you can still sync only the `metrics` table without worrying about their foreign key relationships.

  - Track progress.

    PostgreSQL exposes `COPY` progress under `pg_stat_progress_copy`.

- Synchronize real-time changes from a PostgreSQL instance to a Tiger Cloud service.

- Add and remove tables on demand using the [PostgreSQL PUBLICATION interface](https://www.postgresql.org/docs/current/sql-createpublication.html).

- Enable features such as [hypertables](/learn/hypertables/understand-hypertables/index.md), [columnstore](/learn/columnar-storage/understand-hypercore/index.md), and [continuous aggregates](/learn/continuous-aggregates/index.md) on your logical replica.

- Indexes, primary key, unique constraints, and sequences are **not** migrated. Create needed indexes on the target for your queries.

- TimescaleDB as source has limited support (for example, no continuous aggregates).

- Compressed hypertable chunks are copied in uncompressed form: the connector copies logical data by decompressing chunks on the fly. Copying compressed chunks as-is is on the roadmap but not yet available.

- Schema changes must be coordinated: apply compatible changes on the target first, then on the source.

- WAL volume on the source increases during large table copy.

- **Continuous aggregates:** The connector uses `session_replication_role=replica` during copy, so triggers (including continuous aggregate invalidation) do not run. Data synced during initial load below a continuous aggregate's materialization watermark may not appear in the aggregate until you manually refresh. If the aggregate exists on the source, include it in the connector's publication; if only on the target, use the `force` option of [refresh\_continuous\_aggregate](/reference/timescaledb/continuous-aggregates/refresh_continuous_aggregate/index.md) to refresh affected ranges.

* [Tiger Console](#tab-panel-756)
* [Self-hosted PostgreSQL connector](#tab-panel-757)

## Prerequisites for this integration guide

To follow these steps, you'll need:

- A [Tiger Cloud service](/get-started/quickstart/create-service/index.md).

* Your [connection details](/integrate/find-connection-details/index.md).

- The [PostgreSQL client tools](/integrate/query-administration/psql/index.md) installed on your sync machine.

- The source PostgreSQL instance and the target Tiger Cloud service must have the same extensions installed.

  The source PostgreSQL connector does not create extensions on the target. If the table uses column types from an extension, first create the extension on the target Tiger Cloud service before syncing the table.

## Limitations

- Indexes, including the primary key, unique constraints, and sequences are not migrated to the target Tiger Cloud service.

  We recommend that, depending on your query patterns, you create only the necessary indexes on the target Tiger Cloud service.

* Using TimescaleDB as the source has limited support (no CAGGs).

* The source must be running PostgreSQL 13 or later.

* Schema changes must be co-ordinated.

  Make compatible changes to the schema in your Tiger Cloud service first, then make the same changes to the source PostgreSQL instance.

* Ensure that the source PostgreSQL instance and the target Tiger Cloud service have the same extensions installed.

  The source PostgreSQL connector does not create extensions on the target. If the table uses column types from an extension, first create the extension on the target Tiger Cloud service before syncing the table.

* There is WAL volume growth on the source PostgreSQL instance during large table copy.

* Continuous aggregate invalidation

  The connector uses `session_replication_role=replica` during data replication, which prevents table triggers from firing. This includes the internal triggers that mark continuous aggregates as invalid when underlying data changes.

  If you have continuous aggregates on your target database, they do not automatically refresh for data inserted during the migration. This limitation only applies to data below the continuous aggregate's materialization watermark. For example, backfilled data. New rows synced above the continuous aggregate watermark are used correctly when refreshing.

  This can lead to:

  - Missing data in continuous aggregates for the migration period.
  - Stale aggregate data.
  - Queries returning incomplete results.

  If the continuous aggregate exists in the source database, best practice is to add it to the PostgreSQL connector publication. If it only exists on the target database, manually refresh the continuous aggregate using the `force` option of [refresh\_continuous\_aggregate](/reference/timescaledb/continuous-aggregates/refresh_continuous_aggregate#samples/index.md).

## Set your connection string

This variable holds the connection information for the source database. In the terminal on your migration machine, set the following:

Terminal window

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

Note

When using the `postgres://user:pass@host:port/db` URI format, URL-encode special characters in the password to avoid URL-parsing errors.

PasswordPaste your password

Enter your password to get the URL-encoded version. Characters that are safe in a URI (like `*`, `~`, `.`, `-`, `_`) are left as-is.

Nothing is sent or stored anywhere; both the tool above and the terminal commands below run locally in your browser or shell.

If you prefer not to paste your password into a form, URL-encode it in your terminal instead:

- [Python](#tab-panel-738)
- [Node.js](#tab-panel-739)
- [jq](#tab-panel-740)

Terminal window

```
python3 -c "import sys, urllib.parse; print(urllib.parse.quote(sys.argv[1]))" '<password>'
```

Terminal window

```
node -e "console.log(encodeURIComponent(process.argv[1]))" '<password>'
```

Terminal window

```
jq -rn '@uri "<password>"'
```

Alternatively, use the `keyword=value` parameter format with single-quoted values, which lets you include special characters without encoding:

Terminal window

```
export SOURCE="host=example.com port=5432 dbname=mydb user=postgres password='%^/20'"
```

See the [libpq connection strings reference](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING) for the full list of parameters.

Important

Avoid using connection strings that route through connection poolers like PgBouncer or similar tools. This tool requires a direct connection to the database to function properly.

## Tune your source database

1. **Database parameters**

   Livesync requires logical replication on the source. How you enable it depends on where your source database runs.

   - [Self-hosted](#tab-panel-746)
   - [AWS RDS / Aurora](#tab-panel-747)
   - [Neon](#tab-panel-748)
   - [Supabase](#tab-panel-749)
   - [Azure Flexible Server](#tab-panel-750)

   Apply the parameters with `ALTER SYSTEM`. A restart is required afterwards.

   Terminal window

   ```
   psql "$SOURCE" <<'EOF'
   ALTER SYSTEM SET wal_level = 'logical';
   ALTER SYSTEM SET max_wal_senders = 30;
   ALTER SYSTEM SET max_replication_slots = 30;
   ALTER SYSTEM SET wal_sender_timeout = 0;
   ALTER SYSTEM SET max_locks_per_transaction = 1200;
   EOF
   ```

   After restart, validate:

   Terminal window

   ```
   psql "$SOURCE" <<'EOF'
   SHOW wal_level;
   SHOW max_wal_senders;
   SHOW max_replication_slots;
   SHOW wal_sender_timeout;
   SHOW max_locks_per_transaction;
   EOF
   ```

   RDS and Aurora do not permit `ALTER SYSTEM`. Set the parameters in a custom parameter group:

   1. In the RDS console, create a parameter group for your PostgreSQL major version, or edit the cluster parameter group for Aurora.

   2. Set:

      - `rds.logical_replication = 1` (enables `wal_level=logical`)
      - `max_wal_senders = 30`
      - `max_replication_slots = 30`
      - `wal_sender_timeout = 0`
      - `max_locks_per_transaction = 1200`

   3. Attach the parameter group to your instance or cluster.

   4. Reboot the instance or cluster (for Aurora, reboot the writer) to apply.

   Validate:

   Terminal window

   ```
   psql "$SOURCE" -c "SHOW rds.logical_replication;"  -- expect: on
   psql "$SOURCE" -c "SHOW wal_level;"                -- expect: logical
   ```

   Neon manages logical replication settings through its console.

   1. Open the Neon Console, go to your project, then click `Settings` > `Logical Replication`.
   2. Click `Enable`.

   No restart or `ALTER SYSTEM` is required. Validate:

   Terminal window

   ```
   psql "$SOURCE" <<'EOF'
   SHOW wal_level;            -- expect: logical
   SHOW max_wal_senders;      -- expect: 10
   SHOW max_replication_slots;-- expect: 10
   EOF
   ```

   Neon computes may auto-suspend on idle. For long initial copies, raise the auto-suspend timeout in `Settings` > `Compute`, or pick a plan that disables it.

   `wal_sender_timeout` can only be changed via a Neon support case. Ask support to disable it (set to `0`) or set it to a high value such as 12 hours to avoid timeouts during the initial copy of large tables.

   Supabase PostgreSQL has logical replication enabled by default; no `ALTER SYSTEM` or reboot is required.

   Validate:

   Terminal window

   ```
   psql "$SOURCE" -c "SHOW wal_level;"  -- expect: logical
   ```

   Recommended changes (apply via the [Supabase custom Postgres config](https://supabase.com/docs/guides/database/custom-postgres-config)):

   - `max_slot_wal_keep_size = 102400` (100 GB) prevents the replication slot from being dropped during long-running transactions.

   Use the **direct connection string**, not the connection pooler: pgBouncer doesn't support logical replication. In `Project Settings` > `Database` > `Connection parameters`, disable `Display connection pooler`.

   Azure does not permit `ALTER SYSTEM`. Set the parameters via the `Server parameters` blade in the Azure portal, or with `az postgres flexible-server parameter set`:

   1. Set `wal_level = logical`.
   2. Set `max_wal_senders = 30`, `max_replication_slots = 30`, `wal_sender_timeout = 0`, `max_locks_per_transaction = 1200`.
   3. Save. Azure prompts to restart the server. Restart now.

   Validate:

   Terminal window

   ```
   psql "$SOURCE" -c "SHOW wal_level;"  -- expect: logical
   ```

2. **Migration user**

   Create a dedicated migration user on the source:

   ```
   psql "$SOURCE" <<'EOF'
   CREATE USER livesync_migrate PASSWORD '<strong_password>';
   ALTER ROLE livesync_migrate WITH SUPERUSER REPLICATION;
   GRANT CREATE ON DATABASE <db_name> TO livesync_migrate;
   EOF
   ```

   `SUPERUSER` is only needed on self-hosted TimescaleDB. New hypertable chunks are created on the fly by the extension, and PostgreSQL requires the role calling `ALTER PUBLICATION ... ADD TABLE` to either own the table or be a superuser. Without `SUPERUSER`, chunks created during the migration won't get added to the publication and their data is skipped. If you can't grant `SUPERUSER`, make `livesync_migrate` the owner of every hypertable in the publication instead.

   Note

   **Azure Database for PostgreSQL:** managed Azure instances don't allow creating a new `SUPERUSER`. Skip the `CREATE USER` step and use the existing server administrator account instead, after granting it replication:

   ```
   ALTER ROLE <admin_user> WITH REPLICATION;
   ```

   Then set:

   Terminal window

   ```
   export SOURCE="postgres://<admin_user>:<password>@<host>:<port>/<db>"
   ```

   If the admin lacks ownership of the application's hypertables, transfer ownership (or use `GRANT ... TO <admin_user> WITH ADMIN OPTION`) before creating the publication.

   Note

   **`pg_hba.conf`:** if the source PostgreSQL instance restricts which users can connect via `pg_hba.conf`, add a `host` rule permitting the `livesync_migrate` user (or your chosen connector user) to connect from the migration/connector host. Managed providers (RDS/Aurora, Neon, Supabase, Azure) handle this through their own auth and network controls and do not use `pg_hba.conf`.

   Grant the migration user access to all user schemas:

   Terminal window

   ```
   psql "$SOURCE" -t -A <<'EOF' | psql "$SOURCE"
   SELECT
     'GRANT USAGE ON SCHEMA ' || quote_ident(nspname) || ' TO livesync_migrate;' || chr(10) ||
     'GRANT SELECT ON ALL TABLES IN SCHEMA ' || quote_ident(nspname) || ' TO livesync_migrate;' || chr(10) ||
     'ALTER DEFAULT PRIVILEGES IN SCHEMA ' || quote_ident(nspname) ||
     ' GRANT SELECT ON TABLES TO livesync_migrate;'
   FROM pg_namespace
   WHERE nspname NOT IN ('information_schema')
     AND nspname NOT LIKE '_timescale%'
     AND nspname NOT LIKE 'pg\_%' ESCAPE '\'
     AND nspname NOT LIKE 'pg_toast%'
     AND nspname NOT LIKE 'pg_temp_%'
     AND nspname NOT LIKE 'timescaledb%'
     AND nspname NOT LIKE 'toolkit%'
   ORDER BY nspname;
   EOF
   ```

   Switch to this user for all subsequent steps:

   Terminal window

   ```
   export SOURCE="postgres://livesync_migrate:<password>@<source_host>:<port>/<db_name>"
   ```

## Synchronize data to your Tiger Cloud service

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

1. **Connect to your Tiger Cloud service**

   In [Tiger Console](https://console.cloud.tigerdata.com/dashboard/services), select the service to sync live data to.

2. **Prepare the source database**

   ![PostgreSQL connector wizard in Tiger Console](/_astro/pg-connector-wizard-tiger-console.BPVoP0DK_Z1yPPjw.webp)

   1. Click `Connectors` > `Postgres`.
   2. Set the name for the new connector by clicking the pencil icon.
   3. Check the boxes for `Set wal_level to logical` and `Update your credentials`, then click `Continue`.

3. **Connect the source database and the target service**

   ![PostgreSQL connector connection string in Tiger Console](/_astro/pg-connector-connection-string.DUeVjYuI_ZcLCmf.webp)

   1. Enter your database credentials or a PostgreSQL connection string. This is the connection string for the migration user you created during source setup.

   2. **(Recommended for private databases)** If the source database is not reachable from the public Internet, toggle `Enable SSH tunneling` and route the connector through a bastion host:

      1. Copy the public key shown in the wizard and append it to the `authorized_keys` file of the user the connector will log in as on the bastion host:

         Terminal window

         ```
         echo "<public key from the connector wizard>" >> ~/.ssh/authorized_keys


         # Verify
         cat ~/.ssh/authorized_keys
         ```

      2. Enter the bastion `Hostname`, `User`, and `Port` in the wizard.

   3. Click `Connect to database`. Tiger Console connects to the source database and retrieves the schema information.

4. **Select the data to synchronize**

   ![Starting the PostgreSQL connector in Tiger Console](/_astro/pg-connector-start-tiger-console.DPSXCOf0_jl7mV.webp)

   Choose where the connector picks tables from:

   - **From an existing publication**: select a PostgreSQL `PUBLICATION` name, then pick schemas and tables from those it includes. Use this when you cannot grant the connector user `CREATE` privilege on the source and your DBA can pre-create the publication, or when you want to apply [row or column filters](https://www.postgresql.org/docs/current/logical-replication-row-filter.html) on published tables.
   - **Directly from the source**: select schemas, then pick the tables to sync from them. Tiger Console creates the publication for you.

   Click `Select tables +`. For each selected table, Tiger Console checks the schema and, if possible, suggests the column to use as the time dimension in a hypertable.

5. **Configure Initial Data Copy workers**

   ![Configure Initial Data Copy worker count in Tiger Console](/_astro/pg-connector-configure-idc-worker-count.D6aj0UEK_2ev3xi.webp)

   Specify the number of parallel connections (Initial Data Copy, or IDC, workers) used to process the initial data copy. Higher values can speed up the initial copy but may increase load on the source database. Defaults to `4`.

   Tune this based on the resources available on the source database and the number of tables being synced. Large individual tables still copy through a single connection, so higher worker counts help most when you have many small to medium tables.

   Click `Create Connector`. Tiger Console starts the source PostgreSQL connector between the source database and the target service and displays the progress.

6. **Monitor and manage the connector**

   ![Editing a PostgreSQL connector in Tiger Console](/_astro/edit-pg-connector-tiger-console.CAiEG08p_Z1hkIxV.webp)

   1. To review the syncing progress for each table, click `Connectors` > `Source connectors`, then select the name of your connector in the table.

   2. To edit the connector, click `Connectors` > `Source connectors`, then select the name of your connector in the table. You can rename the connector, delete or add new tables for syncing.

   3. To pause a connector, click `Connectors` > `Source connectors`, then open the three-dot menu on the right and select `Pause`.

   4. To delete a connector, click `Connectors` > `Source connectors`, then open the three-dot menu on the right and select `Delete`. You must pause the connector before deleting it.

And that is it, you are using the source PostgreSQL connector to synchronize all the data, or specific tables, from a PostgreSQL database instance to your Tiger Cloud service, in real time.

## Prerequisites for this integration guide

To follow these steps, you'll need:

- A target [Tiger Cloud service](/get-started/quickstart/create-service/index.md).

  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.

- A migration machine to run the commands that move data from your source database to your target Tiger Cloud service.

  Best practice: 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.

- An [adjusted maintenance window](/deploy/tiger-cloud/tiger-cloud-aws/upgrades#define-your-maintenance-window/index.md) to prevent maintenance from running during migration.

* The source PostgreSQL instance and the target Tiger Cloud service must have the same extensions installed.

  The source PostgreSQL connector does not create extensions on the target. If the table uses column types from an extension, first create the extension on the target Tiger Cloud service before syncing the table.

* [Docker](https://docs.docker.com/engine/install/) installed on your sync machine.

  For a better experience, use a 4 CPU/16GB EC2 instance or greater to run the source PostgreSQL connector.

* The [PostgreSQL client tools](/integrate/query-administration/psql/index.md) installed on your sync machine.

  This includes `psql`, `pg_dump`, `pg_dumpall`, and `vacuumdb` commands.

## Limitations

- The schema is not migrated by the source PostgreSQL connector, you use `pg_dump`/`pg_restore` to migrate it.

* Using TimescaleDB as the source has limited support (no CAGGs).

* The source must be running PostgreSQL 13 or later.

* Schema changes must be co-ordinated.

  Make compatible changes to the schema in your Tiger Cloud service first, then make the same changes to the source PostgreSQL instance.

* Ensure that the source PostgreSQL instance and the target Tiger Cloud service have the same extensions installed.

  The source PostgreSQL connector does not create extensions on the target. If the table uses column types from an extension, first create the extension on the target Tiger Cloud service before syncing the table.

* There is WAL volume growth on the source PostgreSQL instance during large table copy.

* Continuous aggregate invalidation

  The connector uses `session_replication_role=replica` during data replication, which prevents table triggers from firing. This includes the internal triggers that mark continuous aggregates as invalid when underlying data changes.

  If you have continuous aggregates on your target database, they do not automatically refresh for data inserted during the migration. This limitation only applies to data below the continuous aggregate's materialization watermark. For example, backfilled data. New rows synced above the continuous aggregate watermark are used correctly when refreshing.

  This can lead to:

  - Missing data in continuous aggregates for the migration period.
  - Stale aggregate data.
  - Queries returning incomplete results.

  If the continuous aggregate exists in the source database, best practice is to add it to the PostgreSQL connector publication. If it only exists on the target database, manually refresh the continuous aggregate using the `force` option of [refresh\_continuous\_aggregate](/reference/timescaledb/continuous-aggregates/refresh_continuous_aggregate#samples/index.md).

## Set your connection strings

The `<user>` in the `SOURCE` connection must have the replication role granted in order to create a replication slot.

These variables hold the connection information for the source database and target Tiger Cloud service. In Terminal on your migration machine, set the following:

Terminal window

```
export SOURCE="postgres://<user>:<password>@<source host>:<source port>/<db_name>"
export TARGET="postgres://tsdbadmin:<PASSWORD>@<HOST>:<PORT>/tsdb?sslmode=require"
```

You find the connection information for your Tiger Cloud service in the configuration file you downloaded when you created the service.

Important

Avoid using connection strings that route through connection poolers like PgBouncer or similar tools. This tool requires a direct connection to the database to function properly.

Note

When using the `postgres://user:pass@host:port/db` URI format, URL-encode special characters in the password to avoid URL-parsing errors.

PasswordPaste your password

Enter your password to get the URL-encoded version. Characters that are safe in a URI (like `*`, `~`, `.`, `-`, `_`) are left as-is.

Nothing is sent or stored anywhere; both the tool above and the terminal commands below run locally in your browser or shell.

If you prefer not to paste your password into a form, URL-encode it in your terminal instead:

- [Python](#tab-panel-741)
- [Node.js](#tab-panel-742)
- [jq](#tab-panel-743)

Terminal window

```
python3 -c "import sys, urllib.parse; print(urllib.parse.quote(sys.argv[1]))" '<password>'
```

Terminal window

```
node -e "console.log(encodeURIComponent(process.argv[1]))" '<password>'
```

Terminal window

```
jq -rn '@uri "<password>"'
```

Alternatively, use the `keyword=value` parameter format with single-quoted values, which lets you include special characters without encoding:

Terminal window

```
export SOURCE="host=example.com port=5432 dbname=mydb user=postgres password='%^/20'"
```

See the [libpq connection strings reference](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING) for the full list of parameters.

## Tune your source database

1. **Database parameters**

   Livesync requires logical replication on the source. How you enable it depends on where your source database runs.

   - [Self-hosted](#tab-panel-751)
   - [AWS RDS / Aurora](#tab-panel-752)
   - [Neon](#tab-panel-753)
   - [Supabase](#tab-panel-754)
   - [Azure Flexible Server](#tab-panel-755)

   Apply the parameters with `ALTER SYSTEM`. A restart is required afterwards.

   Terminal window

   ```
   psql "$SOURCE" <<'EOF'
   ALTER SYSTEM SET wal_level = 'logical';
   ALTER SYSTEM SET max_wal_senders = 30;
   ALTER SYSTEM SET max_replication_slots = 30;
   ALTER SYSTEM SET wal_sender_timeout = 0;
   ALTER SYSTEM SET max_locks_per_transaction = 1200;
   EOF
   ```

   After restart, validate:

   Terminal window

   ```
   psql "$SOURCE" <<'EOF'
   SHOW wal_level;
   SHOW max_wal_senders;
   SHOW max_replication_slots;
   SHOW wal_sender_timeout;
   SHOW max_locks_per_transaction;
   EOF
   ```

   RDS and Aurora do not permit `ALTER SYSTEM`. Set the parameters in a custom parameter group:

   1. In the RDS console, create a parameter group for your PostgreSQL major version, or edit the cluster parameter group for Aurora.

   2. Set:

      - `rds.logical_replication = 1` (enables `wal_level=logical`)
      - `max_wal_senders = 30`
      - `max_replication_slots = 30`
      - `wal_sender_timeout = 0`
      - `max_locks_per_transaction = 1200`

   3. Attach the parameter group to your instance or cluster.

   4. Reboot the instance or cluster (for Aurora, reboot the writer) to apply.

   Validate:

   Terminal window

   ```
   psql "$SOURCE" -c "SHOW rds.logical_replication;"  -- expect: on
   psql "$SOURCE" -c "SHOW wal_level;"                -- expect: logical
   ```

   Neon manages logical replication settings through its console.

   1. Open the Neon Console, go to your project, then click `Settings` > `Logical Replication`.
   2. Click `Enable`.

   No restart or `ALTER SYSTEM` is required. Validate:

   Terminal window

   ```
   psql "$SOURCE" <<'EOF'
   SHOW wal_level;            -- expect: logical
   SHOW max_wal_senders;      -- expect: 10
   SHOW max_replication_slots;-- expect: 10
   EOF
   ```

   Neon computes may auto-suspend on idle. For long initial copies, raise the auto-suspend timeout in `Settings` > `Compute`, or pick a plan that disables it.

   `wal_sender_timeout` can only be changed via a Neon support case. Ask support to disable it (set to `0`) or set it to a high value such as 12 hours to avoid timeouts during the initial copy of large tables.

   Supabase PostgreSQL has logical replication enabled by default; no `ALTER SYSTEM` or reboot is required.

   Validate:

   Terminal window

   ```
   psql "$SOURCE" -c "SHOW wal_level;"  -- expect: logical
   ```

   Recommended changes (apply via the [Supabase custom Postgres config](https://supabase.com/docs/guides/database/custom-postgres-config)):

   - `max_slot_wal_keep_size = 102400` (100 GB) prevents the replication slot from being dropped during long-running transactions.

   Use the **direct connection string**, not the connection pooler: pgBouncer doesn't support logical replication. In `Project Settings` > `Database` > `Connection parameters`, disable `Display connection pooler`.

   Azure does not permit `ALTER SYSTEM`. Set the parameters via the `Server parameters` blade in the Azure portal, or with `az postgres flexible-server parameter set`:

   1. Set `wal_level = logical`.
   2. Set `max_wal_senders = 30`, `max_replication_slots = 30`, `wal_sender_timeout = 0`, `max_locks_per_transaction = 1200`.
   3. Save. Azure prompts to restart the server. Restart now.

   Validate:

   Terminal window

   ```
   psql "$SOURCE" -c "SHOW wal_level;"  -- expect: logical
   ```

2. **Migration user**

   Create a dedicated migration user on the source:

   ```
   psql "$SOURCE" <<'EOF'
   CREATE USER livesync_migrate PASSWORD '<strong_password>';
   ALTER ROLE livesync_migrate WITH SUPERUSER REPLICATION;
   GRANT CREATE ON DATABASE <db_name> TO livesync_migrate;
   EOF
   ```

   `SUPERUSER` is only needed on self-hosted TimescaleDB. New hypertable chunks are created on the fly by the extension, and PostgreSQL requires the role calling `ALTER PUBLICATION ... ADD TABLE` to either own the table or be a superuser. Without `SUPERUSER`, chunks created during the migration won't get added to the publication and their data is skipped. If you can't grant `SUPERUSER`, make `livesync_migrate` the owner of every hypertable in the publication instead.

   Note

   **Azure Database for PostgreSQL:** managed Azure instances don't allow creating a new `SUPERUSER`. Skip the `CREATE USER` step and use the existing server administrator account instead, after granting it replication:

   ```
   ALTER ROLE <admin_user> WITH REPLICATION;
   ```

   Then set:

   Terminal window

   ```
   export SOURCE="postgres://<admin_user>:<password>@<host>:<port>/<db>"
   ```

   If the admin lacks ownership of the application's hypertables, transfer ownership (or use `GRANT ... TO <admin_user> WITH ADMIN OPTION`) before creating the publication.

   Note

   **`pg_hba.conf`:** if the source PostgreSQL instance restricts which users can connect via `pg_hba.conf`, add a `host` rule permitting the `livesync_migrate` user (or your chosen connector user) to connect from the migration/connector host. Managed providers (RDS/Aurora, Neon, Supabase, Azure) handle this through their own auth and network controls and do not use `pg_hba.conf`.

   Grant the migration user access to all user schemas:

   Terminal window

   ```
   psql "$SOURCE" -t -A <<'EOF' | psql "$SOURCE"
   SELECT
     'GRANT USAGE ON SCHEMA ' || quote_ident(nspname) || ' TO livesync_migrate;' || chr(10) ||
     'GRANT SELECT ON ALL TABLES IN SCHEMA ' || quote_ident(nspname) || ' TO livesync_migrate;' || chr(10) ||
     'ALTER DEFAULT PRIVILEGES IN SCHEMA ' || quote_ident(nspname) ||
     ' GRANT SELECT ON TABLES TO livesync_migrate;'
   FROM pg_namespace
   WHERE nspname NOT IN ('information_schema')
     AND nspname NOT LIKE '_timescale%'
     AND nspname NOT LIKE 'pg\_%' ESCAPE '\'
     AND nspname NOT LIKE 'pg_toast%'
     AND nspname NOT LIKE 'pg_temp_%'
     AND nspname NOT LIKE 'timescaledb%'
     AND nspname NOT LIKE 'toolkit%'
   ORDER BY nspname;
   EOF
   ```

   Switch to this user for all subsequent steps:

   Terminal window

   ```
   export SOURCE="postgres://livesync_migrate:<password>@<source_host>:<port>/<db_name>"
   ```

## Replication identity

Skip this section for `INSERT`-only workloads.

Tables without a primary key need an explicit replica identity for `UPDATE` and `DELETE` replication. The query below finds such tables and generates the appropriate `ALTER TABLE` statement, using a unique index if one exists, otherwise falling back to `REPLICA IDENTITY FULL`.

Terminal window

```
psql "$SOURCE" -t -A <<'EOF'
SELECT
  CASE
    WHEN i.indexrelid IS NOT NULL THEN
      format('ALTER TABLE %I.%I REPLICA IDENTITY USING INDEX %I;',
        n.nspname, c.relname, ic.relname)
    ELSE
      format('ALTER TABLE %I.%I REPLICA IDENTITY FULL;',
        n.nspname, c.relname)
  END
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_constraint pk ON pk.conrelid = c.oid AND pk.contype = 'p'
LEFT JOIN LATERAL (
  SELECT i.indrelid, i.indexrelid
  FROM pg_index i
  WHERE i.indrelid = c.oid
    AND i.indisunique AND i.indisvalid AND i.indpred IS NULL
    AND NOT EXISTS (
      SELECT 1 FROM unnest(i.indkey) k
      JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = k
      WHERE NOT a.attnotnull
    )
  LIMIT 1
) i ON true
LEFT JOIN pg_class ic ON ic.oid = i.indexrelid
WHERE c.relkind = 'r'
  AND pk.oid IS NULL
  AND n.nspname NOT IN ('information_schema')
  AND n.nspname NOT LIKE '_timescale%'
  AND n.nspname NOT LIKE 'pg\_%' ESCAPE '\'
ORDER BY n.nspname, c.relname;
EOF
```

Review the output, then pipe it to `psql "$SOURCE"` to apply.

## Migrate the table schema to the Tiger Cloud service

Use `pg_dump` to:

1. **Download the schema from the source database**

   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 service**

   Terminal window

   ```
   psql $TARGET -f schema.sql
   ```

## Convert partitions and tables with time-series data into hypertables

For efficient querying and analysis, you can convert tables which contain time-series or events data, and tables that are already partitioned using PostgreSQL declarative partition into [hypertables](/learn/hypertables/understand-hypertables/index.md).

1. **Convert tables to hypertables**

   Run the following on each table in the target Tiger Cloud service to convert it to a hypertable:

   Terminal window

   ```
   psql -X -d $TARGET -c "SELECT public.create_hypertable('<table>', by_range('<partition column>', '<chunk interval>'::interval));"
   ```

   For example, to convert the *metrics* table into a hypertable with *time* as a partition column and *1 day* as a partition interval:

   Terminal window

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

2. **Convert PostgreSQL partitions to hypertables**

   Rename the partition and create a new regular table with the same name as the partitioned table, then convert to a hypertable:

   Terminal window

   ```
   psql $TARGET -f - <<'EOF'
      BEGIN;
      ALTER TABLE public.events RENAME TO events_part;
      CREATE TABLE public.events(LIKE public.events_part INCLUDING ALL);
      SELECT create_hypertable('public.events', by_range('time', '1 day'::interval));
      COMMIT;
   EOF
   ```

## Specify the tables to synchronize

Create a [PostgreSQL `PUBLICATION`](https://www.postgresql.org/docs/current/sql-createpublication.html) on the source. This defines what Livesync syncs. You can create more than one publication (for example, to group tables by domain), but avoid creating one per table.

Important

**Do not use `FOR ALL TABLES`.** It includes the `_timescaledb_catalog.*` tables, and replicating those onto the target corrupts the TimescaleDB catalog there. Always list tables explicitly (`FOR TABLE ...`) or scope to user schemas (`FOR TABLES IN SCHEMA <user_schema>, ...`).

```
psql "$SOURCE" <<'EOF'
CREATE PUBLICATION <publication_name> FOR TABLE <table_name>, <table_name>;
EOF
```

To sync every table across one or more user schemas (PostgreSQL 15 or later only), list the schemas explicitly and exclude the TimescaleDB ones:

```
psql "$SOURCE" <<'EOF'
CREATE PUBLICATION <publication_name>
  FOR TABLES IN SCHEMA <user_schema_1>, <user_schema_2>;
EOF
```

To add or remove tables after creating the publication:

```
ALTER PUBLICATION <publication_name> ADD TABLE <table_name>;
ALTER PUBLICATION <publication_name> DROP TABLE <table_name>;
```

Publication changes are picked up only after a Livesync stop/start, or by running the `refresh-publication` command:

Terminal window

```
docker run -it --rm --name livesync-refresh \
  timescale/live-sync:latest refresh-publication \
  --subscription <subscription_name> \
  --source "$SOURCE" \
  --target "$TARGET"
```

If you have PostgreSQL declarative partitioned tables in the publication, leave `publish_via_partition_root` at its PostgreSQL default of `false`. Since Livesync v0.23.0, this makes the initial copy of the partitioned table both parallel (each leaf partition is copied by its own worker, like hypertable chunks) and resumable (already-copied leaves aren't recopied if the sync is interrupted):

```
ALTER PUBLICATION <publication_name> SET (publish_via_partition_root = false);
```

Set `publish_via_partition_root = true` only if you need the older serial, whole-table copy, for example for compatibility with a Livesync version older than v0.23.0:

```
ALTER PUBLICATION <publication_name> SET (publish_via_partition_root = true);
```

To selectively replicate only specific DML events (by default all `INSERT`, `UPDATE`, `DELETE`, and `TRUNCATE` are replicated):

```
CREATE PUBLICATION <publication_name> FOR TABLE <table_name>
  WITH (publish = 'insert, update');
```

Validate:

Terminal window

```
psql "$SOURCE" -c "SELECT * FROM pg_publication_tables WHERE pubname = '<publication_name>';"
```

## Synchronize data to your Tiger Cloud service

You use the source PostgreSQL connector docker image to synchronize changes in real time from a PostgreSQL database instance to a Tiger Cloud service:

1. **Start the source PostgreSQL connector**

   Best practice is to run as a Docker daemon:

   Terminal window

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

   `latest` is the latest available version. See [Docker Hub](https://hub.docker.com/r/timescale/live-sync).

   | Flag                   | Description                                                                                                                                                                                                                  |
   | ---------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
   | `--publication`        | Publication name as created in the publication step. Repeat the flag for multiple publications.                                                                                                                              |
   | `--subscription`       | Name that identifies the subscription on the target.                                                                                                                                                                         |
   | `--source`             | Connection string to the source PostgreSQL database.                                                                                                                                                                         |
   | `--target`             | Connection string to the target Tiger Cloud service.                                                                                                                                                                         |
   | `--table-map`          | (Optional) JSON mapping source tables to target tables. Repeat for multiple mappings. Example: `--table-map '{"source": {"schema": "public", "table": "metrics"}, "target": {"schema": "public", "table": "metrics_data"}}'` |
   | `--table-sync-workers` | (Optional) Number of parallel workers for initial table sync. Default: 4.                                                                                                                                                    |
   | `--copy-data`          | (Optional) Set to `false` to skip initial data copy and only replicate changes made after replication slot creation. Useful for dry-run testing. Default: `true`.                                                            |

2. **Monitor the sync**

   Terminal window

   ```
   docker logs -f livesync
   ```

   **Table sync status**

   Terminal window

   ```
   psql "$TARGET" -c "
     SELECT state, last_error, count(*)
     FROM _ts_live_sync.subscription_rel
     GROUP BY 1, 2 ORDER BY 1, 2;
   "
   ```

   Tables with errors appear as separate rows grouped by `last_error`.

   | State | Meaning                                                                  |
   | ----- | ------------------------------------------------------------------------ |
   | `i`   | Initial state; table data sync not started.                              |
   | `d`   | Initial table data sync is in progress.                                  |
   | `f`   | Initial table data sync completed; catching up with incremental changes. |
   | `s`   | Synchronized; waiting for the main apply worker to take over.            |
   | `r`   | Table is ready; applying changes in real time.                           |

   **COPY progress during initial sync**

   Monitor that the initial data copy is progressing as expected. The number of rows returned should usually match the `--table-sync-workers` value.

   Terminal window

   ```
   psql "$SOURCE" -c "SELECT * FROM pg_stat_progress_copy;"
   ```

   **Replication lag**

   On the source using `pg_replication_slots`:

   Terminal window

   ```
   psql "$SOURCE" -c "
     SELECT slot_name,
       pg_size_pretty(pg_current_wal_flush_lsn() - confirmed_flush_lsn) AS lag
     FROM pg_replication_slots
     WHERE slot_name LIKE 'live_sync_%' AND slot_type = 'logical';
   "
   ```

   On the target using `_ts_live_sync.subscription` (also shows `last_error` if any):

   Terminal window

   ```
   psql "$TARGET" -c "
     SELECT
       pg_size_pretty(source_flush_lsn - last_replicated_lsn) AS lag_bytes,
       (metrics_updated_at - last_replicated_txn_time) AS lag_duration,
       *
     FROM _ts_live_sync.subscription;
   "
   ```

3. **Update table statistics**

   If you have a large table, you can run `ANALYZE` on the target Tiger Cloud service to update the table statistics after the initial sync is complete. This helps the query planner make better decisions for query execution plans.

   Terminal window

   ```
   vacuumdb --analyze --verbose --jobs=8 --dbname="$TARGET"
   ```

4. **Stop the {C.PG\_CONNECTOR}**

   Terminal window

   ```
   docker stop livesync
   ```

5. **Reset sequence nextval on the target {C.SERVICE\_LONG}**

   Important

   Livesync does not replicate sequence values. Skipping this step causes serial or identity columns to reuse already-existing values after cutover, resulting in primary key or unique constraint violations.

   - [copy-sequences command](#tab-panel-744)
   - [Manual SQL](#tab-panel-745)

   Terminal window

   ```
   docker run -it --rm --name livesync-copy-sequences \
     timescale/live-sync:latest copy-sequences \
     --subscription <subscription_name> \
     --source "$SOURCE" \
     --target "$TARGET"
   ```

   ```
   psql "$TARGET" <<'EOF'
   DO $$
   DECLARE
     rec RECORD;
   BEGIN
     FOR rec IN (
       SELECT
         sr.target_schema AS table_schema,
         sr.target_table AS table_name,
         col.column_name,
         pg_get_serial_sequence(
           format('%I.%I', sr.target_schema, sr.target_table),
           col.column_name
         ) AS seqname
       FROM _ts_live_sync.subscription_rel AS sr
       JOIN information_schema.columns AS col
         ON col.table_schema = sr.target_schema
        AND col.table_name = sr.target_table
       WHERE col.column_default LIKE 'nextval(%'
     ) LOOP
       EXECUTE format(
         'SELECT setval(%L, COALESCE((SELECT MAX(%I) FROM %I.%I), 0) + 1, false);',
         rec.seqname, rec.column_name, rec.table_schema, rec.table_name
       );
     END LOOP;
   END;
   $$ LANGUAGE plpgsql;
   EOF
   ```

6. **Clean up**

   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), use the `drop` sub-command to remove the replication slot from the source and all subscription state from the target:

   Terminal window

   ```
   docker run -it --rm --name livesync-cleanup \
     timescale/live-sync:latest drop \
     --subscription <subscription_name> \
     --source "$SOURCE" \
     --target "$TARGET"
   ```

   `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** Tiger Cloud service.

   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.