postgresql11 备份

逻辑备份 

postgres=# select * from t;
 id | name 
----+------
  1 | http
  2 | qdds
(2 rows)

postgres=# l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 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
(3 rows)

postgres=# d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t    | table | postgres
(1 row)

pg_dump -p 5432 --inserts --column-inserts -d postgres | gzip > /u01/pgsql/backup/dump.sql.gz

逻辑恢复

[postgres@redis01 backup]$ gzip -d /u01/pgsql/backup/dump.sql.gz
[postgres@redis01 backup]$ pg_restore -p 5432 -C -d postgres /u01/pgsql/backup/dump.sql
pg_restore: [archiver] input file appears to be a text format dump. Please use psql.
[postgres@redis01 backup]$ psql -p 5432 -d postgres  -f  /u01/pgsql/backup/dump.sql 
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
INSERT 0 1
INSERT 0 1
ALTER TABLE

  

    一个数据库(或者部分对象)导出为脚本文本文件,用psql恢复。
    一个数据库(或者部分对象)导出为归档文件,用pg_restore恢复。

   导出为其他格式的文件

pg_dump -p 5432  -Fc -d postgres  > /u01/pgsql/backup/dump.dmp
[postgres@redis01 backup]$ pg_restore -p 5432  -d postgres /u01/pgsql/backup/dump.dmp

物理备份 冷备

 

pg_ctl stop
tar -cvjpf /u01/pgsql/colddata.tar.gz data
恢复

tar -xvf colddata.tar.gz
pg_ctl start

开启归档热备

[root@redis01 pgsql]# cat hot.sh 
export POSTDATA=/u01/pgsql
export POSTBACKUPDATA=/u01/pgsql/backup
export PGPASSFILE=/u01/pgsql/.pgpass
backupname=`date +"%Y%m%d%H%M%S"`
echo "$backupname"
cd $POSTDATA
echo "$PWD"
psql -h 192.168.20.201  -d postgres -U postgres -c "select pg_start_backup('hot_backup$backupname');"
tar -cvjpf $POSTBACKUPDATA/hot$backupname.tar.gz data
psql -h 192.168.20.201  -d postgres -U postgres -c "select pg_stop_backup();select pg_switch_wal();"

备份完毕以后前面的归档日志可以删除
-rw-r----- 1 postgres postgres 16777216 Oct 12 10:14 pg_00000001000000000000000E
-rwxr-x--- 1 postgres postgres 16777216 Oct 12 10:26 pg_00000001000000000000000F
-rw-r----- 1 postgres postgres 16777216 Oct 12 11:12 pg_000000010000000000000010
-rwxr-x--- 1 postgres postgres 16777216 Oct 12 11:14 pg_000000010000000000000011
-rw-r----- 1 postgres postgres 16777216 Oct 12 11:15 pg_000000010000000000000012
-rw-r----- 1 postgres postgres 16777216 Oct 12 11:39 pg_000000010000000000000013
-rw-r----- 1 postgres postgres 16777216 Oct 12 11:39 pg_000000010000000000000014
-rw-r----- 1 postgres postgres      346 Oct 12 11:39 pg_000000010000000000000014.00000060.backup
[postgres@redis01 archive]$ cat pg_000000010000000000000014.00000060.backup
START WAL LOCATION: 0/14000060 (file 000000010000000000000014)
STOP WAL LOCATION: 0/14000130 (file 000000010000000000000014)
CHECKPOINT LOCATION: 0/14000098
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2019-10-12 11:39:42 CST
LABEL: hot_backup20191012113942
START TIMELINE: 1
STOP TIME: 2019-10-12 11:39:45 CST
STOP TIMELINE: 1

生成大量数据
postgres=# select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 0/15029668
(1 row)

postgres=# insert into t  select  * ,'海天' as "1"  FROM generate_series(1001,10000);
INSERT 0 9000
postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/1515CCA0
(1 row)

postgres=# select  count(1) from t;
 count 
-------
 10000
(1 row)
postgres=# select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 0/16000140
(1 row)

查看归档日志
-rw-r----- 1 postgres postgres      346 Oct 12 11:39 pg_000000010000000000000014.00000060.backup
-rwxr-x--- 1 postgres postgres 16777216 Oct 12 14:23 pg_000000010000000000000015

drop表以后使用上次的热备+归档进行恢复
postgres=# drop table t;
DROP TABLE
pg_ctl stop

