PostGreSql12.6的备份恢复

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                                      数据库列表    名称    |  拥有者  | 字元编码 |  校对规则   |    Ctype    |       存取权限 -----------+----------+----------+-------------+-------------+------------------ -----  postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |  template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres     +            |          |          |             |             | postgres=CTc/post gres  template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres     +            |          |          |             |             | postgres=CTc/post 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                                      数据库列表    名称    |  拥有者  | 字元编码 |  校对规则   |    Ctype    |       存取权限 -----------+----------+----------+-------------+-------------+------------------ -----  mydb      | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |  postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |  template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres     +            |          |          |             |             | postgres=CTc/post gres  template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres     +            |          |          |             |             | postgres=CTc/post 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 SET SET SET  set_config ------------ (1 行记录) SET SET SET SET SET 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                                      数据库列表    名称    |  拥有者  | 字元编码 |  校对规则   |    Ctype    |       存取权限 -----------+----------+----------+-------------+-------------+------------------ -----  postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |  template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres     +            |          |          |             |             | postgres=CTc/post gres  template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres     +            |          |          |             |             | postgres=CTc/post 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                                      数据库列表    名称    |  拥有者  | 字元编码 |  校对规则   |    Ctype    |       存取权限 -----------+----------+----------+-------------+-------------+-----------------------  mydb      | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |  mydb01    | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |  postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |  template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +            |          |          |             |             | postgres=CTc/postgres  template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +            |          |          |             |             | postgres=CTc/postgres (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。 基本的一个操作记录，备查吧。。。。。。