逻辑备份
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.