当前位置：首页 > news >正文

postgreSQL日常维护

news 来源：原创 2025/7/7 2:15:22

登录数据库

数据库操作列出列出库

创建库

删除库

切换库

查看库的大小

数据表操作

列出表

创建表

复制表

删除表

查看表的结构

模式操作命令

创建模式

默认模式

删除模式

查看所有模式

在指定模式中创建表

切换当前模式

查看当前所在schema

查看搜索路径（Search Path）

PostgreSQL的模式隔离性

数据库操作

备份与恢复

SQL转储

从转储中恢复

使用PG_dumpall

远程连接

配置访问权限

验证远程连接

重置密码

登录数据库

Pgsql登录时,必须使用postgres用户,
登录后的命令提示符为"postgres=#"
postgres表示你当前所在的库

[root@bogon^]# su-postgres
[postgres@bogon ^]$ /usr/local/pgsql/bin/psql
psql (16.3)
Type "help" for help.
postgres=#

数据库操作

方法一

postgres=#\1

在PostgreSQL的交互式终端psql中,"\"开头的命令称为元命令(类似
MySQL的SHOW语句),用于快速管理数据库

方法二

postgres=# \l+

的输出比\l多了Size,Tablespace和Description列
+:扩展输出,显示更多字段或详细信息

方法三

使用SQL语句

postgres=# SELECT datname FROM pg_database;

pg_database是系统表:它存储了PostgreSQL实例中所有数据库的元信息(如
数据库名称、所有者、编码等)。属于系统目录(SystemCatalog):类似MySQL
的information_schema,但PostgreSQL的系统目录更底层且直接存储在
pg_catalog模式中。pg_database是系统目录表,所以无论当前连接到哪个数据库,该表始终可见
系统表默认属于pg_catalog模式,而pg_catalog始终位于搜索路径
(search_path)的首位。因此,查询时无需显式指定模式(如pg_catalog.pg_database)。

创建库

postgres=# create database mydb;
CREATE DATABASE

删除库

postgres=# drop database mydb;
DROP DATABASE

切换库

postgres=# \c_mydb
You are now connected to database "mydb" as user "postgres".
mydb=#

查看库的大小

函数以字节为单位返回数据库的大小

postgres=# SELECT pg_database_size('mydb');
pg_database_size
----------------
7594499
(1行记录)
pg_size_pretty()
postgres=# SELECT pg_size_pretty (pg_database_size('mydb'));
pg_size_pretty
---------------
7417 kB
(1行记录)

数据表操作

列出表

常用方法

mydb=#\dt;

列出表(显示search_path中模式里的表,默认public)3

mydb=# \d

列出表，视图和序列

mydb=# \d+

mydb=#\dt my_schema.*

列出指定模式下的表（例如 my_schema）

mydb=#\dt *.*

查看当前数据库的所有表(包括系统表)

mydb=#SELECT * FROM pg_tables WHERE schemaname = 'public';

使用SLQL方式列出当前数据库中public模式下的所有表及其详细信息pg_tables是视图:属于pg_catalog模式,但它是基于pg_class和
pg namespace的逻辑视图,并非物理表。无需切换数据库,直接查询
pg_catalog.pg_tables即可获取当前数据库的表信息

创建表

PostgreSQL支持标准的SQL类型int、smallint、real、douuble precision.
char(N)、varchar(N)、date、time、time、timestamp和interval,还j支持其他的通
用功能的类型和丰富的几何类型。PostgreSQL中可以定制任意数量的用户定义
数据类型。因而类型名并不是语法关键字,除了SQL标准要求支持的特例外。

postgres=# create table test(id int, name char(10)age int);
CREATE TABLE

复制表

要将已有的table_name表复制为新表 new_table,包括表结构和数据,请
使用以下语句

CREATE TABLE new table AS TABLE table name;

例如

postgres=# CREATE TABLE test2 AS TABLE test;
SELECT 0
postgres=# \dt

删除表

postgres=# drop table_test2;

查看表的结构


postgres=\d test:

模式操作命令

在PostgreSQL中,模式(Schema)
是一个逻辑容器,用于组织和管理数
据库对象(如表、视图、函数、索引等)。它类似于文件系统中的文件牛夹,帮助
你在同一个数据库中分类存储不同的对象,避免命名冲突,并实现权限隔离

创建模式

在当前库postgres中创建名为hr的模式

postgres=# CREATE SCHEMA hr:
CREATE SCHEMA

默认模式

PostgreSQL每个数据库都有一个默认模式public。
如果创建对象(表、视图等)时不指定模式,默认会放在 publiic模式中。

通过search_path参数可以设置模式的搜索优先级(类似PAT环境变量):


postgres=# SHOW search_path;

search_path用于控制对象解析顺序,避免每次查询都要写模式名
$user,public表示优先查找当前用户同名模式,再找public模式。

删除模式

删除空模式

postgres=# DROP SCHEMA hr;
DROP SCHEMA

强制删除模式以及所有对象

postgres=# DROP SCHEMA hr CASCADE;
DROP SCHEMA

查看所有模式

元命令列出当前库中所有模式


postgres=# \dn

SQL,查询，列出当前库中所有模式

postgres=# SELECT schema name FROM informmation schema.schemata:

在指定模式中创建表

在postgres库中的hr模式下创建一个名为empllovees的库

postgres=#CREATE TABLE hr.employees (id SERIAL PRIIMARY KEY, name TEXT)；

切换当前模式

切换模式也就是调整search_path的搜索范围切换到单个schema

SET search_path TO new_schema;

切换到多个schema（按优先级顺序）

SET search_path TO hr, public;

表述优先搜索hr模式，其次public

查看当前所在schema

postgres=# SELECT_current_schema();

查看搜索路径（Search Path）

postgres=# SHOW search_path;

PostgreSQL的模式隔离性

模式隔离的主要特性

命名空间隔离
模式的关键作用是提供独立的命名空间。这意味着在同一个数据库中，不同模式可以存在同名的对象，而且彼此不会产生冲突

CREATE SCHEMA schema_a;
CREATE SCHEMA schema_b;CREATE TABLE schema_a.users (id INT, name TEXT);
CREATE TABLE schema_b.users (id INT, email TEXT); -- 不会冲突，因为分属不同模式

权限控制隔离
可以针对模式单独设置权限，以此限制用户对特定模式及其包含对象的访问。

-- 只允许用户user1访问schema_a
GRANT USAGE ON SCHEMA schema_a TO user1;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_a TO user1;

逻辑结构分离
借助模式，能够按照业务功能、团队或者应用模块对数据库对象进行分组

mydatabase/
├── public        -- 默认模式
├── finance       -- 财务相关表和函数
├── marketing     -- 营销数据
└── audit         -- 审计日志

数据库操作

添加数据

在postgres库，新建test表

postgres=# create table test(id int, name char(10),ageint);
CREATE TABLE
postgres=#insert into test values(1, zhangsan',18);
INSERT 0 1

查询数据

postgres=# select * from test;

修改数据

postgres=# update test set age=20 where id=1;
UPDATE 1
postgres=# select * from test;

删除数据

postgres=# delete from test where id=l;
DELETE 1
postgres=# select * from test;

备份与恢复

PostgreSQL数据库应当被定期地备份。虽然过程相当简单,但清晰地理解其
底层技术和假设是非常重要的。
有三种不同的基本方法来备份PostgreSQL数据:
SQL转储
文件系统级备份
连续归档
每一种都有其优缺点,我们主要以SQL转储为主。

SQL转储

SQL 转储是将数据库中的数据和结构导出为文本文件（通常包含 SQL 语句）的过程，主要用于数据备份、迁移、共享或灾难恢复。转储文件可以包含表结构、数据、存储过程、视图等数据库对象的定义，通过执行这些 SQL 语句，可在其他数据库环境中重建相同的数据库结构和数据

SQL 转储的核心作用

数据备份
- 定期转储数据库，防止数据丢失（如硬件故障、误操作等）。
- 示例：每天凌晨自动转储生产数据库，保存到异地服务器。
数据库迁移
- 将数据从一个数据库服务器（如 MySQL）迁移到另一个（如 PostgreSQL），需先转储为通用 SQL 格式，再调整语法差异。
环境同步
- 在开发、测试环境中复制生产数据，用于调试或测试新功能。
数据共享
- 向第三方提供数据库结构和部分数据（如脱敏后的测试数据）。

从转储中恢复

其中dumpfile就是pg_dump命令的输出文件。这条命令不会创建数据库
dbname,你必须在执行psql前自己从template0创建(例如,用命令createdb
-T template0 dbname)。psql支持类似pg_dump的选项用以指定要连接的数
据库服务器和要使用的用户名。参阅psql的手册获取更多信息。非文本文件转
储可以使用pg_restore工具来恢复。
在开始恢复之前,转储库中对象的拥有者以及在其上被授予了权限的用户必
须已经存在。如果它们不存在,那么恢复过程将无法将对象创建成具有原来的所
属关系以及权限(有时候这就是你所需要的,但通常不是)。
默认情况下,psql脚本在遇到一个SQL错误后会继续执行。你也许希望在遇
到一个SQL错误后让psql退出,那么可以设置ON_ERROR_STOP变量来运行psql,
这将使psql在遇到SQL错误后退出并返回状态3:不管怎样,你将只能得到一个部分恢复的数据库。作为另一种中选择,你可以
指定让整个恢复作为一个单独的事务运行,这样恢复要么完全完成要么完全回滚。
这种模式可以通过向psql传递-l或-single-transaction命令行选项来指定。
在使用这种模式时,注意即使是很小的一个错误也会导致运行了数小时的恢复被
回滚。但是,这仍然比在一个部分恢复后手工清理复杂的数据库要更好

使用PG_dumpall

pg_dump每次只转储一个数据库,而且它不会转储关于角色或表空间(因为
它们是集簇范围的)的信息。为了支持方便地转储一个数据库集的全部内容
提供了pg_dumpall程序。pg_dumpall备份一个给定集簇中的每一个数据库,并
且也保留了集簇范围的数据,如角色和表空间定义。该命令的基本用法是

pg_dumpall > dumpfile

存储的结果可以使用psql恢复


psql -f dumpfile postgres

实际上,你可以指定恢复到任何已有数据库名,但是如果你正在将转储载
入到一个空集簇中则通常要用(postgres)。在恢复一个pg_dumpall转储时常
常需要具有数据库超级用户访问权限,因为它需要恢复角色和表空间信息。如果
你在使用表空间,请确保转储中的表空间路径适合于新的安装。
pg_dumpall工作时会发出命令重新创建角色、表空间和空数据库,扫接着
为每一个数据库pg_dump。这意味着每个数据库自身是一致的,但是不同数据库
的快照并不同步。集簇范围的数据可以使用pg_dumpall的-globals-only选项来单独转
储。如果在单个数据库上运行pg_dump命令,上述做法对于完全备份整个集等是
必需的。

远程连接

修改postgreSQL监听地址

默认PostgreSQL监听的地址是127.0.0.1,别的机器无法远程连接上,所以
需要调整,修改postgresql.conf文件。

修改pgsql配置文件

vim /usr/local/pgsql/data/postgresql.cnf
listen_addresses='*' what IP address(es) to listen on;

取消注释将=后面改为*

[root@localhost~]# systemctl restart postgresql

配置访问权限

默认是只能本地访问PostgreSQL的,我们需要在pg_hba.conf里面配置找到IPv4 local connections这一行,在这一行下面添加

[root@localhost ^]#vim /usr/local/pgsql/data/pg_hbaconf
host      all     all      0.0.0.0/0      trust

host:这指定了连接类型。host表示该规则适用于通过过TCP/IP进行的远程
连接。如果是本地连接,通常会使用local。
all:这定义了哪些数据库可以接受这个规则。all表示这个规则适用于所有
数据库。你也可以指定特定的数据库名,例如mydatabase。
>all:这定义了哪些用户可以接受这个规则。all表示这个规则适用于所有用
户。你也可以指定特定的用户名,例如myuser。
0.0.0.0/0:这定义了哪些客户端 IP地址或IP地址范围可以接受这个规
则。0.0.0.0/0 是一个特殊的CIDR表示法,它表示任何 IP 地地址(即没有IP地
址限制)。你也可以指定具体的IP地址,如192.168.1.100,或者IP地址范
围,如192.168.1.0/24。trust:这定义了认证方法。trust表示不需要密码或其他任何形式的认证,
客户端可以直接连接。这通常只在本地或受信任的网络环境中使用,因为它允许
任何人无需认证即可访问数据库。请注意,使用 trust认证正方法允许任何 IP 地
址连接到你的数据库,而不需要任何认证,这是非常不安全的。这通常只在开发
或测试环境中使用,并且应该始终确保数据库服务器不暴露在不受信任的网络中。
在生产环境中,你应该使用更安全的认证方法,如md5或2
password(对于较新
版本的PostgreSQL,建议使用 scram-sha-256)

如果不是设置的trust,而是选择了md5或password之类的,需要有密码才
行,配置PostgreSQL密码流程如下

postgres=# ALTER_USER postgres WITH PASSWORD
123456'
ALTER ROLE
[root@localhost ^]# systemctl start postgresql

验证远程连接

使用其它主机远程连接本机数据库，设置的是trust，无密码，可以直接登录

[postgres@localhost ^]$ psql -h 192.168.10.102
psql (16.3, server 15.12)
Type "help" for help.
postgres=#

如果设置的是md5或passwd之类的需要密码才行

[postgres@localhost ~]$ psql -h 192.168.10.102
Password for user postgres:
psql (16.3, server 15.12)
Type "help" for help.
postgres=#

重置密码

在PostgreSQL中如果密码忘记了怎么重置密码备份配置文件对pghba.conf文件,进行备份

[root@localhost~]# cp /var/lib/pgsql/data/postgresql.conf /var/lib/pgsql/data/postgresql.conf

修改配置文件修改配置文件以信任本地连接不需要密码。将配置文件中的scram-sha-256或
者md5修改为trust

[root@localhost~]# vim /var/lib/pgsql/data/postgresql.conf
# IPv4 local connections:
host   all   all   127.00.0.1/32   trust
host   all   all   0.0.0.0   md5
[root@localhost ^]# systemctl restart postgresql

修改密码登录数据库修改密码，密码自定义

postgres=# ALTER USER postgres WITH PASSWORD
new_password
ALTER ROLE

恢复pg_hba.conf配置文件

将postgresql.confbak文件的内容覆盖pg_hba.conf,重启!PostgreSQL数据
库服务器,重新登陆时,如果提示输入密码,则输入刚才修改的密码即可

模式隔离的主要特性

SQL 转储的核心作用

相关文章：