PG数据的备份

hongjunaiyu

已于 2023-02-21 11:29:35 修改

阅读量6k

点赞数 1

分类专栏：数据库文章标签：数据库 postgresql

于 2023-02-21 11:24:15 首次发布

本文链接：https://blog.csdn.net/hongjunaiyu/article/details/129039349

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

postgresql数据库的备份和恢复

pg数据库的备份方式
具体操作（下面所说的是基于10版本的操作）

pg数据库的备份方式

1逻辑备份

区别于物理复制的是物理复制是基于实例级的复制，只能复制整个PostgreSQL实例，而不能基于部分库及表。从PostgreSQL10开始，出现了基于表级别的复制，即逻辑复制。

2物理备份（也叫流复制）

可以从实例级复制出一个与主库一模一样的实例级的从库。流复制同步模式有同步、异步两种。

异步复制，可以做到较好的性能，而它的劣势是：主库如果宕机，或从库被激活成主库，部分 WAL 没有发送到从库，可能造成数据丢失。

同步复制，能够保证主库上所有事务的修改都能被传送到从库，提高了数据复制安全性的同时也降低了性能。

默认状态下的流复制是以异步模式工作的，主库写WAL日志，通过wal sender进程把WAL日志发送给从库的wal receiver进程，wal receiver接收到WAL日志，并持久化到存储。

3连续归档（Archive）

	 根据归档命令进行备份，通常备份会落后一个WAL日志文件。

具体操作（下面所说的是基于10版本的操作）

逻辑备份
在Postgresql中提供了pg_dump， pg_dumpall工具进行数据库的逻辑备份。pg_dumpall是备份全库，而pg_dump可以选择一个数据库或部分表进行备份。

特点

pg_dump 不会阻塞正常的数据库读写，可以在数据库处于使用状态时进行完整的一致的备份,备份可以简单看作是pg_dump开始运行时的数据库的快照。
pg_dump的备份文件可以是SQL脚本，也可以是归档文件。用psql执行SQL脚本文件可以重建该数据库，甚至不依赖特定的基础设施(例如操作系统，)，脚本修改后甚至可以恢复到非postgres数据库。但是如果是归档文件，则只能用pg_store来重建数据库。
pg_dump和pg_restore可以选择性的备份或恢复部分表或数据库对象。
pg_dumpall 对db cluster中的每个数据库调用pg_dump来完成该工作,还会还转储对所有数据库公用的全局对象（pg_dump不保存这些对象）。目前这包括适数据库用户和组、表空间以及适合所有数据库的访问权限等属性。注意pg_dumpall只能导出脚本文件。

一、备份

备份到本地

[postgres@localhost logic]$ pg_dump -U postgres osdba > osdba_dump1.sql
Password: 
[postgres@localhost logic]$ 
[postgres@localhost logic]$ 
[postgres@localhost logic]$ ll
total 84564
-rw-r--r--. 1 postgres postgres 86591748 Oct  6 16:51 osdba_dump1.sql
[postgres@localhost logic]$ head -n 100 osdba_dump1.sql 
--
-- PostgreSQL database dump
--

-- Dumped from database version 13.3
-- Dumped by pg_dump version 13.3

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: company; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.company (
    id integer,
    name character varying(32)
);


ALTER TABLE public.company OWNER TO postgres;

--
-- Name: people; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.people (
    id integer,
    name character varying(32),
    age integer,
    grade numeric(4,2),
    birthday date,
    logintime timestamp without time zone
);


ALTER TABLE public.people OWNER TO postgres;

--
-- Data for Name: company; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.company (id, name) FROM stdin;
1	2d5e81d30b201cae28e51c3bda7f1e08
2	1c367e0fe7172addf79903dfd9bdfe06
3	01f09ce43ddea1a8207a33e0c2969297
...

使用pg_dump备份一个远程的数据库。

## 远程到192.168.146.132
[postgres@localhost logic]$ ssh 192.168.146.132
The authenticity of host '192.168.146.132 (192.168.146.132)' can't be established.
ECDSA key fingerprint is SHA256:pg1sNWe72JI56IqPW3ZCAme0pL9mNUJvRNkV8peif4A.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.146.132' (ECDSA) to the list of known hosts.
postgres@192.168.146.132's password: 
Activate the web console with: systemctl enable --now cockpit.socket

