PostGreSql 12.6 的备份和恢复

备份和恢复支持如下几种方式：

1. SQL转储

2. 文件系统级别备份

3. 连续归档和时间点恢复（PITR）

一、SQL转储

1.1 pg_dump

[postgres@localhost bin]$ ./pg_dump -d mydb -f /home/postgres/mydb.dump ##全库备份 dbname 是 mydb

[postgres@localhost bin]$ ./pg_dump -d mydb -t weather -f /home/postgres/weather.dump ##表备份， mydb 数据库下的表 weather

[postgres@localhost bin]$ ./dropdb mydb

[postgres@localhost bin]$ ./psql -U postgres -d postgres -h 192.168.56.10 -p 543 2

用户 postgres 的口令：

psql (12.6)

输入 "help" 来获取帮助信息.

postgres=# \l

数据库列表

-----------+----------+----------+-------------+-------------+------------------

-----

+

gres

+

gres

(3 行记录)

## mydb不存在了

postgres=# \q

[postgres@localhost bin]$ ./createdb mydb

[postgres@localhost bin]$ ./psql -U postgres -d postgres -h 192.168.56.10 -p 543 2

用户 postgres 的口令：

psql (12.6)

输入 "help" 来获取帮助信息.

postgres=# \l

数据库列表

-----------+----------+----------+-------------+-------------+------------------

-----

+

gres

+

gres

(4 行记录)

postgres=# \c mydb

您现在已经连接到数据库 "mydb",用户 "postgres".

mydb=# \d

没有找到任何关系.

mydb=# \q

##重建DB，但是没有数据

[postgres@localhost bin]$ ./psql -d mydb -f /home/postgres/mydb.dump

SET

set_config

------------

(1 行记录)

SET

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE VIEW

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE VIEW

ALTER TABLE

COPY 0

COPY 1

COPY 0

COPY 3

[postgres@localhost bin]$ ./psql -U postgres -d postgres -h 192.168.56.10 -p 543 2

用户 postgres 的口令：

psql (12.6)

输入 "help" 来获取帮助信息.

postgres=# \c

您现在已经连接到数据库 "postgres",用户 "postgres".

postgres=# \c mydb

您现在已经连接到数据库 "mydb",用户 "postgres".

mydb=# \d

关联列表

架构模式 | 名称 | 类型 | 拥有者

----------+--------------+--------+----------

public | capitals | 数据表 | postgres

public | cities | 数据表 | postgres

public | cities01 | 视图 | postgres

public | myview | 视图 | postgres

public | non_capitals | 数据表 | postgres

public | weather | 数据表 | postgres

(6 行记录)

###数据表恢复了

1.2 pg_dumpall

##全库备份

[postgres@localhost bin]$ ./pg_dumpall > /home/postgres/pg_all.dump

[postgres@localhost bin]$ ./dropdb mydb

[postgres@localhost bin]$ ./dropdb mydb01

[postgres@localhost bin]$ ./psql -U postgres -d postgres -h 192.168.56.10 -p 543 2

用户 postgres 的口令：

psql (12.6)

输入 "help" 来获取帮助信息.

postgres=# \l

数据库列表

-----------+----------+----------+-------------+-------------+------------------

-----

+

gres

+

gres

(3 行记录)

##mydb 和 mydb01 不存在了

postgres=# \q

[postgres@localhost bin]$ ./psql -f /home/postgres/pg_all.dump

[postgres@localhost bin]$ ./psql -U postgres -d postgres -h 192.168.56.10 -p 5432

用户 postgres 的口令：

psql (12.6)

输入 "help" 来获取帮助信息.

postgres=# \l

数据库列表

-----------+----------+----------+-------------+-------------+-----------------------

(5 行记录)

postgres=# \c mydb01

您现在已经连接到数据库 "mydb01",用户 "postgres".

mydb01=# \d

关联列表

架构模式 | 名称 | 类型 | 拥有者

----------+--------------+--------+----------

public | capitals | 数据表 | postgres

public | cities | 数据表 | postgres

public | cities01 | 视图 | postgres

public | myview | 视图 | postgres

public | non_capitals | 数据表 | postgres

public | weather | 数据表 | postgres

(6 行记录)

mydb01=# select * from weather;

city | temp_lo | temp_hi | prcp | date

---------------+---------+---------+------+------------

San Francisco | 46 | 50 | 0.25 | 1994-11-27

San Francisco | 41 | 55 | 0 | 1994-11-29

Hayward | 35 | 52 | | 1994-11-29

(3 行记录)

