pgsql日志管理和备份
1、日志
1.1、运行日志
记录的服务运行情况信息,12版本之前默认不开启
su - postgres
vim /var/lib/pgsql/14/data/postgresql.conf
log_destination = 'stderr' #日志记录格式stderr, csvlog, syslog, eventlog,csv是Excel表格类型,使用多种会记录多种格式
logging_collector = on #开启日志功能
log_directory = 'log' #日志的存放位置,默认在$PGDATA/log
log_filename = 'postgresql-%a.log' #日志命名格式
log_rotation_age = 1d #日志文件的最长生命期,到时间就会创建一个新的日志文件
log_rotation_size = 0 #日志文件的最大尺寸,0表示禁用基于尺寸的新日志文件创建
log_truncate_on_rotation = on #如新创建一个同名的日志文件,会清空原文件再写入而不是在后面追加
#copy命令可将文件内容和数据库的表内容相互导入,from是将文件导入表里,to是将表内容导入文件
copy pg_log from /var/lib/pgsql/14/data/log/postgresql-Sat.log
#可将日志记录文件改为csvlog,创建相关的表结构,将其导入文件,便于管理
将pgsql新版本的运行日志存储到数据库。
#13版本
[root@ubuntu1804 ~]$vim /pgsql/data/postgresql.conf
log_destination = 'csvlog'
logging_collector = on # Enable capturing of stderr and csvlog
[root@ubuntu1804 ~]$pg_ctl restart
CREATE table postgres_log
(
log_time timestamp(3) with time zone,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text,
backend_type text,
PRIMARY KEY (session_id, session_line_num)
)
copy postgres_log from '/pgsql/data/log/postgresql-2022-11-30_100805.csv' with csv;
1.2、WAL日志
在线日志,相当于mysql的事务日志,存放的是正在发生的事务,提交或未提交都有记录,默认大小16M,仅初始化环境时更改
[ root@rocky2 ~]$ls /var/lib/pgsql/14/data/pg_wal/
000000010000000000000001 archive_status
#大事务执行时文件可累积的大小
vim /var/lib/pgsql/14/data/postgresql.conf
max_wal_size = 1GB
min_wal_size = 80MB
#手动切换WAl
select pg_switch_wal();
#查看WAL日志文件,没设置PTAH变量的需写全路径
pg_waldump /var/lib/pgsql/14/data/pg_wal/000000010000000000000001
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/017686D0, prev 0/01768658, desc: RUNNING_XACTS nextXid 734 latestCompletedXid 733 oldestRunningXid 734
#tx:后面是事务ID
#创建还原点相当于快照
select pg_create_restore_point('test-restore-point');
1.2.1、LSN
日志序列号,WAL日志全局唯一的标识,每个事件都有唯一的LSN号,多个事件组成一个动作,多个动作构成一个事务
00000001 00000000 00000001
时间线 逻辑id 物理id
其中前8位:00000001表示timeline
中间8位: 00000000表示logid,即LSN高32位
最后8位: 00000001表示logseg,即LSN低32位/(2*24) 的值,即低32位中最高8位,16进制的高2位
#查看当前lsn号
select pg_current_wal_lsn();
#查看当前lsn号对应的日志文件
select pg_walfile_name(pg_current_wal_lsn());
#查看当前事务编号
select pg_current_wal_lsn();
1.3、归档WAL日志
只要发现WAL日志文件有更新切换就备份,正在使用的不会备份,远程与本地仅可选一方
vim /var/lib/pgsql/14/data/postgresql.conf
wal_level = replica # minimal, replica, or logical 启用记录日志级别
archive_mode = on #打开日志归档功能
archive_command = '[ ! -f /postgres/%f ] && cp %p /postgres/%f' #需提前创建postgres文件夹并更改属性权限
archive_command = 'DIR=/postgres/`date +%F`;[ -d $DIR ] || mkdir -p #DIR;cp %p $DIR/$f'
archive_command = 'scp %p postgres@10.0.0.8:/postgres/%f' #远程备份到其他主机,基于key验证可免密
# 备份文件存放路径,调用的shell命令
# placeholders: %p = 要归档的文件的路径,全路径
# %f = 只要全路径下的文件名
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f' 官方范例
pg_ctl restart
ll /postgres/
2、备份
2.1、pg_dump和pg_dumpall备份
范例:
#用账号postgres备份test数据库存放在目录/backup/test_backup下
pg_dump -U postgres -f /backup/test_backup test
#远程
pg_dump -h 10.0.0.200 -U postgres -f /backup/test_backup test
#可将所备份的文件导入到数据库hellodb2中,hellodb2需事先存在且为空
psql -h 10.0.0.200 -d hellodb2 -U postgres -f /backup/test_backup
#-C选项如数据库不存在,在还原时会创建以原先所备份的数据库的创建信息来自动创建,大写
pg_dump -h 10.0.0.200 -U postgres -C -f /backup/test_backup test
psql -h 10.0.0.200 -U postgres -f /backup/test_backup #还原
#备份全部数据库,有几个数据库就输入几次密码,且会备份数据库账号信息和创建数据库的指令
pg_dumpall -h 10.0.0.200 -U postgres -f /backup/full_backup.sql
psql -h 10.0.0.200 -U postgres -f /backup/full_backup.sql #还原,原有的数据则不还原
2.2、pg_restore还原
范例
#指定备份格式c,小写
pg_dump -Fc -C -h 10.0.0.200 -U postgres hellodb > /backup/hellodb.dump
#还原数据到hellodb3数据库中,也可不指定
pg_restore -h 10.0.0.200 -U postgres -d hellodb3 /backup/hellodb.dump
#查看备份数据/backup/hellodb.dump的源数据信息
pg_restore -l /backup/hellodb.dump
2.3、copy指令
#copy命令可将文件内容和数据库的表内容相互导入,from是将文件导入表里,to是将表内容导入文件,文件提前创建,属性权限需更改
copy pg_log from /var/lib/pgsql/14/data/log/postgresql-Sat.log
#可将日志记录文件改为csvlog,创建相关的表结构,将其导入文件,便于管理
#拷贝表students的内容到文件/tmp/students.csv中,指定格式为csv,with csv
copy students to '/tmp/students.csv' with csv;
#csv格式,通用格式,兼容性高,可跨操作系统、版本、平台,可以在mysql、postgresql、oracle使用,也可在win系统使用
#挑特定字段,header选项可带表头,还原时也要带上该选项
copy students(stuid,name) to '/tmp/students.csv' with csv header;
#调用select语句
copy (select * from students) to '/tmp/students.csv' with csv;
#还原,表需事先存在,且表结构一致
copy students from '/tmp/students.csv' with csv;
2.4、流复制具体实现
两台主机,一主master,一从standby
版本需相同
2.4.1、在主节点
#创建复制的用户并授权repluser
[postgres@master ~]$ psql
postgres=#create role repluser with replication login password '123456';
#修改pg_hba.conf进行授权
[postgres@master ~]$vi /pgsql/data/pg_hba.conf
host replication repluser 0.0.0.0/0 md5
#修改配置(可选)按需选择:
[postgres@master ~]$vi /pgsq1/data/postgresq1. conf
synchronous_standby_names = '*' #可对*命名
#开启此项,表示同步方式为同步复制,需要同时打开synchronous_commit = on,默认是异步复制
archive_mode = on #建议打开归档模式,防止长时间无法同步,WAL被覆盖造成数据丢失
archive_command = '[ ! -f /archive/%f ] && cp %p /archive/%f'
wal_level = replica #设置wal的级别
max_wal_senders = 5 #这个设置可以最多有几个流复制连接,一般有几个从节点就设置几个
wal_keep_segments = 128 #设置流复制保留的最多的WAL文件数目
wal_sender_timeout =60s #设置流复制主机发送数据的超时时间
max_connections =200 #一般查多于写的应用从库的最大连接数要比较大
hot_standby = on #对主库无影响,用于将来可能会成为从库,这台机器不仅仅是用于数据归档,也用于数据查询,在从库上配置此项后为只读
max_standby_streaming_delay = 30s #数据流备份的最大延迟时间
wal_receiver_status_interval = 10s #多久向主报告一次从的状态,当然从每次数据复制都会向主报告状态,只是设置最长的间隔时间
hot_standby_feedback = on #如果有错误的数据复制,是否向主进行反馈
wal_log_hints = on #对非关键更新进行整页写入
[postgres@master ~]$pg_ctl restart
2.4.2、在从节点
#清空数据和归档
[postgres@standby ~]$ pg_ctl stop -D $PGDATA
[postgres@standby ~]$ rm -rf /pgsql/data/*
[postgres@standby ~]$ mkdir -p /archive/ /pgsql/backup/
[postgres@standby ~]$ chown postgres. /archive/
[postgres@standby ~]$ chown postgres. /pgsql/backup/
#备份主库数据到备库
[postgres@standby ~]$ pg_basebackup -D /pgsql/backup/ -Ft -PV -Urepluser -h 10.0.0.200 -p 5432 -R
#还原备份的数据,实现初始的主从数据同步
[postgres@standby ~]$ tar xf /pgsql/backup/base.tar -C /pgsql/data
[postgres@standby ~]$ tar xf /pgsql/backup/pg_wal.tar -C /archive/
#方法1
#修改postgresql. conf文件
[postgres@standby ~]$ vi /pgsql/data/postgresql.conf
#添加下面两行
primary_conninfo = 'host=10.0.0.200 port=5432 user=repluser password=123456' #指定连接信息
restore_command = 'cp /archive/%f %p' #此项可不配置
[postgres@standby ~]$ pg_ctl -D /pgsql/data start
#方法2
#修改postgresql.auto.conf
[postgres@standby ~]$ vi /pgsql/data/postgresql.auto.conf
primary_conninfo = 'host=10.0.0.200 port=5432 user=repluser password=123456
sslmode=disable sslcompression=0 gssencmode=disable
krbsrvname=post_session_attrs='any' #此行自动生成,只修改用户名即可
restore_command = 'cp /archive/%f %p'
[postgres@standby ~]$ pg_ctl -D /pgsql/data start
#在从节点上进行crontab数据备份
[root@standby ~]#crontab -e
no crontab for root - using an empty one
0 0 * * * bash /root/pg_backup.sh
[root@standby ~]# vim pg_backup.sh
#!/bin/bash
DIR=/data/backup-`date +%F`
[ -d $DIR ] || mkdir -p $DIR
expect <<EOF
set timeout 20
spawn pg_basebackup -D $DIR -Ft -Pv -U postgres -h 10.0.0.180 -R
expect {
"Password" { send "123456\n" }
}
expect eof
EOF
2.3、切换主从
#在从节点上
[postgres@standby ~]$pg_ctl promote
[postgres@standby ~]$psql
postgres=# select pg_wal_replay_resume();
[postgres@standby ~]$pg_ctl restart
#检查
pg_controldata
将主节点切换为从节点,即将建立从节点的步骤在该主机上进行即可