Last login: Thu Oct  7 00:38:32 2021
[postgres@localhost ~]$ ls
backups  data  initdb_postgresql.log
[postgres@localhost ~]$ cd backups/
[postgres@localhost backups]$ ls
[postgres@localhost backups]$ 
[postgres@localhost backups]$ 
[postgres@localhost backups]$ 
[postgres@localhost backups]$ pg_dump -h 192.168.146.131 -U postgres osdba > osdba.sql
Password: 
## 这里查看导出文件第40行到60行的内容
[postgres@localhost backups]$ head -n 60 osdba.sql | tail -n 20
    name character varying(32),
    age integer,
    grade numeric(4,2),
    birthday date,
    logintime timestamp without time zone
);


ALTER TABLE public.people OWNER TO postgres;

--
-- Data for Name: company; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.company (id, name) FROM stdin;
1	2d5e81d30b201cae28e51c3bda7f1e08
2	1c367e0fe7172addf79903dfd9bdfe06
3	01f09ce43ddea1a8207a33e0c2969297
4	e746f65209317296e56b12db276f9bfc
5	2d043d4cb9210c98084bdb00d8f4cfb3

自定义格式的备份文件使用-Fc参数，-F就是format的意思。

[postgres@localhost backups]$ pg_dump -Fc -h 192.168.146.131 -U postgres osdba > osdba131.dump
Password: 
[postgres@localhost backups]$ ll -h
total 116M
-rw-r--r--. 1 postgres postgres 33M Oct  7 01:06 osdba131.dump
-rw-r--r--. 1 postgres postgres 83M Oct  7 01:02 osdba.sql

备份指定的表使用-t 和 -T 用来设置要include与exclude的表。

[postgres@localhost backups]$ pg_dump -t 'company' -h 192.168.146.131 -U postgres osdba > osdba131-company.dump
Password: 
[postgres@localhost backups]$ 
[postgres@localhost backups]$ 
[postgres@localhost backups]$ ll -h
total 116M
-rw-r--r--. 1 postgres postgres 4.4K Oct  7 01:13 osdba131-company.dump
-rw-r--r--. 1 postgres postgres  33M Oct  7 01:06 osdba131.dump
-rw-r--r--. 1 postgres postgres  83M Oct  7 01:02 osdba.sql

pg_dumpall的简单使用

[postgres@localhost dump]$ pg_dumpall -h 127.0.0.1 -p 5432 -U postgres -c -f all_db_bak.sql
Password: 
Password: 
Password: 
[postgres@localhost dump]$ pg_dump -h 127.0.0.1 -p 5432 -U postgres -c -C -f postgres_db_bak.sql postgres
Password: 
[postgres@localhost dump]$ ll -h
total 821M
-rw-r--r--. 1 postgres postgres 309M Oct  6 16:14 all_db_bak.sql
-rw-r--r--. 1 postgres postgres 309M Oct  6 16:15 postgres_db_bak.sql
[postgres@localhost dump]$ wc -l all_db_bak.sql 
1910237 all_db_bak.sql
[postgres@localhost dump]$ wc -l postgres_db_bak.sql 
1910104 postgres_db_bak.sql
[postgres@localhost dump]$

*** 总结 *** ：大型数据库pg_dump使用
在一些旧的操作系统，会对文件的大小有一定的限制，例如windows XP的NTFS文件系统限定文件最大为64G。Linux中ext2文件系统的最大文件为2TB，ext3单文件最大16TB。如果数据库非常巨大，pg_dump可能因为操作系统的限制导致备份失败。

一些Linux的工具可以实现文件的压缩和分割。

pg_dump dbname | gzip > filename.gz
pg_dump dbname | split -b 1m - filename

二、还原备份数据
7. 把osdba131.dump文件恢复到132这台机器的osdba2数据库中。

[postgres@localhost backups]$ createdb -E 'UTF-8' osdba2 ; 
Password: 
[postgres@localhost backups]$ 
[postgres@localhost backups]$ pg_restore -d osdba2 osdba131.dump 
Password: 
[postgres@localhost backups]$ createdb -E 'UTF-8' osdba2 ; 
Password: 
[postgres@localhost backups]$ 
[postgres@localhost backups]$ pg_restore -d osdba2 osdba131.dump 
Password: 
[postgres@localhost backups]$ psql osdba2
Password for user postgres: 
psql (13.3)
Type "help" for help.

osdba2=# select count(0) from people;
  count  
---------
 1000000
(1 row)

osdba2=# select count(0) from company;
 count 
-------
   100
(1 row)

osdba2=#

