Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.