postgres 10 logical replication

Notes from official documentation

publisher side, wal_level must be set to logical, and max_replication_slots must be set to at least the number of subscriptions expected to connect, plus some reserve for table synchronization. And max_wal_senders should be set to at least the same as max_replication_slots plus the number of physical replicas that are connected at the same time.

The subscriber also requires the max_replication_slots to be set. In this case it should be set to at least the number of subscriptions that will be added to the subscriber. max_logical_replication_workers must be set to at least the number of subscriptions, again plus some reserve for the table synchronization. Additionally the max_worker_processes may need to be adjusted to accommodate for replication workers, at least (max_logical_replication_workers + 1). Note that some extensions and parallel queries also take worker slots from max_worker_processes.

The Easy Way to Setup PostgreSQL 10 Logical Replication

One of the most exciting enhancements that comes with the new PostgreSQL 10 release, is Logical Replication. The functionality, although not yet as extensive as pgLogical which it is based on, provides a powerful replication option when you want control over table backups at a finite level — allowing all kinds of ETL goodness like:

• replicating only certain tables, columns, or rows
• consolidating multiple databases into a single one
• sharing a subset of the database between multiple databases
• replicating between different major versions of PostgreSQL

Installing multiple PostgreSQL 10 on your localhost in 5 easy steps

Step 1:

Edit pg_hba.conf [master-slave]

# TYPE DATABASE USER ADDRESS METHOD

# “local” is for Unix domain socket connections only

local all all trust

# IPv4 local connections:

host all all 0.0.0.0/0 trust

# IPv6 local connections:

host all all ::1/128 trust

# Allow replication connections from localhost, by a user with the

# replication privilege.

local replication all trust

host replication all 127.0.0.1/32 trust

host replication all ::1/128 trust

Step : 2

Edit postgresql.conf

For logical replication to work, you will first need to change the configuration parameter wal_level to ‘logical’.

Open the file with your favorite editor and change wal_level from ‘hot_standby’ to ‘logical’

# — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

# WRITE AHEAD LOG

# — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

# — Settings -

wal_level = logical # minimal, replica, or logical

# (change requires restart)

fsync = on # flush data to disk for crash safety

# (turning this off can cause

# unrecoverable data corruption)

synchronous_commit = off # synchronization level;

# off, local, remote_write, remote_apply, or on

#wal_sync_method = fsync # the default is the first option

# supported by the operating system:

# open_datasync

# fdatasync (default on Linux)

# fsync

# fsync_writethrough

-

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — -

# CONNECTIONS AND AUTHENTICATION

# — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

# — Connection Settings -

listen_addresses = ‘*’ # what IP address(es) to listen on;

# comma-separated list of addresses;

# defaults to ‘localhost’; use ‘*’ for all

# (change requires restart)

port = 5432 # (change requires restart)

max_connections = 1000 # (change requires restart)

Step : 3

passwordless ssh connectivity from Slave -> Master

Step : 4

Create the publisher

Connect as a superuser to the PostgreSQL instance that will serve as the publisher:

ssh postgres@db1 or psql -h localhost -p 5432 -U postgres

Create master database:

postgres=# CREATE DATABASE master;
postgres=# \c master;
You are now connected to database "master" as user "postgres".

Create table pink_floyd_album:

CREATE TABLE pink_floyd_album (
name text PRIMARY KEY,
band text NOT NULL,
record_creation_date timestamptz NOT NULL DEFAULT now()
);

Add record to pink_floyd_album table:

- INSERT INTO rocker_girls(name, band) VALUES(‘darkside of the mood’, ‘pink_floyd’);

Check to see that pink_floyd_album table with data was created:

rock_legends_db=# select * from pink_floyd_album;

name | band | record_creation_date
------------ | ------- | -----------------------------
darkside of the moon| pink_floyd | 2017-11-05 12:52:12.177873-04
(1 row)

Setup the database as the publisher for the pink_floyd_album table:

CREATE PUBLICATION pub_rock FOR TABLE pink_floyd_album;

Exit psql:

• rock_legends_db=# \q

Step : 5

Create the subscriber

Connect as a superuser to the PostgreSQL instance that will serve as the subscriber:

#subscriber instance in running on port 5432- the default
psql -h localhost -p 5432 -U postgres or ssh postgres@db2

Repeat steps 2–3 from above. DO NOT add any records to the table!

Setup the database as the subscriber:

\C master [make sure to be connected to the db master]

CREATE SUBSCRIPTION sub_rock

CONNECTION ‘host=master port=5432 dbname=master’

PUBLICATION pub_rock;

4.Verify that the master “dark_side_of_the_moon” row was replicated:

- master=# select * from pink_floyd_album;

- name | band | record_creation_date

- — — — — — — | — — — — | — — — — — — — — — — — — — — -

- darkside of the moon| pink_floyd | 2017–11–05 12:52:12.177873–04

- (1 row)

Test the replication by adding another record

1. Connect to Master again and insert data

1.INSERT INTO pink_floyd_album(name, band) VALUES(‘The wall’, ‘pink_floyd’);

2 .Disconnect from publisher and connect to subscriber:

rock_legends_db=# \q

psql -h localhost -p 5432 -U postgres -d master

3.Run select all on pink_floyd_album:

ock_legends_db=# select * from master;

name | band | record_creation_date

— — — — — — | — — — — — — — — — — — — — — — — — — | — — — — — — — — — — — — — — -

- darkside of the moon| pink_floyd | 2017–11–05 12:52:12.177873–04

- the wall| pink_floyd | 2017–11–05 12:53:29.177873–04

(2 rows)

it has been replicated

How to delete the subscriber and publisher

slave=# drop subscription sub_rock

Monitoring :

subscription site

postgres=# SELECT * FROM pg_stat_subscription;
subid | subname | pid | relid | received_lsn | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_t
ime
— — — -+ — — — — — + — — — -+ — — — -+ — — — — — — — + — — — — — — — — — — — — — — — -+ — — — — — — — — — — — — — — — -+ — — — — — — — — + — — — — — — — — — —
— — — — — -
16535 | sub_rock | 29654 | | 0/1BD7B48 | 2017–11–09 09:19:23.078808+06 | 2017–11–09 09:19:22.810126+06 | 0/1BD7B48 | 2017–11–09 09:19:23
.078808+06
16626 | sub_afis | 27097 | | 0/1BD7B48 | 2017–11–09 09:19:23.078195+06 | 2017–11–09 09:19:22.809648+06 | 0/1BD7B48 | 2017–11–09 09:19:23
.078195+06
(2 rows)

publication site

master =# SELECT application_name,backend_start,sent_lsn,write_lsn,state,sync_state FROM pg_stat_replication ;
application_name | backend_start | sent_lsn | write_lsn | state | sync_state
— — — — — — — — — + — — — — — — — — — — — — — — — -+ — — — — — -+ — — — — — -+ — — — — — -+ — — — — — —
sub_rock | 2017–11–05 05:55:14.036876+06 | 0/1BD7B48 | 0/1BD7B48 | streaming | async
sub_afis | 2017–11–09 09:18:21.279602+06 | 0/1BD7B48 | 0/1BD7B48 | streaming | async
(2 rows)