物理备份 （也叫流复制）
物理备份又分冷备和热备；
冷备
所谓冷备份就是先把数据库停下来，然后拷贝数据库的PGDATA目录。因为PostgreSQL把实例相关的配置文件和数据文件都放在PGDATA目录下，所以冷备份很简单，就是文件拷贝。（一定要先给数据库停下，以免数据不一致的情况）

[postgres@localhost ~]$ cd backups/
[postgres@localhost backups]$ pwd
/var/lib/pgsql/backups
## 备份文件夹
[postgres@localhost backups]$ tar -cf backup.tar /var/lib/pgsql/data
tar: Removing leading \`/\' from member names
[postgres@localhost backups]$ ll
total 323540
-rw-r--r--. 1 postgres postgres 210452480 Oct  7 01:47 backup.tar
-rw-r--r--. 1 postgres postgres      4483 Oct  7 01:13 osdba131-company.dump
-rw-r--r--. 1 postgres postgres  34249605 Oct  7 01:06 osdba131.dump
-rw-r--r--. 1 postgres postgres  86591748 Oct  7 01:02 osdba.sql
[postgres@localhost backups]$ 
## 恢复
[postgres@localhost ~]$ tar -xvf backups/backup.tar
[postgres@localhost ~]$ ls
backups  initdb_postgresql.log  var
[postgres@localhost ~]$ mv var/lib/pgsql/data/ ./
postgres@localhost ~]$ rm -rf var
[postgres@localhost ~]$ ls
backups  data  initdb_postgresql.log

物理热备
在不停止数据库的情况下完成，称之为热备份或在线备份。在Postgres中通常的热备份有以下两种方法：

连续归档与PIRT
使用文件系统或块设备级别的快照功能，该方法要求数据库建立在LVM上。

连续归档与PIRT
PostgreSQL在data目录的pg_wal子目录(10版本前是pg_xlog)中始终维护一份WAL日志文件。该日志文件记录了数据库数据文件的每次改变。当数据库异常崩溃以后，能够通过重放(replay)最后一次检查点(checkpoint)之后的日志记录，就能把数据库推到最终一致的状态。因此我们如果有了最初的数据库备份Base，再加上此备份时间点后的所有数据库的WAL日志，相当于数据库的改变量Δ，然后直接重放Δ就能实现数据库的恢复。
```
 把一个文件系统级别的全量备份和WAL(预写式日志)级别的增量备份结合起来。当需要恢复时，我们先恢复文件系统级别的备份，然后重放备份的WAL文件，把系统恢复到之前的某个状态。
 **连续归档有以下的几个特点：**
