在之前的博客,我们了解的PG的相关信息,如下:
PostgreSQL 学习笔记(1) — PG 概述
https://www.cndba.cn/dave/article/116370
PostgreSQL 学习笔记(2) — PG 版本发布策略 和 生命周期说明
https://www.cndba.cn/dave/article/116372
PostgreSQL 学习笔记(3) — PG 单实例安装手册
https://www.cndba.cn/dave/article/116374
PostgreSQL 学习笔记(4) — PG 启动 与 关闭
https://www.cndba.cn/dave/article/116375
PostgreSQL 学习笔记(5) — psql 工具使用说明
https://www.cndba.cn/dave/article/116376
PostgreSQL 学习笔记(6) — PG 参数管理
https://www.cndba.cn/dave/article/116377
PostgreSQL 学习笔记(7) — PG 数据库 基本操作
https://www.cndba.cn/dave/article/116379
PostgreSQL 学习笔记(8) — PG 模式 与 用户 管理
https://www.cndba.cn/dave/article/116380
PostgreSQL 学习笔记(9) — PG 进程和内存结构说明
https://www.cndba.cn/dave/article/116381
PostgreSQL 学习笔记(10) — PG 目录结构 说明
https://www.cndba.cn/dave/article/116382
PostgreSQL 学习笔记(11) — PG 常用的 系统表 和 系统视图 说明
https://www.cndba.cn/dave/article/116383
PostgreSQL 学习笔记(12) — PG 常用的命令
https://www.cndba.cn/dave/article/116384
本篇我们看下PG的备份与恢复。
1 物理备份
物理备份分冷备份和热备份:
- 冷备份:把数据库停下来,然后拷贝数据库的PGDATA目录。
- 热备份:在不停止数据库的前提下完成数据库的备份,使用pg_basebackup命令完成。
冷备份这里不再描述:直接备份PGDATA 目录。
-bash-4.2$ ps -ef|grep pg
postgres 14752 1 0 01:19 ? 00:00:01 /usr/pgsql-14/bin/postgres -D /var/lib/pgsql/14/data
postgres 27608 11533 0 03:47 pts/3 00:00:00 grep --color=auto pg
-bash-4.2$
我们这里重点看下热备份。
1.1 pg_basebackup 工具说明
该工具相关说明如下:
- 备份整个实例,无法备份单个数据库或者schema;
- pg_basebackup被用于获得一个正在运行的PostgreSQL数据库集簇的基础备份,不会影响连接到该数据库的其他客户端,并且可以被用于时间点恢复。
- 必须由一个超级用户或者一个具有REPLICATION权限的用户建立,并且pg_hba.conf必须显式地允许该复制连接。
- 可以改变数据、配置等文件的路径
命令帮助如下:
-bash-4.2$ pg_basebackup --help
pg_basebackup takes a base backup of a running PostgreSQL server.
Usage:
pg_basebackup [OPTION]...
Options controlling the output:
-D, --pgdata=DIRECTORY receive base backup into directory
-F, --format=p|t output format (plain (default), tar)
-r, --max-rate=RATE maximum transfer rate to transfer data directory
(in kB/s, or use suffix "k" or "M")
-R, --write-recovery-conf
write configuration for replication
-T, --tablespace-mapping=OLDDIR=NEWDIR
relocate tablespace in OLDDIR to NEWDIR
--waldir=WALDIR location for the write-ahead log directory
-X, --wal-method=none|fetch|stream
include required WAL files with specified method
-z, --gzip compress tar output
-Z, --compress=0-9 compress tar output with given compression level
相关说明如下:
F:t tar 格式输出;p plain原样输出
z:输出的tar备份经过gzip压缩
P:实时的打印备份的进度
v:详细模式,当使用-P,还会打印出正在备份哪个具体文件的信息
l:指定备份的一个标识 lable
R:write configuration for replication
X:备份的模式,串行和并行
f :fetch串行复制,数据复制完,再复制wal日志,如果使用tar格式,预写式日志文件将被写入到base.tar文件。
s :stream并行复制,数据和wal日志同步复制,如果使用tar格式,预写式日志文件被写入到一个单独的名为pg_wal.tar的文件;这个值是默认值。
1.2 备份示例
1.2.1 设置 pg_hba.conf
设置 pg_hba.conf,允许客户端机器发起流复制链接,添加如下内容:
host replication all 0.0.0.0/0 md5
-bash-4.2$ cat pg_hba.conf |grep -Ev '^$|^/s*#'
local all all peer
host all all 127.0.0.1/32 scram-sha-256
host all all ::1/128 scram-sha-256
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
host replication all 0.0.0.0/0 md5
-bash-4.2$
1.2.2 设置备份用户
备份用户比如是superuser 或 replication,如果没有超级用户,那么可以创建一个用户:
cndba=# create user backup REPLICATION LOGIN PASSWORD 'backup';
CREATE ROLE
1.2.3 备份成tar 包
-bash-4.2$ pg_basebackup -h127.0.0.1 -p5432 -Ubackup -D /data/postgres/backup -Xs -Ft -z -Pv -Z=9
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/E000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_19264"
52053/52053 kB (100%), 2/2 tablespaces
pg_basebackup: write-ahead log end point: 0/E000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
-bash-4.2$
[dave@www.cndba.cn postgres]# cd backup/
[dave@www.cndba.cn backup]# ll
total 68684
-rw-------. 1 postgres postgres 2560 Aug 13 04:11 16719.tar
-rw-------. 1 postgres postgres 238147 Aug 13 04:11 backup_manifest
-rw-------. 1 postgres postgres 53301760 Aug 13 04:11 base.tar
-rw-------. 1 postgres postgres 16778752 Aug 13 04:11 pg_wal.tar
[dave@www.cndba.cn backup]#
1.2.4 备份成从库
如果是备份是从库,参数需要改成-R,我们这里在单机上跑了一下,报错了:
-bash-4.2$ pg_basebackup -h 127.0.0.1 -p 5432 -U backup -l bk20210727 -Fp -P -R -D /data/postgres/backup2
Password:
pg_basebackup: error: directory "/data/postgres/data" exists but is not empty
pg_basebackup: removing contents of data directory "/data/postgres/backup2"
-bash-4.2$
1.3 恢复示例
1.3.1 创建恢复目录
因为我们这里是单机,所以我们创建一个新的目录来恢复。
-bash-4.2$ mkdir cndba
-bash-4.2$ cd cndba/
-bash-4.2$ pwd
/data/postgres/cndba
1.3.2 解压缩文件
解压base.tar的包
[dave@www.cndba.cn backup]# tar -xvf base.tar -C /data/postgres/cndba
-bash-4.2$ ll
total 72
drwx------. 3 postgres postgres 22 Aug 13 00:57 archivelog
-rwxr-xr-x. 1 postgres postgres 179 Aug 13 01:16 arch.sh
-rw-------. 1 postgres postgres 227 Aug 13 04:12 backup_label
drwx------. 7 postgres postgres 67 Aug 13 04:07 base
-rw-------. 1 postgres postgres 30 Aug 13 01:19 current_logfiles
drwx------. 2 postgres postgres 4096 Aug 13 04:29 global
drwx------. 2 postgres postgres 162 Aug 13 00:00 log
drwx------. 2 postgres postgres 6 Aug 8 09:47 pg_commit_ts
drwx------. 2 postgres postgres 6 Aug 8 09:47 pg_dynshmem
-rw-------. 1 postgres postgres 4645 Aug 13 04:05 pg_hba.conf
-rw-------. 1 postgres postgres 1636 Aug 8 09:47 pg_ident.conf
drwx------. 4 postgres postgres 68 Aug 13 04:12 pg_logical
drwx------. 4 postgres postgres 36 Aug 8 09:47 pg_multixact
drwx------. 2 postgres postgres 6 Aug 8 09:47 pg_notify
drwx------. 2 postgres postgres 6 Aug 13 04:12 pg_replslot
drwx------. 2 postgres postgres 6 Aug 8 09:47 pg_serial
drwx------. 2 postgres postgres 6 Aug 8 09:47 pg_snapshots
drwx------. 2 postgres postgres 6 Aug 13 01:19 pg_stat
drwx------. 2 postgres postgres 6 Aug 13 04:12 pg_stat_tmp
drwx------. 2 postgres postgres 6 Aug 8 09:47 pg_subtrans
drwx------. 2 postgres postgres 6 Aug 10 05:55 pg_tblspc
drwx------. 2 postgres postgres 6 Aug 8 09:47 pg_twophase
-rw-------. 1 postgres postgres 3 Aug 8 09:47 PG_VERSION
drwx------. 3 postgres postgres 28 Aug 13 04:29 pg_wal
drwx------. 2 postgres postgres 18 Aug 8 09:47 pg_xact
-rw-------. 1 postgres postgres 113 Aug 9 04:40 postgresql.auto.conf
-rw-------. 1 postgres postgres 29037 Aug 13 01:18 postgresql.conf
-rw-------. 1 postgres postgres 26 Aug 13 04:12 tablespace_map
-bash-4.2$ cd pg_wal/
-bash-4.2$ ll
total 0
drwx------. 2 postgres postgres 6 Aug 13 04:12 archive_status
-bash-4.2$ pwd
/data/postgres/cndba/pg_wal
此时pg_wal 为空。
解压pg_wal.tar.gz的包
[dave@www.cndba.cn backup]# tar -xvf pg_wal.tar -C /data/postgres/cndba/pg_wal
000000010000000000000010
[dave@www.cndba.cn backup]#
-bash-4.2$ pwd
/data/postgres/cndba/pg_wal
-bash-4.2$ ll
total 0
drwx------. 2 postgres postgres 6 Aug 13 04:12 archive_status
-bash-4.2$ ll
total 16384
-rw-------. 1 postgres postgres 16777216 Aug 13 04:12 000000010000000000000010
drwx------. 2 postgres postgres 6 Aug 13 04:12 archive_status
-bash-4.2$
1.3.3 修改restore_command 参数
在postgresql.conf文件中修改恢复参数restore_command。 这里有如下几种类型的配置:
1. 恢复到最新:
restore_command = 'cp /data/postgres/cndba/pg_wal/%f %p'
recovery_target_timeline = 'latest'
2. 恢复到指定的时间点:
restore_command = 'cp /data/postgres/cndba/pg_wal/%f %p'
recovery_target_time = '2022-12-12 13:16:49.007657+08'
3. 如果有还原点,也可以恢复到指定的还原点:
创建还原点:
SELECT pg_create_restore_point('restore_point1');
恢复到还原点:
restore_command = 'cp /data/postgres/cndba/pg_wal/%f %p'
recovery_target_name ='restore_point1'
4.恢复到指定的事务:
restore_command = 'cp /data/postgres/cndba/pg_wal/%f %p'
recovery_target_xid='501'
具体事务id 可以通过对wal 进行pg_waldump分析。
1.3.4 启动数据库
启动数据库实例:
[dave@www.cndba.cn postgres]# chmod 700 cndba -R
-bash-4.2$ pg_ctl start -D /data/postgres/cndba
waiting for server to start....2022-08-13 04:42:37.693 CST [15860] LOG: redirecting log output to logging collector process
2022-08-13 04:42:37.693 CST [15860] HINT: Future log output will appear in directory "log".
done
server started
-bash-4.2$
验证,因为我是在同一台机器上操作的,所以这里修改了端口号:
-bash-4.2$ psql -p 5431
psql (14.6)
Type "help" for help.
postgres=# /l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
cndba | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
ustc | cndba | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/cndba +
| | | | | cndba=CTc/cndba
(5 rows)
postgres=#
2 逻辑备份
逻辑备份指通过pg_dumpall 和pg_dump 命令进行备份。
- pg_dumpall是将一个PostgreSQL数据库集群全部转储到一个脚本文件中。
- pg_dump命令可以选择一个数据库或部分表进行备份,在数据库处于使用状态时进行完整一致的备份,它并不阻塞其他用户对数据库的访问(读或写)。
逻辑恢复指通过pg_restore命令恢复pg_dump的备份。
这2个工具的帮助较多,具体可以直接查看命令帮助。
-bash-4.2$ pg_dump –help
-bash-4.2$ pg_restore –help
2.1 逻辑备份
备份时可以加上-Fc参数,这样使用自定义PostgreSQL格式,该格式经过压缩并产生最小的备份文件大小, 不加默认为-Fp,即输出SQL 格式。
2.1.1 全库备份
pg_dumpall 只能导出成SQL。
-bash-4.2$ pg_dumpall -U postgres -p 5432 -f /data/postgres/backup/cndba.sql
-bash-4.2$ pwd
/data/postgres/backup
-bash-4.2$ ll -lh
total 72M
-rw-------. 1 postgres postgres 2.5K Aug 13 04:12 16719.tar
-rw-------. 1 postgres postgres 233K Aug 13 04:13 backup_manifest
-rw-------. 1 postgres postgres 51M Aug 13 04:13 base.tar
-rw-r--r--. 1 postgres postgres 2.7M Aug 13 05:13 cndba.sql
-rw-------. 1 postgres postgres 17M Aug 13 04:13 pg_wal.tar
-bash-4.2$ head cndba.sql
--
-- PostgreSQL database cluster dump
--
SET default_transaction_read_only = off;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
--
-bash-4.2$
-bash-4.2$ pg_dumpall -U postgres -p 5432 -Fc -f /data/postgres/backup/cndba.dump
2.1.2 备份单库
-bash-4.2$ pg_dump -h 127.0.0.1 -p 5432 -U postgres cndba -f /data/postgres/backup/ustc.sql
Password:
-bash-4.2$ cd /data/postgres/backup
-bash-4.2$ ll -lh ustc.sql
-rw-r--r--. 1 postgres postgres 2.7M Aug 13 05:17 ustc.sql
-bash-4.2$
-bash-4.2$ pg_dump -h 127.0.0.1 -p 5432 -U postgres cndba -Fc -f /data/postgres/backup/ustc.dump
Password:
-bash-4.2$
2.1.3 只备份表“dave”
-bash-4.2$ pg_dump -d cndba -t dave -Fc -f /data/postgres/backup/dave1.dump
-bash-4.2$ ll /data/postgres/backup/dave1.dump
-rw-r--r--. 1 postgres postgres 1714 Aug 13 06:10 /data/postgres/backup/dave1.dump
-bash-4.2$
-bash-4.2$ pg_dump -d cndba -t dave -f /data/postgres/backup/dave.sql
-bash-4.2$ ll /data/postgres/backup/dave.sql
-rw-r--r--. 1 postgres postgres 1112 Aug 13 05:24 /data/postgres/backup/dave.sql
-bash-4.2$ head -50 /data/postgres/backup/dave.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 14.6
-- Dumped by pg_dump version 14.6
……
--
-- Name: dave; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.dave (
id integer NOT NULL,
url character varying(40)
);
ALTER TABLE public.dave OWNER TO postgres;
--
-- Data for Name: dave; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.dave (id, url) FROM stdin;
1 https://www.cndba.cn
2 https://www.cndba.cn
3 https://www.cndba.cn
/.
--
-- Name: dave dave_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.dave
-bash-4.2$
2.2 逻辑恢复
上节我们做了3个逻辑备份,这里我们逐一回复。
2.2.1 单表恢复
先恢复单表:
cndba=# select * from dave;
id | url
----+----------------------
1 | https://www.cndba.cn
2 | https://www.cndba.cn
3 | https://www.cndba.cn
(3 rows)
cndba=# drop table dave;
DROP TABLE
备份时未指定参数,-F则使用(或使用-F p)默认纯文本SQL格式.恢复时不能用pg_restore,直接使用psql。
-bash-4.2$ pg_restore -h 127.0.0.1 -p 5432 -U postgres -d cndba /data/postgres/backup/dave.sql
pg_restore: error: input file appears to be a text format dump. Please use psql.
-bash-4.2$ psql -h 127.0.0.1 -p 5432 -U postgres -d cndba -f /data/postgres/backup/dave.sql
Password for user postgres:
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 3
ALTER TABLE
-bash-4.2$
cndba=# select * from dave;
id | url
----+----------------------
1 | https://www.cndba.cn
2 | https://www.cndba.cn
3 | https://www.cndba.cn
(3 rows)
cndba=#
恢复成功。
2.2.2 单库恢复
cndba=# create database dave;
CREATE DATABASE
cndba=# /l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
cndba | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
dave | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
ustc | cndba | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/cndba +
| | | | | cndba=CTc/cndba
(6 rows)
cndba=#
-bash-4.2$ pg_restore -d dave /data/postgres/backup/ustc.dump
cndba=# /c dave
You are now connected to database "dave" as user "postgres".
dave=# /d
List of relations
Schema | Name | Type | Owner
--------+----------------------------+----------+----------
public | actor | table | postgres
public | actor_actor_id_seq | sequence | postgres
public | actor_info | view | postgres
public | address | table | postgres
public | address_address_id_seq | sequence | postgres
public | category | table | postgres
public | category_category_id_seq | sequence | postgres
public | city | table | postgres
public | city_city_id_seq | sequence | postgres
public | cndba | table | postgres
public | country | table | postgres
public | country_country_id_seq | sequence | postgres
public | customer | table | postgres
public | customer_customer_id_seq | sequence | postgres
public | customer_list | view | postgres
public | dave | table | postgres
public | film | table | postgres
public | film_actor | table | postgres
public | film_category | table | postgres
public | film_film_id_seq | sequence | postgres
public | film_list | view | postgres
public | inventory | table | postgres
public | inventory_inventory_id_seq | sequence | postgres
public | language | table | postgres
public | language_language_id_seq | sequence | postgres
public | nicer_but_slower_film_list | view | postgres
public | payment | table | postgres
public | payment_payment_id_seq | sequence | postgres
public | rental | table | postgres
public | rental_rental_id_seq | sequence | postgres
public | sales_by_film_category | view | postgres
public | sales_by_store | view | postgres
public | staff | table | postgres
public | staff_list | view | postgres
public | staff_staff_id_seq | sequence | postgres
public | store | table | postgres
public | store_store_id_seq | sequence | postgres
(37 rows)
dave=# select * from dave;
id | url
----+----------------------
1 | https://www.cndba.cn
2 | https://www.cndba.cn
3 | https://www.cndba.cn
(3 rows)
dave=#
2.2.3 全库备份恢复(pg_dumpall)
dave=# /l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
cndba | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
dave | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
ustc | cndba | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/cndba +
| | | | | cndba=CTc/cndba
(6 rows)
dave=# drop database ustc;
DROP DATABASE
#不存在的会导入,存在的不导入
-bash-4.2$ psql -f /data/postgres/backup/cndba.sql
dave=# /l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
cndba | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
dave | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
ustc | cndba | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(6 rows)
dave=#
版权声明:本文为博主原创文章,未经博主允许不得转载。