基于 Point-in-Time Recovery (PITR)恢复
tar -xf hot20191012113942.tar.gz  -C ../
[postgres@redis01 pgsql]$ cd share
[postgres@redis01 share]$ ls
conversion_create.sql  information_schema.sql  pg_service.conf.sample  postgresql.conf.sample  recovery.conf.sample  system_views.sql  tsearch_data
errcodes.txt           pg_hba.conf.sample      postgres.bki            postgres.shdescription  snowball_create.sql   timezone
extension              pg_ident.conf.sample    postgres.description    psqlrc.sample           sql_features.txt      timezonesets
[postgres@redis01 share]$ cp recovery.conf.sample ../data/

mv recovery.conf.sample recovery.conf

[postgres@redis01 data]$ tail -3 recovery.conf 
restore_command = 'cp /u01/pgsql/archive/pg_%f %p' #pg_wal里没有的日志从归档目录处拷贝	
recovery_target_lsn = '0/16000140' #恢复到指定的lsn
recovery_target_inclusive = true  #这个时间点上的commit是否要包括进去.true就是包括

另外结合如下参数进行数据库的恢复:
recovery_target_time (timestamp)
recovery_target_xid (string)
recovery_target_inclusive (boolean)
recovery_target_timeline (string)

恢复的具体日志
[postgres@redis01 log]$ cat postgresql-2019-10-12_150259.log
2019-10-12 15:02:59.624 CST [8319] LOG:  database system was interrupted while in recovery at log time 2019-10-12 11:39:42 CST
2019-10-12 15:02:59.624 CST [8319] HINT:  If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.
2019-10-12 15:02:59.865 CST [8320] FATAL:  the database system is starting up
2019-10-12 15:02:59.872 CST [8321] FATAL:  the database system is starting up
2019-10-12 15:02:59.906 CST [8322] FATAL:  the database system is starting up
2019-10-12 15:02:59.914 CST [8323] FATAL:  the database system is starting up
2019-10-12 15:02:59.947 CST [8324] FATAL:  the database system is starting up
2019-10-12 15:03:00.018 CST [8325] FATAL:  the database system is starting up
2019-10-12 15:03:00.028 CST [8326] FATAL:  the database system is starting up
2019-10-12 15:03:00.062 CST [8327] FATAL:  the database system is starting up
2019-10-12 15:03:00.070 CST [8328] FATAL:  the database system is starting up
2019-10-12 15:03:00.121 CST [8329] FATAL:  the database system is starting up
2019-10-12 15:03:00.128 CST [8330] FATAL:  the database system is starting up
2019-10-12 15:03:00.164 CST [8331] FATAL:  the database system is starting up
2019-10-12 15:03:00.169 CST [8332] FATAL:  the database system is starting up
2019-10-12 15:03:00.207 CST [8333] FATAL:  the database system is starting up
2019-10-12 15:03:00.214 CST [8334] FATAL:  the database system is starting up
2019-10-12 15:03:00.247 CST [8335] FATAL:  the database system is starting up
2019-10-12 15:03:00.283 CST [8336] FATAL:  the database system is starting up
2019-10-12 15:03:00.290 CST [8337] FATAL:  the database system is starting up
2019-10-12 15:03:00.325 CST [8338] FATAL:  the database system is starting up
2019-10-12 15:03:00.336 CST [8339] FATAL:  the database system is starting up
2019-10-12 15:03:00.358 CST [8340] FATAL:  the database system is starting up
2019-10-12 15:03:00.378 CST [8341] FATAL:  the database system is starting up
2019-10-12 15:03:00.397 CST [8342] FATAL:  the database system is starting up
2019-10-12 15:03:00.423 CST [8343] FATAL:  the database system is starting up
2019-10-12 15:03:00.433 CST [8344] FATAL:  the database system is starting up
2019-10-12 15:03:00.466 CST [8345] FATAL:  the database system is starting up
2019-10-12 15:03:00.473 CST [8346] FATAL:  the database system is starting up
2019-10-12 15:03:00.507 CST [8347] FATAL:  the database system is starting up
2019-10-12 15:03:00.523 CST [8348] FATAL:  the database system is starting up
2019-10-12 15:03:00.545 CST [8349] FATAL:  the database system is starting up
2019-10-12 15:03:00.564 CST [8350] FATAL:  the database system is starting up
2019-10-12 15:03:00.592 CST [8351] FATAL:  the database system is starting up
2019-10-12 15:03:00.622 CST [8352] FATAL:  the database system is starting up
2019-10-12 15:03:00.633 CST [8353] FATAL:  the database system is starting up
2019-10-12 15:03:00.657 CST [8354] FATAL:  the database system is starting up
2019-10-12 15:03:00.674 CST [8355] FATAL:  the database system is starting up
2019-10-12 15:03:00.699 CST [8356] FATAL:  the database system is starting up
2019-10-12 15:03:00.718 CST [8357] FATAL:  the database system is starting up
2019-10-12 15:03:00.735 CST [8358] FATAL:  the database system is starting up
2019-10-12 15:03:00.766 CST [8359] FATAL:  the database system is starting up
2019-10-12 15:03:00.778 CST [8360] FATAL:  the database system is starting up
2019-10-12 15:03:00.814 CST [8361] FATAL:  the database system is starting up
2019-10-12 15:03:00.821 CST [8362] FATAL:  the database system is starting up
2019-10-12 15:03:00.860 CST [8363] FATAL:  the database system is starting up
2019-10-12 15:03:00.866 CST [8364] FATAL:  the database system is starting up
2019-10-12 15:03:00.908 CST [8365] FATAL:  the database system is starting up
2019-10-12 15:03:00.914 CST [8366] FATAL:  the database system is starting up
2019-10-12 15:03:00.962 CST [8367] FATAL:  the database system is starting up
2019-10-12 15:03:00.968 CST [8368] FATAL:  the database system is starting up
2019-10-12 15:03:01.008 CST [8369] FATAL:  the database system is starting up
2019-10-12 15:03:01.014 CST [8370] FATAL:  the database system is starting up
2019-10-12 15:03:01.042 CST [8319] LOG:  starting point-in-time recovery to WAL location (LSN) "0/16000140"
2019-10-12 15:03:01.051 CST [8372] FATAL:  the database system is starting up
2019-10-12 15:03:01.057 CST [8373] FATAL:  the database system is starting up
2019-10-12 15:03:01.058 CST [8319] LOG:  restored log file "000000010000000000000014" from archive
2019-10-12 15:03:01.107 CST [8374] FATAL:  the database system is starting up
2019-10-12 15:03:01.112 CST [8375] FATAL:  the database system is starting up
2019-10-12 15:03:01.145 CST [8376] FATAL:  the database system is starting up
2019-10-12 15:03:01.152 CST [8377] FATAL:  the database system is starting up
2019-10-12 15:03:01.182 CST [8378] FATAL:  the database system is starting up
2019-10-12 15:03:01.188 CST [8379] FATAL:  the database system is starting up
2019-10-12 15:03:01.221 CST [8380] FATAL:  the database system is starting up
2019-10-12 15:03:01.226 CST [8381] FATAL:  the database system is starting up
2019-10-12 15:03:01.267 CST [8382] FATAL:  the database system is starting up
2019-10-12 15:03:01.273 CST [8383] FATAL:  the database system is starting up
2019-10-12 15:03:01.300 CST [8319] LOG:  redo starts at 0/14000060
2019-10-12 15:03:01.311 CST [8386] FATAL:  the database system is starting up
2019-10-12 15:03:01.317 CST [8387] FATAL:  the database system is starting up
2019-10-12 15:03:01.351 CST [8388] FATAL:  the database system is starting up
2019-10-12 15:03:01.357 CST [8389] FATAL:  the database system is starting up
2019-10-12 15:03:01.359 CST [8319] LOG:  consistent recovery state reached at 0/14000130
2019-10-12 15:03:01.359 CST [8317] LOG:  database system is ready to accept read only connections
2019-10-12 15:03:01.377 CST [8319] LOG:  restored log file "000000010000000000000015" from archive
2019-10-12 15:03:01.642 CST [8319] LOG:  restored log file "000000010000000000000016" from archive
2019-10-12 15:03:01.775 CST [8319] LOG:  recovery stopping after WAL location (LSN) "0/16000140"
2019-10-12 15:03:01.775 CST [8319] LOG:  recovery has paused
2019-10-12 15:03:01.775 CST [8319] HINT:  Execute pg_wal_replay_resume() to continue.

  

  

原文地址:https://www.cnblogs.com/omsql/p/11656005.html