首页 pg数据库主备安装(异步同步)

pg数据库主备安装(异步同步)

技术张儿 2020-8-5 0 30
标签: postgresql

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
1.png

刷新配置文件
[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)


作者: 技术张儿 本文发布于2020-8-5 08:53:30
免责声明:本文仅代表作者个人观点。