备份数据库

• 备份脚本

#!/bin/bash

#by yangfk

#设置数据库密码
export PGPASSWORD="pgsqlpasswd"
Date=$(date +%F_%H%M)
Path=$(cd $(dirname $0); pwd)

#导出数据库清单
psql -U postgres -h 127.0.0.1  -c "\l" |awk '{print $1}'|sed -nr '4,$p'|grep -vE "postgres|template|\||\(" > ${Path}/database.txt
sed -i '/^[[:space:]]*$/d'  ${Path}/database.txt


#导出数据库
test -d ${Path}/pgsqlbackup-${Date}/ || mkdir -p ${Path}/pgsqlbackup-${Date}/
while read database
do
    pg_dump -h 127.0.0.1 -p 5432 -U postgres -C -F p -b -d $database > ${Path}/pgsqlbackup-${Date}/${database}.bak
done < ${Path}/database.txt

#打包保留7天
cd ${Path} 
tar zcf pgsqlbackup-${Date}.tar.gz pgsqlbackup-${Date} && rm -rf ${Path}/pgsqlbackup-${Date}
find ${Path}/ -mtime +15 -type f -name "pgsqlbackup-*" -exec rm -f {} \;

导出导入

• 导出

pg_dump -h 127.0.0.1 -p 5432 -U postgres -C -F p -b -d dbname -f dbname.sql

• 导入

psql -h 127.0.0.1 -p 5432 -U postgres -f dbname.sql

pg_dump参数

pg_dump 把一个数据库转储为纯文本文件或者是其它格式.

用法: pg_dump [选项]... [数据库名字]

参数	说明
一般选项
-f, --file=FILENAME	output file or directory name
-F,--format=c|d|t|p	output file format (custom, directory, tar, plain text)
-v, --verbose	详细模式
-Z, --compress=0-9	被压缩格式的压缩级别
--lock-wait-timeout=TIMEOUT	在等待表锁超时后操作失败
--help	显示此帮助信息, 然后退出
--versoin	输出版本信息, 然后退出
控制输出内容选项
-a, --data-only	只转储数据,不包括模式
-b, --blobs	在转储中包括大对象
-c, --clean	在重新创建之前，先清除（删除）数据库对象
-C, --create	在转储中包括命令,以便创建数据库
-E, --encoding=ENCODING	转储以ENCODING形式编码的数据
-n, --schema=SCHEMA	只转储指定名称的模式
-N, --exclude-schema=SCHEMA	不转储已命名的模式
-o, --oids	在转储中包括 OID
-O, --no-owner	在明文格式中, 忽略恢复对象所属者
-s, --schema-only	只转储模式, 不包括数据
-S, --superuser=NAME	在转储中, 指定的超级用户名
-t, --table=TABLE	只转储指定名称的表
-T, --exclude-table=TABLE	不转储指定名称的表
--exclude-table-data=public.tb_record	不备份模式 public下的 tb_record 表
-x, --no-privileges	不要转储权限 (grant/revoke)
--binary-upgrade	只能由升级工具使用
--column-inserts	以带有列名的INSERT命令形式转储数据
--disable-dollar-quoting	取消美元 (符号) 引号, 使用 SQL 标准引号
--disable-triggers	在只恢复数据的过程中禁用触发器
--inserts	以INSERT命令，而不是COPY命令的形式转储数据
--no-security-labels	do not dump security label assignments
--no-tablespaces	不转储表空间分配信息
--no-unlogged-table-data	do not dump unlogged table data
--quote-all-identifiers	quote all identifiers, even if not key words
--serializable-deferrable	wait until the dump can run without anomalies
--use-set-session-authorization	使用 SESSION AUTHORIZATION 命令代替ALTER OWNER 命令来设置所有权

主从复制

注意：主从延迟一定要注意时间同步问题,点击我🤞🤞

• 服务器说明

IP	角色
192.168.5.8	master
192.168.5.223	slave

master操作

主库创建复制用户

#登录数据库
create role replica login replication encrypted password 'replica@pgsql';
# 查询用户是否创建成功
SELECT usename from pg_user;
# 查询对应权限是否存在
SELECT rolname from pg_roles;

主库配置文件修改

• pg_hpa.conf

[root@pgsql01 data]# cat pg_hba.conf

local   all             all                                     md5
host    all             all             0.0.0.0/0               md5
host    replication 	replica		192.168.5.186/24            md5

• 主库postgresql.conf

[root@pgsql01 data]# cat postgresql.conf

listen_addresses='*'
max_wal_size = 1GB
min_wal_size = 80MB
log_timezone = 'Asia/Shanghai'
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
default_text_search_config = 'pg_catalog.english'
max_connections = 1000 # 一般查多于写的应用从库的最大连接数要比较大
wal_level = replica  # 这个是设置主为wal的主机

#以下是新增的内容
max_wal_senders = 5 # 这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个
wal_keep_segments = 128 # 设置流复制保留的最多的xlog数目
wal_sender_timeout = 60s # 设置流复制主机发送数据的超时时间
hot_standby = on # 说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间
wal_receiver_status_interval = 10s # 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback = off # 如果有错误的数据复制，是否向主进行反馈
wal_log_hints = on

slave操作

从节点安装postgreSQL

• 查找命令

pg_basebackup命令找不到？？？ 先查看psql命令是在哪里，which psql,然后 ls -l 显示的路径

将master中的数据导入到slave节点

从数据库存储数据路径为/data/pgsql/data

