Skip to content
Search ⌘K
Start a free trial

Sync data from Postgres

Sync updates to your primary Postgres database with Tiger Cloud in real time

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

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.

  • Enable features such as hypertables, columnstore, and continuous aggregates 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).

  • 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 to refresh affected ranges.

Prerequisites for this integration guide

To follow these steps, you'll need:

  • The PostgreSQL client tools 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.

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.

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:

Terminal window
python3 -c "import sys, urllib.parse; print(urllib.parse.quote(sys.argv[1]))" '<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 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.

    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
  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, select the service to sync live data to.

  2. Prepare the source database
    PostgreSQL connector wizard in Tiger Console
    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

    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

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

    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

    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.