postgres unlogged表的数据丢失问题

一环境数据库版本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 unlogged table test_unlogged （ id integer, name text）;test=# create table test （ id integer, name text）;//插入数据测试（不checkpoint ）test=# insert into test select generate_series（1,10000）,"test";INSERT 0 10000test=# 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 | utest=# insert into test_unlogged select generate_series（1,10000）,"test";INSERT 0 10000Time: 6.687 ms //可以看出插入时间test=# insert into test select generate_series（1,10000）,"test";INSERT 0 10000Time: 48.511 ms //可以看出插入时间test=# checkpoint;CHECKPOINTTime: 100.727 ms//此处异常关闭数据库pg_ctl -D /pgdb/data stop -m immediate，并重启test=# q[postgres@pg1 ~]$ psql -d test -U dba -p 5432psql （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 10000test=# checkpointtest-# ;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 10000test=# 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 | upostgres=# select count（*） from test_unlogged; count------- 0test=# 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 1test=# insert into test_unlogged values （3,"twj3"）;INSERT 0 1test=# select * from test_unlogged; id | name----+------ 1 | twj1 2 | twj2 3 | twj3postgres=# 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 ~]$ls000000010000000000000001 000000010000000000000002.00000028.backup000000010000000000000002 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 OK2016-04-13 18:07:40 FULL 0m 58MB 1 OK以下分2种情况（1）这个模拟故障恢复[postgres@pg1 ~]$ pg_ctl -D /pgdb/data stop -m immediatewaiting for server to shut down... doneserver stopped开始恢复（直接在原库操作，原库假设认为损坏）[postgres@pg1 archive1975]$ pg_rman restore[postgres@pg1 ~]$ pg_rman restoreINFO: the recovery target timeline ID is not givenINFO: use timeline ID of current database cluster as recovery target: 1INFO: calculating timeline branches to be used to recovery target pointINFO: searching latest full backup which can be used as restore start pointINFO: 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 filesINFO: clearing restore destinationINFO: validate: "2016-04-13 18:07:40" backup and archive log files by SIZEINFO: backup "2016-04-13 18:07:40" is validINFO: restoring database files from the full mode backup "2016-04-13 18:07:40"INFO: searching incremental backup to be restoredINFO: searching backup which contained archived WAL files to be restoredINFO: backup "2016-04-13 18:07:40" is validINFO: restoring WAL files from backup "2016-04-13 18:07:40"INFO: validate: "2016-04-13 18:12:25" archive log files by SIZEINFO: backup "2016-04-13 18:12:25" is validINFO: restoring WAL files from backup "2016-04-13 18:12:25"INFO: restoring online WAL files and server log filesINFO: generating recovery.confINFO: restore completeHINT: Recovery will start automatically when the PostgreSQL server is started.//启动数据库[postgres@pg1 ~]$ pg_ctl -D /pgdb/data startserver starting[postgres@pg1 ~]$ LOG: database system was interrupted; last known up at 2016-04-13 18:07:41 PDTLOG: starting archive recoveryLOG: restored log file "000000010000000000000002" from archiveLOG: redo starts at 0/2000098LOG: consistent recovery state reached at 0/20000C0LOG: restored log file "000000010000000000000003" from archivecp: cannot stat `/pgdb/archive1975/000000010000000000000004": No such file or directoryLOG: redo done at 0/30000A0LOG: last completed transaction was at log time 2016-04-13 18:08:58.930459-07LOG: restored log file "000000010000000000000003" from archivecp: cannot stat `/pgdb/archive1975/00000002.history": No such file or directoryLOG: selected new timeline ID: 2cp: cannot stat `/pgdb/archive1975/00000001.history": No such file or directoryLOG: archive recovery completeLOG: MultiXact member wraparound protections are now enabledLOG: database system is ready to accept connectionsLOG: 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 restoreINFO: the recovery target timeline ID is not givenINFO: use timeline ID of current database cluster as recovery target: 1INFO: calculating timeline branches to be used to recovery target pointINFO: searching latest full backup which can be used as restore start pointINFO: 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 filesINFO: clearing restore destinationINFO: validate: "2016-04-13 18:07:40" backup and archive log files by SIZEINFO: backup "2016-04-13 18:07:40" is validINFO: restoring database files from the full mode backup "2016-04-13 18:07:40"INFO: searching incremental backup to be restoredINFO: searching backup which contained archived WAL files to be restoredINFO: backup "2016-04-13 18:07:40" is validINFO: restoring WAL files from backup "2016-04-13 18:07:40"INFO: validate: "2016-04-13 18:12:25" archive log files by SIZEINFO: backup "2016-04-13 18:12:25" is validINFO: restoring WAL files from backup "2016-04-13 18:12:25"INFO: restoring online WAL files and server log filesINFO: generating recovery.confINFO: restore completeHINT: 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 TABLEpostgres=# 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 OK2016-04-13 18:12:25 ARCH 0m 16MB 1 OK2016-04-13 18:07:40 FULL 0m 58MB 1 OK[postgres@pg1 ~]$ pg_ctl -D /pgdb/data stop//这里手工保存最后一次正常关闭后归档的日志，然后移除原库或者异机恢复[postgres@pg1 ~]$ pg_rman restoreERROR: 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 accessDETAIL: 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 PDTLOG: starting archive recoveryLOG: restored log file "000000010000000000000002" from archiveLOG: redo starts at 0/2000098LOG: consistent recovery state reached at 0/20000C0LOG: restored log file "000000010000000000000003" from archiveLOG: restored log file "000000010000000000000004" from archiveLOG: restored log file "000000010000000000000005" from archivecp: cannot stat `/pgdb/archive1975/000000010000000000000006": No such file or directoryLOG: redo done at 0/5000050LOG: last completed transaction was at log time 2016-04-13 18:38:34.097399-07LOG: restored log file "000000010000000000000005" from archive（该日志为最后一次正常关闭产生）p: cannot stat `/pgdb/archive1975/00000002.history": No such file or directoryLOG: selected new timeline ID: 2cp: cannot stat `/pgdb/archive1975/00000001.history": No such file or directoryLOG: archive recovery completeLOG: MultiXact member wraparound protections are now enabledLOG: database system is ready to accept connectionsLOG: 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.htmCentOS 6.3环境下yum安装PostgreSQL 9.3 http://www.linuxidc.com/Linux/2014-05/101787.htmPostgreSQL缓存详述 http://www.linuxidc.com/Linux/2013-07/87778.htmWindows平台编译 PostgreSQL http://www.linuxidc.com/Linux/2013-05/85114.htmUbuntu下LAPP（Linux+Apache+PostgreSQL+PHP）环境的配置与安装 http://www.linuxidc.com/Linux/2013-04/83564.htmUbuntu上的phppgAdmin安装及配置 http://www.linuxidc.com/Linux/2011-08/40520.htmCentOS平台下安装PostgreSQL9.3 http://www.linuxidc.com/Linux/2014-05/101723.htmPostgreSQL配置Streaming Replication集群 http://www.linuxidc.com/Linux/2014-05/101724.htm------------------------------------华丽丽的分割线------------------------------------PostgreSQL 的详细介绍：请点这里
PostgreSQL 的下载地址：请点这里本文永久更新链接地址