PostgreSQL常用命令（持续更新）
2021/11/6 2:10:52
本文主要是介绍PostgreSQL常用命令（持续更新），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
PostgreSQL 10.18 on Windows
--
PostgreSQL\10\bin 下可执行exe文件（部分）：
psql.exe
createdb.exe
dropdb.exe
initdb.exe
createuser.exe
dropuser.exe
本文主要展示 psql的使用，以及进入PostgreSQL控制台的命令使用。
超级用户（角色）：postgres
注，下文如未特别说明，都是使用此用户进行操作。
基本使用
使用psql连接DB服务器
帮助信息：
>psql --help
访问本机DB服务器：
>psql -U postgres 用户 postgres 的口令： psql (10.18) 输入 "help" 来获取帮助信息. postgres=#
帮助信息：help
postgres=# help
您正在使用psql, 这是一种用于访问PostgreSQL的命令行界面
键入： \copyright 显示发行条款
\h 显示 SQL 命令的说明
\? 显示 pgsql 命令的说明
\g 或者以分号(;)结尾以执行查询
\q 退出
postgres=#
postgres=# \copyright
PostgreSQL Database Management System
(formerly known as Postgres, then as Postgres95)
Portions Copyright (c) 1996-2017, PostgreSQL Global Development Group
......省略......
postgres=# \h
可用的说明：
ABORT CREATE USER MAPPING
ALTER AGGREGATE CREATE VIEW
ALTER COLLATION DEALLOCATE
ALTER CONVERSION DECLARE
......省略......
postgres=# \?
一般性
\copyright 显示PostgreSQL的使用和发行许可条款
\crosstabview [COLUMNS] 执行查询并且以交叉表显示结果
\errverbose 以最冗长的形式显示最近的错误消息
\g [文件] or; 执行查询 (并把结果写入文件或 |管道)
......省略......
\q 退出脚本。
\h 显示 SQL脚本，\h + 命令，可以查看脚本语法：
postgres=# \h ABORT
命令： ABORT
描述： 中止目前的事务
语法：
ABORT [ WORK | TRANSACTION ]
postgres=# \h SELECT
命令： SELECT
描述： 从数据表或视图中读取数据
语法：
[ WITH [ RECURSIVE ] with查询语句(with_query) [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( 表达式 [, ...] ) ] ]
[ * | 表达式 [ [ AS ] 输出名称 ] [, ...] ]
[ FROM from列表中项 [, ...] ]
[ WHERE 条件 ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING 条件 ]
......省略......
\? 分页显示 一些命令——非SQL脚本，下面将介绍更多这些命令。
说明，
出现“-- More --”时，
按 Enter 看下一行，
按 空格 看下一页，
按 q 结束查看。
其中有很多后面要介绍的命令。
执行 \? 中查到的一些命令，目前主要用了查看 数据库的信息。
当前连接信息：
postgres=# \conninfo 以用户 "postgres" 的身份, 在主机"localhost", 端口"5432"连接到数据库 "postgres"
注，目前只有一个 postgres 数据库，后面再新建数据库后，再讲 切换。
查看数据库列表：\list \list+ \l \l+ (是 L 不是 一）
postgres=# \l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------+----------+----------+--------------------------------+--------------------------------+-----------------------
postgres | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
template0 | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 行记录)
postgres=# \l+
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限 | 大小 | 表空间 | 描述
-----------+----------+----------+--------------------------------+--------------------------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | | 7575 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres +| 7441 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres +| 7441 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(3 行记录)
列出表、视图、序列：
选项说明：S = 显示系统对象, + = 其余的详细信息
postgres=# \d
Did not find any relations.
加了 S ，显示出了表、视图等信息
postgres=# \dS
关联列表
架构模式 | 名称 | 类型 | 拥有者
------------+---------------------------------+--------+----------
pg_catalog | pg_aggregate | 数据表 | postgres
pg_catalog | pg_am | 数据表 | postgres
pg_catalog | pg_amop | 数据表 | postgres
pg_catalog | pg_amproc | 数据表 | postgres
pg_catalog | pg_attrdef | 数据表 | postgres
pg_catalog | pg_attribute | 数据表 | postgres
pg_catalog | pg_auth_members | 数据表 | postgres
pg_catalog | pg_authid | 数据表 | postgres
pg_catalog | pg_available_extension_versions | 视图 | postgres
pg_catalog | pg_available_extensions | 视图 | postgres
......省略......
再添加 加号（+），显示了更多信息，包括表存储空间
postgres=# \dS+
关联列表
架构模式 | 名称 | 类型 | 拥有者 | 大小 | 描述
------------+---------------------------------+--------+----------+------------+------
pg_catalog | pg_aggregate | 数据表 | postgres | 48 kB |
pg_catalog | pg_am | 数据表 | postgres | 40 kB |
pg_catalog | pg_amop | 数据表 | postgres | 80 kB |
pg_catalog | pg_amproc | 数据表 | postgres | 64 kB |
pg_catalog | pg_attrdef | 数据表 | postgres | 8192 bytes |
pg_catalog | pg_attribute | 数据表 | postgres | 416 kB |
pg_catalog | pg_auth_members | 数据表 | postgres | 40 kB |
pg_catalog | pg_authid | 数据表 | postgres | 40 kB |
pg_catalog | pg_available_extension_versions | 视图 | postgres | 0 bytes |
pg_catalog | pg_available_extensions | 视图 | postgres | 0 bytes |
......省略......
\d[S+] 名称 ： 描述表，视图，序列，或索引
postgres=# \dS pg_attribute
数据表 "pg_catalog.pg_attribute"
栏位 | 类型 | Collation | Nullable | Default
---------------+-----------+-----------+----------+---------
attrelid | oid | | not null |
attname | name | | not null |
atttypid | oid | | not null |
attstattarget | integer | | not null |
attlen | smallint | | not null |
attnum | smallint | | not null |
attndims | integer | | not null |
attcacheoff | integer | | not null |
atttypmod | integer | | not null |
attbyval | boolean | | not null |
attstorage | "char" | | not null |
attalign | "char" | | not null |
attnotnull | boolean | | not null |
atthasdef | boolean | | not null |
attidentity | "char" | | not null |
attisdropped | boolean | | not null |
attislocal | boolean | | not null |
attinhcount | integer | | not null |
attcollation | oid | | not null |
attacl | aclitem[] | | |
attoptions | text[] | | |
attfdwoptions | text[] | | |
索引：
"pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
"pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)
postgres=#
postgres=#
postgres=#
postgres=# \dS pg_available_extensions
视图 "pg_catalog.pg_available_extensions"
栏位 | 类型 | Collation | Nullable | Default
-------------------+------+-----------+----------+---------
name | name | | |
default_version | text | | |
installed_version | text | | |
comment | text | | |
postgres=#
添加 加号（+） 会显示更多：
postgres=# \dS+ pg_attribute
数据表 "pg_catalog.pg_attribute"
栏位 | 类型 | Collation | Nullable | Default | 存储 | 统计目标 | 描述
---------------+-----------+-----------+----------+---------+----------+----------+------
attrelid | oid | | not null | | plain | |
attname | name | | not null | | plain | |
atttypid | oid | | not null | | plain | |
attstattarget | integer | | not null | | plain | |
attlen | smallint | | not null | | plain | |
attnum | smallint | | not null | | plain | |
attndims | integer | | not null | | plain | |
attcacheoff | integer | | not null | | plain | |
atttypmod | integer | | not null | | plain | |
attbyval | boolean | | not null | | plain | |
attstorage | "char" | | not null | | plain | |
attalign | "char" | | not null | | plain | |
attnotnull | boolean | | not null | | plain | |
atthasdef | boolean | | not null | | plain | |
attidentity | "char" | | not null | | plain | |
attisdropped | boolean | | not null | | plain | |
attislocal | boolean | | not null | | plain | |
attinhcount | integer | | not null | | plain | |
attcollation | oid | | not null | | plain | |
attacl | aclitem[] | | | | extended | |
attoptions | text[] | | | | extended | |
attfdwoptions | text[] | | | | extended | |
索引：
"pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
"pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)
postgres=#
查看PostgreSQL数据库连接及数量
postgres=# select * from pg_stat_activity;
这个表的信息很多，可以只 查询其中部分字段：
postgres=# select datid,datname,pid,usename,state,client_addr,query from pg_stat_activity;
datid | datname | pid | usename | state | client_addr | query
-------+----------+-------+----------+--------+-------------+---------------------------------------------------------------------------------
| | 18904 | | | |
| | 18556 | postgres | | |
12938 | postgres | 16576 | postgres | active | ::1 | select datid,datname,pid,usename,state,client_addr,query from pg_stat_activity;
16394 | lib1 | 13592 | postgres | idle | 127.0.0.1 | SELECT id,name,age,email FROM guser WHERE id=$1
16394 | lib1 | 11140 | postgres | idle | 127.0.0.1 | SET application_name = 'PostgreSQL JDBC Driver'
16394 | lib1 | 14976 | postgres | idle | 127.0.0.1 | SET application_name = 'PostgreSQL JDBC Driver'
16394 | lib1 | 9508 | postgres | idle | 127.0.0.1 | SET application_name = 'PostgreSQL JDBC Driver'
16394 | lib1 | 6728 | postgres | idle | 127.0.0.1 | SET application_name = 'PostgreSQL JDBC Driver'
16394 | lib1 | 12988 | postgres | idle | 127.0.0.1 | SET application_name = 'PostgreSQL JDBC Driver'
16394 | lib1 | 15288 | postgres | idle | 127.0.0.1 | SET application_name = 'PostgreSQL JDBC Driver'
16394 | lib1 | 12872 | postgres | idle | 127.0.0.1 | SET application_name = 'PostgreSQL JDBC Driver'
16394 | lib1 | 13108 | postgres | idle | 127.0.0.1 | SET application_name = 'PostgreSQL JDBC Driver'
16394 | lib1 | 19376 | postgres | idle | 127.0.0.1 | SET application_name = 'PostgreSQL JDBC Driver'
| | 14144 | | | |
| | 9304 | | | |
| | 992 | | | |
(16 行记录)
注意，其中有10个idle的连接，这个来自本机的一个Spring Boot程序。默认的数据库拦截池使用 HikariCP，十个连接。
更换为Druid数据库后：配置了更少的初始数据库连接（当然，HikariCP也可以配置），，这次只有8个了
lib2=# select datid,datname,pid,usename,state,client_addr,query from pg_stat_activity;
datid | datname | pid | usename | state | client_addr | query
-------+---------+-------+----------+--------+-------------+---------------------------------------------------------------------------------
| | 18904 | | | |
| | 18556 | postgres | | |
16402 | lib2 | 15936 | postgres | active | ::1 | select datid,datname,pid,usename,state,client_addr,query from pg_stat_activity;
16394 | lib1 | 2940 | postgres | idle | 127.0.0.1 | SELECT id,name,age,email FROM guser WHERE id=$1
16402 | lib2 | 18808 | postgres | idle | 127.0.0.1 | SELECT id,sn,name FROM device WHERE id=$1
| | 14144 | | | |
| | 9304 | | | |
| | 992 | | | |
(8 行记录)
数据备份与恢复
方式1：导出sql文件，导入也是sql文件
文本文件导入导出使用 psql
# 导出数据库 lib1 D:\>pg_dump -U postgres -f lib1 lib1 口令: # 将数据库lib1导出才 文本文件 导入数据库 lib3 D:\>psql -U postgres -d lib3 -f lib1 ...内容会很多...
方式2：dump文件导出导入 -Fc
D:\>pg_dump -U postgres -Fc lib1 > lib1.dump 口令: D:\>pg_restore -U postgres -d lib3 lib1.dump > a.txt 口令:
方式3：tar文件导出导入 -Ft
D:\>pg_dump -U postgres -Ft lib1 > lib1.tar 口令: D:\> D:\>pg_restore -U postgres -d lib3 lib1.tar > a.txt 口令:
注意，
1、方式1执行时，使用 版本10的 psql从 版本13 的 PostgreSQL服务器获取数据时，失败了；
后安装了 PostgreSQL 14，就可以从 低版本13 中导出数据了。
2、方式2、3中，导入时的 信息都被 重定向到了 a.txt 文件。
用户&角色管理
todo
参考文档
1、postgresql数据库的数据导出
2、postgresql 查看数据库连接数
3、
这篇关于PostgreSQL常用命令（持续更新）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-01-05快速清空 PostgreSQL 数据库中的所有表格，让你的数据库重新焕然一新！
- 2024-01-04在PostgreSQL中创建角色：判断角色是否存在并创建
- 2023-05-16PostgreSQL一站式插件推荐 -- pg_enterprise_views
- 2022-11-22PostgreSQL 实时位置跟踪
- 2022-11-22如何将PostgreSQL插件移植到openGauss
- 2022-11-11PostgreSQL：修改数据库用户的密码
- 2022-11-06Windows 环境搭建 PostgreSQL 物理复制高可用架构数据库服务
- 2022-10-27Windows 环境搭建 PostgreSQL 逻辑复制高可用架构数据库服务
- 2022-10-11PostgreSql安装(Windows10版本)
- 2022-09-13PostgreSQL-Network Address类型操作和函数
