PostgreSQL时间点恢复（增量备份）

PostgreSQL的“时间点恢复”（PITR）也称为增量数据库备份，在线备份或可能是存档备份。 PostgreSQL服务器记录所有用户的数据修改事务，例如插入，更新或删除，并将其写入文件调用预写（WAL）日志文件中。 该机制使用存储在WAL文件中的历史记录来进行自上次数据库完全备份以来所做的前滚更改。

优点

1. 零停机时间–增量数据库备份对于无法承受一分钟停机时间的关键系统非常重要。 使用时间点恢复，可以完全消除数据库备份的停机时间，因为该机制可以使数据库备份和系统访问同时进行。
2. 节省存储空间–通过增量数据库备份，我们将备份自上次备份以来的最新存档日志文件，而不是每天进行完整数据库备份。

如果您担心上述优点，那么应该始终实施增量数据库备份。 在这里，我演示了如何在PostgreSQL服务器中进行时间点恢复（增量备份）。

PostgreSQL备份步骤摘要

1. 修改postgresql.conf以支持存档日志
2. 进行基本备份（完整数据库备份）
3. 备份基本备份到远程存储。
4. 将WAL（归档日志文件）备份到远程存储（连续过程）

PostgreSQL时间点恢复步骤摘要

1. 从基本备份中提取文件
2. 从pg_xlog文件夹复制文件
3. 创建recovery.conf文件
4. 开始恢复

教程开始了〜漫长的旅程，耐心等待...

数据库初始化

1）创建一个测试数据库，所有数据库文件位于/ usr / local / pgsql / pgDataPITR /

初始化数据库

[mkyong]$ initdb -D /usr/local/pgsql/pgDataPITR/

启动数据库

[mkyong]$ pg_ctl start -D /usr/local/pgsql/pgDataPITR/

2）更改Postgresql配置文件（postgresql.conf） ，我们需要对postgresql.conf文件进行一些更改，以告诉PostgreSQL如何复制或归档从PostgreSQL服务器生成的WAL文件。

修改postgresql.conf

[mkyong]$ vi postgresql.conf

在postgresql.conf中进行以下更改

archive_command = on
archive_command = 'cp %p /usr/local/pgsql/pgDataPITR/wals/%f'

创建一个wals文件夹

[mkyong]$ mkdir /usr/local/pgsql/pgDataPITR/wals

重新启动数据库

[mkyong]$ pg_ctl stop -D /usr/local/pgsql/pgDataPITR/
[mkyong]$ pg_ctl start -D /usr/local/pgsql/pgDataPITR/

注意！！！ 了解PostgreSQL如何处理日志文件，pg_xlog和存档日志
pg_xlog是一个PostgreSQL日志文件文件夹，用于存储所有数据历史记录。 它位于/ usr / local / pgsql / pgDataPITR / pg_xlog。 例如，当用户插入，更新或删除一条记录时，所有历史交易将自动创建或追加到pg_xlog文件夹下的文件日志文件中。 日志文件格式如下所示000000010000000000000001-> 000000010000000000000006

例如，

[postgres@localhost pg_xlog]$ ls -lsh
total 113M
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:25 000000010000000000000006

每个日志文件可以处理大约16M数据，如果超出该限制，它将自动创建一个新的日志文件，文件名遵循0-9和AZ

000000010000000000000001
..
..
000000010000000000000009
..
..
00000001000000000000000A
..
..
00000001000000000000000Z

这是我们将用作前滚PostgreSQL时间点恢复的日志文件🙂

注意！！！ 您还记得我们在postgresql.conf文件中配置了WAL文件路径吗？

archive_command = on
archive_command = 'cp %p /usr/local/pgsql/pgDataPITR/wals/%f'

这意味着当pg_xlog文件夹增长到一定限制时（例如6个日志文件每个包含16M），当PostgreSQL尝试插入新的历史记录并检测到pg_xlog已满时，它将自动存档最早的历史记录日志文件并将其移至/ usr / local / pgsql / pgDataPITR / wals文件夹。

注意！！！ 我们必须连续备份这些归档文件（这就是为什么称增量备份🙂）。 我们不再需要进行完整的数据库备份，但是我们会不断备份那些存档日志文件。

重要日志文件文件夹

/usr/local/pgsql/pgDataPITR/pg_xlog
/usr/local/pgsql/pgDataPITR/wals

数据模拟与备份过程

创建虚拟表和记录–我们将在新表中动态创建455,252条记录，400k条记录将迫使PostgreSQL在pg_xlog文件夹中创建足够的日志文件，并触发归档过程以从/ usr / local / pgsql / pgDataPITR /归档日志文件pg_xlog到/ usr / local / pgsql / pgDataPITR / wals ，每个日志文件包含大约16M大小的文件。

1）表testPITR1创建于2008-11-25 17:17

# psql
# select (*) from pg_class; –- contain 229 records
# select (*) from pg_description; –- contains 1988 records

# create table testPITR1 as select * from pg_class, pg_description; 
#--totally 229 x 1988 = 455,252 records
# select * from current_timestamp; –-2008-11-25 17:17

日志文件如下所示

[postgres@localhost pgDataPITR]$ cd pg_xlog/
[postgres@localhost pg_xlog]$ ls -lsh
total 113M
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:25 000000010000000000000006
17M -rw——