pglogical

pig repo add pgdg -u          # Add PGDG repo and update cache

shared_preload_libraries = 'pglogical';

CREATE EXTENSION pglogical;

wal_level = 'logical'
max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10
shared_preload_libraries = 'pglogical'

CREATE EXTENSION pglogical;

-- Create a node on the provider
SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=providerhost port=5432 dbname=mydb'
);

-- Add all tables in public schema to default replication set
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

-- Add all sequences in public schema
SELECT pglogical.replication_set_add_all_sequences('default', ARRAY['public']);

-- Create a node on the subscriber
SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=subscriberhost port=5432 dbname=mydb'
);

-- Create a subscription to the provider
SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=providerhost port=5432 dbname=mydb'
);

-- Create a custom replication set
SELECT pglogical.create_replication_set('my_set');

-- Add a specific table
SELECT pglogical.replication_set_add_table('my_set', 'my_table', true);

-- Remove a table
SELECT pglogical.replication_set_remove_table('my_set', 'my_table');

-- Row filtering: only replicate rows matching a condition
SELECT pglogical.replication_set_add_table(
    set_name := 'default',
    relation := 'my_table',
    row_filter := 'id > 1000'
);

-- Column filtering: only replicate specific columns
SELECT pglogical.replication_set_add_table(
    set_name := 'default',
    relation := 'my_table',
    columns := '{id, name, updated_at}'
);

-- Check subscription status
SELECT * FROM pglogical.show_subscription_status();

-- Drop subscription
SELECT pglogical.drop_subscription('subscription1');