[译]PG复制和自动故障转移--2

SELECT datname, oid FROM pg_database WHERE datname = 'postgres';
datname  |  oid  
----------+-------
postgres | 15709
Note the database OID, i.e. 15709
复制

SELECT oid,* from pg_tablespace;
oid  |  spcname   | spcowner | spcacl | spcoptions
------+------------+----------+--------+------------
1663 | pg_default |       10 |        |
1664 | pg_global  |       10 |        |
Note the table space OID, i.e. 1663
复制

SELECT pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/1C420B8
Note the LSN, i.e. 0/1C420B8
复制

SELECT pg_relation_filepath('abc');
pg_relation_filepath
----------------------
base/15709/16384
Note the relation filename, base/15709/16384
复制

15709 → database postgres → noted in step 1
16384 → table abc → noted in step 5
复制

SELECT pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/1C43258
(1 row)
8)
INSERT INTO abc VALUES('pkn');
复制

1663 → pg_default tablespace → noted in step 2
15709 → database postgres → noted in step 1
16384 → table abc → noted in step 5
复制

SELECT pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/1C432F8
(1 row)
11）
INSERT INTO abc VALUES('ujy');
复制

---------+---------------------------------------------------+----------------+
 Offset  | Hex Bytes                                         | ASCII chars    |
---------+---------------------------------------------------+----------------+
00000060 | 3b 00 00 00 c3 04 00 00  28 00 40 02 00 00 00 00  |;.......(.@.....|
00000070 | 00 0a 00 00 ec 28 75 6e  00 20 0a 00 7f 06 00 00  |.....(un. ......|
00000080 | 5d 3d 00 00 00 40 00 00  00 00 00 00 ff 03 01 00  |]=...@..........|
00000090 | 02 08 18 00 09 70 6b 6e  03 00 00 00 00 00 00 00  |.....pkn........|
000000a0 | 22 00 00 00 c3 04 00 00  60 00 40 02 00 00 00 00  |".......`.@.....|
000000b0 | 00 01 00 00 dd 4c 87 04  ff 08 e4 73 44 e7 41 26  |.....L.....sD.A&|
000000c0 | 02 00 00 00 00 00 00 00  32 00 00 00 00 00 00 00  |........2.......|
000000d0 | a0 00 40 02 00 00 00 00  10 08 00 00 9e 01 36 88  |..@...........6.|
000000e0 | ff 18 00 00 00 00 00 00  00 00 00 03 00 00 c4 04  |................|
000000f0 | 00 00 c4 04 00 00 c3 04  00 00 00 00 00 00 00 00  |................|

00000100 | 3b 00 00 00 c4 04 00 00  c8 00 40 02 00 00 00 00  |;.........@.....|
00000110 | 00 0a 00 00 33 df b4 71  00 20 0a 00 7f 06 00 00  |....3..q. ......|
00000120 | 5d 3d 00 00 00 40 00 00  00 00 00 00 ff 03 01 00  |]=...@..........|
00000130 | 02 08 18 00 09 75 6a 79  04 00 00 00 00 00 00 00  |.....ujy........|
00000140 | 22 00 00 00 c4 04 00 00  00 01 40 02 00 00 00 00  |".........@.....|
00000150 | 00 01 00 00 96 2e 96 a6  ff 08 d8 f3 79 ed 41 26  |............y.A&|
00000160 | 02 00 00 00 00 00 00 00  32 00 00 00 00 00 00 00  |........2.......|
00000170 | 40 01 40 02 00 00 00 00  10 08 00 00 eb 6b 95 36  |@.@..........k.6|
00000180 | ff 18 00 00 00 00 00 00  00 00 00 03 00 00 c5 04  |................|
00000190 | 00 00 c5 04 00 00 c4 04  00 00 00 00 00 00 00 00  |................|
000001a0 | 00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
复制

sudo firewall-cmd --state
sudo systemctl stop firewalld
sudo systemctl disable firewalld
sudo systemctl mask --now firewalld
复制

Local  all           all                       md5
host   all           all   172.16.214.167/24   md5
host   all           all   ::1/128             md5
local  replication   all                       md5
host   replication   all   172.16.214.167/24   md5
host   replication   all   ::1/128             md5
复制

max_wal_senders = 10
wal_level = replica
max_replication_slots = 10
synchronous_commit = on
synchronous_standby_names = '*'
listen_addresses = '*'
复制

./pg_basebackup
--pgdata=/tmp/sb_data/
--format=p
--write-recovery-conf
--checkpoint=fast
--label=mffb
--progress
--verbose
--host=172.16.214.167
--port=5432
--username=postgres
复制

START WAL LOCATION: 0/2000028 (file 000000010000000000000002)
CHECKPOINT LOCATION: 0/2000060
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2019-02-24 05:25:30 EST
LABEL: mffb
复制

standby_mode = 'on'
primary_conninfo = 'user=enterprisedb
password=abc123
host=172.16.214.167
port=5432
sslmode=prefer
sslcompression=1
krbsrvname=postgres
target_session_attrs=any'
primary_slot_name = 'node_a_slot'
复制

edb=# SELECT * FROM pg_create_physical_replication_slot('node_a_slot');
      slot_name  | xlog_position
    -------------+---------------
     node_a_slot |
    (1 row)
复制

edb=# SELECT slot_name, slot_type, active FROM pg_replication_slots;
      slot_name  | slot_type | active
    -------------+-----------+--------
     node_a_slot | physical  | f
    (1 row)
复制

-bash-4.2$ ./edb-psql -p 5432 edb
Password:
psql.bin (10.7)
Type "help" for help.


create table abc(a int, b varchar(250));
insert into abc values(1,'One');
insert into abc values(2,'Two');
insert into abc values(3,'Three');
复制

./psql -p 5432 -U postgres postgres
Password for user postgres:
psql.bin (10.7)
Type "help" for help.


postgres=# select * from abc;
a |   b   
---+-------
1 | One
2 | Two
3 | Three
(3 rows)
复制

-bash-4.2$ ./edb-psql -p 5432 edb
Password:
psql.bin (10.7)
Type "help" for help.
edb=# insert into abc values(4,'Four');
复制