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