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    恢复前通常需要先创建一个空的数据库

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

# 创建新表：
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