##恢复之后，表、数据都恢复了

1.3 压缩和分割

主要是利用Linux的管线命令实现备份文件的压缩与分割。

[postgres@localhost bin]$ ./pg_dumpall | gzip > /home/postgres/pg_all.gz

[postgres@localhost bin]$ ls -al /home/postgres/

总用量 52

drwx------. 2 postgres postgres 4096 4月 13 07:18 .

drwxr-xr-x. 4 root root 39 4月 12 15:26 ..

-rw-------. 1 postgres postgres 1400 4月 12 17:43 .bash_history

-rw-r--r--. 1 postgres postgres 18 9月 7 2017 .bash_logout

-rw-r--r--. 1 postgres postgres 211 4月 12 17:43 .bash_profile

-rw-r--r--. 1 postgres postgres 231 9月 7 2017 .bashrc

-rw-rw-r--. 1 postgres postgres 2938 4月 13 06:05 mydb.dump

-rw-rw-r--. 1 postgres postgres 10854 4月 13 07:10 pg_all.dump

-rw-rw-r--. 1 postgres postgres 1328 4月 13 07:18 pg_all.gz

-rw-------. 1 postgres postgres 3537 4月 13 07:17 .psql_history

-rw-rw-r--. 1 postgres postgres 292 4月 12 16:04 t01.txt

-rw-rw-r--. 1 postgres postgres 1070 4月 13 06:06 weather.dump

[postgres@localhost bin]$ ./pg_dumpall | split -b 10k - /home/postgres/pg_all01.dump ##测试没有数据， 10k一个文件

[postgres@localhost bin]$ ls -al /home/postgres/

总用量 68

drwx------. 2 postgres postgres 4096 4月 13 07:32 .

drwxr-xr-x. 4 root root 39 4月 12 15:26 ..

-rw-------. 1 postgres postgres 1400 4月 12 17:43 .bash_history

-rw-r--r--. 1 postgres postgres 18 9月 7 2017 .bash_logout

-rw-r--r--. 1 postgres postgres 211 4月 12 17:43 .bash_profile

-rw-r--r--. 1 postgres postgres 231 9月 7 2017 .bashrc

-rw-rw-r--. 1 postgres postgres 2938 4月 13 06:05 mydb.dump

-rw-rw-r--. 1 postgres postgres 10240 4月 13 07:32 pg_all01.dumpaa

-rw-rw-r--. 1 postgres postgres 735 4月 13 07:32 pg_all01.dumpab

-rw-rw-r--. 1 postgres postgres 10854 4月 13 07:10 pg_all.dump

-rw-rw-r--. 1 postgres postgres 1328 4月 13 07:18 pg_all.gz

-rw-------. 1 postgres postgres 3537 4月 13 07:17 .psql_history

-rw-rw-r--. 1 postgres postgres 292 4月 12 16:04 t01.txt

[postgres@localhost bin]$ ./dropdb mydb01

[postgres@localhost bin]$ ./dropdb mydb

[postgres@localhost bin]$ ./psql -U postgres -d postgres -h 192.168.56.10 -p 5432

[postgres@localhost bin]$ cat /home/postgres/pg_all01.dump* | psql

[postgres@localhost bin]$ ./dropdb mydb

[postgres@localhost bin]$ ./dropdb mydb01

[postgres@localhost bin]$ gunzip -c /home/postgres/pg_all.gz | psql

二、文件系统级别备份

冷备了，停机之后拷贝对应的文件，一般是有重大升级、迁移前会做一次，一般不会这样操作。

三、连续归档和时间点恢复

热备了，在系统工作的时候，开启归档，进行数据的备份，需要恢复的时候，先回复基础备份，之后再通过归档文件做前滚操作，恢复数据到发生事故前的状态。

3.1 启用归档

[postgres@localhost ~]$ cat /var/lib/pgsql/12/data/postgresql.conf

archive_mode = on # enables archiving; off, on, or always

# (change requires restart)

archive_command = 'test ! -f /home/postgres/arc/%f && cp %p /home/postgres/arc/%f'

# 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/archivedi

[postgres@localhost bin]$ ./pg_ctl status -D /var/lib/pgsql/12/data

pg_ctl: 正在运行服务器进程(PID: 2024)

/usr/pgsql-12/bin/postgres "-D" "/var/lib/pgsql/12/data"

[postgres@localhost bin]$ ./pg_ctl restart -D /var/lib/pgsql/12/data

等待服务器进程关闭 .... 完成

服务器进程已经关闭