• 修改systemd（我这里是yum安装12）

sed -i 's#^Environment=PGDATA.*#Environment=PGDATA=/data/pgsql/data/#g' /usr/lib/systemd/system/postgresql-12.service
systemctl daemon-reload

mkdir /data/pgsql/data #默认是没有这个目录
pg_basebackup -D /data/pgsql/data -h 192.168.5.8 -p 5432 -U replica -X stream -P
#从密码：replica@pgsql

添加standby.signal文件，设置只读模式

记录：9.0 的版本是recovery.conf，12 的版本是standby.signal

echo 'standby_mode = "on"' >> /data/pgsql/data/standby.signal

修改slave配置文件postgresql.conf

• 从库postgresql.conf

[root@pgsql02 data]# cat postgresql.conf

listen_addresses='*'
max_wal_size = 1GB
min_wal_size = 80MB
log_timezone = 'Asia/Shanghai'
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
default_text_search_config = 'pg_catalog.english'
max_connections = 1000 # 大于等于主节点，正式环境应当重新考虑此值的大小
wal_level = replica  # 这个是设置主为wal的主机

#以下是新增的内容
max_wal_senders = 5 # 这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个
wal_keep_segments = 128 # 设置流复制保留的最多的xlog数目
wal_sender_timeout = 60s # 设置流复制主机发送数据的超时时间
hot_standby = on # 说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间
wal_receiver_status_interval = 10s # 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback = off # 如果有错误的数据复制，是否向主进行反馈
wal_log_hints = on

#以下是从库新加的参数
primary_conninfo = 'host=192.168.5.8 port=5432 user=replica password=replica@pgsql' #主库复制用户连接地址
recovery_target_timeline = latest # 默认
recovery_min_apply_delay = 2h # slave 恢复来自于 master 节点的 WAL 记录。延时拷贝，提供纠正数据丢失问题。

修改从库数据库文件权限

chmod 700 /data/pgsql/data
chmod 600 /data/pgsql/data/postgresql.conf
chmod 600 /data/pgsql/data/standby.signal
chown -R postgres:postgres /data/pgsql/data

• 启动服务

systemctl restart postgresql-12

检查服务状态

• master操作

命令行操作显示 主库为wal sender

ps -ef|grep postg |grep wal

/usr/pgsql-12/bin/pg_controldata -D /data/pgsql/data |grep 'Database cluster state' 注意：数据目录位置

• 登录数据库执行sql检查

postgres=# select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
 pid  |   state   |  client_addr  | sync_priority | sync_state 
------+-----------+---------------+---------------+------------
 1925 | streaming | 192.168.5.223 |             0 | async
(1 row)

postgres=#  select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f  #这里为 false
(1 row)

• slave操作

命令行操作显示 备库为wal receiver

ps -ef|grep postg |grep wal

• 登录数据库执行sql检查

postgres=# select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
 pid | state | client_addr | sync_priority | sync_state 
-----+-------+-------------+---------------+------------
(0 rows)

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t  #这里为 true
(1 row)

• pg_stat_replication参数说明

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 3201       # sender的进程
usesysid         | 16385      # 复制的用户id
usename          | replica    # 复制的用户用户名
application_name | walreceiver  
client_addr      | 192.168.5.223 # 复制的客户端地址
client_hostname  |
client_port      | 58366  # 复制的客户端端口
backend_start    | 2021-09-24 18:50:03.72794+08  # 这个主从搭建的时间
backend_xmin     |
state            | streaming  # 同步状态 startup: 连接中、catchup: 同步中、streaming: 同步
sent_location    | 3/50016D0 # Master传送WAL的位置
write_location   | 3/50016D0 # Slave接收WAL的位置
flush_location   | 3/50016D0 # Slave同步到磁盘的WAL位置
replay_location  | 3/50016D0 # Slave同步到数据库的WAL位置
sync_priority    | 0  #同步Replication的优先度
                      0: 异步、1～?: 同步(数字越小优先度越高)
sync_state       | async  # 有三个值，async: 异步、sync: 同步、potential: 虽然现在是异步模式，但是有可能升级到同步模式

插入数据检查同步情况

注意：从库配置postgres.conf 里面有个 recovery_min_apply_delay = 2h 这个时间设置的2h ,测试同步把时间可设置为秒单位

cd /data/pgsql/data
sed -i 's#^recovery_min_apply_delay.*#recovery_min_apply_delay=30s#g' postgresql.conf
systemctl restart postgresql-12

主库执行SQL，创建 testdb 库，testtb 表，插入数据

create database testdb;

CREATE TABLE testtb (     
    product_no integer,
    name text,
    price numeric
);

INSERT INTO testtb VALUES (1, 'yyy', 2.10);
INSERT INTO testtb VALUES (2, 'fff', 0.11);
INSERT INTO testtb VALUES (2, 'kkk', 0.12);

select * from testtb;

记录：延迟复制，这里的wal日志和mysql也是类似，当从库设置过延迟时间，主库有新数据插入，会立即把wal日志复制到从库，但不会立即执行sql,等延迟时间过了才会执行sql

测试：设置从库延迟时间为1分钟，主从状态正常，在主库插入几条数据，停掉主库，检查从库数据，会发现过了一分钟后，从库的数据会增加主库操作的数据

从切换为主

/usr/pgsql-12/bin/pg_ctl promote -D /data/pgsql/data

主从切换需要切换到postgres用户执行， -D 指定数据目录，执行结束会发现 standby.signal 文件删除了