PostgreSQL 常用命令

PostgreSQL 常用命令

使用

• 登录交互客户端

sudo -u postgres psql

• 常用命令

\password           设置密码
\q                  退出
\h                  查看SQL命令的解释，比如\h select
\?                  查看psql命令列表
\l                  列出所有数据库
\c [database_name]  连接其他数据库
\d                  列出当前数据库的所有表格
\d [table_name]     列出某一张表格的结构
\d+ [table_name]     列出某一张表格的结构，会显示更详细的信息，包括表的大小和描述
\dt                 列出当前数据库中的所有表
\du                 列出所有用户
\e                  打开文本编辑器
\conninfo           列出当前数据库和连接的信息
\i /path/to/your/script.sql    恢复前通常需要先创建一个空的数据库

连接到 psql 命令行工具

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

• -U username: 指定连接的用户名。

• -d dbname: 指定要连接的数据库名称。

• -h host: 指定数据库服务器的主机名或 IP 地址（如果数据库在本地，可以省略或使用 localhost）。

• -p port: 指定数据库服务器的端口号（默认为 5432）。

• 如果数据库在本地，且您想以当前系统用户身份连接到默认数据库，可以直接输入 psql。

• 只连接到特定数据库并使用默认用户：

psql dbname

创建用户和库

root@t2:~# sudo -i -u postgres
postgres@t2:~$ psql
psql (14.12 (Ubuntu 14.12-0ubuntu0.22.04.1))
Type "help" for help.

postgres=#\q

# \q 退出

# 查看用户
SELECT usename from pg_user;

# 修改 postgres 密码，类似于 MySQL 的 root
ALTER USER postgres WITH PASSWORD '123456';

# 创建用户和数据库
CREATE USER <dbuser> WITH PASSWORD '<******>';
CREATE DATABASE <dbname> OWNER <dbuser>;

# 授予用户权限
GRANT ALL PRIVILEGES ON DATABASE <dbname> TO <dbuser>;
GRANT SELECT, INSERT ON employees TO new_user;

# 撤销用户权限
REVOKE DELETE ON employees FROM new_user;

# 删除用户
DROP USER new_user;

# Linux 创建用户
sudo adduser dbuser
sudo passwd dbuser

su - dbuser
psql -d <dbname>

更改密码

postgres=# \password dbuser
postgres=# \q

删除用户

postgres=# drop user dbuser;

数据库操作

• 列出数据库名

\l
或
\list
或
SELECT datname FROM pg_database;

• 创建数据库

CREATE DATABASE database_name;

• 删除数据库

DROP DATABASE database_name;

• 切换数据库

\c database_name
或
\connect database_name

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

• 查看表

postgres=# \d

• 查看表结构

postgres=# \d user_tab1

• table 操作

# 创建新表：
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE DEFAULT CURRENT_DATE,
    salary DECIMAL(10, 2)
);

# 添加新列
ALTER TABLE employees ADD COLUMN phone_number VARCHAR(20);

# 删除列
ALTER TABLE employees DROP COLUMN phone_number;

# 修改列的数据类型
ALTER TABLE employees ALTER COLUMN email TYPE VARCHAR(150);

# 重命名表
ALTER TABLE employees RENAME TO staff;

# 删除表
DROP TABLE employees;

查看占用大小

# 查看数据库占用空间大小
postgres=# select datname, pg_size_pretty (pg_database_size(datname)) AS size from pg_database;
  datname  |   size
-----------+----------
 postgres  | 8561 kB
 template1 | 8561 kB
 template0 | 8409 kB
 openwebui | 10233 kB
(4 rows)

# 查看每个表所占用磁盘空间大小
postgres=# SELECT table_schema || '.' || table_name AS table_full_name, pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')AS size
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;

备份与还原

备份

# 备份单个数据库为 SQL 脚本文件
pg_dump -U username -d dbname -h host -p port > backup.sql

pg_dump -h localhost -p 5432 -U username -F p -f your_database_backup.sql your_database_name

# 备份单个数据库为自定义格式pg_dump -h localhost -p 5432 -U username -F c -f your_database_backup.dump your_database_name

# 备份所有数据库
pg_dumpall -h localhost -p 5432 -U username > your_all_databases_backup.sql

• 参数说明

  • -h: 数据库主机名。
  • -p: 数据库端口。
  • -U: 数据库用户。
  • -F p: 指定输出格式为纯文本 SQL 脚本（plain text）。
  • -f: 指定输出文件路径。
  • -F c: 指定输出格式为自定义归档格式（custom）。

• 其他备份方式

  • 停服，备份 /var/lib/postgresql/X.Y/main

还原

# 从 SQL 脚本文件还原
psql -U username -d dbname -h host -p port < backup.sql

psql -h localhost -p 5432 -U username -d your_new_database_name -f your_database_backup.sql

# 从自定义格式归档文件还原
pg_restore -h localhost -p 5432 -U username -d your_new_database_name your_database_backup.dump

• -d: 指定要还原到的数据库。
• --clean: 在还原前清除（删除并重新创建）数据库中的所有对象。谨慎使用！
• --create: 在还原前尝试创建数据库。