数据库版本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 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数据库