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