TutorialDBA - Support | Training | Consultant

The pglogical extension provides logical streaming replication for PostgreSQL We use the following terms to describe data streams between nodes, deliberately reused from the earlier Slony technology: • Nodes - PostgreSQL database instances • Providers and Subscribers - roles taken by Nodes • Replication Set - a collection of tables  Architectural details: • pglogical works on a per-database level, not whole server level like physical streaming replication • One Provider may feed multiple Subscribers without incurring additional disk write overhead • One Subscriber can merge changes from several origins and detect conflict between changes with automatic and configurable conflict resolution (some, but not all aspects required for multi-master). • Cascading replication is implemented in the form of changeset forwarding. Requirements :-   To use pglogical the provider and subscriber must be running PostgreSQL 9.4 or newer. The pglogical exten...

In this tutorial, we are going to demonstrate you how to setup a  logical replication  between two  PostgreSQL 10  nodes listening on port  5432 . On both servers we already installed PostgreSQL 10. – On the primary node, we need to set the parameter  wal_level  to  logical  in the  postgresql.conf  file [root@ylclpsql01 ~]# vi /var/lib/pgsql/10/data/postgresql.conf # - Settings - wal_level = logical # minimal, replica, or logical # (change requires restart) – Restart the PostgreSQL service using the following command: [root@ylclpsql01 ~]# systemctl restart postgresql-10 – To verify if the logical replication is properly configured use the following command: postgres=# show wal_level; wal_level ----------- logical (1 row) – On the primary node, let’s create a database named  testdb  and a table  article : postgres=# CREATE DATABASE testdb; ...

The pglogical extension provides logical streaming replication for PostgreSQL, using a publish/subscribe module. It is based on technology developed as part of the  BDR Project . We use the following terms to describe data streams between nodes, deliberately reused from the earlier Slony technology: Nodes - PostgreSQL database instances Providers and Subscribers - roles taken by Nodes Replication Set - a collection of tables pglogical is new technology utilising the latest in-core features, so we have these version restrictions: Provider & subscriber nodes must run PostgreSQL 9.4+ PostgreSQL 9.5+ is required for replication origin filtering and conflict detection Additionaly, subscriber can be Postgres-XL 9.5+ Use cases supported are: Upgrades between major versions (given the above restrictions) Full database replication Selective replication of sets of tables using replication sets Selective replication of table rows at either publisher or subscriber si...