Postgresql数据库主从流复制

2020-06-14 19:40:55

一、安装依赖二、安装PG三、配置PG环境变量1、添加postgres用户四、配置PG主库1、初始化PG数据库2、启动PG数据库，并创建同步用户3、配置用户允许远程登录4、修改PG主库配置，并重启五、配置PG备库1、从库不用初始化，直接同步主库数据2、添加一个recovery.conf配置文件3、修改PG从库配置六、验证是否正常同步1、主库查看2、通过查看进程3、通过主库增删改查验证七、手动测试切换主从1、手动停止主库，模拟主库宕机2、从库手动生成触发文件3、新的主库生成测试数据4、旧的主库恢复为新的从库4.1 生成recovery.conf4.2 启动新的从库，并查看数据是否同步八、配置启动脚本1、修改linux文件属性，添加X属性2、复制linux文件到/etc/init.d目录下，更名为postgresql3、修改/etc/init.d/postgresql文件的两个变量4、执行service postgresql start，就可以启动PostgreSQL服务5、设置postgresql服务开机自启动

由于某个项目前期资源紧张，使用的Postgresql单节点数据库；为解决项目数据库安全性问题，小蜗牛应要求整理Postgresql数据库主从高可用方案，以下操作为部署主从流复制测试过程，后续整理pgpool高可用、负载均衡方案。。

一、安装依赖

yum install -y gcc-c++ zlib-devel vim openssh-clients

二、安装PG

tar zxvf postgresql-9.3.4.tar.gz 
mkdir /opt/pgsql-9.3
cd postgresql-9.3.4
./configure --prefix=/opt/pgsql-9.3/ --without-readline
make -j 2
make install

三、配置PG环境变量

vim /etc/profile
export PGHOME=/opt/pgsql-9.3
export PGDATA=/var/lib/pgsql/9.3/data/
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PGHOME/lib/
export PATH=$PGHOME/bin:$PATH:$HOME/bin
source /etc/profile

1、添加postgres用户

useradd postgres -d /var/lib/pgsql

四、配置PG主库

1、初始化PG数据库

su - postgres
initdb

2、启动PG数据库，并创建同步用户

pg_ctl -D /var/lib/pgsql/9.3/data/ -l logfile start
psql
postgres# CREATE ROLE replica login replication encrypted password 'replica';

3、配置用户允许远程登录

vi  /var/lib/pgsql/9.3/data/pg_hba.conf
host    replication     replica     192.168.52.0/24                 trust

4、修改PG主库配置，并重启

vi /var/lib/pgsql/9.3/data/postgresql.conf 
listen_addresses = '*'
wal_level = hot_standby  # 这个是设置主为wal的主机
max_wal_senders = 2 # 这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个
wal_keep_segments = 7 ＃ 设置流复制保留的最多的xlog数目
wal_sender_timeout = 60s ＃ 设置流复制主机发送数据的超时时间
max_connections = 100 # 这个设置要注意下，从库的max_connections必须要大于主库的
pg_ctl -D /var/lib/pgsql/9.3/data/ -l logfile restart

五、配置PG备库

1、从库不用初始化，直接同步主库数据

su - postgres
pg_basebackup -F p --progress -D $PGDATA -h 192.168.52.101 -p 5432 -U replica --password

2、添加一个recovery.conf配置文件

vim  $PGDATA/recovery.conf
standby_mode = on  # 这个说明这台机器为从库
primary_conninfo = 'host=192.168.52.101 port=5432 user=replica password=replica'  # 这个说明这台机器对应主库的信息
trigger_file = '/var/lib/pgsql/9.3/data/postgresql.trigger.192.168.52.101'
recovery_target_timeline = 'latest' # 这个说明这个流复制同步到最新的数据

3、修改PG从库配置

vi $PGDATA/postgresql.conf 
max_connections = 1000 ＃ 一般查多于写的应用从库的最大连接数要比较大
hot_standby = on  ＃ 说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间
wal_receiver_status_interval = 1s  # 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback = on # 如果有错误的数据复制，是否向主进行反馈

六、验证是否正常同步

1、主库查看

$ psql
psql (9.3.4)
Type "help" for help.
postgres=# select client_addr,sync_state from pg_stat_replication;
  client_addr   | sync_state 
----------------+------------
 192.168.52.102 | async
(1 row)
postgres=#

2、通过查看进程

ps aux | grep postgres

主节点有如下进程

postgres  74442  0.0  0.2 258652  2424 ?        Ss   15:02   0:00 postgres: wal sender process replica 192.168.52.102(58199) streaming 0/30101D0

从节点有如下进程

