PostgreSQL 常用命令

PostgreSQL 常用命令

使用

• 登录交互客户端

sudo -u postgres psql

• 常用命令

\du                 查看所有用户
\password           设置密码
\q                  退出
\h                  查看SQL命令的解释，比如\h select
\?                  查看psql命令列表
\l                  列出所有数据库
\c [database_name]  连接其他数据库
\d                  列出当前数据库的所有表格
\d [table_name]     列出某一张表格的结构
\du                 列出所有用户
\e                  打开文本编辑器
\conninfo           列出当前数据库和连接的信息

创建用户和库

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>;

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

su - dbuser
psql -d <dbname>

更改密码

postgres=# \password dbuser
postgres=# \q

删除用户

postgres=# drop user dbuser;

数据库操作

• 列出数据库名

\l
或
SELECT datname FROM pg_database;

• 切换数据库

\c 数据库名

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

• 查看表

postgres=# \d

• 查看表结构

postgres=# \d user_tab1

查看占用大小

# 查看数据库占用空间大小
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 -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 -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: 在还原前尝试创建数据库。