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_multixactpg_notifypostmaster.pidpg_serialpg_twophasepostgresql.confpg_ident.confpg_xlogpg_snapshotspg_tblspcglobalpg_logpg_subtransPG_VERSIONpg_clogpostmaster.optsbaserecovery.donepg_hba.confpg_stat_tmppg_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：
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=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN digoal=# SELECT pg_export_snapshot();pg_export_snapshot --------------------000007A4-1 (1 row)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 | 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=#
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转换为其实际存储的文件名。pg_xlogfile_name_offset：将事务location转换为实际存储的文件名和此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_xlog_location_diff：计算两个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_current_xlog_location ：得到当前xlog的transaction的locationdigoal=# select pg_xlog_location_diff('0/70005E8','0/7000000');pg_xlog_location_diff -----------------------1512 (1 row)
pg_start_backup & pg_stop_backup & pg_create_restore_point： http://blog.csdn.net/bielidefeng/article/details/27650053digoal=# select pg_current_xlog_location(); pg_current_xlog_location --------------------------0/9001118 (1 row)
Recovery Control Functions：
pg_is_in_recovery()：判断数据库是否正在recovery中。如果恢复结束后数据库目录下的recover.conf会自动更名为recover.done。本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！