共计 12549 个字符，预计需要花费 32 分钟才能阅读完成。
一环境
数据库版本 Postgres 9.5.1
操作系统系统：CentOS 6.5 64
二问题发现结果
(1) 一个 unlogged 表 (不管是否 checkpoint), 当数据库异常关机重启后，该表数据被清空。
(2) 一个 unlogged 表, 插入数据，切换日志，之后用 pg_rman 备份数据库与归档日志，然后正常关闭数据库，利用备份还原恢复数据库时，会把 unlogged 表中的数据清空。
(3) 利用 pg_dump 逻辑导出 unlogged 表数据时，如果采用文本文件方式可以发现，其定义创建语句也是 unlogged 方式，恢复时请注意。
注意
大家是不是要小心使用 unlogged 表了 - 能提升数据插入时的性能，但是插入完成一定记得改为 logged 表。
三实验
postgres 版本
PostgreSQL9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (RedHat 4.4.7-4), 64-bit
(1row)
3.1 测试 1
创建普通表和日志表，插入数据测试 (checkpoint)，正常关闭数据库。
结果：普通表和日志表数据都不丢失
// 建表 test=# create table test (id integer, name text); // 插入数据测试 (不 checkpoint) test=# insert into test select generate_series(1,10000),’test’; INSERT 0 10000 test=# insert into test_unlogged select generate_series(1,10000),’test’; INSERT 0 10000 // 正常关机重启后查数据 pg_ctl -D /pgdb/data stop; test=# select count(*) from test; count ——- 10000 (1 row) test=# select count(*) from test_unlogged; count ——- 10000 (1 row) |
3.2 测试 2
创建普通表和日志表
插入数据测试 (不 checkpoint)
异常关闭数据库 pg_ctl -D /pgdb/data stop -m immediate
操作过程同上。
结果：异常关闭数据库重启后 unlogged 表 test_unlogged 数据清空；logged 表 test 数据不丢失。
3.3 测试 3
创建普通表和日志表
插入数据测试 (checkpoint)
异常关闭数据库 pg_ctl -D /pgdb/data stop -m immediate，之后重启
操作步骤同上
结果：异常关闭数据库重启后，unlogged 表 test_unlogged 数据无；logged 表 test 数据不丢失。
test=# drop table test; test=# drop table test_unlogged; test=# create unlogged table test_unlogged (id integer, name text); test=# create table test (id integer, name text); test=# select relname,relpersistence from pg_class where relname like ‘test%’; relname | relpersistence —————+—————- test | p test_unlogged | u test=# insert into test_unlogged select generate_series(1,10000),’test’; INSERT 0 10000 Time: 6.687 ms // 可以看出插入时间 test=# insert into test select generate_series(1,10000),’test’; INSERT 0 10000 Time: 48.511 ms // 可以看出插入时间 test=# checkpoint; CHECKPOINT Time: 100.727 ms // 此处异常关闭数据库 pg_ctl -D /pgdb/data stop -m immediate，并重启 test=# \q [postgres@pg1 ~]$ psql -d test -U dba -p 5432 psql (9.5.1) Type “help” for help. test=# \dt List of relations Schema | Name | Type | Owner ——–+—————+——-+——- public | test | table | dba public | test_unlogged | table | dba (2 rows) test=# select count(*) from test; count ——- 10000 (1 row) test=# select count(*) from test_unlogged; count ——- 0 (1 row) |
3.4 测试 4
创建 unlogged 表 test_unlogged
插入数据，正常关机，之后，test_unlogged 数据正常不丢失，
然后再次插入数据，异常关机，重启之后 unlogged 表 test_unlogged 数据无。
test=# \dt List of relations Schema | Name | Type | Owner ——–+—————+——-+——- public | test | table | dba public | test_unlogged | table | dba (2 rows) test=# select count(*) from test_unlogged; count ——- 0 (1 row) test=# select relname,relpersistence from pg_class where relname like ‘test%’; relname | relpersistence —————+—————- test | p test_unlogged | u (2 rows) test=# insert into test_unlogged select generate_series(1,10000),’test’; INSERT 0 10000 // 此处正常关闭数据库 [postgres@pg1 ~]$ pg_ctl -D /pgdb/data stop // 再启动查询 test=# select count(*) from test_unlogged; count ——- 10000 (1 row) // 再次插入数据 test=# insert into test_unlogged select generate_series(1,10000),’test’; INSERT 0 10000 test=# checkpoint test-# ; CHECKPOINT // 此处异常关闭数据库 [postgres@pg1 ~]$ pg_ctl -D /pgdb/data stop -m immediate // 再启动, 查看数据 test=# select count(*) from test_unlogged; count ——- 0 (1 row) |
3.5 测试 5
同测试 4,，不过异常关机改为 kill checkpointer process 进程，结果结论同测试 4，重启之后 unlogged 表 test_unlogged 数据无。
test=# select count(*) from test_unlogged; count ——- 0 (1 row) test=# select relname,relpersistence from pg_class where relname like ‘test%’; relname | relpersistence —————+—————- test | p test_unlogged | u (2 rows) test=# insert into test_unlogged select generate_series(1,10000),’test’; INSERT 0 10000 // 此处正常关闭数据库，重启 test=# select count(*) from test_unlogged; count ——- 10000 (1 row) test=# insert into test_unlogged select generate_series(1,10000),’test’; INSERT 0 10000 test=# checkpoint; CHECKPOINT // 此处准备杀 checkpointer process，以使数据库重启 //[postgres@pg1 ~]$ ps -ef |grep postgres //[postgres@pg1 ~]$ kill -9 checkpointer process 进程号 test=# select count(*) from test_unlogged; count ——- 0 (1 row) |
四其他
4.1pg_dump 逻辑导出数据
Unlogged 表逻辑 dump 之后，在 dump 文件中建表语句也是 Unlogged 方式，可想而知恢复导入时也是 Unlogged 表。
4.2pg_rman 物理备份数据
postgres=# create unlogged table test_unlogged (id integer, name text); postgres=# select relname,relpersistence from pg_class where relname like ‘test%’; relname | relpersistence —————+—————- test_unlogged | u postgres=# select count(*) from test_unlogged; count ——- 0 test=# insert into test_unlogged values (1,’twj1′); INSERT 0 1 // 此时备份数据库全备： [root@pg1 Desktop]# mkdir /pgdb/backup/pg_rman [root@pg1 Desktop]# chown -R postgres:postgres /pgdb [root@pg1 Desktop]# su – postgres [postgres@pg1 ~]$ pg_rman init -B /pgdb/backup/pg_rman [postgres@pg1 ~]$ export BACKUP_PATH=/pgdb/backup/pg_rman [postgres@pg1 ~]$ export ARCLOG_PATH=/pgdb/archive5432 [postgres@pg1 ~]$ pg_rman backup -b full -p 5432 -U dba [postgres@pg1 ~]$ pg_rman validate postgres@pg1 ~]$ pg_rman show ========================================================== StartTime Mode Duration Size TLI Status ========================================================== 2016-04-13 18:07:40 FULL 0m 58MB 1 OK // 之后插入数据继续 test=# insert into test_unlogged values (2,’twj2′); INSERT 0 1 test=# insert into test_unlogged values (3,’twj3′); INSERT 0 1 test=# select * from test_unlogged; id | name —-+—— 1 | twj1 2 | twj2 3 | twj3 postgres=# select pg_switch_xlog(); pg_switch_xlog —————- 0/30000B8 (1 row) postgres=# select * from pg_xlogfile_name_offset(‘0/30000B8’); file_name | file_offset ————————–+————- 000000010000000000000003 | 184 (1 row) // 同时观察归档文档： [postgres@pg1 ~]$ cd /pgdb/archive5432 [postgres@pg1 ~]$ls 000000010000000000000001 000000010000000000000002.00000028.backup 000000010000000000000002 000000010000000000000003 // 继续归档日志 [postgres@pg1 ~]$ pg_rman backup -b archive -p 5432 -U dba [postgres@pg1 ~]$ pg_rman validate [postgres@pg1 ~]$ pg_rman show ========================================================== StartTime Mode Duration Size TLI Status ========================================================== 2016-04-13 18:12:25 ARCH 0m 16MB 1 OK 2016-04-13 18:07:40 FULL 0m 58MB 1 OK 以下分 2 种情况 (1) 这个模拟故障恢复 [postgres@pg1 ~]$ pg_ctl -D /pgdb/data stop -m immediate waiting for server to shut down… done server stopped 开始恢复（直接在原库操作，原库假设认为损坏） [postgres@pg1 archive1975]$ pg_rman restore [postgres@pg1 ~]$ pg_rman restore INFO: the recovery target timeline ID is not given INFO: use timeline ID of current database cluster as recovery target: 1 INFO: calculating timeline branches to be used to recovery target point INFO: searching latest full backup which can be used as restore start point INFO: found the full backup can be used as base in recovery: “2016-04-13 18:07:40” INFO: copying online WAL files and server log files INFO: clearing restore destination INFO: validate: “2016-04-13 18:07:40” backup and archive log files by SIZE INFO: backup “2016-04-13 18:07:40” is valid INFO: restoring database files from the full mode backup “2016-04-13 18:07:40” INFO: searching incremental backup to be restored INFO: searching backup which contained archived WAL files to be restored INFO: backup “2016-04-13 18:07:40” is valid INFO: restoring WAL files from backup “2016-04-13 18:07:40” INFO: validate: “2016-04-13 18:12:25” archive log files by SIZE INFO: backup “2016-04-13 18:12:25” is valid INFO: restoring WAL files from backup “2016-04-13 18:12:25” INFO: restoring online WAL files and server log files INFO: generating recovery.conf INFO: restore complete HINT: Recovery will start automatically when the PostgreSQL server is started. // 启动数据库 [postgres@pg1 ~]$ pg_ctl -D /pgdb/data start server starting [postgres@pg1 ~]$ LOG: database system was interrupted; last known up at 2016-04-13 18:07:41 PDT LOG: starting archive recovery LOG: restored log file “000000010000000000000002” from archive LOG: redo starts at 0/2000098 LOG: consistent recovery state reached at 0/20000C0 LOG: restored log file “000000010000000000000003” from archive cp: cannot stat `/pgdb/archive1975/000000010000000000000004′: No such file or directory LOG: redo done at 0/30000A0 LOG: last completed transaction was at log time 2016-04-13 18:08:58.930459-07 LOG: restored log file “000000010000000000000003” from archive cp: cannot stat `/pgdb/archive1975/00000002.history’: No such file or directory LOG: selected new timeline ID: 2 cp: cannot stat `/pgdb/archive1975/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 // 然后连接查看： postgres=# select * from test_unlogged; id | name —-+—— (0 rows) 结果数据为空。 (2) 这个模拟正常关机后恢复 [postgres@pg1 ~]$ pg_ctl -D /pgdb/data stop [postgres@pg1 ~]$ pg_rman restore INFO: the recovery target timeline ID is not given INFO: use timeline ID of current database cluster as recovery target: 1 INFO: calculating timeline branches to be used to recovery target point INFO: searching latest full backup which can be used as restore start point INFO: found the full backup can be used as base in recovery: “2016-04-13 18:07:40” INFO: copying online WAL files and server log files INFO: clearing restore destination INFO: validate: “2016-04-13 18:07:40” backup and archive log files by SIZE INFO: backup “2016-04-13 18:07:40” is valid INFO: restoring database files from the full mode backup “2016-04-13 18:07:40” INFO: searching incremental backup to be restored INFO: searching backup which contained archived WAL files to be restored INFO: backup “2016-04-13 18:07:40” is valid INFO: restoring WAL files from backup “2016-04-13 18:07:40” INFO: validate: “2016-04-13 18:12:25” archive log files by SIZE INFO: backup “2016-04-13 18:12:25” is valid INFO: restoring WAL files from backup “2016-04-13 18:12:25” INFO: restoring online WAL files and server log files INFO: generating recovery.conf INFO: restore complete HINT: Recovery will start automatically when the PostgreSQL server is started. // 连接查询 postgres=# select * from test_unlogged; id | name —-+—— (0 rows) 同样结果没有数据。 (3) 再上例的环境下，再次新建表，再备份之后原机恢复或者异机恢复备份数据。 test=# select * from test_unlogged; id | name —-+—— 1 | twj1 2 | twj2 3 | twj3 // 再次建一个 logged 表与插入数据 postgres=# create table tt(id int); CREATE TABLE postgres=# insert into tt values(1); INSERT 0 1 // 备份日志 [postgres@pg1 ~]$ pg_rman backup -b archive -p 5432 -U dba [postgres@pg1 ~]$ pg_rman show ========================================================== StartTime Mode Duration Size TLI Status ========================================================== 2016-04-13 18:38:33 ARCH 0m 16MB 1 OK 2016-04-13 18:12:25 ARCH 0m 16MB 1 OK 2016-04-13 18:07:40 FULL 0m 58MB 1 OK [postgres@pg1 ~]$ pg_ctl -D /pgdb/data stop // 这里手工保存最后一次正常关闭后归档的日志，然后移除原库或者异机恢复 [postgres@pg1 ~]$ pg_rman restore ERROR: could not open pg_controldata file “/pgdb/data/global/pg_control”: No such file or directory [postgres@pg1 ~]$ mkdir -p /pgdb/data/global/ [postgres@pg1 ~]$ cp ‘/pgdb/backup/pg_rman/20160413/180740/database/global/pg_control’ /pgdb/data/global/pg_control // 从备份中获取控制文件信息 [postgres@pg1 ~]$ pg_rman restore [postgres@pg1 ~]$ pg_ctl -D /pgdb/data start [postgres@pg1 ~]$ FATAL: data directory “/pgdb/data” has group or world access DETAIL: Permissions should be u=rwx (0700). [root@pg2 Desktop]# chmod -R 0700 /pgdb/data [postgres@pg1 ~]$ pg_ctl -D /pgdb/data start [postgres@pg1 ~]$ LOG: database system was interrupted; last known up at 2016-04-13 18:07:41 PDT LOG: starting archive recovery LOG: restored log file “000000010000000000000002” from archive LOG: redo starts at 0/2000098 LOG: consistent recovery state reached at 0/20000C0 LOG: restored log file “000000010000000000000003” from archive LOG: restored log file “000000010000000000000004” from archive LOG: restored log file “000000010000000000000005” from archive cp: cannot stat `/pgdb/archive1975/000000010000000000000006′: No such file or directory LOG: redo done at 0/5000050 LOG: last completed transaction was at log time 2016-04-13 18:38:34.097399-07 LOG: restored log file “000000010000000000000005” from archive(该日志为最后一次正常关闭产生) p: cannot stat `/pgdb/archive1975/00000002.history’: No such file or directory LOG: selected new timeline ID: 2 cp: cannot stat `/pgdb/archive1975/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 之后启动数据库再次访问表，结果 unlogged 表数据无；logged 表数据与实际一致，没丢失。 postgres=# select * from test_unlogged; id | name —-+—— (0 rows)
postgres=# select * from tt; id —- 1 (1 row) |
ok.
———————————— 华丽丽的分割线 ————————————
在 CentOS 6.5 上编译安装 PostgreSQL 9.3 数据库 http://www.linuxidc.com/Linux/2016-06/132272.htm
CentOS 6.3 环境下 yum 安装 PostgreSQL 9.3 http://www.linuxidc.com/Linux/2014-05/101787.htm
PostgreSQL 缓存详述 http://www.linuxidc.com/Linux/2013-07/87778.htm
Windows 平台编译 PostgreSQL http://www.linuxidc.com/Linux/2013-05/85114.htm
Ubuntu 下 LAPP（Linux+Apache+PostgreSQL+PHP) 环境的配置与安装 http://www.linuxidc.com/Linux/2013-04/83564.htm
Ubuntu 上的 phppgAdmin 安装及配置 http://www.linuxidc.com/Linux/2011-08/40520.htm
CentOS 平台下安装 PostgreSQL9.3 http://www.linuxidc.com/Linux/2014-05/101723.htm
PostgreSQL 配置 Streaming Replication 集群 http://www.linuxidc.com/Linux/2014-05/101724.htm
———————————— 华丽丽的分割线 ————————————
PostgreSQL 的详细介绍 ：请点这里
PostgreSQL 的下载地址 ：请点这里
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-11/137240.htm
