PostgreSql流复制

准备环境
两台装好数据库的虚拟机

配置主库pg_hba.conf

su - postgres
vi $PGDATA/pg_hba.conf

host    replication     postgres        0.0.0.0/0               md5
host    all             all             0.0.0.0/0               md5

vi $PGDATA/postgresql.conf
listen_addresses = '*'
port = 5432
max_wal_senders = 5
hot_standby = on
wal_level = hot_standby

psql
alter user postgres with password 'Abcd-1234';
\q

vi $PGDATA/recovery.done
standby_mode = 'on'
primary_conninfo = 'user=postgres password=Abcd-1234 host=10.138.117.96 port=5432 sslmode=disable sslcompression=1'

pg_ctl restart

从库基础复制数据

su - postgres
pg_ctl stop
cd $PGDATA
rm -rf *
pg_basebackup -Fp -D /app/pg/data/ -Xs -v -P -h 10.138.117.95 -p 5432 -U postgres -R
rm -rf recovery.done

启动从库验证主从流复制

从库
pg_ctl start
ps -ef|grep postgres
root      34660  26419  0 16:03 pts/0    00:00:00 su - postgres
postgres  34661  34660  0 16:03 pts/0    00:00:00 -bash
postgres  34802      1  0 16:41 pts/0    00:00:00 /app/pg/pgsql/bin/postgres
postgres  34803  34802  0 16:41 ?        00:00:00 postgres: startup process   recovering 000000010000000000000003
postgres  34804  34802  0 16:41 ?        00:00:00 postgres: checkpointer process   
postgres  34805  34802  0 16:41 ?        00:00:00 postgres: writer process   
postgres  34806  34802  0 16:41 ?        00:00:00 postgres: stats collector process   
postgres  34807  34802  1 16:41 ?        00:00:00 postgres: wal receiver process   streaming 0/30003E0
postgres  34808  34661  1 16:41 pts/0    00:00:00 ps -ef
postgres  34809  34661  0 16:41 pts/0    00:00:00 grep postgres

psql
postgres=# create database test;
ERROR:  cannot execute CREATE DATABASE in a read-only transaction
从库为只读

主库
ps -ef|grep postgres
root      40852   2162  0 16:08 pts/0    00:00:00 su - postgres
postgres  40853  40852  0 16:08 pts/0    00:00:00 -bash
postgres  40922      1  0 16:17 pts/0    00:00:00 /app/pg/pgsql/bin/postgres -D /app/pg/data
postgres  40924  40922  0 16:17 ?        00:00:00 postgres: checkpointer process            
postgres  40925  40922  0 16:17 ?        00:00:00 postgres: writer process                  
postgres  40926  40922  0 16:17 ?        00:00:00 postgres: wal writer process              
postgres  40927  40922  0 16:17 ?        00:00:00 postgres: autovacuum launcher process     
postgres  40928  40922  0 16:17 ?        00:00:00 postgres: stats collector process         
postgres  41023  40922  0 16:41 ?        00:00:00 postgres: wal sender process postgres 10.138.117.96(33719) streaming 0/30003E0
postgres  41034  40853  0 16:44 pts/0    00:00:00 ps -ef
postgres  41035  40853  0 16:44 pts/0    00:00:00 grep postgres

postgres=# create database test;
CREATE DATABASE
主库可写

psql
\x
select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 41023
usesysid         | 10
usename          | postgres
application_name | walreceiver
client_addr      | 10.138.117.96
client_hostname  | 
client_port      | 33719
backend_start    | 2018-12-19 16:41:41.767349+08
backend_xmin     | 
state            | streaming
sent_location    | 0/30004C0
write_location   | 0/30004C0
flush_location   | 0/30004C0
replay_location  | 0/30004C0
sync_priority    | 0
sync_state       | async