PostgreSQL9.1 PITR示例
本教程是PostgreSQL Cluster系列教程的一部分,该系列包括:
- PostgreSQL9.1 PITR示例 (该教程主要阐述DBA如何基于WAL日志做备份恢复)
- PostgreSQL9.1 Warm-Standby ---之基于拷贝WAL文件的方法 (file-based log shipping)
- PostgreSQL9.1 Warm-Standby ---之基于流复制的方法 (streaming replication)
- PostgreSQL9.1 Warm-Standby ---之基于同步复制的方法 (Synchronous Replication)
- PostgreSQL9.1 Hot-Standby ---之基于拷贝WAL文件的方法
- PostgreSQL9.1 Hot-Standby ---之基于流复制的方法
- PostgreSQL9.1 Hot-Standby ---之基于同步复制的方法
- PG9.1+pgpool-II3.1--之HA (Hot-Standby+Streaming Replication)
- PG9.1+pgpool-II3.1--之Load Balancing (when meeting large amounts of requests)
- PG9.1+pgpool-II3.1--之Parallel Query (when meeting large amounts of data)
- PostgreSQL9.1 HA --- 之Slony
本教程尽量写的简单,以让初学者可以很轻松的理解和动手实验,而尽量不出错。
关于Continuous Archiving,先说原理,咱看图说话:
从该图中,我们看到PostgreSQL会不断的更新WAL日志所在的目录,并最终写到数据库存储文件中(如上图中的mydb数据库所在的/home/postgres/db/master/pgsql/data/base/16384目录中)。故我们可以会得出这么一个结论:
- 连续备份WAL日志,即Continuous Archiving,是要连续备份WAL目录下的所有文件,即那些类似文件名为000000010000000000000005的文件。
WAL目录,即图中的/home/postgres/db/master/pgsql/data/pg_xlog目录,其下的每个文件(即WAL段)大小为16MB,那么总共有多少个16M的文件呢?(参阅这里:There will always be at least one WAL segment file, and will normally not be more than (2 + checkpoint_completion_target) * checkpoint_segments + 1 or checkpoint_segments +wal_keep_segments + 1 files. ..., Ordinarily, when old log segment files are no longer needed, they are recycled),从上面这段话还可以看出,当到达极限时,会循环利用,所以pg_xlog目录不会无限增大。
我们还会得出另外一个结论:
- 连续备份WAL日志只是备份对数据库的更改,我们还得把最初的data/目录备份一下,这就是24.3.2. Making a Base Backup所介绍的基础备份。
也有朋友会提问了,既然WAL日志会不断的存起来,干嘛还要备份最初的数据库呀,这是因为WAL日志并不会永远保存,还记得WAL目录下的各个16M文件会循环利用吗,这可能会把最开始的WAL日志丢掉,故还得备份原始的data目录。
关于WAL目录中的各个16M文件,我们再多说一下,并不是写一下这个文件就备份一次,而是等写满了16M的时候才备份,这说明了什么,这说明如果还没到16M的时候,这个WAL是还没备份的,这也就是下面这段话(参考自)中所解释的:
If you are concerned about being able to recover right up to the current instant, you may want to take additional steps to ensure that the current, partially-filled WAL segment is also copied someplace. This is particularly important if your server generates only little WAL traffic (or has slack periods where it does so), since it could take a long time before a WAL segment file is completely filled and ready to archive.
即:部分填充的WAL段(partially-filled WAL segment),你得自己想办法备份,这也是下面所说明的这句话(摘自这里)的意思:
3.7 定期备份WAL新生成日志
如果单独通过archive_command来备份WAL的话, 能根本就做不到PITR, 因为pg_xlog/下面可能还有数据没有备份到archive_command指定的目录里;所以需要另外写脚本把/data/pg_xlog/下的 WAL日志文件备份到预设的归档目录下,保证产生的WAL日志都已归档, 这里写了个脚本,每五分钟执行一次
一个更好的示意图如下(摘自Getting ready for PostgreSQL 9.1,第25页):
更加清楚的表明了备份的内容,即data和wal日志,好了,唠叨了这么多,我们开工:
实验环境:
- 一台T61笔记本
- RHEL 6.0
- 假定OS已经有postgres用户,并用该用户登录
- 下载postgresql-9.1.2.tar.gz,并放在/home/postgres/develop目录下
下面是各步骤:
1.安装PostgreSQL9.1,创建示例数据库mydb,和表foo
为了后续教程系列warm-standby和hot-standby示例的方便,此处把PostgreSQL安装在:/home/postgres/db/master里,即您首先需建立/home/postgres/db/master/psql/data目录。
[postgres@localhost ~]cd/home/postgres/develop/[postgres@localhostdevelop] tar zxf postgresql-9.1.2.tar.gz
[postgres@localhost develop]cdpostgresql−9.1.2[postgres@localhostdevelop] ./configure --prefix=/home/postgres/db/master/pgsql --with-includes=/usr/local/readline/include --with-libraries=/usr/local/readline/lib
注意上面的readline库路径,请用whereis命令查看readline在哪,并做相应修改,如果没有,请放入RHEL6光盘,在System->Administration->Add/Remove Software 安装. 如果您对安装PostgreSQL不熟悉,请参考:rhel6+postgresql8.4+postgis1.4+eclipse CDT3.6 调试环境搭建
[postgres@localhost develop]make[postgres@localhostdevelop] make install
好,接下来初始化数据库:
[postgres@localhost postgresql-9.1.2]/home/postgres/db/master/pgsql/bin/initdb−D/home/postgres/db/master/pgsql/data然后,启动数据库服务器,并创建示例数据库mydb[postgres@localhostpostgresql−9.1.2]/home/postgres/db/master/pgsql/bin/postmaster -D /home/postgres/db/master/pgsql/data
LOG: database system was shut down at 2012-02-16 10:07:15 CST
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
[postgres@localhost ~]/home/postgres/db/master/pgsql/bin/createdbmydb执行下面,如果出现如下内容,则成功:[postgres@localhost ]/home/postgres/db/master/pgsql/bin/psql mydb
psql (9.1.2)
Type "help" for help.
mydb=#
然后创建示例表foo,为了简单,咱就只生成100万条记录(若您想生成更加复杂的测试数据,请参阅Postgresql生成大量测试数据 ):
mydb=# create table foo(id bigint);
mydb=# insert into foo select * from generate_series(1,1000000);
好,我们看一下到底这100万条占多大硬盘空间:
mydb=# SELECT oid from pg_database where datname='mydb';
oid
-------
16384
(1 row)
然后开CMD:
[postgres@localhost ~]cd/home/postgres/db/master/pgsql/data/base/16384[postgres@localhost16384] du -sh
41M
40多M,不大不小,满足我们笔记本上测试的需要。
2.设置postgresql.conf
先关闭数据库,创建WAL日志将被备份的目录/home/postgres/archive。
既然上面配置好了一台普通的PostgreSQL服务器,那我们就来配置一下服务器了,以让其支持不断的对外输出WAL日志。那配置什么呢,根据9.1手册里24.3.1. Setting Up WAL Archiving知道,要配置:
To enable WAL archiving, set the wal_level configuration parameter to archive (or hot_standby), archive_mode to on, and specify the shell command to use in the archive_command configuration parameter.
即postgresql.conf文件中的三个参数:
- wal_level = archive
- archive_mode = on
- archive_command = 'cp %p /home/postgres/archive/%f'
其中archive_command中%p会自动识别为WAL目录,你不用管,%f你也不用管。这个archive_command在什么时候执行呢,即PostgreSQL在每次WAL日志16MB段满的时候才执行,即把其拷贝到/home/postgres/archive中,那么为了想在不满的时候也备份,怎么办?也即可采用Linux中定时任务的方式来实现,即文《Postgesql数据库备份与恢复实验 (PITR) 》中的“--每5分钟备份 (通过Crontab执行)....“,此处为简化本教程,不再说明,感兴趣的朋友可自行学习。
好了,配置完,重启服务器,看看有没异常,若没异常,则成功了一半,先别高兴。
3.做一次基础备份
先建立存储基础备份的目录:/home/postgres/base。
参考24.3.2. Making a Base Backup,执行:
mydb=# SELECT pg_start_backup('bak20120216');
pg_start_backup
-----------------
0/6000020
(1 row)
其中bak20120216是标签,你可以随便改成自己可识别的,然后备份整个data目录,即把/home/postgres/db/master/psql/data/ 目录全部拷贝一份,并压缩,存储在/home/postgres/base中:
[postgres@localhost pgsql]cd/home/postgres/db/master/pgsql/[postgres@localhostpgsql] tar czvf /home/postgres/base/base_data.tar.gz data/
接下来:
mydb=# SELECT pg_stop_backup();
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
pg_stop_backup
----------------
0/6000094
(1 row)
切换日志 postgres=# pg_switch_xlog();
此时表示备份成功,你可以查看/home/postgres/archive目录中是否有了备份的wal日志段了。
4.恢复
既然备份成功了,我们就得尝试尝试是不是可以恢复。
确保已经启动开数据库,打开psql,我们再在foo表中插入100万条新记录,使得foo的总记录数为200万条:
mydb=# insert into foo select * from generate_series(1,1000000);
假定此时由于某种原因我们新的插入100万条记录的数据库出问题了,我们的疑问是可否利用前面的“基础备份库”+“新插入100万条记录所产生的WAL备份日志”恢复呢?让我们看看如何一步步恢复:
首先关闭数据库。
接着把data目录改名:
[postgres@localhost pgsql]cd/home/postgres/db/master/pgsql/[postgres@localhostpgsql] mv data data_bk
然后把压缩备份的目录/home/postgres/base里的基础库解压缩到这里:
[postgres@localhost pgsql]tar−xzvf/home/postgres/base/basedata.tar.gz清空pgxlog,并创建pgxlog/archivestatus目录,删除postmaster.pid文件:[postgres@localhostpgsql] rm -r data/pg_xlog/
[postgres@localhost pgsql]mkdir−pdata/pgxlog/archivestatus[postgres@localhostpgsql] rm data/postmaster.pid
从share目录,拷贝一份recovery.conf:
[postgres@localhost pgsql]cp/home/postgres/db/master/pgsql/share/recovery.conf.sample/home/postgres/db/master/pgsql/data/recovery.conf编辑recovery.conf:restorecommand=′cp/home/postgres/archive//home/postgres/db/master/pgsql/bin/postmaster -D /home/postgres/db/master/pgsql/data
LOG: database system was interrupted; last known up at 2012-02-16 22:29:41 CST
LOG: starting archive recovery
LOG: restored log file "00000001000000000000000A" from archive
LOG: consistent recovery state reached at 0/AD37404
LOG: redo starts at 0/AD37404
cp: cannot stat `/home/postgres/archive/00000001000000000000000B': No such file or directory
LOG: could not open file "pg_xlog/00000001000000000000000B" (log file 0, segment 11): No such file or directory
LOG: redo done at 0/AD37404
LOG: restored log file "00000001000000000000000A" from archive
cp: cannot stat `/home/postgres/archive/00000002.history': No such file or directory
LOG: selected new timeline ID: 2
cp: cannot stat `/home/postgres/archive/00000001.history': No such file or directory
LOG: archive recovery complete
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
打开psql:
[postgres@localhost ~]$ /home/postgres/db/master/pgsql/bin/psql mydb
psql (9.1.2)
Type "help" for help.
mydb=# select count(*) from foo;
count
---------
2000000
(1 row)
至此,恢复成功。
当然你也可以清除刚才没有删除而只重命名的data目录。
当然如何恢复到某一个时间点的transaction,即PITR,您可以再继续学习24.3.4. Timelines。此教程不再讲述。
另外,Getting ready for PostgreSQL 9.1,第26页的PITR的示意图我没看明白,如下(研究过PITR的朋友请给我说):
参考:
[1] Postgesql数据库备份与恢复实验 (PITR) , http://francs3.blog.163.com/blog/static/405767272010729528450/
[2] Continuous Archiving and Point-in-Time Recovery (PITR), http://www.postgresql.org/docs/9.1/static/continuous-archiving.html
[3] Simple HA with PostgreSQL Point-In-Time Recovery
[4] How To Set Up An Active/Passive PostgreSQL Cluster With Pacemaker, Corosync, And DRBD (CentOS 5.5)
[5] PostgreSQL warm standby on ZFS crack
[6] HA+DRBD+Postgres - PostgresWest '08
[7] Replication in PostgreSQL (II) – Hot Standby/Streaming Replication
[8] postgres数据备份及恢复终结版
相关推荐
分享一套数据库课程——PostgreSQL DBA实战视频教程(2023完结新课),文档资料齐全。希望对大家有帮助。
百度网盘下载===PostgreSQL从入门到精通视频教程(已整理全)
这篇 PostgreSQL 教程提供 SQL 的基本和高级概念。此 PostgreSQL 系列教程是专为初学 者和专业人士编写提供的,需要读者有一些基本的数据库和编程基础。
sky_postgresql_cluster sky_postgresql_cluster 是一个写在 shell 中的 PostgreSQL HA 模块,HA 通过三个主机,包括两个 postgresql (主要和流复制备用)和一票主机。 应用程序通过虚拟连接到 sky_postgresql_...
PostgreSql入门学习教程
介绍PostgreSQL数据库,适用于培训。
postgresql数据库编程语言自学版本,可以通过其学会简单的postgre编程语法
PostgreSQL 11.0 DBA快速上手教程
分享一套PostgreSQL视频教程——《PostgreSQL DBA实战视频教程》,2023年完结新课,基于PostgreSQL 14.6版本!7门课程合集,提供配套的全部文档资料! 课程1、PostgreSQL安装与管理 课程2、PostgreSQL数据库SQL基础...
里面包含postgresSQL安装包(postgresql-11.2-2-windows-x64),安装步骤,安装失败解决,PostgreSQL对象 - 关系数据库管理系统
postgresql 教程 pdf资料整理;内含多本资料,适合不同阶段的学习。
PostgreSQL DBA实战视频教程2023|PostgreSQL1 4.6教程 课程1、PostgreSQL安装与管理 课程2、PostgreSQL数据库SQL基础 课程3、PostgreSQL表详解 课程4、PostgreSQL索引详解 课程5、PostgreSQL对象管理 课程6、...
postgresql安装教程.txt
PostgreSQL_8.1_中文(2)I教程
PostgreSQL中文学习手册 PostgreSQL PostgreSQL PostgreSQL学习手册 学习手册 学习手册 (数据表 数据表 ) 4 一、表的定义: 一、表的定义: 一、表的定义: . 4 PostgreSQL PostgreSQL PostgreSQL学习手册 学习手册...
这些教程为读者提供了数据库基础的全面概述,从解释为什么可能需要使用PostgreSQL数据库开始,然后概述与电子表格等替代方法相比必须提供的不同数据库类型。
分享一套视频教程,关于PostgreSQL数据库入门的视频教程,帮您轻松搞定环境搭建,软件安装的问题,让学习不再止于环境搭建,视频手把手教学,通俗易懂!!
postgresql PGCA 课程PPT01_postgresql_PGCA_PGCP_PGCM_课程全免费 postgresql PGCA 课程PPT01_postgresql_PGCA_PGCP_PGCM_课程全免费 postgresql PGCA 课程PPT01_postgresql_PGCA_PGCP_PGCM_课程全免费 postgresql ...
PostgreSQL(postgresql-13.5.tar.bz2) PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系开发的POSTGRES,4.2版本为基础的对象关系型数据库管理系统。...