等待服务器进程启动 ....2021-04-13 08:04:31.954 CST [4872] 日志: 正在启动 PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

2021-04-13 08:04:31.954 CST [4872] 日志: 正在监听IPv4地址"0.0.0.0"，端口 5432

2021-04-13 08:04:31.954 CST [4872] 日志: 正在监听IPv6地址"::"，端口 5432

2021-04-13 08:04:31.956 CST [4872] 日志: 在Unix套接字 "/var/run/postgresql/.s.PGSQL.5432"上侦听

2021-04-13 08:04:31.960 CST [4872] 日志: 在Unix套接字 "/tmp/.s.PGSQL.5432"上侦听

2021-04-13 08:04:31.964 CST [4872] 日志: 日志输出重定向到日志收集进程

2021-04-13 08:04:31.964 CST [4872] 提示: 后续的日志输出将出现在目录 "log"中.完成

服务器进程已经启动

[postgres@localhost bin]$ ps -ef|grep postgres:|grep -v grep

postgres 4873 4872 0 08:04 ? 00:00:00 postgres: logger

postgres 4875 4872 0 08:04 ? 00:00:00 postgres: checkpointer

postgres 4876 4872 0 08:04 ? 00:00:00 postgres: background writer

postgres 4877 4872 0 08:04 ? 00:00:00 postgres: walwriter

postgres 4878 4872 0 08:04 ? 00:00:00 postgres: autovacuum launcher

postgres 4879 4872 0 08:04 ? 00:00:00 postgres: archiver last was 000000010000000000000003

postgres 4880 4872 0 08:04 ? 00:00:00 postgres: stats collector

postgres 4881 4872 0 08:04 ? 00:00:00 postgres: logical replication launcher

postgres 4969 4872 0 08:08 ? 00:00:00 postgres: postgres postgres 192.168.56.10(40860) idle

[postgres@localhost bin]$ ./psql -U postgres -d postgres -h 192.168.56.10 -p 5432

用户 postgres 的口令：

psql (12.6)

输入 "help" 来获取帮助信息.

postgres=# select pg_switch_wal();

pg_switch_wal

---------------

0/18EC338

(1 行记录)

## 如果在 /var/lib/pgsql/12/data/pg_wal 和 /home/postgres/arc/ 产生新的归档文件，设置就成功了。

3.2 开始备份

[postgres@localhost 12]$ touch /var/lib/pgsql/backup_in_progress

[postgres@localhost 12]$ psql -c "select pg_start_backup('hot_backup');"

pg_start_backup

-----------------

0/20000028

(1 行记录)

[postgres@localhost 12]$ tar -Pcvf /var/lib/pgsql/12/backup.tar.gz /var/lib/pgsql/12/data/

[postgres@localhost 12]$ psql -c "select pg_stop_backup();"

注意: 所有需要的WAL段都已经归档完成

pg_stop_backup

----------------

0/20000138

(1 行记录)

[postgres@localhost 12]$ rm /var/lib/pgsql/backup_in_progress

[postgres@localhost 12]$ tar -Prf /var/lib/pgsql/12/backup.tar.gz /var/lib/pgsql/12/archive/

[postgres@localhost 12]$ ls -al

总用量 124556

drwx------. 6 postgres postgres 4096 4月 13 19:13 .

drwx------. 3 postgres postgres 35 4月 13 19:13 ..

drwxrwxr-x. 2 postgres postgres 6 4月 13 10:54 archive

drwx------. 2 postgres postgres 6 2月 11 09:16 backups

-rw-rw-r--. 1 postgres postgres 127528960 4月 13 19:14 backup.tar.gz

可以做成一个脚本：

##******

touch /var/lib/pgsql/backup_in_progress

psql -c "select pg_start_backup('hot_backup');"

cd /var/lib/pgsql/12/

tar -Pcvf /var/lib/pgsql/12/backup.tar.gz /var/lib/pgsql/12/data/

psql -c "select pg_stop_backup();"

rm /var/lib/pgsql/backup_in_progress

tar -Prf /var/lib/pgsql/12/backup.tar.gz /var/lib/pgsql/12/archive/

##******

备份后的操作

mydb=# alter table weather rename to weather09;

ALTER TABLE

mydb=# \d

关联列表

架构模式 | 名称 | 类型 | 拥有者

----------+-----------+--------+----------

public | weather01 | 数据表 | postgres

public | weather09 | 数据表 | postgres

(2 行记录)

mydb=# \i /home/postgres/weather.dump

mydb=# \d

关联列表

