Postgresql主從異步流復(fù)制方案的深入探究

$ groupadd postgres

$ useradd postgres -g postgres

$ passwd postgres

$ mkdir -p /data/pg10/pg_root

$ mkdir -p /data/pg10/tbs

$ chown -R postgres:postgres /data/pg10

wget https://ftp.postgresql.org/pub/source/v10.0/postgresql-10.0.tar.gz

tar -zxvf postgresql-10.0.tar.gz

yum groupinstall "Development tools”

yum install -y bison flex readline readline-devel zlib zlib-devel

vi /etc/profile.d/pgsql.sh

export PGPORT=1921
export PGUSER=postgres
export PGDATA=/data/pg10/pg_root
export LANG=en_US.utf8
export PGHOME=/apps/svr/pgsql
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH

alias rm='rm -i'
alias ll='ls -lh'

source /etc/profile.d/pgsql.sh

./configure —prefix=/apps/svr/pgsql_10.0/ --with-pgport=1921

gmake

gmake install

$ postgres --version

postgres (PostgreSQL) 10.0

su postgresql

echo "123456" >> /data/pg10/pgpass

initdb -D /data/pg10/pg_root -E UTF8 --locale=C -U postgres --pwfile=/data/pg10/pgpass

PG_VERSION pg_dynshmem pg_multixact pg_snapshots pg_tblspc postgresql.auto.conf
base  pg_hba.conf pg_notify pg_stat pg_twophase postgresql.conf
global pg_ident.conf pg_replslot pg_stat_tmp pg_wal
pg_commit_ts pg_logical pg_serial pg_subtrans pg_xact

listen_addresses = '*'
wal_level = replica  
archive_mode = on  
archive_command = '/bin/date' 
max_wal_senders = 10  
wal_keep_segments = 512
hot_standby = on

host replication repuser  172.17.0.2/32  md5
host replication repuser  172.17.0.5/32  md5

$ pg_ctl start -D $PGDATA

...
...
database system is ready to accept connections
 done
server started

$ psql -U postgres -p 1921
psql (10.0)
Type "help" for help.

postgres=# CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD 'domac123';

CREATE ROLE

postgres=# select pg_start_backup('domacli_bak');

 pg_start_backup
-----------------
 0/2000060
(1 row)

$ tar czvf pg_root.tar.gz pg_root --exclude=pg_root/pg_wal

$ scp pg_root.tar.gz postgres@172.17.0.5:/data/pg10

$ tar xvf pg_root.tar.gz

postgres=# select pg_stop_backup();

NOTICE: pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup
----------------
 0/2000168
(1 row)

cp $PGHOME/share/recovery.conf.sample /data/pg10/pg_root/recovery.conf

recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=172.17.0.2 port=1921 user=repuser'

$ cd ~
$ touch .pgpass
$ chmod 0600 .pgpass

172.17.0.2:1921:replication:repuser:domac123
172.17.0.5:1921:replication:repuser:domac123

$ pg_ctl start

...

database system is ready to accept read only connections
 done
server started

ps -ef | grep wal

postgres 6939 6935 0 23:16 ? 00:00:00 postgres: wal writer process
postgres 6983 6935 0 23:42 ? 00:00:00 postgres: wal sender process repuser 172.17.0.5(45910) streaming 0/3000140

ps -ef | grep wal

postgres 26481 26479 0 23:42 ? 00:00:00 postgres: wal receiver process streaming 0/3000140
postgres 26486 26448 0 23:42 ? 00:00:00 grep --color=auto wal

$ pg_ctl stop -m fast
waiting for server to shut down.... done
server stopped

$ rm -rf $PGDATA
$ rm -rf /data/pg10/pg_tbs

pg_basebackup -D $PGDATA -Fp -Xs -v -P -h 172.17.0.2 -p 1921 -U repuser -W

pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/20007A8 on timeline 1
pg_basebackup: starting background WAL receiver
22655/22655 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/2000888
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=172.17.0.2 port=1921 user=repuser password=domac123'

recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=172.17.0.2 port=1921 user=repuser'

postgres=# create table test_ms(id int4);

CREATE TABLE

postgres=# insert into test_ms values(6);

INSERT 0 1

postgres=# select * from test_ms;
 id
----
 6
(1 row)

postgres=# insert into test_ms values(9);
INSERT 0 1

postgres=# delete from test_ms where id=6;
DELETE 1

postgres=# select * from test_ms;
 id
----
 9
(1 row)

postgres=# insert into test_ms values(6);

ERROR: cannot execute INSERT in a read-only transaction
STATEMENT: insert into test_ms values(6);
ERROR: cannot execute INSERT in a read-only transaction

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

$ pg_ctl stop -m fast

pg_ctl promote -D $PGDATA
waiting for server to promote....2018-09-30 00:10:30.222 UTC [26480] LOG: received promote request
LOG: redo done at 0/4000028
LOG: last completed transaction was at log time 2018-09-29 23:50:52.502513+00
LOG: selected new timeline ID: 2
LOG: archive recovery complete
LOG: database system is ready to accept connections
Sun Sep 30 00:10:30 UTC 2018
Sun Sep 30 00:10:30 UTC 2018
 done
server promoted

recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=172.17.0.5 port=1921 user=repuser password=domac123'

recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=172.17.0.5 port=1921 user=repuser'

$ cd ~
$ touch .pgpass
$ chmod 0600 .pgpass

172.17.0.2:1921:replication:repuser:domac123
172.17.0.5:1921:replication:repuser:domac123

pg_ctl start

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)

postgres=# insert into test_ms values(11);
INSERT 0 1

postgres=# select * from test_ms;
 id
----
 9
 11
(2 rows)