Centos7搭建postgresql10主从复制

作者: liurongming | 来源:发表于2021-08-20 11:53 被阅读0次

目录
Centos7搭建postgresql10主从复制
DBA（MySQL）学习-主从复制基础
centos7下面搭建redis5.0哨兵和主从复制集群模式
Day011-MySQL主从复制
postgis 安装和允许远程调用2018-10-20
主从复制（基础）
redis集群
数据库的主从复制
mysql 主从复制

一、环境准备

1、系统及安装包

操作系统：CentOS Linux release 7.9.2009 （虚拟机）
数据库版本：postgresql-10.18-1-linux-x64.run
主服务器IP：172.18.5.129
从服务器IP：172.18.5.137

备注：
1、由于要做主从复制，因此操作系统和数据库版本以及安装路径，均建议保持一致。
2、下载PostgreSQL：https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

2、配置防火墙及selinux

关闭防火墙和selinux【不推荐】

#关闭防火墙
systemctl stop firewalld

#开机禁用防火墙
systemctl disable firewalld

#临时关闭selinux
setenforce 0

#重启机器
reboot

或者不关闭打开端口【推荐】

# 打开端口
firewall-cmd --zone=public --add-port=5432/tcp --permanent
firewall-cmd --reload

注意：每台机器都需要执行相同的操作。

编辑selinux 提醒级别【推荐】

vim /etc/selinux/config

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=permissive
# SELINUXTYPE= can take one of three values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected. 
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

二、安装记录

快捷安装

一键方式安装，相对比较简单，但需要注意几个设置选项。
【推荐】字符集选择es_US.utf8，仅安装PostgreSQL Server 及Command Line Tools即可。

cd /opt 
wget https://get.enterprisedb.com/postgresql/postgresql-10.18-1-linux-x64.run
chmod +x postgresql-10.18-1-linux-x64.run
./postgresql-10.18-1-linux-x64.run
[root@itdev7003 tmp]# ./postgresql-10.18-1-linux-x64.run
----------------------------------------------------------------------------
Welcome to the PostgreSQL Setup Wizard.

----------------------------------------------------------------------------
Select the components you want to install; clear the components you do not want
to install. Click Next when you are ready to continue.

PostgreSQL Server : Y (Cannot be edited)

pgAdmin 4 [Y/n] :n

Stack Builder [Y/n] :n

Command Line Tools : Y (Cannot be edited)

Is the selection above correct? [Y/n]:

安装结束后，查看系统启动文件

[root@itdev9903 ~]# systemctl list-units | grep postgres
postgresql-10.service                                                                            loaded active running   PostgreSQL 10 database server
[root@itdev9903 ~]#

安装完成后，记录安装信息：【每台路径和配置都应相同】

Installation Directory: /opt/PostgreSQL/10
Server Installation Directory: /opt/PostgreSQL/10
Data Directory: /opt/PostgreSQL/10/data
Database Port: 5432
Database Superuser: postgres
Operating System Account: postgres
Database Service: postgresql-10
Command Line Tools Installation Directory: /opt/PostgreSQL/10

打开远程访问

# 添加授权
vim pg_hba.conf
host    all             all             0.0.0.0/0               md5

# 检出是否为 *  
vim postgresql.conf
listen_addresses = '*'

# 重启
[root@itdev9903 ~]# systemctl list-units | grep postgres
postgresql-10.service                                                                            loaded active running   PostgreSQL 10 database server
[root@itdev9903 ~]#
systemctl restart postgresql-10.service

设置环境变量

vim /etc/profile
# 设置内容
PG_HOME=/opt/PostgreSQL/10
PATH=$PATH:$PG_HOME/bin
export PATH PG_HOME
# 立即生效
source /etc/profile

登录数据库

1、切换用户登录

su - postgres
bin/psql

2、不切换用户登录

psql -h hostname -U username -d dbname -p port

三、主从设置

Master：172.18.5.129
Standby：172.18.5.137

1、主库设置

创建同步账号
登陆Master库，创建具有用于传递数据的具有replication权限的用户【也可以直接用Super user当作replication用户，但不推荐】。

CREATE ROLE repl login replication password '123456';