```

不需要完美的一致性的备份，备份中的任何非一致性数据都可以通过重放WAL日志文件得以纠正。
可以结合一个无穷长的WAL日志序列用于重放，可以通过简单的归档WAL文件来达到连续备份。
不需要重放WAL日志到最后，可以在任何点停止重放，并使数据库恢复到某个时间点的一致性状态，这就是基于时间点的备份，英文为"Point-In-Time Recovery"简称“PITR”。
可以连续的将一系列WAL文件输送给另外一台已经载入了相同基础备份文件的机器，得到一个实时的热备份系统。
基础备份
使用简单的cp命令或其他命令把数据库在线复制出来的备份，称为基础备份，从基础备份操作开始之后产生的WAL日志和基础备份本身就构成了一个完整的备份。我们当然也可以直接cp的方式来备份，但是PosggreSQL提供了pg_basebackup命令行工具来实现更加灵活和安全的方式完成基础备份。

pg_basebackup工具把整个数据库实例的全部数据都物理的复制出来(包括配置文件)，而不是也不能是只把实例种的部分内容单独备份出来例如只备份某些表。该工具使用流复制的协议连接到主数据库上，所以数据库上必须允许replication连接。

pg_hba.conf
```
host    replication     all             ip/32               trust
```
1. 备份本机的数据库
  pg_basebackup -D base20211007 -Ft -z -P
  -D 指定备份的目标目录，可以不用预先创建。 -Ft 指定备份文件的格式为tar，相当于把备份文件输出到一个tar文件中。 -z 仅仅与tar输出模式配合使用，就是对tar文件进行gzip压缩，便于传输。 -P 用来输出备份的进度

[postgres@raw140 backups]$ pg_basebackup -D base20211007 -Ft -z -P
479078/479078 kB (100%), 1/1 tablespace
[postgres@raw140 backups]$ ls
base20211007
[postgres@raw140 backups]$ cd base20211007/
[postgres@raw140 base20211007]$ ll -h
total 188M
-rw------- 1 postgres postgres 133K Oct  6 23:22 backup_manifest
-rw------- 1 postgres postgres 188M Oct  6 23:22 base.tar.gz
-rw------- 1 postgres postgres  17K Oct  6 23:22 pg_wal.tar.gz
[postgres@raw140 base20211007]$

这里backup_manifest文件是备份的元文件，包括备份的一些基础信息，例如CRC校验等等。
2. 远程备份
下面这个例子在141这台机器上备份140机器上的数据库。
-l label 用来指定备份的一个标识。便于以后维护人员识别该备份。
其中的postgres 为140中数据库中的用户要在pg_hba.conf中配置允许replication连接。
cd /u01/install/postgresql-10.7/src/bin/pg_basebackup/
./pg_basebackup -h 172.17.15.217 -U postgresql -D /u01/main_back_up -Ft -Pv -p 15432 -R

pg_basebackup -h 192.168.203.140 -Upostgres -Ft -z -P -D pg140_base_backup -l pg140_base_20211007
[postgres@raw141 backups]$ pwd
/var/lib/pgsql/backups
[postgres@raw141 backups]$ pg_basebackup -h 192.168.203.140 -Upostgres -Ft -z -P -D pg140_base_backup -l pg140_base_20211007
Password: 
479078/479078 kB (100%), 1/1 tablespace
[postgres@raw141 backups]$ ll
total 0
drwx------ 2 postgres postgres 69 Oct  6 23:34 pg140_base_backup
[postgres@raw141 backups]$ cd pg140_base_backup/
[postgres@raw141 pg140_base_backup]$ ll -h
total 188M
-rw------- 1 postgres postgres 133K Oct  6 23:34 backup_manifest
-rw------- 1 postgres postgres 188M Oct  6 23:34 base.tar.gz
-rw------- 1 postgres postgres  18K Oct  6 23:34 pg_wal.tar.gz

WAL日志连续归档
在完成了数据库的基础备份之后，还要对数据库WAL日志进行连续归档，因为默认PostgreSQL的WAL文件只有16个大小为16M的段，当16个seq写完后，PG会重复利用旧的seq并覆盖原先的内容。因此必须在wal的段文件覆盖之前把他备份到其他的地方，这就需要WAL日志归档功能。
修改配置文件postgresql.conf文件中的archive_mode和archive_command来打开WAL日志归档。
postgresql.conf

archive_mode=on表示打开归档备份，archive_command中的命令就是完成归档日志的备份。命令中的%p表示在线WAL日志的全路径名，%f表示不包括路径的WAL文件名。当然也可以通过scp命令来把归档日志备份到其他机器上。

# - Archiving -
archive_mode = on               # enables archiving; off, on, or always
                                # (change requires restart)
#archive_command = 'cp %p /var/lib/pgsql/backups/pg_wall_archive/%f '           

archive_command = 'test ! -f /usr/local/pg_back/archive_wals/%f && /usr/bin/lz4 -q -z %p /usr/local/pg_back/archive_wals/%f.lz4'

 # command to use to archive a logfile segment
                                # placeholders: %p = path of file to archive
                                #               %f = file name only
                                # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

物理热备与恢复实战
pg数据库机器: 192.168.203.140 backup机器: 192.168.203.141
Step1. 140开启postgres用户免密登录141。

[postgres@raw140 data]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa): 
Created directory '/var/lib/pgsql/.ssh'.
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa.
Your public key has been saved in /var/lib/pgsql/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:4nVAX2sAOLo05VjVE4/Cx47MSZd297HtYNyvv+iWjDs postgres@raw140.jack007.com
The key's randomart image is:
+---[RSA 3072]----+
|       o+oo..    |
|      =o oo* .   |
|     * .= O.= .. |
|    = .+ X o o o+|
|   . o. S o   +.+|
|    .. o .   . o.|
|      .     o . o|
|           E +.. |
|           .=o.oo|
+----[SHA256]-----+
[postgres@raw140 data]$ 
[postgres@raw140 data]$ ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@192.168.203.141
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/var/lib/pgsql/.ssh/id_rsa.pub"
The authenticity of host '192.168.203.141 (192.168.203.141)' can't be established.
ECDSA key fingerprint is SHA256:lh9uH9d0WGG740IJ+73DSI1Jm7/CW7jxQeeQFd7w4IQ.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
postgres@192.168.203.141's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'postgres@192.168.203.141'"
and check to make sure that only the key(s) you wanted were added.

[postgres@raw140 data]$ ssh 192.168.203.141
Last login: Wed Oct  6 23:17:19 2021
[postgres@raw141 ~]$

Step2. 140数据库开启日志归档,并设置归档命令为scp到远程。开始远程归档前，可以测试一下scp命令是否允许正常,然后确保远程的备份文件夹已经准备好。

## postgresql.conf
archive_mode = on               # enables archiving; off, on, or always
                                # (change requires restart)
archive_command = 'scp %p postgres@192.168.203.141:/var/lib/pgsql/backups/pg140_wal_archive/%f'

Step3. 设置pg_hba.conf，允许其他主机允许pg_basebackup连接，重启数据库

host    replication     all             0.0.0.0/0               md5
[jack@raw140 ~]$ sudo systemctl stop postgresql
[jack@raw140 ~]$ sudo systemctl start postgresql

Step4. 在141上执行基础备份。

[postgres@raw141 backups]$ ll
total 4
drwxr-xr-x 2 postgres postgres 4096 Oct  7 02:33 pg140_wal_archive
[postgres@raw141 backups]$ pg_basebackup -h 192.168.203.140 -Upostgres -Ft -z -P -D pg140_base_backup -l pg140_base_20211007
Password: 
923157/923157 kB (100%), 1/1 tablespace
[postgres@raw141 backups]$ ll -h
total 4.0K
drwx------ 2 postgres postgres   69 Oct  7 02:38 pg140_base_backup
drwxr-xr-x 2 postgres postgres 4.0K Oct  7 02:38 pg140_wal_archive

Step5. 在141上恢复数据库确保141的pg是停止状态。

[jack@raw141 ~]$ sudo systemctl stop postgresql 
[sudo] password for jack: 
[jack@raw141 ~]$ 
[jack@raw141 ~]$ 
[jack@raw141 ~]$ 
[jack@raw141 ~]$ sudo systemctl status postgresql 
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Thu 2021-10-07 02:54:39 EDT; 53min ago

解压全量备份的包到数据库的目录。

[postgres@raw141 pg140_base_backup]$ ll
total 457748
-rw------- 1 postgres postgres    137054 Oct  7 02:38 backup_manifest
-rw------- 1 postgres postgres 468571788 Oct  7 02:38 base.tar.gz
-rw------- 1 postgres postgres     17729 Oct  7 02:38 pg_wal.tar.gz
[postgres@raw141 pg140_base_backup]$ echo $PGDATA
/var/lib/pgsql/data
## 解压base.tar.gz到主目录
[postgres@raw141 pg140_base_backup]$ tar -zxvf base.tar.gz -C $PGDATA
backup_label
...
## 解压wal文件到pg_wal目录
[postgres@raw141 pg140_base_backup]$ tar -zxvf pg_wal.tar.gz -C $PGDATA/pg_wal
00000001000000000000004D
archive_status/00000001000000000000004D.done
[postgres@raw141 pg140_base_backup]$ 

[postgres@raw141 data]$ vim postgresql.conf
## 在postgresql.conf中取消掉140的wal归档设置
# - Archiving -

# archive_mode = on             # enables archiving; off, on, or always
                                # (change requires restart)
# archive_command = 'scp %p postgres@192.168.203.141:/var/lib/pgsql/backups/pg140_wal_archive/%f'

启动141的数据库
Step 6. 在141上应用最新的wal归档
因此需要修改postgresql.conf参数，添加与恢复相关的参数。我们知道140的wal归档路径为141的/var/lib/pgsql/backups/pg140_wal_archive目录。

recovery.conf中修改如下命令

（
restore_command = ‘cp /archive/%f %p’
recovery_target = ‘immediate’

）

restore_command = 'cp /var/lib/pgsql/backups/pg140_wal_archive/%f %p'           # command to use to restore an archived logfile segment
                                # placeholders: %p = path of file to restore
                                #               %f = file name only
                                # e.g. 'cp /mnt/server/archivedir/%f %p'
                                # (change requires restart)
recovery_target_timeline = 'latest'     # 'current', 'latest', or timeline ID
                                # (change requires restart)

在$PGDATA目录下创建一个信号文件

[postgres@raw141 data]$ touch recovery.signal
[postgres@raw141 data]$ 
[postgres@raw141 data]$ ll recovery.signal 
-rw-r--r-- 1 postgres postgres 0 Oct  7 04:17 recovery.signal

启动数据库