PostgreSQL 常用维护操作
时间:2019-02-18 11:40 来源:未知 作者:IT
1. 查看数据库到目前为止启动时长。
it_test=# SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;
uptime
----------
05:46:47
(1 row)
it_test=#
2.查看启动时间。
it_test=# SELECT pg_postmaster_start_time() as start_time; start_time ------------------------------- 2011-07-18 01:09:36.447192+08 (1 row)
it_test=#
3,pg的server控制文件pg_controldata，此文件为初始化数据库时产生，有的字段随时更新，请不要试图手工修改它。
[root@it bin]# pwd
/opt/postgresql/bin
[root@it 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@it bin]#
4.查询结果输出格式控制 \x [on|off]选项
it_test=# \x on
Expanded display is on.
it_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 | it_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 |
it_test=#
it_test=#
it_test=# \x off
Expanded display is off.
it_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 | |
it_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)
it_test=#
4.查看当前数据库中有多少张用户自己的表。
it_test=# SELECT count(*) FROM information_schema.tables
it_test-# WHERE table_schema
it_test-# NOT IN ('information_schema', 'pg_catalog');
count
-------
1
(1 row)
it_test=#
it_test=#
it_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
---------------+--------------+---------------+------------+------------------------------+--------------------
--+---------------------------+--------------------------+------------------------+--------------------+-------
---+---------------
it_test | public | tbl_test_port | BASE TABLE | |
| | | | YES | NO
|
(1 row)
it_test=#
附：视图information_schema.tables的结构。
it_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));
it_test=#
(责任编辑：IT)
| 1. 查看数据库到目前为止启动时长。 it_test=# SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime; uptime ---------- 05:46:47 (1 row) it_test=# 2.查看启动时间。 it_test=# SELECT pg_postmaster_start_time() as start_time; start_time ------------------------------- 2011-07-18 01:09:36.447192+08 (1 row) it_test=# 3,pg的server控制文件pg_controldata，此文件为初始化数据库时产生，有的字段随时更新，请不要试图手工修改它。 [root@it bin]# pwd /opt/postgresql/bin [root@it 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@it bin]# 4.查询结果输出格式控制 \x [on|off]选项 it_test=# \x on Expanded display is on. it_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 | it_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 | it_test=# it_test=# it_test=# \x off Expanded display is off. it_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 | | it_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) it_test=# 4.查看当前数据库中有多少张用户自己的表。 it_test=# SELECT count(*) FROM information_schema.tables it_test-# WHERE table_schema it_test-# NOT IN ('information_schema', 'pg_catalog'); count ------- 1 (1 row) it_test=# it_test=# it_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 ---------------+--------------+---------------+------------+------------------------------+-------------------- --+---------------------------+--------------------------+------------------------+--------------------+------- ---+--------------- it_test | public | tbl_test_port | BASE TABLE | | | | | | YES | NO | (1 row) it_test=# 附：视图information_schema.tables的结构。 it_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)); it_test=# (责任编辑：IT) |