PostgreSQL 常用命令

列出所有的数据库

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

postgres=# \list
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

# 列出所有的数据库的大小

postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/postgres         +| 8593 kB | pg_default | default administrative connection database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +| 8385 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +| 8385 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
 testdb    | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |                       | 8537 kB | pg_default |
(4 rows)

切换数据库

postgres=# \c testdb;
You are now connected to database "testdb" as user "postgres".
testdb=#

查询所有数据库大小

postgres=# select datname, pg_size_pretty (pg_database_size(datname)) AS size from pg_database;
  datname  |  size
-----------+---------
 postgres  | 8593 kB
 template1 | 8385 kB
 template0 | 8385 kB
 testdb    | 8537 kB
(4 rows)

查看PostgreSQL数据库连接及数量

postgres=# select * from pg_stat_activity;
 datid | datname  |  pid  | leader_pid | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query
_start          |         state_change          | wait_event_type |     wait_event      | state  | backend_xid | backend_xmin | query_id |              query              |         backend_type
-------+----------+-------+------------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+---------------
----------------+-------------------------------+-----------------+---------------------+--------+-------------+--------------+----------+---------------------------------+------------------------------
       |          |  1665 |            |       10 | postgres |                  |             |                 |             | 2023-01-02 10:39:32.136348+08 |                               |
                |                               | Activity        | LogicalLauncherMain |        |             |              |          |                                 | logical replication launcher
       |          |  1663 |            |          |          |                  |             |                 |             | 2023-01-02 10:39:32.137592+08 |                               |
                |                               | Activity        | AutoVacuumMain      |        |             |              |          |                                 | autovacuum launcher
 13892 | postgres | 10563 |            |       10 | postgres | psql             |             |                 |          -1 | 2023-01-02 19:58:21.927915+08 | 2023-01-02 20:08:52.494496+08 | 2023-01-02 20:
08:52.494496+08 | 2023-01-02 20:08:52.494497+08 |                 |                     | active |             |          824 |          | select * from pg_stat_activity; | client backend
       |          |  1661 |            |          |          |                  |             |                 |             | 2023-01-02 10:39:32.136859+08 |                               |
                |                               | Activity        | BgWriterHibernate   |        |             |              |          |                                 | background writer
       |          |  1660 |            |          |          |                  |             |                 |             | 2023-01-02 10:39:32.139787+08 |                               |
                |                               | Activity        | CheckpointerMain    |        |             |              |          |                                 | checkpointer
       |          |  1662 |            |          |          |                  |             |                 |             | 2023-01-02 10:39:32.137144+08 |                               |
                |                               | Activity        | WalWriterMain       |        |             |              |          |                                 | walwriter
(6 rows)