Post

PostgreSQL数据库操作实战指南：从连接到用户管理全解析

2026-04-30

PostgreSQL数据库操作指南

概述

本文整理了PostgreSQL数据库的连接、创建、表操作、数据管理、备份恢复及用户管理的常用命令和操作步骤，适用于日常数据库运维和开发场景。

数据库连接与基础操作

连接数据库

使用psql命令行工具连接数据库：

psql -U username -d dbname

若需指定主机和端口：

psql -U username -h host -p port -d dbname

数据库管理

创建数据库：
```
CREATE DATABASE dbname;
```
列出所有数据库：
```
\l
```
删除数据库：
```
DROP DATABASE dbname;
```
切换数据库：
```
\c dbname
```

表结构与数据操作

表操作

查看表列表：
```
\dt
```

创建表：

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype
);

查看表结构：
```
\d table_name
```

修改表结构：

添加列：

ALTER TABLE table_name ADD COLUMN column_name datatype;

修改列类型：

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_datatype;

数据操作

插入数据：

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

查询数据：

SELECT column1, column2 FROM table_name WHERE condition;

更新数据：

UPDATE table_name SET column1 = value1 WHERE condition;

删除数据：

DELETE FROM table_name WHERE condition;

约束与索引管理

添加约束

主键约束：

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

外键约束：

ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES other_table(other_column);

创建索引

CREATE INDEX index_name ON table_name (column_name);

删除索引：

DROP INDEX index_name;

数据备份与恢复

导出与导入

导出数据库：
```
pg_dump dbname > dbname_backup.sql
```
导入数据库：
```
psql dbname < dbname_backup.sql
```

使用`pg_restore`恢复备份

适用于自定义格式（.dump）的备份文件：

pg_restore -U username -d dbname /path/to/backup.dump

仅恢复数据：

pg_restore -U username -d dbname -a /path/to/backup.dump

并行恢复（提升速度）：

pg_restore -U username -d dbname -j 4 /path/to/backup.dump

用户权限管理

用户操作

创建用户：

CREATE USER username WITH PASSWORD 'password';

授予权限：

GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

删除用户：
```
DROP USER username;
```

事务控制

开始事务：
```
BEGIN;
```
提交事务：
```
COMMIT;
```
回滚事务：
```
ROLLBACK;
```

常见命令速查

命令	用途
`\conninfo`	查看当前连接信息
`SELECT * FROM table_name;`	查看表全部数据
`SELECT version();`	查看PostgreSQL版本
`SHOW ALL;`	查看数据库当前配置

注意事项

使用pg_restore时，需确保目标数据库已存在（可通过CREATE DATABASE预先创建）。
若备份文件为纯SQL格式（.sql），应使用psql而非pg_restore导入。
恢复数据前建议先备份目标数据库，避免数据覆盖风险。

（注：本文内容基于通用PostgreSQL操作整理，具体环境可能因版本或配置存在差异。）