Skip to content

Instantly share code, notes, and snippets.

@imantung

imantung/pg_logical_replication.md

Last active April 17, 2023 01:03
Show Gist options
  • Save imantung/2fc1fe564ffd46c3df246278b16f6493 to your computer and use it in GitHub Desktop.
Save imantung/2fc1fe564ffd46c3df246278b16f6493 to your computer and use it in GitHub Desktop.
Download ZIP
Posgres Logical Replication Notes
Raw
pg_logical_replication.md

Postges Logical Replication

Overview

  • Table specific replicatlion
  • Support on postgres 10 above
  • Pub-sub model
  • Also known as Change Data Capture (CDC)

Logical VS Physical Replication

  • Logical Replication

    • by replication identity (Primary Key)
    • Publisher - subscriber relationship

  • Physcal Replicaton

    • Physical by WAL/bytes
    • Master - Slave relationship

Pro

  • Cross Version Support
  • Flexible (Row filtering, column filtering, data transformation)
  • Lighweight (compare other replication type)

Cons

  • Only DML operations are supported. No DDL. The schema has to be defined beforehand
  • Sequences are not replicated
  • Large Objects are not replicated
  • Only plain base tables are supported (materialized views, partition root tables, foreign tables are not supported)

Caveat

  • Data Conflict can be problem
    • Conflict halt the replication
    • Can caused publisher out of disk
  • Slot Failover learn more
  • Not support logical replication from a REPLICA/SLAVE. learn more --> Use pg_replication_slot_advance

Typical use case

  • Sending incremental changes in a single database or a subset of a database to subscribers as they occur.
  • Firing triggers for individual changes as they arrive on the subscriber.
  • Consolidating multiple databases into a single one (for example for analytical purposes).
  • Replicating between different major versions of PostgreSQL.
  • Replicating between PostgreSQL instances on different platforms (for example Linux to Windows)
  • Giving access to replicated data to different groups of users.
  • Sharing a subset of the database between multiple databases.

Related Extension

Related Configuration

  • max_wal_senders
  • max_replication_slots

Monitoring

  • pg_catalog.pg_publication
  • pg_catalog.pg_publication_tables
  • pg_current_wal_lsn()
  • pg_stat_replication
  • pg_stat_subscription
  • pg_replication_slots

Learn more:

Example

CREATE PUBLICATION my_publication FOR TABLE replicate_me WITH (publish = 'insert');
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=172.168.18.50 port=5432 user=repl dbname=testdb'
PUBLICATION my_publication;

Reference

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment