PostgreSQL基础操作

伤不起bb

于 2025-05-21 12:19:10 发布

阅读量1k

点赞数 29

文章标签：数据库 postgresql 运维 Linux

本文链接：https://blog.csdn.net/2301_80839375/article/details/148106518

版权

（8）查看搜索路径（Search Path）

（5）恢复 pg_hba.conf 配置文件

一、基本使用

1. 登录数据库

登录账户   su - postgres
启动服务   pg_ctl -D /usr/local/pgsql/data -l logfile start
登录数据库   psql

2. 数据库操作

（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）查看所有模式

\dn

SQL查询，列出当前库中所有模式
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.conf
	host  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 
重启服务