PostgreSQL 常用维护操作


1. 查看数据库到目前为止启动时长。

  1. kevin_test=# SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;  
  2.   uptime  
  3. ----------  
  4.  05:46:47  
  5. (1 row)  
  6.   
  7. kevin_test=#  

2.查看启动时间。

  1. <p>kevin_test=# SELECT pg_postmaster_start_time() as start_time;  
  2.           start_time  
  3. -------------------------------  
  4.  2011-07-18 01:09:36.447192+08  
  5. (1 row)</p><p>kevin_test=#  
  6. </p>  

3,pg的server控制文件pg_controldata，此文件为初始化数据库时产生，有的字段随时更新，请不要试图手工修改它。

  1. [root@kevin bin]# pwd  
  2. /opt/postgresql/bin  
  3. [root@kevin bin]#  ./pg_controldata  
  4. pg_control version number:            843  
  5. Catalog version number:               200904091  
  6. Database system identifier:           5627161982392579794  
  7. Database cluster state:               in production  
  8. pg_control last modified:             2011年07月18日 星期一 01时09分36秒  
  9. Latest checkpoint location:           0/4C722C  
  10. Prior checkpoint location:            0/4C71E8  
  11. Latest checkpoint's REDO location:    0/4C722C  
  12. Latest checkpoint's TimeLineID:       1  
  13. Latest checkpoint's NextXID:          0/711  
  14. Latest checkpoint's NextOID:          16420  
  15. Latest checkpoint's NextMultiXactId:  1  
  16. Latest checkpoint's NextMultiOffset:  0  
  17. Time of latest checkpoint:            2011年07月17日 星期日 16时17分44秒  
  18. Minimum recovery ending location:     0/0  
  19. Maximum data alignment:               4  
  20. Database block size:                  8192  
  21. Blocks per segment of large relation: 131072  
  22. WAL block size:                       8192  
  23. Bytes per WAL segment:                16777216  
  24. Maximum length of identifiers:        64  
  25. Maximum columns in an index:          32  
  26. Maximum size of a TOAST chunk:        2000  
  27. Date/time type storage:               64-bit integers  
  28. Float4 argument passing:              by value  
  29. Float8 argument passing:              by reference  
  30. [root@kevin bin]#  


4.查询结果输出格式控制 \x [on|off]选项

  1. kevin_test=# \x on  
  2. Expanded display is on.  
  3. kevin_test=# select * from pg_database;  
  4. -[ RECORD 1 ]-+------------------------------------  
  5. datname       | template1  
  6. datdba        | 10  
  7. encoding      | 6  
  8. datcollate    | zh_CN.UTF-8  
  9. datctype      | zh_CN.UTF-8  
  10. datistemplate | t  
  11. datallowconn  | t  
  12. datconnlimit  | -1  
  13. datlastsysoid | 11563  
  14. datfrozenxid  | 648  
  15. dattablespace | 1663  
  16. datconfig     |  
  17. datacl        | {=c/postgres,postgres=CTc/postgres}  
  18. -[ RECORD 2 ]-+------------------------------------  
  19. datname       | template0  
  20. datdba        | 10  
  21. encoding      | 6  
  22. datcollate    | zh_CN.UTF-8  
  23. datctype      | zh_CN.UTF-8  
  24. datistemplate | t  
  25. datallowconn  | f  
  26. datconnlimit  | -1  
  27. datlastsysoid | 11563  
  28. datfrozenxid  | 648  
  29. dattablespace | 1663  
  30. datconfig     |  
  31. datacl        | {=c/postgres,postgres=CTc/postgres}  
  32. -[ RECORD 3 ]-+------------------------------------  
  33. datname       | postgres  
  34. datdba        | 10  
  35. encoding      | 6  
  36. datcollate    | zh_CN.UTF-8  
  37. datctype      | zh_CN.UTF-8  
  38. datistemplate | f  
  39. datallowconn  | t  
  40. datconnlimit  | -1  
  41. datlastsysoid | 11563  
  42. datfrozenxid  | 648  
  43. dattablespace | 1663  
  44. datconfig     |  
  45. datacl        |  
  46. -[ RECORD 4 ]-+------------------------------------  
  47. datname       | kevin_test  
  48. datdba        | 10  
  49. encoding      | 6  
  50. datcollate    | zh_CN.UTF-8  
  51. datctype      | zh_CN.UTF-8  
  52. datistemplate | f  
  53. datallowconn  | t  
  54. datconnlimit  | -1  
  55. datlastsysoid | 11563  
  56. datfrozenxid  | 648  
  57. dattablespace | 1663  
  58. datconfig     |  
  59. datacl        |  
  60. -[ RECORD 5 ]-+------------------------------------  
  61. datname       | pg_test_database_3  
  62. datdba        | 16390  
  63. encoding      | 6  
  64. datcollate    | zh_CN.UTF-8  
  65. datctype      | zh_CN.UTF-8  
  66. datistemplate | f  
  67. datallowconn  | t  
  68. datconnlimit  | -1  
  69. datlastsysoid | 11563  
  70. datfrozenxid  | 648  
  71. dattablespace | 1663  
  72. datconfig     | {add_missing_from=off}  
  73. datacl        |  
  74.   
  75. kevin_test=#  
  76. kevin_test=#  
  77. kevin_test=# \x off  
  78. Expanded display is off.  
  79. kevin_test=# select * from pg_database;  
  80.       datname       | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespa  
  81. ce |       datconfig        |               datacl  
  82. --------------------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------  
  83. ---+------------------------+-------------------------------------  
  84.  template1          |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | t             | t            |           -1 |         11563 |          648 |          16  
  85. 63 |                        | {=c/postgres,postgres=CTc/postgres}  
  86.  template0          |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | t             | f            |           -1 |         11563 |          648 |          16  
  87. 63 |                        | {=c/postgres,postgres=CTc/postgres}  
  88.  postgres           |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | f             | t            |           -1 |         11563 |          648 |          16  
  89. 63 |                        |  
  90.  kevin_test         |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | f             | t            |           -1 |         11563 |          648 |          16  
  91. 63 |                        |  
  92.  pg_test_database_3 |  16390 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | f             | t            |           -1 |         11563 |          648 |          16  
  93. 63 | {add_missing_from=off} |  
  94. (5 rows)  
  95.   
  96. kevin_test=#  

