Pglogical 2.2.0 configuration instructions
Master: postgresql 9.5 (192.168.31.205)
Standby: postgresql 10.3 (192.168.31.202)
1.postgresql configuration
1) .vi postgresql.conf
Wal_level = 'logical'
Max_worker_processes = 10 # one per database needed on provider node
/ / # one per node needed on subscriber node
Max_replication_slots = 10 # one per node needed on provider node
Max_wal_senders = 10 # one per node needed on provider node
Shared_preload_libraries = 'pglogical'
Track_commit_timestamp = on # needed for last/first update wins conflict resolution
/ / # property available in PostgreSQL 9.5 +
2). Pg_hba.conf
Pg_hba.conf has to allow replication connections from localhost.
-# replication privilege.
# local replication postgres trust
# host replication postgres 127.0.0.1/32 trust
# host replication postgres:: 1/128 trust
Host replication postgres 192.168.31.0/24 trust
2.pglogical installation
Source
Https://github.com/2ndQuadrant/pglogical/releases
1) .install
Export PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/opt/pgsql/9.5/bin
Make USE_PGXS=1 clean all
Export PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/opt/pgsql/9.5/bin
Make USE_PGXS=1 install
2). Create extention
(master/standby)
CREATE EXTENSION pglogical
3.pglogical configuration
Master/standby
Create table T1 (id serial primary key, name varchar (20))
1) .master
Postgres=# SELECT pglogical.create_node (
Postgres (# node_name: = 'provider1'
Postgres (# dsn: = 'host=192.168.31.205 port=5432 dbname=postgres'
Postgres (#)
Create_node
/ /-
2976894835
(1 row)
Postgres=# SELECT pglogical.replication_set_add_all_tables ('default', ARRAY [' public'])
Replication_set_add_all_tables
/ /-
T
(1 row)
Postgres=#
Postgres=# select * from pglogical.node_interface
If_id | if_name | if_nodeid | if_dsn
-+-
2402836775 | provider1 | 2976894835 | host=192.168.31.205 port=5432 dbname=postgres
(1 row)
Postgres=# create table T1 (id serial primary key, name varchar (20))
2). Standby
Postgres=# SELECT pglogical.create_node (
Node_name: = 'subscriber1'
Dsn: = 'host=192.168.31.202 port=5432 dbname=postgres'
);
Create_node
/ /-
330520249
(1 row)
Postgres=# select * from pglogical.node
Node_id | node_name
-+-
330520249 | subscriber1
(1 row)
Postgres=# SELECT pglogical.create_subscription (
Subscription_name: = 'subscription1'
Provider_dsn: = 'host=192.168.31.205 port=5432 dbname=postgres password=123456'
);
Create_subscription
/ /-
1763399739
(1 row)
Postgres=#
/ /-
/ /-
/ /-
Custom replication_set
1. Environment description
Master (provider node)
Pg9.5: 192.168.31.205
Standby (subscriber node)
Pg10.3: 192.168.31.202
two。 Configuration file
Postgresql.conf
Wal_level = 'logical'
Max_worker_processes = 10 # one per database needed on provider node
/ / # one per node needed on subscriber node
Max_replication_slots = 10 # one per node needed on provider node
Max_wal_senders = 10 # one per node needed on provider node
Shared_preload_libraries = 'pglogical'
Track_commit_timestamp = on # needed for last/first update wins conflict resolution
/ / # property available in PostgreSQL 9.5 +
3. Configure logical replication
1) .master (provider node)
Pg_hba.conf
-- # TYPE DATABASE USER ADDRESS METHOD
-# "local" is for Unix domain socket connections only
Local all all trust
-- # IPv4 local connections:
Host all all 127.0.0.1/32 trust
Host all all 192.168.31.0/24 trust
Host all all 0/0 md5
-- # IPv6 local connections:
Host all all:: 1/128 trust
-- # Allow replication connections from localhost, by a user with the
-# replication privilege.
# local replication postgres trust
# host replication postgres 127.0.0.1/32 trust
# host replication postgres:: 1/128 trust
Host replication postgres 192.168.31.0/24 trust
[postgres@pg95 pgdata95] $
Postgres=#
Postgres=# create database phriday
CREATE DATABASE
Postgres=#\ c phriday
You are now connected to database "phriday" as user "postgres".
Phriday=#
Phriday=#
Phriday=# CREATE TABLE sensor_log (
Id SERIAL PRIMARY KEY NOT NULL
Location VARCHAR NOT NULL
Reading BIGINT NOT NULL
Reading_date TIMESTAMP NOT NULL
);
CREATE TABLE
Phriday=#
Phriday=#
Phriday=# INSERT INTO sensor_log (location, reading, reading_date)
SELECT s.id 1000, round (random () 100)
CURRENT_DATE + INTERVAL '1d'-(s.id 10):: TEXT | |' s'):: INTERVAL
FROM generate_series (1, 1000000) s (id)
INSERT 0 1000000
Phriday=#
Phriday=#
Phriday=#
Phriday=# CREATE EXTENSION pglogical
CREATE EXTENSION
Phriday=#
Phriday=#
Phriday=# SELECT pglogical.create_node (
Node_name: = 'prod_sensors'
Dsn: = 'host=localhost port=5432 dbname=phriday'
);
Create_node
/ /-
2582514159
(1 row)
Phriday=# SELECT pglogical.create_replication_set (
Set_name: = 'logging'
Replicate_insert: = TRUE, replicate_update: = FALSE
Replicate_delete: = FALSE, replicate_truncate: = FALSE
);
Create_replication_set
/ /-
2763884914
(1 row)
Phriday=#
Phriday=# SELECT pglogical.replication_set_add_table (
Set_name: = 'logging', relation: =' sensor_log'
Synchronize_data: = TRUE
);
Replication_set_add_table
/ /--
T
(1 row)
Phriday=#
Phriday=#
2) .standby (subscriber node)
Pg_hba.conf
/ / # TYPE DATABASE USER ADDRESS METHOD
/ / # "local" is for Unix domain socket connections only
Local all all trust
/ / # IPv4 local connections:
Host all all 127.0.0.1/32 trust
Host all all 192.168.31.0/24 trust
Host all all 0/0 md5
/ / # 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
[postgres@pg10 pgdata10] $
Postgres=# create database phriday
CREATE DATABASE
Postgres=#\ c phriday
You are now connected to database "phriday" as user "postgres".
Phriday=#
Phriday=# CREATE TABLE sensor_log (
Id SERIAL PRIMARY KEY NOT NULL
Location VARCHAR NOT NULL
Reading BIGINT NOT NULL
Reading_date TIMESTAMP NOT NULL
);
CREATE TABLE
Phriday=#
Phriday=# create extension pglogical
CREATE EXTENSION
Phriday=#
Phriday=#\ dx
List of installed extensions
Name | Version | Schema | Description
-+-
Pglogical | 2.2.0 | pglogical | PostgreSQL Logical Replication
Plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
Phriday=#
Phriday=# SELECT pglogical.create_node (
Node_name: = 'sensor_warehouse'
Dsn: = 'host=localhost port=5432 dbname=phriday'
);
Create_node
/ /-
2202660864
(1 row)
Phriday=#
Phriday=#
Phriday=# SELECT pglogical.create_subscription (
Subscription_name: = 'wh_sensor_data'
Replication_sets: = array ['logging']
Provider_dsn: = 'host=192.168.31.205 port=5432 dbname=phriday'
);
(note: standby node pglogical.create_subscription function, using host partial authentication of master (provider node) node pg_hba.conf)
Create_subscription
/ /-
942472455
(1 row)
Phriday=#
Phriday=# select pg_sleep (10)
Pg_sleep
/ /-
(1 row)
Phriday=# select * from sensor_log limit 10
Id | location | reading | reading_date
-- +-
1 | 1 | 34 | 2018-03-29 23:59:50
2 | 2 | 78 | 2018-03-29 23:59:40
3 | 3 | 19 | 2018-03-29 23:59:30
4 | 4 | 84 | 2018-03-29 23:59:20
5 | 5 | 57 | 2018-03-29 23:59:10
6 | 6 | 9 | 2018-03-29 23:59:00
7 | 7 | 88 | 2018-03-29 23:58:50
8 | 8 | 75 | 2018-03-29 23:58:40
9 | 9 | 48 | 2018-03-29 23:58:30
10 | 10 | 24 | 2018-03-29 23:58:20
(10 rows)
Phriday=# select count (*) from sensor_log
Count
/ /-
1000000
(1 row)
Phriday=#
