PostgreSQL常用管理命令汇总
一、数据库管理命令
1.1 创建数据库
在PostgreSQL中，使用createdb命令可以创建一个新的数据库。createdb是一个命令行工具，它是对SQL语句CREATE DATABASE的封装，使得在命令行环境下创建数据库更加便捷。
语法：
createdb [连接选项] [选项] 数据库名
连接选项：用于指定连接到PostgreSQL服务器的参数，例如-h指定服务器主机名，-p指定端口号，-U指定用户名等。
常用选项：
-O, --owner=用户名：指定新数据库的所有者。-E, --encoding=编码：指定数据库的字符编码，如UTF8。-T, --template=模板数据库：指定用于创建新数据库的模板，默认使用template1。
示例：创建一个名为mydb，所有者为myuser，编码为UTF8的数据库。
createdb -O myuser -E UTF8 mydb
对应的SQL语句为：
CREATE DATABASE mydb
WITH
OWNER = myuser
ENCODING = 'UTF8';
1.2 删除数据库
删除数据库使用dropdb命令，它同样是对SQL语句DROP DATABASE的封装。
语法：
dropdb [连接选项] [选项] 数据库名
连接选项与createdb类似。
常用选项：
-i, --interactive：在删除数据库之前提示确认。
示例：删除名为mydb的数据库，并提示确认。
dropdb -i mydb
对应的SQL语句为：
DROP DATABASE mydb;
1.3 列出数据库
要查看当前PostgreSQL服务器上所有的数据库，可以使用psql工具中的\l命令。psql是PostgreSQL的交互式终端工具，提供了在命令行与数据库交互的功能。
在psql中使用：
\l
这将列出所有数据库的名称、所有者、编码等信息。
示例输出：
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
mydb | myuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
二、用户管理命令
2.1 创建用户
在PostgreSQL中，可以使用createuser命令创建新用户，它也是对SQL语句CREATE USER的封装。
语法：
createuser [连接选项] [选项] 用户名
连接选项：同前面数据库管理命令的连接选项。
常用选项：
-d, --createdb：允许用户创建数据库。-s, --superuser：将用户设置为超级用户，超级用户拥有所有权限。-P, --password：创建用户时提示输入密码。
示例：创建一个名为newuser，具有创建数据库权限，并设置密码的用户。
createuser -d -P newuser
对应的SQL语句为：
CREATE USER newuser WITH CREATEDB PASSWORD '用户密码';
2.2 删除用户
删除用户使用dropuser命令，对应SQL语句DROP USER。
语法：
dropuser [连接选项] [选项] 用户名
连接选项不变。
常用选项：
-i, --interactive：删除用户前提示确认。
示例：删除名为newuser的用户并提示确认。
dropuser -i newuser
对应的SQL语句为：
DROP USER newuser;
2.3 列出用户
在psql中，可以使用\du命令列出所有用户及其权限。
在psql中使用：
\du
示例输出：
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
myuser | Create DB | {}
newuser | Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
(3 rows)
三、表空间管理命令
3.1 创建表空间
表空间允许用户指定数据库对象（如表、索引等）存储的物理位置。使用CREATE TABLESPACE SQL语句来创建表空间。
语法：
CREATE TABLESPACE 表空间名
OWNER 用户
LOCATION '路径';
示例：创建一个名为mytablespace，所有者为myuser，位于/var/lib/postgresql/mytablespace的表空间。
CREATE TABLESPACE mytablespace
OWNER myuser
LOCATION '/var/lib/postgresql/mytablespace';
在命令行中，也可以使用psql执行上述SQL语句来创建表空间。
3.2 删除表空间
删除表空间使用DROP TABLESPACE SQL语句。
语法：
DROP TABLESPACE [IF EXISTS] 表空间名;
示例：删除名为mytablespace的表空间。
DROP TABLESPACE mytablespace;
IF EXISTS选项用于避免在表空间不存在时抛出错误。
3.3 列出表空间
在psql中，可以使用\db命令列出所有表空间及其相关信息。
在psql中使用：
\db
示例输出：
List of tablespaces
Name | Owner | Location
------------+----------+----------
mytablespace | myuser | /var/lib/postgresql/mytablespace
pg_default | postgres |
pg_global | postgres |
(3 rows)
四、数据库备份与恢复命令
4.1 备份数据库
4.1.1 使用pg_dump进行备份
pg_dump是PostgreSQL提供的用于备份数据库的工具，它可以将数据库中的数据和对象以SQL语句的形式输出到文件中，也可以输出为自定义格式、目录格式等，以便后续恢复。
语法：
pg_dump [连接选项] [选项] 数据库名 > 备份文件名
连接选项：如-h、-p、-U等用于指定连接参数。
常用选项：
-F, --format=c|d|t|p：指定备份格式，c为自定义格式，d为目录格式，t为tar格式，p为纯SQL格式（默认）。-f, --file=文件名：指定备份文件的名称。-v, --verbose：详细模式，输出更多备份过程信息。
示例：以纯SQL格式备份名为mydb的数据库到mydb_backup.sql文件。
pg_dump -U myuser mydb > mydb_backup.sql
若要以自定义格式备份：
pg_dump -F c -U myuser mydb -f mydb_backup.custom
4.1.2 使用pg_dumpall进行全库备份
pg_dumpall用于备份整个PostgreSQL数据库集群，包括所有数据库、用户、表空间等信息。
语法：
pg_dumpall [连接选项] [选项] > 备份文件名
连接选项与pg_dump类似。
常用选项：
-g, --globals-only：只备份全局对象（用户、表空间等）。-d, --database-only：只备份数据库内容，不备份全局对象。
示例：备份整个数据库集群到cluster_backup.sql文件。
pg_dumpall -U postgres > cluster_backup.sql
4.2 恢复数据库
4.2.1 恢复pg_dump备份
恢复以纯SQL格式备份的数据库，可以使用psql工具来执行备份文件中的SQL语句。
语法：
psql [连接选项] 数据库名 < 备份文件名
示例：恢复mydb_backup.sql到mydb数据库。
psql -U myuser mydb < mydb_backup.sql
对于自定义格式的备份，需要使用pg_restore工具。
语法：
pg_restore [连接选项] [选项] -d 目标数据库名 备份文件名
常用选项：
-v, --verbose：详细模式。-l, --list：列出备份文件中的内容。
示例：恢复mydb_backup.custom到mydb数据库。
pg_restore -U myuser -d mydb mydb_backup.custom
4.2.2 恢复pg_dumpall备份
恢复pg_dumpall备份同样可以使用psql。
语法：
psql [连接选项] -f 备份文件名
示例：恢复cluster_backup.sql。
psql -U postgres -f cluster_backup.sql
五、服务器管理命令
5.1 启动与停止PostgreSQL服务
在不同的操作系统上，启动和停止PostgreSQL服务的方式有所不同。
5.1.1 在Linux系统（以systemd为例）
- 启动服务：
sudo systemctl start postgresql
- 停止服务：
sudo systemctl stop postgresql
- 重启服务：
sudo systemctl restart postgresql
- 查看服务状态：
sudo systemctl status postgresql
5.1.2 在Windows系统
可以通过服务管理器来启动、停止和重启PostgreSQL服务。在“控制面板” -> “管理工具” -> “服务”中，找到PostgreSQL服务，右键点击选择相应操作。也可以使用命令行方式，在命令提示符（需以管理员身份运行）中执行：
- 启动服务：
net start PostgreSQL
- 停止服务：
net stop PostgreSQL
5.2 查看服务器配置参数
在psql中，可以使用SHOW命令查看当前服务器的配置参数。
语法：
SHOW 参数名;
示例：查看shared_buffers参数的值，该参数用于设置PostgreSQL服务器共享内存缓冲区的大小。
SHOW shared_buffers;
输出示例：
shared_buffers
----------------
2GB
(1 row)
若要查看所有配置参数，可以使用：
SHOW ALL;
5.3 修改服务器配置参数
PostgreSQL的主要配置文件是postgresql.conf，位于数据目录中。可以直接编辑该文件来修改配置参数，修改后需要重启PostgreSQL服务使配置生效。
例如，要增加shared_buffers的值，在postgresql.conf中找到shared_buffers参数行，修改其值：
shared_buffers = '4GB' # 修改为4GB
然后重启PostgreSQL服务。
也可以在运行时使用SET命令临时修改某些配置参数，但这种修改只在当前会话中有效。
语法：
SET 参数名 = 值;
示例：临时将statement_timeout设置为5秒（5000毫秒）。
SET statement_timeout = 5000;
六、SQL命令在管理中的应用
6.1 查看数据库对象信息
6.1.1 查看表结构
在psql中，可以使用\d命令查看表的结构，包括列名、数据类型、约束等信息。
语法：
\d 表名
示例：查看名为employees的表结构。
\d employees
示例输出：
Table "public.employees"
Column | Type | Collation | Nullable | Default
------------+-----------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(50) | | |
age | integer | | |
department | character varying(50) | | |
Indexes:
"employees_pkey" PRIMARY KEY, btree (id)
6.1.2 查看索引信息
使用\d命令并指定索引名可以查看索引的详细信息，也可以通过pg_catalog.pg_indexes系统视图来查询索引信息。
使用\d查看索引：
\d 索引名
通过系统视图查询：
SELECT * FROM pg_catalog.pg_indexes WHERE tablename = '表名';
示例：查询employees表上的所有索引。
SELECT * FROM pg_catalog.pg_indexes WHERE tablename = 'employees';
6.2 执行管理任务
6.2.1 授权与撤销权限
使用GRANT和REVOKE SQL语句来授予和撤销用户对数据库对象的权限。
授予权限：
GRANT 权限列表 ON 对象类型 对象名 TO 用户;
示例：授予newuser对employees表的SELECT权限。
GRANT SELECT ON employees TO newuser;
撤销权限：
REVOKE 权限列表 ON 对象类型 对象名 FROM 用户;
示例：撤销newuser对employees表的SELECT权限。
REVOKE SELECT ON employees FROM newuser;
6.2.2 修改表结构
使用ALTER TABLE SQL语句可以修改表的结构，如添加列、修改列数据类型、删除列等。
添加列：
ALTER TABLE 表名 ADD COLUMN 列名 数据类型;
示例：在employees表中添加一个email列。
ALTER TABLE employees ADD COLUMN email character varying(100);
修改列数据类型：
ALTER TABLE 表名 ALTER COLUMN 列名 TYPE 新数据类型;
示例：将employees表中age列的数据类型从integer修改为smallint。
ALTER TABLE employees ALTER COLUMN age TYPE smallint;
删除列：
ALTER TABLE 表名 DROP COLUMN 列名;
示例：删除employees表中的email列。
ALTER TABLE employees DROP COLUMN email;
通过上述对PostgreSQL常用管理命令的汇总，涵盖了数据库、用户、表空间、备份恢复、服务器管理以及SQL命令在管理中的应用等多个方面，希望能帮助读者更好地管理和维护PostgreSQL数据库系统。在实际应用中，应根据具体需求和场景，合理选择和使用这些命令，以确保数据库的高效运行和数据的安全可靠。