一、基本使用1. 登录数据库使用psql命令行工具连接到数据库su - postgres psql2. 数据库操作(1) 列出库\l -- 或 SELECT datname FROM pg_database;(2) 创建库CREATE DATABASE 数据库名;(3) 删除库DROP DATABASE 数据库名;(4) 切换库\c 数据库名 -- 或 \connect 数据库名(5) 查看库大小SELECT pg_size_pretty(pg_database_size(数据库名));3. 数据表操作(1) 列出表\dt -- 或 SELECT table_name FROM information_schema.tables WHERE table_schema public;(2) 创建表CREATE TABLE 表名 ( 列名1 数据类型 [约束], 列名2 数据类型 [约束], ... );(3) 复制表CREATE TABLE 新表名 AS SELECT * FROM 原表名; -- 复制结构和数据 CREATE TABLE 新表名 (LIKE 原表名 INCLUDING ALL); -- 复制结构包括约束、索引等(4) 删除表DROP TABLE 表名; -- 删除表结构和数据 DROP TABLE 表名 CASCADE; -- 级联删除依赖对象如视图(5) 查看表结构\d 表名 -- 或 SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name 表名;4. 模式 (Schema) 操作命令(1) 创建模式CREATE SCHEMA 模式名;(2) 默认模式PostgreSQL 默认使用public模式(3) 删除模式DROP SCHEMA 模式名; DROP SCHEMA 模式名 CASCADE; -- 级联删除模式内的所有对象(4) 查看所有模式\dn -- 或 SELECT schema_name FROM information_schema.schemata;(5) 在指定模式中创建表CREATE TABLE 模式名.表名 (...);(6) 切换当前模式SET search_path TO 模式名;(7) 查看当前所在 schemaSELECT current_schema();(8) 查看搜索路径搜索路径决定了对象名的解析顺序。SHOW search_path;(9) PostgreSQL 的模式隔离模式提供了命名空间隔离。不同模式下的对象如表可以同名。访问非当前模式或搜索路径中模式的对象时需要使用模式名.对象名的格式5. 数据操作(1) 添加数据INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...); INSERT INTO 表名 SELECT ... FROM ...; -- 从查询结果插入(2) 查询数据SELECT 列1, 列2, ... FROM 表名 WHERE 条件 ORDER BY 排序列 LIMIT 数量;(3) 修改数据UPDATE 表名 SET 列1 值1, 列2 值2, ... WHERE 条件;(4) 删除数据DELETE FROM 表名 WHERE 条件; TRUNCATE TABLE 表名; -- 快速删除所有数据并重置序列如果有6. 备份与恢复(1) SQL 转储 (备份单个数据库)pg_dump -U 用户名 -d 数据库名 -f 备份文件名.sql(2) 从转储中恢复psql -U 用户名 -d 目标数据库名 -f 备份文件名.sql(3) 使用 pg_dumpall (备份所有数据库)pg_dumpall -U 用户名 -f 备份文件名.sql恢复pg_dumpall生成的备份通常需要超级用户权限7. 远程连接(1) 修改 PostgreSQL 监听地址编辑配置文件postgresql.conf(通常位于/etc/postgresql/版本/main/或/var/lib/pgsql/版本/data/)找到listen_addresses参数listen_addresses * # 监听所有 IP 地址或指定具体 IP(2) 配置访问权限编辑配置文件pg_hba.conf(与postgresql.conf同目录)添加允许远程连接的规则例如# TYPE DATABASE USER ADDRESS METHOD host all all 0.0.0.0/0 md5 # 允许所有用户从任何 IP 使用密码(md5)连接所有数据库(3) 重启服务使配置生效。sudo systemctl restart postgresql(4) 验证远程连接从远程机器尝试连接psql -U 用户名 -d 数据库名 -h PostgreSQL服务器IP -p 端口8. 重置密码(1) 备份配置文件在进行任何修改前备份pg_hba.conf(2) 修改配置文件在pg_hba.conf中临时添加一行允许本地无需密码的连接用于重置# TYPE DATABASE USER ADDRESS METHOD local all all trust # 仅用于临时重置密码完成后务必删除或改回(3) 重启服务应用pg_hba.conf的更改sudo systemctl restart postgresql(4) 修改密码使用psql本地免密登录后修改密码psql -U postgres ALTER USER 用户名 WITH PASSWORD 新密码; \q完成后立即将pg_hba.conf中那行trust改回原来的安全设置如md5并再次重启 PostgreSQL 服务