PostgreSQL 常用维护操作

hotnet

　　1. 查看数据库到目前为止启动时长。
kevin_test=# SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;uptime----------05:46:47(1 row)kevin_test=#　　
2.查看启动时间。
　　kevin_test=# SELECT pg_postmaster_start_time() as start_time;start_time-------------------------------2011-07-18 01:09:36.447192+08(1 row)
　　kevin_test=#
　　
3,pg的server控制文件pg_controldata，此文件为初始化数据库时产生，有的字段随时更新，请不要试图手工修改它。
[iyunv@kevin bin]# pwd/opt/postgresql/bin[iyunv@kevin bin]#  ./pg_controldatapg_control version number:            843Catalog version number:               200904091Database system identifier:           5627161982392579794Database cluster state:               in productionpg_control last modified:             2011年07月18日 星期一 01时09分36秒Latest checkpoint location:           0/4C722CPrior checkpoint location:            0/4C71E8Latest checkpoint's REDO location:    0/4C722CLatest checkpoint's TimeLineID:       1Latest checkpoint's NextXID:          0/711Latest checkpoint's NextOID:          16420Latest checkpoint's NextMultiXactId:  1Latest checkpoint's NextMultiOffset:  0Time of latest checkpoint:            2011年07月17日 星期日 16时17分44秒Minimum recovery ending location:     0/0Maximum data alignment:               4Database block size:                  8192Blocks per segment of large relation: 131072WAL block size:                       8192Bytes per WAL segment:                16777216Maximum length of identifiers:        64Maximum columns in an index:          32Maximum size of a TOAST chunk:        2000Date/time type storage:               64-bit integersFloat4 argument passing:              by valueFloat8 argument passing:              by reference[iyunv@kevin bin]#　　
4.查询结果输出格式控制 \x [on|off]选项
kevin_test=# \x onExpanded display is on.kevin_test=# select * from pg_database;-[ RECORD 1 ]-+------------------------------------datname       | template1datdba        | 10encoding      | 6datcollate    | zh_CN.UTF-8datctype      | zh_CN.UTF-8datistemplate | tdatallowconn  | tdatconnlimit  | -1datlastsysoid | 11563datfrozenxid  | 648dattablespace | 1663datconfig     |datacl        | {=c/postgres,postgres=CTc/postgres}-[ RECORD 2 ]-+------------------------------------datname       | template0datdba        | 10encoding      | 6datcollate    | zh_CN.UTF-8datctype      | zh_CN.UTF-8datistemplate | tdatallowconn  | fdatconnlimit  | -1datlastsysoid | 11563datfrozenxid  | 648dattablespace | 1663datconfig     |datacl        | {=c/postgres,postgres=CTc/postgres}-[ RECORD 3 ]-+------------------------------------datname       | postgresdatdba        | 10encoding      | 6datcollate    | zh_CN.UTF-8datctype      | zh_CN.UTF-8datistemplate | fdatallowconn  | tdatconnlimit  | -1datlastsysoid | 11563datfrozenxid  | 648dattablespace | 1663datconfig     |datacl        |-[ RECORD 4 ]-+------------------------------------datname       | kevin_testdatdba        | 10encoding      | 6datcollate    | zh_CN.UTF-8datctype      | zh_CN.UTF-8datistemplate | fdatallowconn  | tdatconnlimit  | -1datlastsysoid | 11563datfrozenxid  | 648dattablespace | 1663datconfig     |datacl        |-[ RECORD 5 ]-+------------------------------------datname       | pg_test_database_3datdba        | 16390encoding      | 6datcollate    | zh_CN.UTF-8datctype      | zh_CN.UTF-8datistemplate | fdatallowconn  | tdatconnlimit  | -1datlastsysoid | 11563datfrozenxid  | 648dattablespace | 1663datconfig     | {add_missing_from=off}datacl        |kevin_test=#kevin_test=#kevin_test=# \x offExpanded display is off.kevin_test=# select * from pg_database;datname       | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace |       datconfig        |               datacl--------------------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+---------------+------------------------+-------------------------------------template1          |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | t             | t            |           -1 |         11563 |          648 |          1663 |                        | {=c/postgres,postgres=CTc/postgres}template0          |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | t             | f            |           -1 |         11563 |          648 |          1663 |                        | {=c/postgres,postgres=CTc/postgres}postgres           |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | f             | t            |           -1 |         11563 |          648 |          1663 |                        |kevin_test         |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | f             | t            |           -1 |         11563 |          648 |          1663 |                        |pg_test_database_3 |  16390 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | f             | t            |           -1 |         11563 |          648 |          1663 | {add_missing_from=off} |(5 rows)kevin_test=#　　4.查看当前数据库中有多少张用户自己的表。
kevin_test=# SELECT count(*) FROM information_schema.tableskevin_test-# WHERE table_schemakevin_test-# NOT IN ('information_schema', 'pg_catalog');count-------1(1 row)kevin_test=#kevin_test=#kevin_test=# SELECT * FROM information_schema.tablesWHERE table_schemaNOT IN ('information_schema', 'pg_catalog');table_catalog | table_schema |  table_name   | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action---------------+--------------+---------------+------------+------------------------------+----------------------+---------------------------+--------------------------+------------------------+--------------------+----------+---------------kevin_test    | public       | tbl_test_port | BASE TABLE |                              ||                           |                          |                        | YES                | NO|(1 row)kevin_test=#　　附：视图information_schema.tables的结构。
kevin_test=# \d information_schema.tablesView "information_schema.tables"Column            |               Type                | Modifiers------------------------------+-----------------------------------+-----------table_catalog                | information_schema.sql_identifier |table_schema                 | information_schema.sql_identifier |table_name                   | information_schema.sql_identifier |table_type                   | information_schema.character_data |self_referencing_column_name | information_schema.sql_identifier |reference_generation         | information_schema.character_data |user_defined_type_catalog    | information_schema.sql_identifier |user_defined_type_schema     | information_schema.sql_identifier |user_defined_type_name       | information_schema.sql_identifier |is_insertable_into           | information_schema.character_data |is_typed                     | information_schema.character_data |commit_action                | information_schema.character_data |View definition:SELECT current_database()::information_schema.sql_identifier AS table_catalog, nc.nspname::information_schema.sql_identifier AS table_schema, c.relname::information_schema.sql_identifier AS table_name,CASEWHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::textWHEN c.relkind = 'r'::"char" THEN 'BASE TABLE'::textWHEN c.relkind = 'v'::"char" THEN 'VIEW'::textELSE NULL::textEND::information_schema.character_data AS table_type, NULL::character varying::information_schema.sql_identifier AS self_referencing_column_name, NULL::character varying::information_schema.character_data AS reference_generation, NULL::character varying::information_schema.sql_identifier AS user_defined_type_catalog, NULL::character varying::information_schema.sql_identifier AS user_defined_type_schema, NULL::character varying::information_schema.sql_identifier AS user_defined_type_name,CASEWHEN c.relkind = 'r'::"char" OR c.relkind = 'v'::"char" AND (EXISTS ( SELECT 1FROM pg_rewriteWHERE pg_rewrite.ev_class = c.oid AND pg_rewrite.ev_type = '3'::"char" AND pg_rewrite.is_instead)) THEN 'YES'::textELSE 'NO'::textEND::information_schema.character_data AS is_insertable_into, 'NO'::character varying::information_schema.character_data AS is_typed,CASEWHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE'::textELSE NULL::textEND::information_schema.character_data AS commit_actionFROM pg_namespace nc, pg_class cWHERE c.relnamespace = nc.oid AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char"])) AND NOT pg_is_other_temp_schema(nc.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text));kevin_test=#