pgsql日志管理和备份_postgresql日志备份

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，创建相关的表结构，将其导入文件，便于管理
1
2
3
4
5
6
7
8
9
10
11
12
13

将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
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

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
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

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
2
3
4
5
6
7
8
9
10
11
12
13

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/
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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    #还原，原有的数据则不还原
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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
1
2
3
4
5
6
7

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

2.3、切换主从

#在从节点上
[postgres@standby ~]$pg_ctl promote
[postgres@standby ~]$psql
postgres=# select pg_wal_replay_resume();

[postgres@standby ~]$pg_ctl restart

#检查
pg_controldata
1
2
3
4
5
6
7
8
9

将主节点切换为从节点，即将建立从节点的步骤在该主机上进行即可