PostgreSQL基础操作
目录
一、基本使用
1. 登录数据库
2. 数据库操作
（1）列出库
（2）创建库
（3）删除库
（4）切换库
（5）查看库大小
3. 数据表操作
（1）列出表
（2）创建表
（3）复制表
（4）删除表
（5）查看表结构
4. 模式操作命令
（1）创建模式
（2）默认模式
（3）删除模式
（4）查看所有模式
（5）在指定模式中创建表
（6）切换当前模式
（7）查看当前所在schema
（8）查看搜索路径（Search Path）
（9）PostgreSQL 的模式隔离性
5. 数据操作
（1）添加数据
（2）查询数据
（3）修改数据
（4）删除数据
6. 备份与恢复
（1）SQL 转储
（2）从转储中恢复
（3）使用pg_dumpall
7. 远程连接
（1）修改PostgreSQL 监听地址
（2）配置访问权限
（3）重启服务
（4）验证远程连接
8. 重置密码
（1）备份配置文件
（2）修改配置文件
（3）重启服务
（4）修改密码
（5）恢复 pg_hba.conf 配置文件
一、基本使用
1. 登录数据库
登录账户 su - postgres
启动服务 pg_ctl -D /usr/local/pgsql/data -l logfile start
登录数据库 psql2. 数据库操作
（1）列出库
方法一：postgres=# \l方法二：
“+”扩展输出，显示更多字段或详细信息
postgres=# \l+方法三：
使用SQl命令
postgres=# select datname from pg_database;在 PostgreSQL 的交互式终端 psql 中，“\” 开头的命令称为元命令（类似 MySQL 的 SHOW 语句），用于快速管理数据库。
常用元命令有： \l 列出所有数据库。 \c [数据库名] 或 \connect [数据库名] \dn 列出所有模式（Schema）。 \db 列出所有表空间。 ? 显示 psql 命令的说明 (元命令查询帮助) \q 退出 psql \dt 列出当前数据库的所有表 \d [TABLE] 查看表结构 \du 列出所有用户
（2）创建库
postgres=# create database mydb;create database 库名;（3）删除库
postgres=# drop database mydb;dorp database 库名;（4）切换库
postgres=# \c mydb
\c 库名;（5）查看库大小
select pg_database_size('mydb');
3. 数据表操作
（1）列出表
常用方法：
\dt :列出表（显示search_path 中模式里的表，默认public）
\d :列出表、视图和序列
\d+ +：这是一个量词，它表示前面的元素（这里是 \d）必须出现一次或多次
\dt my_schema.* 列出指定模式下的所有表
\dt *.* 查看当前数据库的所有表（包括系统表）select * from pg_tables where schemaname = 'public'; #SQL查询，列出当前数据库中public 模式下的所有表及其详细信息（2）创建表
create table 表名(内容)；例：create table test(id int,name char(10),age int);（3）复制表
create table 新表 as table 老表;
例：create table test2 as table test;
将test复制为test的新表，包括表结构和数据（4）删除表
drop table test2;
（5）查看表结构
\d 表名；
\d test;4. 模式操作命令
模式是一个逻辑容器，用于组织和管理数据对象。类似文件系统的文件夹，帮助你在同一个数据库中分类存储不同的对象，避免命名冲突，并实现权限隔离。
（1）创建模式
\c 库create schema 模式名
create schema hr;（2）默认模式
每个数据库都有一个默认模式 pubic
不指定模式时，默认会放在public 模式中。
show search_path;search_path 用于控制对象解析顺序，避免每次查询都要写模式名
$user,public 表示优先查找当前用户同名模式，再找public 模式（3）删除模式
删除空模式
drop schema 模式；强制删除模式及其所有对象
drop schema 模式 cascade;（4）查看所有模式
\dnSQL查询，列出当前库中所有模式
select schema_name from information_schema.schemata;#information_schema.schemata系统表中查询所有数据库的名称。
schema_name：要查询的列名，这里表示数据库的名称。
FROM：SQL语句中的关键字，用于指定从哪个表中获取数据。
information_schema.schemata：information_schema是MySQL中的一个系统数据库，它存储了关于数据库元数据的信息，schemata是information_schema中的一个系统表，该表存储了所有数据库的相关信息，包括数据库名称、默认字符集等。 （5）在指定模式中创建表
未指定模式时，创建的对象会按search_path 顺序创建第一个可用的模式中例：在postgres 库中的hr模式下创建一个名为test的库postgres=# create table hr.test (id serial primary key,name text);（6）切换当前模式
SET search_path TO schema_name; -- 临时生效
-- 永久修改需在 postgresql.conf 或用户会话中设置
切换多个schema（按优先级顺序）
set search_path to hr,public; #","隔开（7）查看当前所在schema
SELECT current_schema(); （8）查看搜索路径（Search Path）
SHOW search_path; -- 默认值：'"$user", public' （9）PostgreSQL 的模式隔离性
-
不同模式中的同名表互不影响，需通过
<schema>.<table>访问。跨模式查询 需显式指定模式名（进库后查询，因为默认模式路径是public） select * from 模式名.表名;不需显式指定模式名 set search_path to schemal; #设置search_path 切换默认模式 select * from users; --默认访问schemal.users
5. 数据操作
（1）添加数据
INSERT INTO table_name (col1, col2) VALUES (val1, val2);
-- 批量插入
INSERT INTO users (name) VALUES ('Alice'), ('Bob'); （2）查询数据
SELECT * FROM table_name WHERE condition;
-- 示例：条件查询
SELECT * FROM users WHERE age > 18; （3）修改数据
UPDATE table_name SET col1 = val1 WHERE condition;
-- 示例：更新特定行
UPDATE users SET status = 'active' WHERE id = 1; （4）删除数据
DELETE FROM table_name WHERE condition;
-- 清空表（谨慎使用）
TRUNCATE TABLE table_name; 6. 备份与恢复
pg_dump 导出 psql 导入
pg_dump dbname >dumpfile
psql dbname <dumpfile反复导入(遇到SQL错误后会继续执行)：psql --set ON_ERROR_STOP=on dbname <infile
（1）SQL 转储
pg_dump -U username -d dbname -f backup.sql
-- 示例：导出为自定义格式（支持并行恢复）
pg_dump -Fc -U postgres mydb > mydb.dump （2）从转储中恢复
psql -U username -d dbname -f backup.sql
-- 自定义格式恢复
pg_restore -U postgres -d mydb mydb.dump （3）使用pg_dumpall
所有库导出：pg_dumpall >mybakall
psql -f dumpfile postgres --恢复7. 远程连接
（1）修改PostgreSQL 监听地址
编辑 postgresql.conf：listen_addresses = '*' -- 允许所有 IP （2）配置访问权限
编辑 pg_hba.conf：
# 允许所有 IP 通过密码连接
host all all 0.0.0.0/0 trust #其他 trust为信任，表示不需要密码或任何形式认证
host all all 127.0.0.1/32 trust #本地的
#可以设置有密码连接 把trust换成password/md5（3）重启服务
重启服务 pg_ctl -D /usr/local/pgsql/data -l logfile restart（4）验证远程连接
useradd postgres (因为root不让连)
安装客户端 dnf -y install postgresql 远程连接 psql -h IP地址###如果是md5或password 需要有密码才行
先在数据库设置密码
为用户加密码 alter user postgres with password 'pwd123'; 8. 重置密码
（1）备份配置文件
cp /usr/local/pgsql/data/postgresql.conf /usr/local/pgsql/data/postgresql.confbak（2）修改配置文件
vim /usr/local/pgsql/data/postgresql.confhost all all 127.0.0.1/32 md5 #本地的（3）重启服务
pg_ctl -D /usr/local/pgsql/data -l logfile restart（4）修改密码
alter user postgres with password '新密码'; （5）恢复 pg_hba.conf 配置文件
用mv 把之前备份的覆盖掉pg_hba.conf
重启服务