PG DG架构配置方法

1. PG版本postgresql-10.5.tar.gz
2. 主库10.76.17.117 从库10.76.17.250
3. 主库备库安装PG，主备库都安装好PG之后把备库/Data/pgdata底下全部清空了(rm-rf *)留下/usr/local/pgsql10.5/里的东西
4. vi .bash_profile —主备库都设置
   export PATH
   export PGDATA=/Data/pgdata
   export PGHOME=/usr/local/pgsql10.5/
   export PATH=$PATH:$HOME/bin:$PGHOME/bin export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql10.5/lib
   source .bash_profile

5.主库/Data/pgdata vi postgresql.conf
port = 5436
max_connections = 100
wal_level = hot_standby
max_wal_senders = 30
log_connections = on
logging_collector = on
log_directory = ‘pg_log’
log_destination = ‘csvlog’
archive_mode= on
archive_command =‘DATE=date +%Y%m%d;DIR="/Data/archive/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp %p $DIR/%f’ —归档日志存放位置(没有目录要建)
wal_keep_segments = 1024
synchronous_standby_names = ‘*’

注主库建立的目录备库也要建立一份

6. 主库/Data/pgdata vi pg_hba.conf

IPv4 local connections:

host all all 0.0.0.0/0 trust
host replication repl 0.0.0.0/0 trust
host replication all ::1/128 trust

注 repl是主库之后建立的用户(下一步建立)，用来给备库传送归档日志并让备库使用Trust的意思是传文件不需要输入密码 0.0.0.0/0是指所有计算机都可以传

7.启动主库pg_ctl start
检测数据库是否启动成功
建一个超级用户来专门负责让standby连接去拖WAL日志
postgres=#create user repl superuser password ‘111111’;

[postgres@ty-dxbtest02 ]$ ./psql -d postgres
psql(9.3.0)
Type"help" for help.

postgres=# #说明启动成功了
8.基本流程：在主数据库服务器上执行pg_start_backup(),复制data目录，在执行pg_stop_backup()。
[postgres@ty-dxbtest02 ] psql–d postgres
postgres=#select pg_start_backup(‘base backup for logstreaming’);

备库操作===========

1. 登陆将主库/Data/pgdata/下全部东西传到备库
   scp -r * postgres@10.76.17.250:/Data/pgdata/

2. 修改从库vi postgresql.conf
   port = 5432 —这个port不要和主库一样不然会起不来数据库
   max_connections = 100
   wal_level = hot_standby
   hot_standby = on
   max_wal_senders = 30
   log_connections = on
   logging_collector = on
   log_directory = ‘pg_log’
   log_destination = ‘csvlog’
   archive_mode= on
   archive_command =‘DATE=date +%Y%m%d;DIR="/Data/archive/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp %p $DIR/%f’
   wal_keep_segments = 1024

3. 修改完后登陆主库停止主数据库基础备份
   [postgres@ty-dxbtest02 pgdata]$ psql
   postgres=# select pg_stop_backup();

4. 新建备库/Data/pgdata/中 recovery.conf
   [postgres@ty-dxbtest03 pgdata]$ vi recovery.conf
   standby_mode= on
   primary_conninfo= ‘host=10.76.17.117 port=5436 user=repl’

加入主库host user=repl—这个用户主库要提前建立

5.备库删除postmaster.pid
Rm –rf /Data/pgdata/postmaster.pid

5. 启动备库
   Pg_ctl start

=============验证阶段

1.主库 观察CSVLOG
/Data/pgdata/pg_log
[postgres@ty-dxbtest02 pg_log]$ tail -f 11:57 postgresql-2019-04-23_110812.csv
2 CST,0,LOG,00000,“connection received: host=10.76.17.250 port=5789”,""
2019-04-23 11:59:12.465 CST,“postgres”,"",45015,“10.76.17.250:5789”,5cbe8d90.afd7,2,“authentication”,2019-04-23 11:59:12 CST,3/342,0,FATAL,28000,“no pg_hba.conf entry for replication connection from host ““10.76.17.250"”, user ““postgres””, SSL off”,”"
2019-04-23 11:59:17.470 CST,45017,“10.76.17.250:5790”,5cbe8d95.afd9,1,"",2019-04-23 11:59:17 CST,0,LOG,00000,“connection received: host=10.76.17.250 port=5790”,""
2019-04-23 11:59:17.471 CST,“postgres”,"",45017,“10.76.17.250:5790”,5cbe8d95.afd9,2,“authentication”,2019-04-23 11:59:17 CST,3/345,0,FATAL,28000,“no pg_hba.conf entry for replication connection from host ““10.76.17.250"”, user ““postgres””, SSL off”,”"
显示了备库的IP10.76.17.250说明就连通了

2. 观察主库WAL-Sender进程
   ps -ef |grep post --显示如下方可证明成功
   postgres 42407 42353 0 11:10 ? 00:00:00 postgres: wal sender process repl 10.76.17.250(5203) streaming 0/9017838

3．观察备库WAL-接收进程
ps -ef |grep post --显示如下接受进程方可证明成功
postgres 42921 42916 0 11:10 ? 00:00:07 postgres: wal receiver process streaming 0/9017838

4．建表测试
Psql 主库建表
create table table3(id integer);
\d 查看下

备库查看同步过来没有。

5.只读模式测试
备库执行create table table3(id integer);
备库会显示只读模式cannot execute CREATE TABLE in a read-onlytransaction