Skip to content

Replication Types

Comparison: https://www.postgresql.org/docs/12/different-replication-solutions.html

Logical Replication

  • Open the firewall so that the PostgreSQL subscribers can reach the publisher
  • Align the subscriber database data with the publisher database data
  • Change the PostgreSQL listen IP so the downstream server can connect in postgresql.conf
    listen_addresses = '::'
  • Change wal_level to logical in postgresql.conf
    wal_level = logical
  • On the publisher, create a user for replication
    CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'secret';
  • On the publisher, grant privileges to the new user
    GRANT ALL PRIVILEGES ON DATABASE pdns TO replicator;
  • On the publisher, also grant rights on the schema
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO replicator;
  • On the publisher, modify pg_hba.conf to allow subscriber access
    host pdns replicator 2001:1234:abcd:5000::13/128 md5
  • Restart PostgreSQL