学习目的
• postgresql是这样处理备份，opengauss如何处理的呢，GaussDB又是如何处理的呢？
• 如何找一致性点位的呢？
• 分布式环境下如何PITR?
环境条件
正常情况下数据库目录不应该有backup_label文件
启动备份数据库，检测到有backup_label文件时，则认为是从一个备份文件中进行恢复，读取backup_label中的检查点信息，而不是从pg_control中读取。
判断 WAL归档是否已开启 如果没有开启，备份依然会进行， 但在备份结束后会显示提醒信息： NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup
archive_mode = on
archive_command = 'date'
意思是WAL归档未启用，必须确保通过其他方式复制所有必需的WAL以完成备份。对于较大的、写入频繁的生产环境数据库来说等pg_start_backup命令结束再去复制必需的WAL是不现实的，很可能那些文件已经被重用了，务必提前开启归档
强制进入全页写模式 判断当前配置是否为全页写模式，如果当前full_page_writes设置为off，则强制更改为on
postgres=# show full_page_writes ;
full_page_writes
------------------
on
(1 row)
创建一个检查点
• 排他基础备份的情况下还会创建backup_label文件
10.0及以后版本：
postgres=# select pg_current_wal_lsn(),
postgres-# pg_walfile_name(pg_current_wal_lsn()),
postgres-# pg_walfile_name_offset(pg_current_wal_lsn());
pg_current_wal_lsn | pg_walfile_name | pg_walfile_name_offset
--------------------+--------------------------+--------------------------------
0/F000110 | 00000001000000000000000F | (00000001000000000000000F,272)
(1 row)
postgres=#
10.0之前版本：
select pg_current_xlog_location(),
pg_xlogfile_name(pg_current_xlog_location()),
pg_xlogfile_name_offset(pg_current_xlog_location());
数据库版本
postgres=# show server_version;
-[ RECORD 1 ]--+------
server_version | 13.14
postgres=#
postgres=# select version();
-[ RECORD 1 ]--------------------------------------------------------------------------
version | PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 10.3.1, 64-bit
postgres=#
postgres=#
涉及的核心函数
postgres=# \x
List of functions
-[ RECORD 1 ]-------+------------------------------------------------------------------------------------------------------------------
Schema | pg_catalog
Name | pg_backup_start_time
Result data type | timestamp with time zone
Argument data types |
Type | func
-[ RECORD 2 ]-------+------------------------------------------------------------------------------------------------------------------
Schema | pg_catalog
Name | pg_is_in_backup
Result data type | boolean
Argument data types |
Type | func
-[ RECORD 3 ]-------+------------------------------------------------------------------------------------------------------------------
Schema | pg_catalog
Name | pg_start_backup
Result data type | pg_lsn
Argument data types | label text, fast boolean DEFAULT false, exclusive boolean DEFAULT true
Type | func
-[ RECORD 4 ]-------+------------------------------------------------------------------------------------------------------------------
Schema | pg_catalog
Name | pg_stop_backup
Result data type | pg_lsn
Argument data types |
Type | func
-[ RECORD 5 ]-------+------------------------------------------------------------------------------------------------------------------
Schema | pg_catalog
Name | pg_stop_backup
Result data type | SETOF record
Argument data types | exclusive boolean, wait_for_archive boolean DEFAULT true, OUT lsn pg_lsn, OUT labelfile text, OUT spcmapfile text
Type | func
postgres=#
手动备份
主库
停启数据库
su - postgres -c "/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile stop"
su - postgres -c "/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start"
执行pg_start_backup
su - postgres
/usr/local/pgsql/bin/psql
postgres=# SELECT pg_start_backup('mybackup');
pg_start_backup
-----------------
0/D000060
(1 row)
插卡日志文件
[postgres@primary data]$ pwd
/usr/local/pgsql/data
[postgres@primary data]$
[postgres@primary data]$ cat backup_label
START WAL LOCATION: 0/D000060 (file 00000001000000000000000D)
CHECKPOINT LOCATION: 0/D000098
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2024-05-15 05:50:36 UTC
LABEL: mybackup
START TIMELINE: 1
[postgres@primary data]$
rsyc到从库
rsync -avz --exclude postmaster.pid --exclude pg_hba.conf \
--exclude postgresql.conf --exclude postmaster.opts \
--exclude pg_xlog /usr/local/pgsql/data/ /tmp/standby
rsync -avz /usr/local/pgsql/data/ /tmp/standby
执行pg_stop_backup
[postgres@primary ~]$ /usr/local/pgsql/bin/psql
psql (13.14)
Type "help" for help.
postgres=# SELECT pg_stop_backup();
NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup
pg_stop_backup
----------------
0/D000170
(1 row)
postgres=#
查看生成文件000000010000000000000010.00000060.backup
[postgres@primary pg_wal]$ ll
total 49160
-rw-------. 1 postgres postgres 16777216 May 15 08:25 000000010000000000000010
-rw-------. 1 postgres postgres 330 May 15 08:25 000000010000000000000010.00000060.backup
-rw-------. 1 postgres postgres 16777216 May 15 08:25 000000010000000000000011
-rw-------. 1 postgres postgres 16777216 May 15 08:24 000000010000000000000012
drwx------. 2 postgres postgres 4096 May 15 08:25 archive_status
[postgres@primary pg_wal]$ pwd
/usr/local/pgsql/data/pg_wal
[postgres@primary pg_wal]$
启动从库
复制到备库
[root@primary tmp]# scp -rp standby root@10.0.2.7:/tmp/
把备份文件改属主
[root@replicate2 data]# cd /tmp
[root@replicate2 tmp]# chown postgres:postgres standby/ -R
删除旧数据库文件
[postgres@replicate2 standby]$ rm -rf /usr/local/pgsql/data/*
迁移数据库文件到数据库目录
[postgres@replicate2 standby]$ mv * /usr/local/pgsql/data/
建立复制关系
[root@replicate2 data]# cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=replication passfile=''/home/postgres/.pgpass'' channel_binding=disable host=10.0.2.15 port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
[root@replicate2 data]#
建立从库标记
touch /usr/local/pgsql/data/standby.signal
查看数据库日志
[postgres@replicate2 data]$ tail -f /usr/local/pgsql/data/log/postgresql-2024-05-15_122916.log
2024-05-15 12:29:16.878 UTC [1903] LOG: listening on IPv6 address "::1", port 5432
2024-05-15 12:29:16.878 UTC [1903] LOG: listening on IPv4 address "127.0.0.1", port 5432
2024-05-15 12:29:16.882 UTC [1903] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-05-15 12:29:16.889 UTC [1905] LOG: database system was interrupted; last known up at 2024-05-15 12:25:26 UTC
2024-05-15 12:29:18.000 UTC [1905] LOG: entering standby mode
2024-05-15 12:29:18.006 UTC [1905] LOG: redo starts at 0/21000060
2024-05-15 12:29:18.006 UTC [1905] LOG: invalid record length at 0/21000110: wanted 24, got 0
2024-05-15 12:29:18.013 UTC [1908] LOG: started streaming WAL from primary at 0/21000000 on timeline 1
2024-05-15 12:29:18.136 UTC [1905] LOG: consistent recovery state reached at 0/21000170
2024-05-15 12:29:18.136 UTC [1903] LOG: database system is ready to accept read only connections
停启数据库
su - postgres -c "/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile stop"
su - postgres -c "/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start"
设置统一时间
timedatectl set-timezone PRC
timedatectl set-local-rtc 1
参考
https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-BACKUP
文章转载自SmallDB，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
