postgresql-同机物理异步主备库搭建

前言：

​ 之前一直学习oracle，鉴于国产替代化和去o，就了解学习一下其他mysql和pg数据库，发现pg很小巧灵活，特别是psql客户端感觉很好用，就是不知道实际生产使用时性能和安全相比怎么样。

环境：（同一个主机）

	主机1	主机2
os_version	oracle Linux Server release 5.4 32位	oracle Linux Server release 5.4 32位
ip	192.168.100.203	192.168.100.203
pg_version	PostgreSQL 10.0 on i686-pc-linux-gnu	PostgreSQL 10.0 on i686-pc-linux-gnu
pg_install_dir	/opt/pgsql	/opt/pgsql
pg_data_dir	/pgdata/10/data	/pgdata2/10/data

1. 修改主库相关参数

alter system set archive_mode = 'on';     -- 启动归档模式
alter system set archive_command = 'cp %p /pgdata/10/archive_wals/%f';  -- 归档命令
alter system set wal_level = 'replica';
alter system set max_wal_senders = 10;    -- 默认就是10， 归档进程数
alter system set wal_keep_segments = 512;     -- 默认为0，pg_wal保留的最小wal文件数量
alter system set hot_standby = 'on';    -- 默认为on，备库为read only状态

2. 修改主库访问权限文件（本次是同机，如果是异机，需要主备host都加上各一行）

vim pg_hba.conf
#增加一行
host    replication     repuser             192.168.100.203/32                 md5

3. 重启主库（生效参数），新建replication用户

pg_ctl stop -D /pgdata/10/data
pg_ctl start -D /pgdata/10/data

CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD 're12a345';
-- 查看新建的用户角色
\d pg_roles
select rolname from pg_roles;

4. 热备主库到备库位置

select pg_start_backup('standby_bak1');

cd /pgdata/10/data/
tar -zcf all_data.tgz * --exclude=pg_wal/*
mv all_data.tgz  /pgdata2/10/data/
cd /pgdata2/10/data/
tar -zxvf all_data.tgz
chown 700 ../data
# 清理
rm -f all_data.tgz l
rm -f postmaster.*
rm -f logfile

select pg_stop_backup();

5. 修改备份配置文件

vim postgresql.conf
# 因为本次是同机，异机无需修改port
----------------------------------------------------------------------------------
port = 5433                             # (change requires restart)
----------------------------------------------------------------------------------

vim recovery.conf
----------------------------------------------------------------------------------
recovery_target_timeline = 'latest'
standby_mode = on # 启用备库查询

primary_conninfo = 'host=192.168.100.203 port=5432 user=repuser'  # 设置主库信息，密码由后面密码文件提供
----------------------------------------------------------------------------------

6. 设置主备库复制用户密码文件

touch /home/postgres/.pgpass; chmod 600 /home/postgres/.pgpass

vim /home/postgres/.pgpass
# 增加下面2行
----------------------------------------------------------------------------------
192.168.100.203:5432:replication:repuser:re12a345
192.168.100.203:5433:replication:repuser:re12a345
----------------------------------------------------------------------------------

7. 启动备库（查看logfile有无异常，无异常则正确启动，提示entering standby mode，db接收只读连接）

pg_ctl start -D /pgdata2/10/data/ -l logfile

8. 检查数据同步

-- 查看wal复制进程（显示2个wal receiver和sender进程）
ps -ef |grep post|grep wal

postgres 12055 12053  0 16:00 ?        00:00:00 postgres: wal receiver process   streaming 0/1E014558
postgres 12056 11389  0 16:00 ?        00:00:00 postgres: wal sender process repuser 192.168.100.203(58787) streaming 0/1E014558

-- 主库登录插入测试数据
psql -d yg_test

insert into test values (8,'yyy');

-- 备库登录查询测试数据

psql -p 5433 -d yg_test

yg_test@[local]:5433=#select * from test;
 id | name
----+------
  1 | yg1
  2 | yg2
  3 | yg3
  4 | yg4
  5 | yg5
  6 | yg6
  7 | xxx
  8 | yyy        -- 备库正常同步收到主库刚刚插入的数据
(8 rows)


-- 备库测试插入数据报错：（logfile中也会有提示）
yg_test@[local]:5433=#insert into test values (9,'zzz');
ERROR:  cannot execute INSERT in a read-only transaction


-- 查看replication状态
yg_test@[local]:5432=#select usename, application_name, client_addr, client_hostname, client_port, state, sync_state from pg_stat_replication;
 usename | application_name |   client_addr   | client_hostname | client_port |   state   | sync_state
---------+------------------+-----------------+-----------------+-------------+-----------+------------
 repuser | walreceiver      | 192.168.100.203 |                 |       58787 | streaming | async
(1 row)

至此，本次简单pg同步搭建完毕，谢谢