PostgreSQL 常用命令与操作指南

概述

本文整理了 PostgreSQL 数据库连接、操作、备份恢复及用户管理的常用命令，涵盖数据表管理、事务处理及导入导出方法。适用于日常数据库维护、开发调试及自动化脚本编写场景。

数据库连接与基础操作

连接数据库

• 基础连接

  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
  

• 删除表

  DROP TABLE table_name;
  

表结构修改

• 添加列

  ALTER TABLE table_name ADD COLUMN column_name datatype;
  

• 修改列类型

  ALTER TABLE table_name ALTER COLUMN column_name TYPE new_datatype;
  

• 重命名表或列

  ALTER TABLE old_table RENAME TO new_table;
  ALTER TABLE table_name RENAME COLUMN old_column TO new_column;
  

• 删除列

  ALTER TABLE table_name DROP COLUMN column_name;
  

数据操作与事务

数据增删改查

• 插入数据

  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;
  

事务控制

• 开启事务

  BEGIN;
  

• 提交事务

  COMMIT;
  

• 回滚事务

  ROLLBACK;
  

约束与索引管理

添加约束

• 主键约束

  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;
  

数据备份与恢复

导出数据库

• 生成 SQL 备份文件

  pg_dump dbname > dbname_backup.sql
  

恢复数据库

• 导入 SQL 文件

  psql -U username -d dbname -f /path/to/backup.sql
  

• 使用 pg_restore 恢复自定义格式备份

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

pg_restore 常用选项

注意事项

• pg_restore 仅适用于自定义格式（如 pg_dump -Fc 生成的 .dump 文件），普通 SQL 文件需用 psql 导入。
• 恢复前需确保目标数据库已存在，或通过 psql -c "CREATE DATABASE dbname;" 创建。

用户权限管理

用户操作

• 创建用户

  CREATE USER username WITH PASSWORD 'password';
  

• 授予权限

  GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
  

• 删除用户

  DROP USER username;
  

其他实用命令

• 查看连接信息

  \conninfo
  

• 查看 PostgreSQL 版本

  SELECT version();
  

• 查看数据库配置

  SHOW ALL;
  

• 查询表全部数据

  SELECT * FROM table_name;
  

导入 SQL 文件的多种方式

通过 psql 命令行

psql -U postgres -d mydb -f /path/to/backup.sql

通过 pgAdmin 图形化工具

1. 连接数据库后，右键目标数据库 → Query Tool。
2. 使用 打开文件 图标加载 SQL 文件。
3. 点击 执行（F5）运行脚本。

通过 pg_restore 恢复备份

• 恢复到新数据库

  psql -U postgres -c "CREATE DATABASE newdb;"
  pg_restore -U postgres -d newdb /path/to/backup.dump
  

• 覆盖现有数据

  pg_restore -U postgres -d mydb --clean /path/to/backup.dump
  

适用边界与注意事项

1. 命令适用性

   • pg_dump 生成的 .sql 文件需用 psql 导入，pg_restore 仅支持自定义格式（.dump）。
   • pg_restore 的 --clean 选项可能删除目标数据库中已有数据，需谨慎使用。

2. 环境依赖

   • 导入操作需确保目标数据库已存在，或通过 CREATE DATABASE 预先创建。
   • 容器化部署（如 Docker）中使用 PostgreSQL 时，需注意卷挂载与数据持久化配置（参见 container-database 话题）。