PostgreSQL 8.1.22 开启归档模式及在线备份恢复

最新推荐文章于 2024-08-13 04:14:07 发布

lk_db

最新推荐文章于 2024-08-13 04:14:07 发布

阅读量2.2k

点赞数 1

分类专栏： Highgo DB PostgreSQL

本文链接：https://blog.csdn.net/lk_db/article/details/78294402

版权

本文详细介绍了如何在PostgreSQL 8.1.22版本中开启归档模式并进行在线备份。通过修改archive_command参数、执行pg_ctl reload命令来启用归档功能。接着，通过pg_start_backup和pg_stop_backup创建备份，并观察日志文件以了解备份过程。在归档目录中，可以看到生成的备份文件，这些文件在备份完成后可被安全删除。此外，文章还提及了PG 8.1中缺少的一些功能，如pg_switch_xlog()函数和wal_segment_size参数。

摘要由CSDN通过智能技术生成

以下内容参考自：
http://www.kuqin.com/postgreSQL8.1_doc/backup-online.html
https://www.postgresql.org/docs/8.1/static/backup-online.html

数据库版本：
postgres=# select version();
version
-------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.22 on x86_64-unknown-linux-gnu, compiled by GCC gcc (SUSE Linux) 4.3.4 [gcc-4_3-branch revision 152973]
(1 row)

PG 8.1中还没有 wal_level和archive_mode参数，只有archive_command参数。
修改如下参数：
archive_command = 'cp -i %p /usr/local/pgsql/data/archivelog/%f'

pg_ctl reload使修改生效：
postgres=# pg_ctl reload

验证参数已生效：
postgres=# show archive_command;
archive_command
----------------------------------------------
cp -i %p /usr/local/pgsql/data/archivelog/%f
(1 row)

创建测试表，插入测试数据，模拟WAL日志切换：
postgres=# create table tbl_kenyon(id int,cname varchar(50),remark text);
CREATE TABLE

插入测试数据：
postgres=# insert into tbl_kenyon select generate_series(1,10000000),'kenyon good boy',md5('kenyon good boy');
LOG: archived transaction log file "000000010000000000000001"
LOG: archived transaction log file "000000010000000000000002"
LOG: archived transaction log file "000000010000000000000003"
LOG: checkpoints are occurring too frequently (14 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
LOG: archived transaction log file "000000010000000000000004"
LOG: archived transaction log file "000000010000000000000005"
LOG: archived transaction log file "000000010000000000000006"
LOG: checkpoints are occurring too frequently (5 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
LOG: archived transaction log file "000000010000000000000007"
LOG: archived transaction log file "000000010000000000000008"
LOG: checkpoints are occurring too frequently (3 seconds apart)