System Administration Functions

http://www.postgresql.org/docs/9.3/static/functions-admin.html

Generic File Access Functions：
普通文件的访问方法：

digoal=# select pg_ls_dir('.');
    pg_ls_dir    
-----------------
 pg_multixact
 pg_notify
 postmaster.pid
 pg_serial
 pg_twophase
 postgresql.conf
 pg_ident.conf
 pg_xlog
 pg_snapshots
 pg_tblspc
 global
 pg_log
 pg_subtrans
 PG_VERSION
 pg_clog
 postmaster.opts
 base
 recovery.done
 pg_hba.conf
 pg_stat_tmp
 pg_stat
(21 rows)
digoal=# select pg_read_file('./pg_hba.conf');
                               pg_read_file                               
--------------------------------------------------------------------------
 # PostgreSQL Client Authentication Configuration File                   +
 # ===================================================                   +
 #                                                                       +
 # Refer to the "Client Authentication" section in the PostgreSQL        +
 # documentation for a complete description of this file.  A short       +
 # synopsis follows.                                                     +
.....
digoal=# select pg_read_file('./pg_hba.conf') into tbtbmp;--将文件内容插入到一个新表中
SELECT 1
digoal=# select (pg_stat_file('./pg_hba.conf')).modification;
      modification      
------------------------
 2014-04-08 20:05:30+08
digoal=# select pg_stat_file('./pg_hba.conf');
                                     pg_stat_file                                     
--------------------------------------------------------------------------------------
 (4548,"2014-05-21 16:20:19+08","2014-04-08 20:05:30+08","2014-04-08 20:05:30+08",,f)
(1 row)
digoal=# SELECT convert_from(pg_read_binary_file('./pg_hba.conf'), 'UTF8');                
                               convert_from                               
--------------------------------------------------------------------------
 # PostgreSQL Client Authentication Configuration File                   +
 # ===================================================                   +
 #                                                                       +
 # Refer to the "Client Authentication" section in the PostgreSQL        +
 # documentation for a complete description of this file.  A short       +
......

     
     

Snapshot Synchronization Functions:

使用快照同步： http://www.postgresql.org/docs/9.3/static/sql-set-transaction.html

有一点限制：必须先打开一个transactionA不能关闭，在此transactionA处于开启状态中，其他事务（必须是BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;）都可以使用set transaction snapshot '***'来回归到 transactionA这一个快照点的数据库状态。

sessionA：

digoal=# select * from foo;
 fooid | foosubid | fooname | col1 | col2 | col3 | col4 | col5 
-------+----------+---------+------+------+------+------+------
     4 |        5 | six     |      |   90 |   22 |  202 |     
     5 |        5 | two     |      |   90 |   22 |  202 |     
     7 |        7 | rrr     |      |   90 |   22 |  202 |     
     6 |        6 | vvvvv   |      |   90 |   22 |  202 |     
     1 |        2 | test    |      |   90 |   22 |  202 |     
(5 rows)
digoal=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
digoal=# SELECT pg_export_snapshot();
 pg_export_snapshot 
--------------------
 000007A4-1
(1 row)

digoal=# 

sessionB：

digoal=# select * from foo;
 fooid | foosubid | fooname | col1 | col2 | col3 | col4 | col5 
-------+----------+---------+------+------+------+------+------
     4 |        5 | six     |      |   90 |   22 |  202 |     
     5 |        5 | two     |      |   90 |   22 |  202 |     
     7 |        7 | rrr     |      |   90 |   22 |  202 |     
     6 |        6 | vvvvv   |      |   90 |   22 |  202 |     
     1 |        2 | test    |      |   90 |   22 |  202 |     
(5 rows)

digoal=# update foo set fooname='nnnn' where fooid=1;
UPDATE 1
digoal=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
digoal=# SET TRANSACTION SNAPSHOT '000007A4-1';
SET
digoal=# select * from foo;
 fooid | foosubid | fooname | col1 | col2 | col3 | col4 | col5 
-------+----------+---------+------+------+------+------+------
     4 |        5 | six     |      |   90 |   22 |  202 |     
     5 |        5 | two     |      |   90 |   22 |  202 |     
     7 |        7 | rrr     |      |   90 |   22 |  202 |     
     6 |        6 | vvvvv   |      |   90 |   22 |  202 |     
     1 |        2 | test    |      |   90 |   22 |  202 |     
(5 rows)

digoal=# 

sessionC：

digoal=# select * from foo;
 fooid | foosubid | fooname | col1 | col2 | col3 | col4 | col5 
-------+----------+---------+------+------+------+------+------
     4 |        5 | six     |      |   90 |   22 |  202 |     
     5 |        5 | two     |      |   90 |   22 |  202 |     
     7 |        7 | rrr     |      |   90 |   22 |  202 |     
     6 |        6 | vvvvv   |      |   90 |   22 |  202 |     
     1 |        2 | nnnn    |      |   90 |   22 |  202 |     
(5 rows)

数据库系统函数：

location：即xlog的transaction位置。

pg_switch_xlog：归档，即如果postgresql.conf中 的 archive_mode    和  archive_command    设置的话，会将启用 archive_command    中的cp命令。

Force switch to a new transaction log file (restricted to superusers)

pg_xlogfile_name：将事务日志的location转换为其实际存储的文件名。

digoal=# select pg_switch_xlog ();
 pg_switch_xlog 
----------------
 0/629ACC0
(1 row)
digoal=# select pg_switch_xlog ();
 pg_switch_xlog 
----------------
 0/7000000
(1 row)

digoal=# select pg_xlogfile_name('0/7000000');
     pg_xlogfile_name     
--------------------------
 000000010000000000000006
(1 row)

digoal=# select pg_xlogfile_name('0/629ACC0');
     pg_xlogfile_name     
--------------------------
 000000010000000000000006
(1 row)

pg_xlogfile_name_offset：将事务location转换为实际存储的文件名和此location在此文件中的偏移量

digoal=# select pg_xlogfile_name_offset('0/629ACC0');
      pg_xlogfile_name_offset       
------------------------------------
 (000000010000000000000006,2731200)
(1 row)
digoal=# select pg_xlogfile_name_offset('0/7000000');
   pg_xlogfile_name_offset    
------------------------------
 (000000010000000000000006,0)
(1 row)
digoal=# select pg_switch_xlog ();
 pg_switch_xlog 
----------------
 0/70005E8
(1 row)
digoal=# select pg_xlogfile_name_offset('0/70005E8');
     pg_xlogfile_name_offset     
---------------------------------
 (000000010000000000000007,1512)
(1 row)

pg_xlog_location_diff：计算两个location的距离

digoal=# select pg_xlog_location_diff('0/70005E8','0/7000000');
 pg_xlog_location_diff 
-----------------------
                  1512
(1 row)

pg_current_xlog_location ：得到当前xlog的transaction的location

digoal=# select pg_current_xlog_location();  
 pg_current_xlog_location 
--------------------------
 0/9001118
(1 row)

pg_start_backup & pg_stop_backup & pg_create_restore_point：

http://blog.csdn.net/bielidefeng/article/details/27650053

Recovery Control Functions：

pg_is_in_recovery()：判断数据库是否正在recovery中。如果恢复结束后数据库目录下的recover.conf会自动更名为recover.done。