pg数据库异步同步

[TOC]

pg数据库主备安装（异步同步）

主库配置：

1：在主服务器安装PostgreSQL，主服务器初始化并启动PostgreSQL并作如下配置。

[root@pg01 ~]# su - postgres
[postgres@pg01 ~]$ pg_ctl start
[postgres@pg01 ~]$ psql

2：主PostgreSQL上，创建备份用户，并设置权限：

postgres=# create user standby_user CREATEDB SUPERUSER LOGIN REPLICATION password '123456';
postgres=# \du
                                     List of roles
  Role name   |                         Attributes                         | Member of 
--------------+------------------------------------------------------------+-----------
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 standby_user | Superuser, Create DB, Replication                          | {}

3：修改pg_hba.conf指定从服务器，赋予replication权限并刷新配置文件

[postgres@pg01 ~]$ vim /data/pg_data/pg_hba.conf
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             10.0.0.31/16            md5
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             10.0.0.31/16            md5
host    replication     all             ::1/128                 trust

刷新配置文件

[postgres@pg01 ~]$ pg_ctl reload
2020-08-05 21:01:34.603 CST [23391] LOG:  received SIGHUP, reloading configuration files

4：修改主PostgreSQL复制参数，并重启服务

[postgres@pg01 ~]$ vim /data/pg_data/postgresql.conf
max_wal_senders = 10  #连接数
wal_level = replica  #流复制，它的值应设置为replica
wal_log_hints = on  #在PostgreSQL服务器一个检查点之后页面被第一次修改期间，把该磁盘页面的整个内容都写入 WAL，即使对所谓的提示位做非关键修改也会这样做
wal_keep_segments = 10  #指定在后备服务器需要为流复制获取日志段文件的情况下，目录下所能保留的过去日志文件段的最小数目

重启

[postgres@pg01 ~]$ pg_ctl restart

备份服务器配置：

1：备份服务器安装pg，但是不需要执行初始化数据库，需要执行下面的基础备份

hostnamectl set-hostname pg02

2：修改目录权限

[root@pg02 ~]# chmod -R 0700 /data/pg_data/

3：在备PostgreSQL进行基础备份：

[root@pg02 postgresql-11.8]# su - postgres
[postgres@pg02 ~]$ pg_basebackup -Xs -h10.0.0.11 -p5432 -Ustandby_user -D $PGDATA -v -Fp
pg_basebackup: could not connect to server: could not connect to server: Connection refused
 Is the server running on host "10.0.0.11" and accepting
 TCP/IP connections on port 5432?

处理报错问题，修改主服务器监听地址

[postgres@pg01 ~]$ vim /data/pg_data/postgresql.conf
listen_addresses = '*'
port = 5432
[postgres@pg01 ~]$ pg_ctl restart

从服务器测试连接主服务器

[postgres@pg02 ~]$ psql -h 10.0.0.11 -p5432 -Ustandby_user -d postgres
Password for user standby_user: 123456
psql (11.8)
Type "help" for help.

postgres=#

再次测试在备PostgreSQL进行基础备份

[postgres@pg02 ~]$ pg_basebackup -Xs -h10.0.0.11 -p5432 -Ustandby_user -D $PGDATA -v -Fp
Password: 123456
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_23461"
pg_basebackup: write-ahead log end point: 0/20000F8
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

4：设置备份服务器参数

[postgres@pg02 ~]$ vim /data/pg_data/postgresql.conf
hot_standby = on  #开启备份
wal_receiver_status_interval = 5s  #多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback = on  #如果有错误的数据复制，是否向主进行反馈

5：复制recovery.conf文件并启动postgresql

[postgres@pg02 ~]$ cp /data/pgsql/share/recovery.conf.sample /data/pg_data/recovery.conf
[postgres@pg02 ~]$ vim /data/pg_data/recovery.conf
standby_mode = on
primary_conninfo = 'host=10.0.0.11 port=5432 user=standby_user password=123456'
trigger_file = 'tgfile' 
recovery_target_timeline = 'latest'

启动

[postgres@pg02 ~]$ pg_ctl start

6：测试主备

[postgres@pg02 ~]$ psql -Upostgres -d postgres
postgres-# \l

[postgres@pg01 ~]$ psql -Upostgres -d postgres
postgres=# select * from pg_stat_replication;
postgres=# \x #以列表来显示
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 23464
usesysid         | 16384
usename          | standby_user
application_name | walreceiver
client_addr      | 10.0.0.31
client_hostname  |
client_port      | 51924
backend_start    | 2020-08-05 21:06:32.011619+08
backend_xmin     | 569
state            | streaming
sent_lsn         | 0/3000060
write_lsn        | 0/3000060
flush_lsn        | 0/3000060
replay_lsn       | 0/3000060
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async

postgres=# create database cp_test;
CREATE DATABASE
postgres=# \c cp_test
You are now connected to database "cp_test" as user "postgres".
cp_test=# create table test(id int,name varchar(10));
CREATE TABLE
cp_test=# insert into test (id,name) values (111,'sss');
INSERT 0 1

在备服务器查看数据库（出现cp_test库）

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 cp_test   | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

postgres-# \c cp_test
You are now connected to database "cp_test" as user "postgres".
cp_test=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | test | table | postgres
(1 row)
cp_test=# select * from test;
 id  | name
-----+------
 111 | sss
(1 row)

pg数据库 异步同步

主库配置：

备份服务器配置：

pg数据库异步同步