postgres   1676  0.0  0.0 113328   748 ?        Ss   15:01   0:00 postgres: stats collector process

3、通过主库增删改查验证

postgres@master ~]$ psql
psql (9.3.4)
Type "help" for help.
postgres=# create table t_user(id int primary key,name varchar(30));
CREATE TABLE
postgres=# dt
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | t_user | table | postgres
(1 row)
postgres=# insert into t_user values(1,'kbson');
INSERT 0 1
postgres=# insert into t_user values(2,'kbsonlong');                                                          
INSERT 0 1
postgres=# select * from t_user;
 id |   name    
----+-----------
  1 | kbson
  2 | kbsonlong
(2 rows)
postgres=#

从库查看是否同步

[postgres@slave ~]$ psql
psql (9.3.4)
Type "help" for help.
postgres=# dt
No relations found.   ##最开始从库并没有表
postgres=# dt
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | t_user | table | postgres
(1 row)
postgres=# select * from t_user;
 id |   name    
----+-----------
  1 | kbson
  2 | kbsonlong
(2 rows)
postgres=#

七、手动测试切换主从

1、手动停止主库，模拟主库宕机

[postgres@master data]$ pg_ctl stop

此时查看从库会提示连接不上主库

LOG:  replication terminated by primary server
DETAIL:  End of WAL reached on timeline 1 at 0/3000518.
FATAL:  could not send end-of-streaming message to primary: no COPY in progress
LOG:  record with zero length at 0/3000518
FATAL:  could not connect to the primary server: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
FATAL:  could not connect to the primary server: could not connect to server: Connection refused
        Is the server running on host "192.168.52.101" and accepting
        TCP/IP connections on port 5432?
FATAL:  could not connect to the primary server: could not connect to server: Connection refused
        Is the server running on host "192.168.52.101" and accepting
        TCP/IP connections on port 5432?

2、从库手动生成触发文件

在从库生成一个触发文件，就是在配置从库中recovery.conf中配置的trigger_file

[postgres@kbsonlong data]$ touch /var/lib/pgsql/9.3/data/postgresql.trigger.5432
    [postgres@kbsonlong data]$ ll /var/lib/pgsql/9.3/data/postgresql.trigger.5432 
-rw-rw-r-- 1 postgres postgres 0 Jul 30 17:49 /var/lib/pgsql/9.3/data/postgresql.trigger.5432

此时日志会提示找到触发文件，自动切换为新的主库

LOG:  trigger file found: /var/lib/pgsql/9.3/data/postgresql.trigger.5432
LOG:  redo done at 0/30004B0
LOG:  last completed transaction was at log time 2017-07-30 17:38:43.592351+08
LOG:  selected new timeline ID: 2
LOG:  archive recovery complete
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

3、新的主库生成测试数据

在新的主库写入测试数据，方便后面旧的主库恢复为从库是查看是否同步数据

postgres=# select * from t_user;
 id |   name    
----+-----------
  1 | kbson
  2 | kbsonlong
(2 rows)
postgres=# insert into t_user values(3,'kbsonlong3');  
insert into t_user values(4,'kbsonlong4');  
insert into t_user values(5,'kbsonlong5');  INSERT 0 1
postgres=# INSERT 0 1
postgres=# 
INSERT 0 1
postgres=# select * from t_user;
 id |    name    
----+------------
  1 | kbson
  2 | kbsonlong
  3 | kbsonlong3
  4 | kbsonlong4
  5 | kbsonlong5
(5 rows)
postgres=#

4、旧的主库恢复为新的从库

4.1 生成recovery.conf

[postgres@master data]$ cat recovery.conf |grep -v '^#'
    standby_mode = on 
    primary_conninfo = 'host=192.168.52.102 port=5432 user=replica password=replica'   ##注意host指向新的主库
    trigger_file = '/var/lib/pgsql/9.3/data/postgresql.trigger.5432'
    recovery_target_timeline = 'latest'
    [postgres@master data]$

4.2 启动新的从库，并查看数据是否同步

pg_ctl start
[postgres@master data]$ psql 
psql (9.3.4)
Type "help" for help.
postgres=# select * from t_user;
 id |    name    
----+------------
  1 | kbson
  2 | kbsonlong
  3 | kbsonlong3
  4 | kbsonlong4
  5 | kbsonlong5
(5 rows)
postgres=# insert into t_user values(6,'kbsonlong6');  
ERROR:  cannot execute INSERT in a read-only transaction
STATEMENT:  insert into t_user values(6,'kbsonlong6');
ERROR:  cannot execute INSERT in a read-only transaction
postgres=#

可以看到刚才在新的主库写入的测试数据已经同步，并且无法再次新的从库写入数据