架构模式 | 名称 | 类型 | 拥有者

----------+-----------+--------+----------

public | weather | 数据表 | postgres

public | weather01 | 数据表 | postgres

public | weather09 | 数据表 | postgres

(3 行记录)

mydb=# select pg_switch_wal();

pg_switch_wal

---------------

0/21025C90

(1 行记录)

3.3 恢复测试

[postgres@localhost ~]$ pg_ctl stop -D /var/lib/pgsql/12/data

等待服务器进程关闭 .... 完成

服务器进程已经关闭

[postgres@localhost 12]$ mv data data.new.bak

[postgres@localhost 12]$ tar -Pxvf backup.tar.gz -C /

[postgres@localhost 12]$ ls -al

总用量 124560

drwx------. 7 postgres postgres 4096 4月 13 19:39 .

drwx------. 3 postgres postgres 35 4月 13 19:13 ..

drwxrwxr-x. 2 postgres postgres 6 4月 13 10:54 archive

drwx------. 2 postgres postgres 6 2月 11 09:16 backups

-rw-rw-r--. 1 postgres postgres 127528960 4月 13 19:14 backup.tar.gz

drwx------. 20 postgres postgres 4096 4月 13 19:13 data

drwx------. 20 postgres postgres 4096 4月 13 12:16 data01.bak

drwx------. 20 postgres postgres 4096 4月 13 19:38 data.new.bak

-rw-------. 1 postgres postgres 997 4月 12 15:32 initdb.log

修改恢复参数：

[postgres@localhost log]$ cat /var/lib/pgsql/12/data/postgresql.conf

restore_command = 'cp /home/postgres/arc/%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_time = '2021-04-13 19:43:16' ##操作的当前时间

[postgres@localhost data]$ touch /var/lib/pgsql/12/data/recovery.signal ##pg12之后使用这个标识文件了，空的文件就可以

[postgres@localhost data]$ pg_ctl start -D /var/lib/pgsql/12/data

等待服务器进程启动 ....2021-04-13 19:47:53.898 CST [9681] 日志: 正在启动 PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

2021-04-13 19:47:53.898 CST [9681] 日志: 正在监听IPv4地址"0.0.0.0"，端口 5432

2021-04-13 19:47:53.898 CST [9681] 日志: 正在监听IPv6地址"::"，端口 5432

2021-04-13 19:47:53.900 CST [9681] 日志: 在Unix套接字 "/var/run/postgresql/.s.PGSQL.5432"上侦听

2021-04-13 19:47:53.904 CST [9681] 日志: 在Unix套接字 "/tmp/.s.PGSQL.5432"上侦听

2021-04-13 19:47:53.908 CST [9681] 日志: 日志输出重定向到日志收集进程

2021-04-13 19:47:53.908 CST [9681] 提示: 后续的日志输出将出现在目录 "log"中.. 完成

服务器进程已经启动

[postgres@localhost log]$ psql -U postgres -d postgres -h 192.168.56.10 -p 5432

用户 postgres 的口令：

psql (12.6)

输入 "help" 来获取帮助信息.

postgres=# \c

您现在已经连接到数据库 "postgres",用户 "postgres".

postgres=# \c mydb

您现在已经连接到数据库 "mydb",用户 "postgres".

mydb=# \d

关联列表

架构模式 | 名称 | 类型 | 拥有者

----------+-----------+--------+----------

public | weather | 数据表 | postgres

public | weather01 | 数据表 | postgres

public | weather09 | 数据表 | postgres

(3 行记录)

mydb=# \d weather09

数据表 "public.weather09"

栏位 | 类型 | 校对规则 | 可空的 | 预设

---------+-----------------------+----------+--------+------

city | character varying(80) | | |

temp_lo | integer | | |

temp_hi | integer | | |

prcp | real | | |

date | date | | |

mydb=# insert into weather09 values('Dalian',50,60,0.75,'1994-11-27');

INSERT 0 1

mydb=# select * from weather09;

city | temp_lo | temp_hi | prcp | date

---------------+---------+---------+------+------------

San Francisco | 46 | 50 | 0.25 | 1994-11-27

San Francisco | 41 | 55 | 0 | 1994-11-29

Hayward | 35 | 52 | | 1994-11-29

Dalian | 50 | 60 | 0.75 | 1994-11-27

(4 行记录)

##备份之后的操作结果也存在恢复的数据里面了。数据表也可以insert shuju。

基本的一个操作记录，备查吧。。。。。。

ITPub博客

PostGreSql12.6的备份恢复

最新文章