4.查看当前数据库中有多少张用户自己的表。

  1. kevin_test=# SELECT count(*) FROM information_schema.tables  
  2. kevin_test-# WHERE table_schema  
  3. kevin_test-# NOT IN ('information_schema', 'pg_catalog');  
  4.  count  
  5. -------  
  6.      1  
  7. (1 row)  
  8.   
  9. kevin_test=#  
  10. kevin_test=#  
  11. kevin_test=# SELECT * FROM information_schema.tables  
  12. WHERE table_schema  
  13. NOT IN ('information_schema', 'pg_catalog');  
  14.  table_catalog | table_schema |  table_name   | table_type | self_referencing_column_name | reference_generatio  
  15. n | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typ  
  16. ed | commit_action  
  17. ---------------+--------------+---------------+------------+------------------------------+--------------------  
  18. --+---------------------------+--------------------------+------------------------+--------------------+-------  
  19. ---+---------------  
  20.  kevin_test    | public       | tbl_test_port | BASE TABLE |                              |  
  21.   |                           |                          |                        | YES                | NO  
  22.    |  
  23. (1 row)  
  24.   
  25. kevin_test=#  

附：视图information_schema.tables的结构。

  1. kevin_test=# \d information_schema.tables  
  2.                        View "information_schema.tables"  
  3.             Column            |               Type                | Modifiers  
  4. ------------------------------+-----------------------------------+-----------  
  5.  table_catalog                | information_schema.sql_identifier |  
  6.  table_schema                 | information_schema.sql_identifier |  
  7.  table_name                   | information_schema.sql_identifier |  
  8.  table_type                   | information_schema.character_data |  
  9.  self_referencing_column_name | information_schema.sql_identifier |  
  10.  reference_generation         | information_schema.character_data |  
  11.  user_defined_type_catalog    | information_schema.sql_identifier |  
  12.  user_defined_type_schema     | information_schema.sql_identifier |  
  13.  user_defined_type_name       | information_schema.sql_identifier |  
  14.  is_insertable_into           | information_schema.character_data |  
  15.  is_typed                     | information_schema.character_data |  
  16.  commit_action                | information_schema.character_data |  
  17. View definition:  
  18.  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,  
  19.         CASE  
  20.             WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::text  
  21.             WHEN c.relkind = 'r'::"char" THEN 'BASE TABLE'::text  
  22.             WHEN c.relkind = 'v'::"char" THEN 'VIEW'::text  
  23.             ELSE NULL::text  
  24.         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,  
  25.         CASE  
  26.             WHEN c.relkind = 'r'::"char" OR c.relkind = 'v'::"char" AND (EXISTS ( SELECT 1  
  27.                FROM pg_rewrite  
  28.               WHERE pg_rewrite.ev_class = c.oid AND pg_rewrite.ev_type = '3'::"char" AND pg_rewrite.is_instead)) THEN 'YES'::text  
  29.             ELSE 'NO'::text  
  30.         END::information_schema.character_data AS is_insertable_into, 'NO'::character varying::information_schema.character_data AS is_typed,  
  31.         CASE  
  32.             WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE'::text  
  33.             ELSE NULL::text  
  34.         END::information_schema.character_data AS commit_action  
  35.    FROM pg_namespace nc, pg_class c  
  36.   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));  
  37.   
  38. kevin_test=#  
  • 1
  • 2
  • 3
  • 下一页

相关内容