postgres主从搭建步骤

-----------------------------------------------------  删除备库用户
psql.exe -U postgres -h 127.0.0.1 -p 5432 -c "drop user if exists repl;"


---------------------------------------------------   新增备库用户
psql.exe -U postgres -h 127.0.0.1 -p 5432 -c "create user repl with login replication password 'repl';" postgres


----------------------------------------------------- 修改pg_hba.conf文件：

# replication privilege.
host    replication     repl            10.33.47.9/32		    md5


-----------------------------------------------------  修改 postgres.conf文件：

---- 取消注释wal_level = replica， 改为： wal_level = hot_standby

wal_level = hot_standby

----- 取消注释max_wal_senders = 10

max_wal_senders = 10

----- 取消注释wal_keep_segments = 0， 改为：wal_keep_segments = 256

wal_keep_segments = 256

-----------------------------------------------重启主库


net stop postgresql-x64-11

net start postgresql-x64-11

---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------

------ 停止从库
net stop postgresql-x64-11


------ 删除从库data目录下文件
cd /d D:\PostgreSQL\11
for /d %i in (data\*.*) do (rd /s /q %i)
del /f /s /q data\*.*

------------------------------------------------------------


----- 使用 pg_basebackup 生成备库

cd /d D:\PostgreSQL\11\bin

--- 下述命令执行需要输入从库密码： repl
pg_basebackup.exe -D "D:\PostgreSQL\11\data" -Fp -Xs --progress -v -U repl -h 10.12.66.50 -p 5432


----修改配置文件postgresql.conf，配置备库的流复制参数，如下

---取消注释
hot_standby = on

-- 取消注释：wal_receiver_status_interval = 10s, 改为 wal_receiver_status_interval = 2s
wal_receiver_status_interval = 2s

---- 取消注释：hot_standby_feedback = off,  改为hot_standby_feedback = on
hot_standby_feedback = on

---------------------------------------

修改配置文件 recover.conf
cd /d D:\PostgreSQL\11
copy /y  share\recovery.conf.sample   data\recovery.conf

配置如下参数
--- 取消注释
recovery_target_timeline = 'latest'

---修改standby_mode = off为standby_mode = on
standby_mode = on

primary_conninfo = 'host=10.12.66.50 port=5432 user=repl password=repl'

-- 修改trigger_file = ''， 为：trigger_file = 'tgfile'
trigger_file = 'tgfile'

重启：

net start  postgresql-x64-11




------------------------------------验证

--- 主库中查询
select * from pg_stat_replication;

-- 查询被分库
psql.exe -U postgres -h 10.33.47.9 -c "select datname from pg_database" postgres

-- 主库中建立新库
psql.exe -U postgres -h 10.12.66.50 -c "create database cmsdb" postgres

---再次查询备份库
psql.exe -U postgres -h 10.33.47.9 -c "select datname from pg_database" postgres


--备份机查询用户
psql.exe -U postgres -h 10.33.47.9 -c "select * from pg_user" postgres

--主机创建用户
psql.exe -U postgres -h 10.12.66.50 -c "create user test01 with password 'test01' " postgres


--备机再次查询用户
psql.exe -U postgres -h 10.33.47.9 -c "select * from pg_user" postgres


---------------------------------------------------------------------

psql.exe -U postgres -h 10.33.47.9 -c "select * from pg_tables where tablename='tb_test01'" postgres

psql.exe -U postgres -h 10.12.66.50 -c " create table tb_test01(id int, code varchar(64))" postgres

psql.exe -U postgres -h 10.33.47.9 -c "select * from pg_tables where tablename='tb_test01'" postgres