Got it, let's break down how to set up reliable sync between your client PostgreSQL database and your local one—since you already have the initial data pulled, we just need to handle the incremental new records. Here are the most practical, battle-tested approaches:

This is the official, low-latency solution for incremental sync, perfect if you need near-real-time updates between the two databases. It works natively with PostgreSQL 10+ and supports syncing specific tables or entire schemas.

Step-by-Step Setup: #

• First, configure the client database to enable logical replication. Edit postgresql.conf:

  wal_level = logical  # Required for logical replication
  max_replication_slots = 5  # Adjust based on your needs, minimum 1
  max_wal_senders = 5  # Minimum 1, matches replication slots count
  

  Restart the client database service after saving changes.

• Create a dedicated replication role on the client DB with necessary permissions:

  CREATE ROLE sync_replicator WITH REPLICATION LOGIN PASSWORD 'your_strong_password';
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO sync_replicator;
  -- Auto-grant access to future tables in the schema
  ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO sync_replicator;
  

• Create a Publication on the client DB to define what data gets synced:

  -- Sync all tables in the public schema
  CREATE PUBLICATION client_sync_pub FOR ALL TABLES IN SCHEMA public;
  -- Or sync specific tables only:
  -- CREATE PUBLICATION client_sync_pub FOR TABLE customers, orders;
  

• Create a Subscription on your local DB to pull data from the client's publication:

  CREATE SUBSCRIPTION local_sync_sub
  CONNECTION 'host=client_db_host port=5432 dbname=client_db_name user=sync_replicator password=your_strong_password'
  PUBLICATION client_sync_pub;
  

  That's it! Any new (and updated/deleted, unless restricted) records on the client DB will now auto-sync to your local DB.

If you need to add custom logic during sync—like filtering certain records, transforming fields, or logging failures—this approach gives you full control.

Step-by-Step Setup: #

• First, install the dblink extension on the client DB (to connect to your local DB):

  CREATE EXTENSION IF NOT EXISTS dblink;
  

• Create a write role on your local DB for sync operations:

  CREATE ROLE sync_writer WITH LOGIN PASSWORD 'secure_local_pass';
  GRANT INSERT ON ALL TABLES IN SCHEMA public TO sync_writer;
  

• Create a trigger function on the client DB that pushes new records to the local DB:

  CREATE OR REPLACE FUNCTION sync_new_records()
  RETURNS TRIGGER AS $$
  BEGIN
    -- Connect to local DB
    PERFORM dblink_connect('local_db_link', 'host=localhost port=5432 dbname=local_db_name user=sync_writer password=secure_local_pass');
    -- Insert the new record into the matching local table
    PERFORM dblink_exec('local_db_link', format(
      'INSERT INTO %I.%I VALUES (%L)',
      TG_TABLE_SCHEMA, TG_TABLE_NAME, quote_literal(NEW.*)
    ));
    PERFORM dblink_disconnect('local_db_link');
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  

• Attach the trigger to tables you want to sync (run this for each target table):

  CREATE TRIGGER trigger_sync_new_data
  AFTER INSERT ON your_target_table
  FOR EACH ROW EXECUTE FUNCTION sync_new_records();
  

  Note: Add error handling (like logging failed syncs to a dedicated table) if you need to handle cases where the local DB is unavailable.

If you don't need instant sync—e.g., daily/ hourly updates—a scheduled script is a simple, low-overhead option. You can use Python, shell scripts, or even PostgreSQL's pg_cron extension.

Example Python Script (using psycopg2): #

import psycopg2
from datetime import datetime

def sync_incremental_data():
    # Connect to client DB
    client_conn = psycopg2.connect("host=client_host dbname=client_db user=client_user password=client_pass")
    client_cursor = client_conn.cursor()

    # Connect to local DB
    local_conn = psycopg2.connect("host=localhost dbname=local_db user=local_user password=local_pass")
    local_cursor = local_conn.cursor()

    # Fetch last sync timestamp (store this in a local config table)
    local_cursor.execute("SELECT last_sync FROM sync_config LIMIT 1")
    last_sync = local_cursor.fetchone()[0] or datetime.min

    # Pull new records from client DB (adjust the WHERE clause to match your incremental key)
    client_cursor.execute("SELECT * FROM orders WHERE created_at > %s", (last_sync,))
    new_records = client_cursor.fetchall()

    # Insert records into local DB
    for record in new_records:
        local_cursor.execute("INSERT INTO orders VALUES (%s, %s, %s, %s)", record)  # Match your table's columns

    # Update last sync timestamp
    local_cursor.execute("UPDATE sync_config SET last_sync = %s", (datetime.now(),))

    # Commit changes and clean up
    local_conn.commit()
    client_cursor.close()
    client_conn.close()
    local_cursor.close()
    local_conn.close()

if __name__ == "__main__":
    sync_incremental_data()

Schedule this script with cron (Linux) or Task Scheduler (Windows) to run at your desired interval.

Quick Notes: #

• For logical replication: Ensure table schemas stay in sync between client and local DB—schema changes on the client will break sync until you update the local schema.
• For triggers: Avoid this if your client DB has high write throughput, as triggers add overhead to each insert.
• For scheduled scripts: Use unique constraints on your local tables to prevent duplicate records if sync runs multiple times.

内容的提问来源于stack exchange，提问作者Piyu