postgres=# CREATE ROLE repl login replication password '123456';
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repl      | Replication                                                | {}

postgres=#

Master库网络策略

1、修改Master库的pg_hba.conf，把Master库和Standby库的IP地址添加进Master库网络策略白名单中，使Standby库可以连上 Master库，同时便于主备切换。
添加内容：

# 开启远程访问
host    all             all             0.0.0.0/0               md5
# 主从库地址均配上
host    replication     repl            172.18.5.129/32         md5
host    replication     repl            172.18.5.137/32         md5

cd /opt/PostgreSQL/10/data
vi pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
# user add
host    replication     repl            172.18.5.129/32         md5
host    replication     repl            172.18.5.137/32         md5

2、修改master库：postgresql.conf文件。
3、新建归档目录： mkdir /opt/PostgreSQL/10/data/arch_dir /opt/PostgreSQL/10/data/arch_dir_master

cd /opt/PostgreSQL/10/data
mkdir {arch_dir,arch_dir_master}
chown -R postgres:postgres  arch_dir
chown -R postgres:postgres  arch_dir_master

vim postgresql.conf
wal_level = logical
max_wal_senders = 10
archive_mode = on 
archive_command =  'test ! -f /opt/PostgreSQL/10/data/arch_dir/%f && cp %p /opt/PostgreSQL/10/data/arch_dir/%f'
synchronous_standby_names = ''  # standby servers that provide sync rep
hot_standby = on

4、当master切换作为standby库时，使用同步配置文件为recovery.done。

cd /opt/PostgreSQL/10/data
vi recovery.done
standby_mode=on
restore_command = 'cp /opt/PostgreSQL/10/data/arch_dir_master/%f %p'
primary_conninfo='application_name=pg2 host=172.18.5.137 port=5432 user=repl password=123456'
archive_cleanup_command ='pg_archivecleanup /opt/PostgreSQL/10/data/arch_dir_master %r'
recovery_target_timeline = 'latest'

# 改变用户及属组
chown postgres:postgres recovery.done

5、重启master主库systemctl restart postgresql-10.service 。至此主库上的操作已完成。接下来看看standby备库的。

2、从库设置

standby库需要以master库的完整备份+归档日志恢复而来，如果master库尚未对外提供服务，也可以直接复制master库的数据文件目录，这里采用第一种方法，更贴近实际环境。
使用主库的热备创建standby库(此步骤在主库执行)

# 从库直接连接主库
psql -h 172.18.5.129 -p 5432 -U postgres -d postgres
 
postgres=# select pg_start_Backup('backuptag',true);
 pg_start_backup
-----------------
 0/2000028
(1 row)

postgres=#

回到从库机器

# 停止数据库
systemctl stop postgresql-10.service

# 进入目录
cd /opt/PostgreSQL/10/data
# 备份
mv data/ data.bak
# 拷贝主
scp -r root@172.18.5.129:/opt/PostgreSQL/10/data /opt/PostgreSQL/10
# 改变权限
chown -R postgres:postgres data

# 切换主库
psql -h 172.18.5.129 -p 5432 -U postgres -d postgres
# 停止主库的热备锁定
[root@itdev7003 10]# psql -h 172.18.5.129 -p 5432 -U postgres -d postgres
Password for user postgres:
psql.bin (10.18)
Type "help" for help.

postgres=# select pg_stop_backup();
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup
----------------
 0/2000130
(1 row)

postgres=#

清理复制过来的主库文件

