PostgreSQL Cascade Replication
node1:master:10.2.208.10:repclia(user)
node2:upstreamnode:10.2.208.11:replica(user)
node3:downstreamnode:10.2.208.12:replica(user)
[root@node1 data]# uname -av
Linux node1 3.10.0-229.el7.x86_64 #1 SMP Fri Mar 6 11:36:42 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
[root@node2 ~]# uname -av
Linux node2 3.10.0-229.el7.x86_64 #1 SMP Fri Mar 6 11:36:42 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
[root@node3 data]# uname -a
Linux node3 3.10.0-229.el7.x86_64 #1 SMP Fri Mar 6 11:36:42 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
[replica@node1 bin]$ ./psql -h localhost -p 6432 -U replica -d postgres
psql (9.5.1)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)
[replica@node2 bin]$ ./psql -h localhost -p 6432 -d postgres
psql (9.5.1)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)
[replica@node3 bin]$ ./psql -h localhost -p 6432 -d postgres -U replica
psql (9.5.1)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)
1 master:installing，init,start
postgresql.conf
wal_level = hot_standby
fsync = on
synchronous_commit = on
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 10
synchronous_standby_names = 'node2'
hot_standby = on
pg_hba.conf
host all all 0.0.0.0/0 trust
host replication replica 0.0.0.0/0 trust
[replica@node1 bin]$ ./pg_ctl -D ../data/ start
server starting
[replica@node1 bin]$ LOG: database system was shut down at 2016-03-08 11:27:56 CST
LOG: MultiXact member wraparound protections are now enabled
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
[replica@node1 bin]$ ./pg_controldata -D /home/replica/data/
pg_control version number: 942
Catalog version number: 201510051
Database system identifier: 6259518217727406929
Database cluster state: in production
pg_control last modified: Tue 08 Mar 2016 03:20:08 PM CST
Latest checkpoint location: 0/3016740
Prior checkpoint location: 0/3016660
Latest checkpoint's REDO location: 0/3016708
Latest checkpoint's REDO WAL file: 000000010000000000000003
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0/1834
Latest checkpoint's NextOID: 24576
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 1822
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 1834
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Tue 08 Mar 2016 03:20:08 PM CST
Fake LSN counter for unlogged rels: 0/1
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: hot_standby
wal_log_hints setting: off
max_connections setting: 100
max_worker_processes setting: 8
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0
[replica@node1 bin]$ ./psql -h localhost -p 6432 -U replica -d postgres
psql (9.5.1)
Type "help" for help.
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
postgres=# x
Expanded display is on.
postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid | 29562
usesysid | 10
usename | replica
application_name | node2
client_addr | 10.2.208.11
client_hostname |
client_port | 44082
backend_start | 2016-03-08 15:17:25.334968+08
backend_xmin |
state | streaming
sent_location | 0/30169A8
write_location | 0/30169A8
flush_location | 0/30169A8
replay_location | 0/30169A8
sync_priority | 1
sync_state | sync
[replica@node1 bin]$ ./psql -h localhost -p 6432
FATAL: database "replica" does not exist
psql: FATAL: database "replica" does not exist
[replica@node1 bin]$ ./psql -h localhost -p 6432 -d postgres
psql (9.5.1)
Type "help" for help.
postgres=# d
No relations found.
[replica@node1 bin]$ pwd
/home/replica/bin
replica 25816 1 0 11:40 pts/4 00:00:00 /home/replica/bin/postgres -D ../data
replica 25818 25816 0 11:40 ? 00:00:00 postgres: checkpointer process
replica 25819 25816 0 11:40 ? 00:00:00 postgres: writer process
replica 25820 25816 0 11:40 ? 00:00:00 postgres: wal writer process
replica 25821 25816 0 11:40 ? 00:00:00 postgres: autovacuum launcher process
replica 25822 25816 0 11:40 ? 00:00:00 postgres: archiver process last was 000000010000000000000002.00000028.backup
replica 25823 25816 0 11:40 ? 00:00:00 postgres: stats collector process
replica 28345 25816 0 14:06 ? 00:00:00 postgres: wal sender process replica 10.2.208.11(44077) streaming 0/30004C0
replica 28679 25340 0 14:26 pts/4 00:00:00 grep --color=auto postgres
2 upstreamnode，installing，from master do pg_basebackup
postgresql.conf
wal_level = hot_standby
fsync = on
synchronous_commit = on
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 10
synchronous_standby_names = ' '
hot_standby = on
recovery.conf
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=10.2.208.10 port=6432 user=replica password=replica application_name=node2'
pg_hba.conf
host all all 0.0.0.0/0 trust
host replication replica 0.0.0.0/0 trust
[replica@node2 bin]$ ./pg_basebackup -h 10.2.208.10 -P -Fp -Xs -v -p 6432 -U replica -D /home/replica/data/
transaction log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
22511/22511 kB (100%), 1/1 tablespace
transaction log end point: 0/2000130
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
[replica@node2 bin]$ ./pg_ctl -D /home/replica/data/ start
server starting
[replica@node2 bin]$ LOG: database system was interrupted; last known up at 2016-03-08 14:00:06 CST
LOG: entering standby mode
LOG: redo starts at 0/2000028
LOG: consistent recovery state reached at 0/2000130
LOG: database system is ready to accept read only connections
LOG: started streaming WAL from primary at 0/3000000 on timeline 1
[replica@node2 bin]$ ./pg_controldata -D /home/replica/data/
pg_control version number: 942
Catalog version number: 201510051
Database system identifier: 6259518217727406929
Database cluster state: in archive recovery
pg_control last modified: Tue 08 Mar 2016 03:22:22 PM CST
Latest checkpoint location: 0/3016740
Prior checkpoint location: 0/3016318
Latest checkpoint's REDO location: 0/3016708
Latest checkpoint's REDO WAL file: 000000010000000000000003
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0/1834
Latest checkpoint's NextOID: 24576
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 1822
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 1834
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Tue 08 Mar 2016 03:20:08 PM CST
Fake LSN counter for unlogged rels: 0/1
Minimum recovery ending location: 0/30167E8
Min recovery ending loc's timeline: 1
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: hot_standby
wal_log_hints setting: off
max_connections setting: 100
max_worker_processes setting: 8
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0
[replica@node2 bin]$ ./psql -h localhost -p 6432 -d postgres
psql (9.5.1)
Type "help" for help.
postgres=# select * from pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
postgres=# x
Expanded display is on.
postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid | 8594
usesysid | 10
usename | replica
application_name | walreceiver
client_addr | 10.2.208.12
client_hostname |
client_port | 54798
backend_start | 2016-03-08 15:17:25.291373+08
backend_xmin |
state | streaming
sent_location | 0/30169A8
write_location | 0/30169A8
flush_location | 0/30169A8
replay_location | 0/30169A8
sync_priority | 0
sync_state | async
[replica@node2 bin]$ ./psql -h localhost -p 6432 -d postgres
psql (9.5.1)
Type "help" for help.
postgres=# d
No relations found.
postgres=# q
[replica@node2 bin]$ ps -ef|grep postgres
replica 7564 1 0 14:06 pts/2 00:00:00 /home/replica/bin/postgres -D /home/replica/data
replica 7565 7564 0 14:06 ? 00:00:00 postgres: startup process recovering 000000010000000000000003
replica 7566 7564 0 14:06 ? 00:00:00 postgres: checkpointer process
replica 7567 7564 0 14:06 ? 00:00:00 postgres: writer process
replica 7568 7564 0 14:06 ? 00:00:00 postgres: stats collector process
replica 7569 7564 0 14:06 ? 00:00:00 postgres: wal receiver process streaming 0/3000568
replica 7927 7047 0 14:30 pts/2 00:00:00 grep --color=auto postgres
3 downstreamnode:installing，from upstreamnode do pg_basebackup
postgresql.conf
wal_level = hot_standby
fsync = on
synchronous_commit = on
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 10
synchronous_standby_names = ' '
hot_standby = on
recovery.conf
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=10.2.208.11 port=6432 user=replica password=replica'
pg_hba.conf
host all all 0.0.0.0/0 trust
host replication replica 0.0.0.0/0 trust
[replica@node3 bin]$ ./pg_basebackup -h 10.2.208.11 -P -Fp -Xs -v -p 6432 -U replica -D /home/replica/data/
transaction log start point: 0/3000220 on timeline 1
pg_basebackup: starting background WAL receiver
22512/22512 kB (100%), 1/1 tablespace
transaction log end point: 0/3000300
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
[replica@node3 bin]$ ./pg_ctl -D ../data/ start
server starting
[replica@node3 bin]$ LOG: database system was interrupted while in recovery at log time 2016-03-08 14:15:07 CST
HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.
LOG: entering standby mode
LOG: redo starts at 0/3000220
LOG: consistent recovery state reached at 0/3000300
LOG: invalid record length at 0/3000300
LOG: database system is ready to accept read only connections
LOG: started streaming WAL from primary at 0/3000000 on timeline 1
[replica@node3 bin]$ ./pg_controldata -D /home/replica/data/
pg_control version number: 942
Catalog version number: 201510051
Database system identifier: 6259518217727406929
Database cluster state: in archive recovery
pg_control last modified: Tue 08 Mar 2016 03:27:30 PM CST
Latest checkpoint location: 0/3016820
Prior checkpoint location: 0/3016740
Latest checkpoint's REDO location: 0/30167E8
Latest checkpoint's REDO WAL file: 000000010000000000000003
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0/1834
Latest checkpoint's NextOID: 24576
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 1822
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 1834
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Tue 08 Mar 2016 03:25:08 PM CST
Fake LSN counter for unlogged rels: 0/1
Minimum recovery ending location: 0/3016708
Min recovery ending loc's timeline: 1
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: hot_standby
wal_log_hints setting: off
max_connections setting: 100
max_worker_processes setting: 8
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0
[replica@node3 bin]$ ./psql -h localhost -p 6432 -d postgres -U replica
psql (9.5.1)
Type "help" for help.
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
[replica@node3 bin]$ ./psql -h localhost -p 6432 -d postgres -U replica
psql (9.5.1)
Type "help" for help.
postgres=# select * from pg_stat_replication ;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_locati
on | write_location | flush_location | replay_location | sync_priority | sync_state
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+------------
---+----------------+----------------+-----------------+---------------+------------
(0 rows)
[replica@node3 bin]$ ./psql -h localhost -p 6432 -d postgres -U replica
psql (9.5.1)
Type "help" for help.
postgres=# d
No relations found.
在master上创建表并插入数据
[replica@node1 bin]$ ./psql -h localhost -p 6432 -d postgres -U replica
psql (9.5.1)
Type "help" for help.
postgres=# create table test_tb(id int);
CREATE TABLE
postgres=# insert into test_tb values (1),(2),(3),(4),(5);
INSERT 0 5
postgres=# select * from test_tb ;
id
----
1
2
3
4
5
(5 rows)
upstreamnode
[replica@node2 bin]$ ./psql -h localhost -p 6432 -d postgres
psql (9.5.1)
Type "help" for help.
postgres=# d
List of relations
Schema | Name | Type | Owner
--------+---------+-------+---------
public | test_tb | table | replica
(1 row)
postgres=# select * from test_tb ;
id
----
1
2
3
4
5
(5 rows)
downstreamnode
[replica@node3 bin]$ ./psql -h localhost -p 6432 -d postgres -U replica
psql (9.5.1)
Type "help" for help.
postgres=# d
List of relations
Schema | Name | Type | Owner
--------+---------+-------+---------
public | test_tb | table | replica
(1 row)
postgres=# select * from test_tb ;
id
----
1
2
3
4
5
(5 rows)