PostgreSQL PITR实践
Contents
初始化示例数据库
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 03:22:35]
└─[0] <> mkdir pitr-data
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 03:22:47]
└─[0] <> initdb -D pitr-data
The files belonging to this database system will be owned by user "sky".
This user must also own the server process.
The database cluster will be initialized with locale "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".
Data page checksums are disabled.
fixing permissions on existing directory pitr-data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D pitr-data -l logfile start
修改配置文件
创建一个保存归档文件的目录：
┌─[sky@sky-linux] - [/ihome/db/postgresql/pitr-data] - [2016-09-19 03:31:47]
└─[0] <> mkdir archive_logs
┌─[sky@sky-linux] - [/ihome/db/postgresql/pitr-data] - [2016-09-19 03:31:59]
└─[0] <> pwd
/ihome/db/postgresql/pitr-data
修改配置文件postgresql.conf
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 03:24:51]
└─[0] <> vim pitr-data/postgresql.conf
将下面三个参数修改如下:
wal_level = hot_standby # minimal, archive, hot_standby, or logical
# (change requires restart)
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'cp %p /ihome/db/postgresql/pitr-data/archive_logs/%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/archivedir/%f'
然后启动PG:
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 03:36:08]
└─[1] <> pg_ctl -D pitr-data start
server starting
插入测试数据
[local]:5432 sky@postgres=# CREATE TABLE test_pitr (id int);
CREATE TABLE
Time: 2.653 ms
[local]:5432 sky@postgres=# INSERT INTO test_pitr VALUES (1), (2
[local]:5432 sky@postgres=# INSERT INTO test_pitr VALUES (1), (2), (3);
INSERT 0 3
Time: 1.695 ms
[local]:5432 sky@postgres=# SELECT * from test_pitr ;
id
----
1
2
3
(3 rows)
Time: 0.144 ms
[local]:5432 sky@postgres=# SELECT now();
now
-------------------------------
2016-09-19 15:38:48.364466+08
(1 row)
Time: 0.196 ms
[local]:5432 sky@postgres=#
然后进行基础备份
[local]:5432 sky@postgres=# select pg_start_backup('basebackup20160915');
pg_start_backup
-----------------
0/3000028
(1 row)
Time: 1913.146 ms
[local]:5432 sky@postgres=#
执行完上面的命令后，不要关闭该终端，然后从另一个终端执行备份:
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 03:41:36]
└─[0] <> tar -cvf pitr-data.tar.gz pitr-data
基础备份完后，回到psql终端，停止基础备份状态:
[local]:5432 sky@postgres=# select pg_stop_backup();
NOTICE: 00000: pg_stop_backup complete, all required WAL segments have been archived
LOCATION: do_pg_stop_backup, xlog.c:10543
pg_stop_backup
----------------
0/3000168
(1 row)
Time: 1102.195 ms
[local]:5432 sky@postgres=#
再插入不同时间点的数据
[local]:5432 sky@postgres=# INSERT INTO test_pitr VALUES (111),(222),(333);
INSERT 0 3
Time: 1.695 ms
[local]:5432 sky@postgres=# SELECT now();
now
-------------------------------
2016-09-19 15:44:32.996545+08
(1 row)
Time: 0.149 ms
[local]:5432 sky@postgres=# INSERT INTO test_pitr VALUES (1111),(2222),(3333);
INSERT 0 3
Time: 0.807 ms
[local]:5432 sky@postgres=# SELECT now();
now
-------------------------------
2016-09-19 15:44:58.188828+08
(1 row)
Time: 0.147 ms
[local]:5432 sky@postgres=#
恢复
切换事务日志文件:
[local]:5432 sky@postgres=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/4000510
(1 row)
Time: 41.775 ms
停止PostgreSQL
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 03:41:36]
└─[0] <> pg_ctl -D pitr-data stop
waiting for server to shut down.... done
server stopped
恢复到时间点 2016-09-19 15:44:32.996545+08
即上面的:
[local]:5432 sky@postgres=# INSERT INTO test_pitr VALUES (111),(222),(333);
INSERT 0 3
Time: 1.695 ms
[local]:5432 sky@postgres=# SELECT now();
now
-------------------------------
2016-09-19 15:44:32.996545+08
(1 row)
Time: 0.149 ms
执行以下命令：
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 03:50:07]
└─[0] <> mv pitr-data pitr-data.bak
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 03:50:19]
└─[0] <> ll
total 138M
drwxr-xr-x 3 sky sky 4.0K 1月 12 2016 build
lrwxrwxrwx 1 sky sky 16 9月 19 15:29 current -> postgresql-9.5.0
drwx------ 20 sky sky 4.0K 9月 19 15:46 pitr-data.bak
-rw-r--r-- 1 sky sky 102M 9月 19 15:41 pitr-data.tar.gz
drwxr-xr-x 7 sky sky 4.0K 1月 15 2016 postgresql-9.5.0
-rw-r--r-- 1 sky sky 18M 1月 12 2016 postgresql-9.5.0.tar.bz2
drwxr-xr-x 7 sky sky 4.0K 8月 18 14:32 postgresql-9.6.0
drwxr-xr-x 6 sky sky 4.0K 8月 18 14:26 postgresql-9.6beta4
-rw-r--r-- 1 sky sky 19M 8月 11 23:54 postgresql-9.6beta4.tar.bz2
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 03:50:37]
└─[0] <> tar -xvf pitr-data.tar.gz pitr-data
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 03:51:13]
└─[0] <> ll
total 138M
drwxr-xr-x 3 sky sky 4.0K 1月 12 2016 build
lrwxrwxrwx 1 sky sky 16 9月 19 15:29 current -> postgresql-9.5.0
drwx------ 20 sky sky 4.0K 9月 19 15:39 pitr-data
drwx------ 20 sky sky 4.0K 9月 19 15:46 pitr-data.bak
-rw-r--r-- 1 sky sky 102M 9月 19 15:41 pitr-data.tar.gz
drwxr-xr-x 7 sky sky 4.0K 1月 15 2016 postgresql-9.5.0
-rw-r--r-- 1 sky sky 18M 1月 12 2016 postgresql-9.5.0.tar.bz2
drwxr-xr-x 7 sky sky 4.0K 8月 18 14:32 postgresql-9.6.0
drwxr-xr-x 6 sky sky 4.0K 8月 18 14:26 postgresql-9.6beta4
-rw-r--r-- 1 sky sky 19M 8月 11 23:54 postgresql-9.6beta4.tar.bz2
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 03:51:40]
└─[0] <>
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 03:51:40]
└─[0] <> rm -rf pitr-data/pg_xlog
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 03:52:28]
└─[0] <> cp -r pitr-data.bak/pg_xlog pitr-data/
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 03:52:58]
└─[0] <> cp -r pitr-data.bak/archive_logs pitr-data/
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 03:54:23]
└─[0] <> rm -rf pitr-data/pg_xlog/archive_status/*
zsh: sure you want to delete all the files in /ihome/db/postgresql/pitr-data/pg_xlog/archive_status [yn]? y
创建恢复文件：
这个文件名字为recovery.conf，放在$DATA目录的根目录下即可。
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 03:55:51]
└─[0] <> cp current/share/recovery.conf.sample pitr-data/
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 03:56:09]
└─[0] <> vim pitr-data/recovery.conf.sample
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 03:56:39]
└─[0] <> mv pitr-data/recovery.conf.sample pitr-data/recovery.conf
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 03:56:51]
└─[0] <> vim pitr-data/recovery.conf
添加以下内容：
recovery_target_time = '2016-09-19 15:44:32.996545+08' # e.g. '2004-07-14 22:39:00 EST'
restore_command = 'cp /ihome/db/postgresql/pitr-data/archive_logs/%f %p' # e.g. 'cp /mnt/server/archivedir/%f %p'
recovery_target_time：即是我们想要恢复到的某个时间点.
restore_command：即是原来在postgresql.conf里配置的archive_command，只是将%p与%f互换下位置即可.
配置好之后，即可重启PG了.
由于在操作时有点问题，我又重做了一次以上操作并修正了忽略的步骤就成功了，时间点恢复为:2016-09-19 16:21:06.174798+08
如果成功的话，可以看到类似以下的信息:
┌─[sky@sky-linux] - [/ihome/db/postgresql] - [2016-09-19 04:29:22]
└─[0] <> LOG: database system was interrupted; last known up at 2016-09-19 16:19:36 CST
LOG: starting point-in-time recovery to 2016-09-19 16:21:06.174798+08
LOG: restored log file "000000010000000000000002" from archive
LOG: redo starts at 0/2000060
LOG: consistent recovery state reached at 0/2000168
LOG: restored log file "000000010000000000000003" from archive
LOG: restored log file "000000010000000000000004" from archive
LOG: recovery stopping before commit of transaction 666, time 2016-09-19 16:21:48.575228+08
LOG: redo done at 0/4000120
LOG: last completed transaction was at log time 2016-09-19 16:21:03.886997+08
cp: cannot stat ‘/ihome/db/postgresql/pitr-data/archive_logs/00000002.history’: No such file or directory
LOG: selected new timeline ID: 2
cp: cannot stat ‘/ihome/db/postgresql/pitr-data/archive_logs/00000001.history’: No such file or directory
LOG: archive recovery complete
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
[local]:5432 sky@postgres=# SELECT * from test_pitr ;
id
-----
1
2
3
111
222
333
(6 rows)
Time: 0.224 ms
[local]:5432 sky@postgres=#
可以看到1111, 2222, 3333这数据并没有了.