pglogical 2.2.0配置说明

master: postgresql 9.5(192.168.31.205)
standby: postgresql 10.3(192.168.31.202)

1.postgresql配置

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安装

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配置

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=#
//---------

//---------
//---------

自定义replication_set

1.环境描述

master(provider node)
pg9.5: 192.168.31.205

standby(subscriber node)
pg10.3: 192.168.31.202

2.配置文件

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.配置逻辑复制

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'
);
(注意：standby节点pglogical.create_subscription函数,使用master(provider node)节点pg_hba.conf的host部分认证)
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=#