rm -rf /opt/PostgreSQL/10/data/pg_wal
rm -rf /opt/PostgreSQL/10/data/postmaster.pid
rm -rf /opt/PostgreSQL/10/data/arch_dir/*

修改备库的recovery文件

# 进入目录
cd /opt/PostgreSQL/10/data
mv recovery.done recovery.conf
# 修改IP指向主
standby_mode=on
restore_command = 'cp /opt/PostgreSQL/10/data/arch_dir_master/%f %p'
primary_conninfo='application_name=pg3 host=172.18.5.129 port=5432 user=repl password=123456'
archive_cleanup_command ='pg_archivecleanup /opt/PostgreSQL/10/data/arch_dir_master %r'
recovery_target_timeline = 'latest'

模拟刷新的wal和归档文件

# 清空主库复制文件
rm -rf /opt/PostgreSQL/10/data/pg_wal
rm -rf /opt/PostgreSQL/10/data/postmaster.pid
rm -rf /opt/PostgreSQL/10/data/arch_dir/*

# 物理拷贝
scp -r root@172.18.5.129:/opt/PostgreSQL/10/data/pg_wal /opt/PostgreSQL/10/data/
scp -r root@172.18.5.129:/opt/PostgreSQL/10/data/arch_dir/. /opt/PostgreSQL/10/data/arch_dir_master
 
# 更换权限
chown -R postgres:postgres /opt/PostgreSQL/10/data

或者是使用basebackup命令进行热备【不推荐】：默认只复制更改部分，当前从库提升为主库时，可能会有问题。

pg_basebackup -D /opt/PostgreSQL/10/data/ -F p -X stream -v -P -h 172.18.5.129 -p 5432 -U repl

启动备库，观察备库日志

# 启动数据库
systemctl  start postgresql-10.service
# 查看日志
cd /opt/PostgreSQL/10/data/log
ll -t |grep .log| awk 'NR==1{print$NF}'|xargs -i tail -f {}

# 可以看到 database system is ready to accept read only connections 
# 和 started streaming WAL from primary at 0/F000000 on timeline 1 
# 从库启动成功
[root@itdev7003 log]# ll -t |grep .log| awk 'NR==1{print$NF}'|xargs -i tail -f {}
2021-08-20 11:43:01.711 CST [5941] LOG:  database system was shut down in recovery at 2021-08-20 11:43:01 CST
cp: no se puede efectuar `stat' sobre «/opt/PostgreSQL/10/data/arch_dir_master/00000002.history»: No existe el fichero o el directorio
2021-08-20 11:43:01.739 CST [5941] LOG:  entering standby mode
cp: no se puede efectuar `stat' sobre «/opt/PostgreSQL/10/data/arch_dir_master/00000001000000000000000F»: No existe el fichero o el directorio
2021-08-20 11:43:01.746 CST [5941] LOG:  redo starts at 0/F0004A0
2021-08-20 11:43:01.746 CST [5941] LOG:  consistent recovery state reached at 0/F000580
2021-08-20 11:43:01.746 CST [5941] LOG:  invalid record length at 0/F000580: wanted 24, got 0
2021-08-20 11:43:01.746 CST [5939] LOG:  database system is ready to accept read only connections
2021-08-20 11:43:01.754 CST [5947] LOG:  started streaming WAL from primary at 0/F000000 on timeline 1

至此，主从同步搭建完毕。接下来还可以通过进程检查。

# 主库相关进程 【注writer process已启动】
[root@itdev9903 ~]#  ps -ef | grep postgres
postgres 30570 30734  0 11:43 ?        00:00:00 postgres: wal sender process repl 172.18.5.137(53308) streaming 0/F000580
postgres 30734     1  0 Aug18 ?        00:00:01 /opt/PostgreSQL/10/bin/postgres -D /opt/PostgreSQL/10/data
postgres 30735 30734  0 Aug18 ?        00:00:00 postgres: logger process
postgres 30737 30734  0 Aug18 ?        00:00:00 postgres: checkpointer process
postgres 30738 30734  0 Aug18 ?        00:00:01 postgres: writer process
postgres 30739 30734  0 Aug18 ?        00:00:01 postgres: wal writer process
postgres 30740 30734  0 Aug18 ?        00:00:00 postgres: autovacuum launcher process
postgres 30741 30734  0 Aug18 ?        00:00:00 postgres: archiver process   last was 00000001000000000000000E.00000028.backup
postgres 30742 30734  0 Aug18 ?        00:00:01 postgres: stats collector process
postgres 30743 30734  0 Aug18 ?        00:00:00 postgres: bgworker: logical replication launcher
root     30865 30823  0 11:48 pts/0    00:00:00 grep --color=auto postgres
[root@itdev9903 ~]#

# 从库相关进程 【注wal receiver process已启动】
[root@itdev7003 ~]#  ps -ef | grep postgres
root      5674  5646  0 11:38 pts/1    00:00:00 grep --color=auto postgres
postgres 28138     1  0 Aug18 ?        00:00:00 /opt/PostgreSQL/10/bin/postgres -D /opt/PostgreSQL/10/data
postgres 28139 28138  0 Aug18 ?        00:00:00 postgres: logger process
postgres 28140 28138  0 Aug18 ?        00:00:01 postgres: startup process   recovering 00000001000000000000000F
postgres 28144 28138  0 Aug18 ?        00:00:01 postgres: checkpointer process
postgres 28145 28138  0 Aug18 ?        00:00:01 postgres: writer process
postgres 28146 28138  0 Aug18 ?        00:00:00 postgres: stats collector process
postgres 28158 28138  0 Aug18 ?        00:01:33 postgres: wal receiver process   streaming 0/F000580
[root@itdev7003 ~]#

3、从库测试

Master建立T1表,并插入数据：

psql -U postgres
postgres=# create table t1(id varchar(32),name varchar(64));
postgres=# \d t1
                        Table "public.t1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 id     | character varying(32) |           |          |
 name   | character varying(64) |           |          |

postgres=#
# 插入数据后，查询
postgres=# insert into t1 values('1','zhangsan');
postgres=# insert into t1 values('2','lisi');
postgres=# select * from t1;
 id |   name
----+-----------
 1  | zhangshan
 2  | lisi
(2 rows)

postgres=#

在Slaves数据中查看：【从库：提示只读】

psql -U postgres
postgres=# \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | t1   | table | postgres
(1 row)

postgres=# select * from t1;
 id |   name
----+-----------
 1  | zhangshan
 2  | lisi
(2 rows)

postgres=#
postgres=# create table t1(id varchar(32),name varchar(64));
ERROR:  cannot execute CREATE TABLE in a read-only transaction
postgres=#
postgres=# insert into t1 values('3','wangwu');
ERROR:  cannot execute INSERT in a read-only transaction
postgres=

通过测试情况可知：
1、主库建表及插入数据，从库可以同步获得。
2、从库无论建表还是插入数据，均提示只读。

至此，postgresql10 主从数据库成功搭建完成。

附录：

psql常用命令

# (1)列出所有的数据库
mysql: show databases
psql: \l或\list

# (2)切换数据库
mysql: use dbname
psql: \c dbname
 
# (3)列出当前数据库下的数据表
mysql: show tables
psql: \d
 
# (4)列出指定表的所有字段
mysql: show columns from table name
psql: \d tablename
 
# (5)查看指定表的基本情况
mysql: describe tablename
psql: \d+ tablename
 
# (6)退出登录
mysql: quit 或者\q
psql:\q

目录
centos7联网安装postgresql10
Centos7搭建postgresql10主从复制
一、环境准备 1、系统及安装包操作系统：CentOS Linux release 7.9.2009 （虚拟机）...
DBA（MySQL）学习-主从复制基础
1. 主从复制 1.1 介绍 1.2 主从复制的前提（如何搭建主从复制） 2. 搭建主从复制 2.1 搭建多实例 ...
centos7下面搭建redis5.0哨兵和主从复制集群模式
centos7下面搭建redis5.0哨兵和主从复制集群模式本文介绍的都是在一台虚拟机上搭建的,真实物理机下面可...
Day011-MySQL主从复制
1. 主从复制介绍 2. 主从复制的前提（搭建主从复制） 3. 搭建主从复制步骤 3.1 准备多实例 3.2 检查...
postgis 安装和允许远程调用2018-10-20
系统：centos7 数据库：Postgresql10 数据库管理工具：Navicat Premium 首先是获取...
主从复制（基础）
01，主从复制简介 02，主从搭建简述 03，主从复制搭建(Classic replication) 1 清理主库...
redis集群
一，主从复制搭建哨兵
数据库的主从复制
1.介绍 2.主从复制的前提 3、搭建主从复制
mysql 主从复制
mysql 主从复制网易数据库石勇提纲什么是主从复制主从复制的原理主从复制的用途主从复制的搭建主从...