PG日常管理参考

1. 查看表结构

1.1SQL查询

SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a.atttypmod AS lengthvar,a.attnotnull AS notnull
FROM pg_class c,pg_attribute a,pg_type t
WHERE c.relname='t1' and  a.attnum > 0  and  a.attrelid = c.oid and a.atttypid = t.oid
postgres=# SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a.atttypmod AS lengthvar,a.attnotnull AS notnull
postgres-# FROM pg_class c,pg_attribute a,pg_type t
postgres-# WHERE c.relname='t1' and  a.attnum > 0  and  a.attrelid = c.oid and a.atttypid = t.oid
postgres-# ;
 attnum | field | type | length | lengthvar | notnull
--------+-------+------+--------+-----------+---------
      1 | id    | int4 |      4 |        -1 | t
1.2快捷查询

postgres-# \d+ t1;
                                    Table "public.t1"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           | not null |         | plain   | 10000        |
Indexes:
    "pk_t1_id" PRIMARY KEY, btree (id)
    "idx_t1_id" btree (id)
Tablespace: "ts_data"
Access method: heap
Options: autovacuum_vacuum_cost_delay=10, autovacuum_vacuum_threshold=10000, autovacuum_analyze_threshold=10000, autovacuum_vacuum_cost_limit=10000, autovacuum_vacuum_scale_factor=0, autovacuum_analyze_scale_factor=0

2.查看锁定表

SELECT pg_class.relname AS table, pg_database.datname AS database,  pid, mode, granted
FROM pg_locks, pg_class, pg_database
WHERE pg_locks.relation = pg_class.oid
AND pg_locks.database = pg_database.oid;
postgres=# SELECT pg_class.relname AS table, pg_database.datname AS database,  pid, mode, granted
postgres-# FROM pg_locks, pg_class, pg_database
postgres-# WHERE pg_locks.relation = pg_class.oid
postgres-# AND pg_locks.database = pg_database.oid;
               table               | database |  pid  |      mode       | granted
-----------------------------------+----------+-------+-----------------+---------
 pg_class_tblspc_relfilenode_index | postgres | 34425 | AccessShareLock | t
 pg_class_relname_nsp_index        | postgres | 34425 | AccessShareLock | t
 pg_class_oid_index                | postgres | 34425 | AccessShareLock | t
 pg_class                          | postgres | 34425 | AccessShareLock | t
 pg_locks                          | postgres | 34425 | AccessShareLock | t
(5 rows)

3.查看数据库大小

postgres=# SELECT pg_size_pretty(pg_database_size('POSTGRES')) As fulldbsize;
 fulldbsize 
------------
 5151 MB
(1 row)

4.快捷查询

4.1查看schema/owner关系
postgres-# \dn
        List of schemas
        Name        |  Owner   
--------------------+----------
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 pg_toast_temp_1    | postgres
 public             | postgres
(5 rows)
4.2、查看管理员用户
postgres-# \du
                        List of roles
  Role name  |            Attributes             | Member of 
-------------+-----------------------------------+-----------
 gisplatform |                                   | {}
 postgres    | Superuser, Create role, Create DB | {}
4.3、查看数据库列表(dl)--l不是1
postgres-# \dl
                                 List of databases
   Name    |  Owner   | Encoding | Collation |    Ctype    |   Access privileges   
-----------+----------+----------+-----------+-------------+-----------------------
 postgis   | postgres | UTF8     | C         | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | C         | en_US.UTF-8 | 
 template0 | postgres | UTF8     | C         | en_US.UTF-8 | =c/postgres          +
           |          |          |           |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | C         | en_US.UTF-8 | =c/postgres          +
           |          |          |           |             | postgres=CTc/postgres
(4 rows)