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,此文件为初始化数据库时产生,有的字段随时更新,请不要试图手工修改它。
4.查询结果输出格式控制 \x [on|off]选项
4.查看当前数据库中有多少张用户自己的表。
kevin_test=# SELECT count(*) FROM information_schema.tables kevin_test-# WHERE table_schema kevin_test-# NOT IN ('information_schema', 'pg_catalog'); count ------- 1 (1 row) kevin_test=# kevin_test=# kevin_test=# SELECT * FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'pg_catalog'); table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generatio n | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typ ed | commit_action ---------------+--------------+---------------+------------+------------------------------+-------------------- --+---------------------------+--------------------------+------------------------+--------------------+------- ---+--------------- kevin_test | public | tbl_test_port | BASE TABLE | | | | | | YES | NO | (1 row) kevin_test=#附:视图information_schema.tables的结构。
kevin_test=# \d information_schema.tables View "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, CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::text WHEN c.relkind = 'r'::"char" THEN 'BASE TABLE'::text WHEN c.relkind = 'v'::"char" THEN 'VIEW'::text ELSE NULL::text END::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, CASE WHEN c.relkind = 'r'::"char" OR c.relkind = 'v'::"char" AND (EXISTS ( SELECT 1 FROM pg_rewrite WHERE pg_rewrite.ev_class = c.oid AND pg_rewrite.ev_type = '3'::"char" AND pg_rewrite.is_instead)) THEN 'YES'::text ELSE 'NO'::text END::information_schema.character_data AS is_insertable_into, 'NO'::character varying::information_schema.character_data AS is_typed, CASE WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE'::text ELSE NULL::text END::information_schema.character_data AS commit_action FROM pg_namespace nc, pg_class c WHERE 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=#