前言
PostgreSQL冷备份，需要在数据库关闭状态下，对数据文件进行备份操作。尽管在一情况下并不适用，但并不妨碍我们去了解它的特性。
PostgreSQL热备份，standby数据库在应用WAL日志的同时，也可以提供只读服务，在oracle中叫activedataguard，在PostgreSQL中称为hotstandby。在postgresql9.0以后的版本中，用户可以在备用数据库上进行查询、报表等操作，也可用做读写分离。在生产环境中，热备一直备受追捧
今天，我们就来聊一聊PostgreSQL的冷备与热备。一、PostgreSQLCold standby
备份对象为数据库集群主目录$PGDATA,表空间目录，事务日志(pg_log)目录。当然如果参数文件指定了其他目录或文件，根据需要，也需要备份下来。
数据库主目录及所在位置
| [telepg@test-telepg-01 ~]$ cd $PGDATA[telepg@test-telepg-01 data]$ pwd/app/pg/data_50_18802/data |
主目录中的文件和文件夹
| [telepg@test-telepg-01 data]$ ls -lrt-rwx——. 1 telepg telepg 3 Sep 27 08:39 PG_VERSION-rwx——. 1 telepg telepg 1636 Sep 27 08:39 pg_ident.confdrwx——. 10 telepg telepg 150 Nov 30 09:02 basedrwx——. 3 telepg telepg 37 Nov 30 09:06 tbs_lh-rwx——. 1 telepg telepg 26809 Dec 1 11:08 postgresql.conf-rw——-. 1 telepg telepg 310 Dec 3 10:28 postgresql.auto.conf-rw——-. 1 telepg telepg 78 Dec 3 16:10 postmaster.opts-rw——-. 1 telepg telepg 13947 Dec 3 16:10 console.log-rw——-. 1 telepg telepg 86 Dec 3 16:10 postmaster.piddrwx——. 2 telepg telepg 10 Dec 3 16:10 pg_stat-rwx——. 1 telepg telepg 696 Dec 3 16:17 pg_hba.confdrwx——. 2 telepg telepg 4096 Dec 28 10:35 globaldrwx——. 2 telepg telepg 4096 Dec 28 10:36 pg_xactdrwx——. 2 telepg telepg 8192 Dec 28 11:30 pg_subtransdrwx——. 2 telepg telepg 135168 Jan 1 17:36 pg_commit_tsdrwx——. 3 telepg telepg 249856 Jan 27 11:53 pg_waldrwx——. 2 telepg telepg 8192 Feb 1 00:00 log-rw——-. 1 telepg telepg 44 Feb 1 00:00 current_logfilesdrwx——. 4 telepg telepg 84 Feb 1 07:37 pg_logicaldrwx——. 2 telepg telepg 262 Feb 1 08:25 pg_stat_tmp |
近期事务日志目录
| [telepg@test-telepg-01 data]$ cd log[telepg@test-telepg-01 log]$ ls -lrt-rw——-. 1 telepg telepg 14978379 Jan 29 00:00 postgresql-2021-01-28_000000.log-rw——-. 1 telepg telepg 14979822 Jan 30 00:00 postgresql-2021-01-29_000000.log-rw——-. 1 telepg telepg 14975700 Jan 31 00:00 postgresql-2021-01-30_000000.log-rw——-. 1 telepg telepg 14984205 Feb 1 00:00 postgresql-2021-01-31_000000.log-rw——-. 1 telepg telepg 5268099 Feb 1 08:26 postgresql-2021-02-01_000000.log |
表空间目录
| [telepg@test-telepg-01 data]$ cd tbs_lh[telepg@test-telepg-01 tbs_lh]$ ls -lrtdrwx——. 2 telepg telepg 10 Nov 30 09:06 PG_12_201909212 |
备份需要提前准备好所需要的空间，可以是本地的，也可以是异地的存储。
查看数据库的大小：
| lh=# select round(sum(pg_database_size(oid))/1024/1024/1024.0,2)||’GB’ from pg_database;column———-329.86GB |
准备好可以放下整个备份的目录
| [telepg@test-telepg-01 data]$ df -h/dev/sdb 55T 1.1T 54T 2% /app |
停库
| [telepg@test-telepg-01 ~]$ pg_ctl stop -m fastwaiting for server to shut down…. doneserver stopped |
备份$PGDATA,排除pg_xlog,pg_log以及不需要备份的目录pgbak.
| rsync -acvz -L –exclude “pg_xlog” –exclude “pgbak” –exclude “pg_log” $PGDATA /app/pg/data_50_18802/data/pgbackup/ |
备份pg_xlog
| [telepg@test-telepg-01 ~]$ pg_controldata |grep checkpointLatest checkpoint location: 125/32D4DF90Latest checkpoint’s REDO location: 125/32D4DF58Latest checkpoint’s REDO WAL file: 000000010000012500000032Latest checkpoint’s TimeLineID: 1Latest checkpoint’s PrevTimeLineID: 1Latest checkpoint’s full_page_writes: onLatest checkpoint’s NextXID: 0:830624231Latest checkpoint’s NextOID: 2120456Latest checkpoint’s NextMultiXactId: 1Latest checkpoint’s NextMultiOffset: 0Latest checkpoint’s oldestXID: 632099619Latest checkpoint’s oldestXID’s DB: 1449363Latest checkpoint’s oldestActiveXID: 830624231Latest checkpoint’s oldestMultiXid: 1Latest checkpoint’s oldestMulti’s DB: 17823Latest checkpoint’s oldestCommitTsXid:632099619Latest checkpoint’s newestCommitTsXid:830624230Time of latest checkpoint: Mon 01 Feb 2021 08:37:49 AM CST |
在备份目录中创建pg_xlog目录并修改权限
| [telepg@test-telepg-01 ~]$rsync -acvz -L –exclude “pg_xlog” –exclude “pgbak” –exclude “pg_log” $PGDATA /app/pg/data_50_18802/data/pgbackup/[telepg@test-telepg-01 ~]$chmod 777 /app/pg/data_50_18802/data/pgbackup/pg_xlog |
查找需要的pg_xlog文件
| [telepg@test-telepg-01 ~]$ cd $PGDATA [telepg@test-telepg-01~]$ls -lrt $PGDATA/pgbackup/pg_xlog/000000030000000E000000E*-rw——- 1 pg93 pg93 16M Feb 1 12:24 /pgdata1999/pg_xlog/000000030000000E000000EA -rw——- 1 pg93 pg93 16M Feb 1 12:24 /pgdata1999/pg_xlog/000000030000000E000000EB |
拷贝需要的pg_xlog文件
| [telepg@test-telepg-01~]$cp`$PGDATA/pg_xlog/000000030000000E000000EB/pgdata/digoal/1921/data04/pg93backup/pgdata1999/pg_xlog/ |
检查备份目录,是否备份正常
| [telepg@test-telepg-01 data]$ ll-rwx——. 1 telepg telepg 3 Sep 27 08:39 PG_VERSION-rwx——. 1 telepg telepg 1636 Sep 27 08:39 pg_ident.confdrwx——. 10 telepg telepg 150 Nov 30 09:02 basedrwx——. 3 telepg telepg 37 Nov 30 09:06 tbs_lh-rwx——. 1 telepg telepg 26809 Dec 1 11:08 postgresql.conf-rw——-. 1 telepg telepg 310 Dec 3 10:28 postgresql.auto.conf-rw——-. 1 telepg telepg 78 Dec 3 16:10 postmaster.opts-rw——-. 1 telepg telepg 13947 Dec 3 16:10 console.log-rw——-. 1 telepg telepg 86 Dec 3 16:10 postmaster.piddrwx——. 2 telepg telepg 10 Dec 3 16:10 pg_stat-rwx——. 1 telepg telepg 696 Dec 3 16:17 pg_hba.confdrwx——. 2 telepg telepg 4096 Dec 28 10:35 globaldrwx——. 2 telepg telepg 4096 Dec 28 10:36 pg_xactdrwx——. 2 telepg telepg 8192 Dec 28 11:30 pg_subtransdrwx——. 2 telepg telepg 135168 Jan 1 17:36 pg_commit_tsdrwx——. 3 telepg telepg 249856 Jan 27 11:53 pg_waldrwx——. 2 telepg telepg 8192 Feb 1 00:00 log-rw——-. 1 telepg telepg 44 Feb 1 00:00 current_logfilesdrwx——. 4 telepg telepg 84 Feb 1 07:37 pg_logicaldrwx——. 2 telepg telepg 262 Feb 1 08:25 pg_stat_tmp |
二、PostgreSQL Hot standby
PostgreSQL9.0之后的版本中，日志传送的方法有以下两种：
基于文件(base_file)的传送方式：服务器写完一个WAL日志文件后，再把WAL日志文件拷贝到standby数据库上去应用。
流复制(streamingreplication)的方式：这是PostgreSQL9.0才提供的新方法，在事务提交后，就会把生成的日志异步的传送到standby数据库上应用，这比基本文件的日志传送方法有更低的数据延迟。
今天主要聊一聊流复制热备方式。
PostgreSQL9.0之后的版本中引入了主从的流复制机制，，从服务器通过tcp流从主服务器中同步相应的数据。流复制允许备库更新，同时也能提供只读服务，流复制默认是异步的。
流复制架构图
实例准备
| role | ip | port | version |
| Main | 192.168.122.1 | 18802 | 12.3 |
| Slave | 192.168.122.2 | 18802 | 12.3 |
首先我们需要对主库进行配置，创建复制用户repuser，并赋予复制和登录的权限。
| lh=# create user repuser replication login connection limit 2 encrypted password ‘repuser’;CREATE ROLE |
更新认证方式，编辑配置文件编辑配置文件pg_hba.conf，新增以下IP
| host all all 0.0.0.0/0 scram-sha-256host all all ::/0 scram-sha-256 |
编辑配置文件postgresql.conf
| listen_addresses = ‘*’wal_log_hints = onarchive_mode = onarchive_command = ‘cp %p /var/lib/pgsql/12/pg_archive/%f’wal_keep_segments = 64 |
新增归档目录pg_archive
| [telepg@test-telepg-01 data]$mkdir /var/lib/pgsql/12/pg_archive |
重启主库
| [telepg@test-telepg-01 data]$systemctl restart postgresql-12.3 |
配置从库
在postgres用户根目录下创建.pgpass文件，并追加认证信息
| [telepg@test-telepg-01 data]touch .pgpass[telepg@test-telepg-01 data]chmod 777 .pgpass[telepg@test-telepg-01 data]cat .pgpass192.168.122.1:18802:lh:repuser:repuser |
从主节点拷贝数据到从节点
| $ su – telepgLast login: Mon Feb 1 14:27:20 CST 2021 on pts/0$ pg_basebackup -h 192.168.122.1 -U repuser -D /var/lib/pgsql/12/data/ -X stream -PPassword:24308/24308 kB (100%), 1/1 tablespace |
拷贝配置文件recovery.conf.sample为recovery.conf
| [telepg@test-telepg-01data]$cp`/usr/pgsql-12/share/recovery.conf.sample/var/lib/pgsql/12/data/recovery.conf |
更新配置文件recovery.conf
| standby_mode = onprimary_conninfo = ‘host=192.168.122.1 port=18802 user=repuser password=repuser’recovery_target_timeline = ‘latest’ |
重启从库
| [telepg@test-telepg-01data]$systemctl restart postgresql-12.3 |
在主节点上命令验证
| lh=# select application_name, client_addr, sync_state from pg_stat_replication;application_name | client_addr | sync_state——————+—————+————walreceiver | 192.168.122.2 | async(1 row) |
说明192.168.122.2是从服务器，在接收流，而且是异步流复制。可以分辨在主、从节点上查看进程来进行验证
主服务器上有一个walsender 进程
| [telepg@test-telepg-01data]$$ ps -ef | grep postgrespostgres 20645 19653 0 2021 ? 00:00:00 postgres: wal sender process repuser 192.168.122.2(59176) streaming 0/70029E0 |
从服务器上有一个walsender 进程
| [telepg@test-telepg-01data]$$ $ ps -ef | grep postgrespostgres 18019 18012 0 2021 ? 00:00:00 postgres: wal receiver process streaming 0/7002A18 |
来源：IT那活儿，本文观点不代表自营销立场，网址：https://www.zyxiao.com/p/120429
