PostgreSQL 常用维护操作

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,此文件为初始化数据库时产生,有的字段随时更新,请不要试图手工修改它。

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


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

kevin_test=# \x on
Expanded display is on.
kevin_test=# select * from pg_database;
-[ RECORD 1 ]-+------------------------------------
datname       | template1
datdba        | 10
encoding      | 6
datcollate    | zh_CN.UTF-8
datctype      | zh_CN.UTF-8
datistemplate | t
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 11563
datfrozenxid  | 648
dattablespace | 1663
datconfig     |
datacl        | {=c/postgres,postgres=CTc/postgres}
-[ RECORD 2 ]-+------------------------------------
datname       | template0
datdba        | 10
encoding      | 6
datcollate    | zh_CN.UTF-8
datctype      | zh_CN.UTF-8
datistemplate | t
datallowconn  | f
datconnlimit  | -1
datlastsysoid | 11563
datfrozenxid  | 648
dattablespace | 1663
datconfig     |
datacl        | {=c/postgres,postgres=CTc/postgres}
-[ RECORD 3 ]-+------------------------------------
datname       | postgres
datdba        | 10
encoding      | 6
datcollate    | zh_CN.UTF-8
datctype      | zh_CN.UTF-8
datistemplate | f
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 11563
datfrozenxid  | 648
dattablespace | 1663
datconfig     |
datacl        |
-[ RECORD 4 ]-+------------------------------------
datname       | kevin_test
datdba        | 10
encoding      | 6
datcollate    | zh_CN.UTF-8
datctype      | zh_CN.UTF-8
datistemplate | f
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 11563
datfrozenxid  | 648
dattablespace | 1663
datconfig     |
datacl        |
-[ RECORD 5 ]-+------------------------------------
datname       | pg_test_database_3
datdba        | 16390
encoding      | 6
datcollate    | zh_CN.UTF-8
datctype      | zh_CN.UTF-8
datistemplate | f
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 11563
datfrozenxid  | 648
dattablespace | 1663
datconfig     | {add_missing_from=off}
datacl        |

kevin_test=#
kevin_test=#
kevin_test=# \x off
Expanded display is off.
kevin_test=# select * from pg_database;
      datname       | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespa
ce |       datconfig        |               datacl
--------------------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------
---+------------------------+-------------------------------------
 template1          |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | t             | t            |           -1 |         11563 |          648 |          16
63 |                        | {=c/postgres,postgres=CTc/postgres}
 template0          |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | t             | f            |           -1 |         11563 |          648 |          16
63 |                        | {=c/postgres,postgres=CTc/postgres}
 postgres           |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | f             | t            |           -1 |         11563 |          648 |          16
63 |                        |
 kevin_test         |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | f             | t            |           -1 |         11563 |          648 |          16
63 |                        |
 pg_test_database_3 |  16390 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | f             | t            |           -1 |         11563 |          648 |          16
63 | {add_missing_from=off} |
(5 rows)

kevin_test=#

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=#




 

  • 1
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值