PostgreSQL 日常维护

[root@bogon ~]#  su - postgres
[postgres@bogon ~]$ pg_ctl -D /usr/local/pgsql/data/ -l logfile restart
[postgres@bogon ~]$ psql
psql (15.4)
Type "help" for help.

postgres=#

2、数据库操作

（1）列出库

常用的三种方法如下：

方法一：

postgres=# \l

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

常用原命令有：

\l	列出所有数据库。
\c	[数据库名] 或 \connect [数据库名]
\dn	列出所有模式(Schema)。
\db	列出所有表空间。
\?	显示 pgsql 命令的说明(元命令查询帮助)
\q	退出 psql
\dt	列出当前数据库的所有表
\d [TABLE]	查看表结构
\du	列出所有用户

方法二：

postgres=# \l+
    #\l+ 的输出比 \l 多了Size，Tablespace 和 Description 列

+ ：扩展输出，显示更多字段或详细信息

方法三：

使用SQL 命令

postgres=# select datname from pg_database;

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

（2）创建库

create database mydb;

select datname from pg_database;

（3）删除库

drop database mydb;

select datname from pg_database;

（4）切换库

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

（5）查看库大小

mydb=# select pg_database_size('mydb');    #函数以字节为单位返回数据库的大小
 pg_database_size 
------------------
          7508783
(1 row)

mydb=# select pg_size_pretty(pg_database_size('mydb'));    #pg_size_pretty() 函数将字节转换为更易于阅读值
 pg_size_pretty 
----------------
 7333 kB
(1 row)

3、数据表操作

（1）列出表

列出表的常用方法：

mydb=# \dt;
    #列出表（显示search_path 中模式里的表，默认public）

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 即可获取当前数据库的表信息。

（2）创建表

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

mydb=# create table test(id int,name char(20),age int);

（3）复制表

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

CREATE TABLE new_table AS TABLE table_name;

mydb=# create table test2 as table test;

mydb=# \dt

（4）删除表

mydb=# drop table test2;

（5）查看表结构

mydb=# \d test;
                   Table "public.test"
 Column |     Type      | Collation | Nullable | Default 
--------+---------------+-----------+----------+---------
 id     | integer       |           |          | 
 name   | character(20) |           |          | 
 age    | integer       |           |          |

4、模式操作命令

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

（1）创建模式

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

postgres=# create schema hr;

（2）默认模式

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

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

postgres=# show search_path 
postgres-# ;
   search_path   
-----------------
 "$user", public
(1 row)

search_path用于控制对象解析顺序，避免每次查询都要写模式名

$user，public 表示优先查找当前用户同名模式，再找public 模式。

（3）删除模式

删除空模式

postgres=# drop schema hr;

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

postgres=# drop schema hr cascade;

（4）查看所用模式

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

postgres=# \dn
      List of schemas
  Name  |       Owner       
--------+-------------------
 hr     | postgres
 public | pg_database_owner
(2 rows)

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

postgres=# select schema_name from information_schema.schemata;
    schema_name     
--------------------
 public
 hr
 information_schema
 pg_catalog
 pg_toast
(5 rows)

（5）在指定模式中创建表

未指定模式时，创建的对象(表，视图等)会按search_path 顺序创建到第一个可用的模式中

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

postgres=# create table hr.employees(id serial primary key,name text);

（6）切换当前模式

切换模式也就是调整 search path的搜索范围

切换到单个schema

set search_path to new_schema;

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

set search_path to hr,public;
    #表示优先搜索 hr 模式，其次 public

（7）查看当前所在schema

postgres=# select current_schema();
 current_schema 
----------------
 public
(1 row)

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

postgres=# show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

（9）PostgreSQL 的模式隔离性

PostgreSQL 的模式是数据库内的逻辑分组，不同模式可以存在同名表。这也是和 mysql 的不同之处。

跨模式查询需显式指定模式名(如 schema1.users)，或通过 search_path 设置默认模式。

无需切换数据库连接，所有操作在同一数据库内完成。

1.创建一个数据库

创建数据库mydb

postgres=# create database mydb;
    #创建数据库mydb

postgres=# \c mydb
    #切换到mydb
mydb=#

2.在数据库中创建两个模式

create schema sc1;
create schema sc2;
    #创建模式 sc1 和 sc2

3.在每个模式中创建同名表，并插入数据

#在sc1 中创建users 表
create table sc1.users(id int);
insert into sc1.users valuer(1);


#在sc2 中创建users 表
create table sc2.users(id int);
insert into sc2.users values(2);

4.跨模式查询

查询sc1.users 和sc2.users （需显式指定模式名）

select * from sc1.users;
select * from sc2.users;

设置search_path 切换默认模式（不需显式指定模式名）

set search_path to sc1;
select * from users;    #默认访问sc1.users

set search_path to sc2;
select * from users;    #默认访问sc2.users

set search_path to sc2,sc1;
    #也可以设置两个，会按顺序访问
select * from users;

5、数据操作

（1）添加数据

在postgres 库，新建表test

create table test(id int,name char(10),age int);

insert into test values(1,'zhangsan',18);

（2）查询数据

postgres=# select * from test;
 id |    name    | age 
----+------------+-----
  1 | zhangsan   |  18
(1 row)

（3）修改数据

postgres=# update test set age=20 where id=1;

postgres=# select * from test;
 id |    name    | age 
----+------------+-----
  1 | zhangsan   |  20
(1 row)

（4）删除数据

postgres=# delete from test where id=1;

postgres=# select * from test;
 id | name | age 
----+------+-----
(0 rows)

6、备份与恢复

PostgreSq 数据库应当被定期地备份。虽然过程相当简单，但清晰地理解其底层技术和假设是非常重要的。

有三种不同的基本方法来备份 PostgreSQL 数据：

SQL 转储
文件系统级备份
连续归档

每一种都有其优缺点，我们主要以 SQL 转储为主。

（1）SQL 转储

SQL 转储方法的思想是创建一个由 SQL 命令组成的文件，当把这个文件回馈给服务器时，服务器将利用其中的SQL命令重建与转储时状态一样的数据库。 PostgreSQL为此提供了工具pg_dump。这个工具的基本用法是：

pg_dump dbname > dumpfile

[postgres@bogon ~]$ pg_dump mydb > mybak2

正如你所见，pg_dump 把结果输出到标准输出。我们后面将看到这样做有什么用处。尽管上述命令会创建一个文本文件，pg_dump 可以用其他格式创建文件以支持并行和细粒度的对象恢复控制。

pg_dump 是一个普通的 PostgreSQl, 客户端应用(尽管是个相当聪明的东西)。这就意味着你可以在任何可以访问该数据库的远端主机上进行备份工作。但是请记住 pg_dump不会以任何特殊权限运行。具体说来，就是它必须要有你想备份的表的读权限，因此为了备份整个数据库你几乎总是必须以一个数据库超级用户来运行它(如果你没有足够的特权来备份整个数据库，你仍然可以使用诸如 -n schema 或-t table 选项来备份该数据库中你能够访问的部分)。

要声明pg_dump连接哪个数据库服务器，使用命令行选项-h host和 -p port。默认主机是本地主机或你的 PGHOST 环境变量指定的主机。类似地，默认端口是环境变量 PGPORT 或(如果PGPORT 不存在)内建的默认值。(服务器通常有相同的默认值，所以还算方便。)

和任何其他 PostgreSQL 客户端应用一样，pg_dump 默认使用与当前操作系统用户名同名的数据库用户名进行连接。要使用其他名字，要么声明 -U 选项，要么设置环境变量 PGUSER。请注意 pg_dump 的连接也要通过客户认证机制。

pg_dump 对于其他备份方法的一个重要优势是，pg_dump 的输出可以很容易地在新版本的PostgreSQL 中载入，而文件级备份和连续归档都是极度的服务器版本限定的。pg_dump也是唯一可以将一个数据库传送到一个不同机器架构上的方法，例如从一个 32位服务器到一个 64 位服务器。

由 pg_dump 创建的备份在内部是一致的，也就是说，转储表现了 pg_dump开始运行时刻的数据库快照，且在 pg_dump 运行过程中发生的更新将不会被转储。pg_dump 工作的时候并不阻塞其他的对数据库的操作。(但是会阻塞那些需要排它锁的操作，比如大部分形式的 ALTER TABLE)

（2）从转储中恢复

pg_dump 生成的文本文件可以由 psql 程序读取。

从转储中恢复的常用命令是：

psql dbname < dumpfile

[postgres@bogon ~]$ psql mydb < mybak2

其中 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 --set ON_ERROR_STOP=on dbname < infile

不管怎样，你将只能得到一个部分恢复的数据库。作为另一种选择，你可以指定让整个恢复作为一个单独的事务运行,这样恢复要么完全完成要么完全回滚这种模式可以通过向 psql 传递-l 或--single-transaction 命令行选项来指定。在使用这种模式时,注意即使是很小的一个错误也会导致运行了数小时的恢复被回滚。但是，这仍然比在一个部分恢复后手工清理复杂的数据库要更好。

pg_dump 和 psql 读写管道的能力使得直接从一个服务器转储一个数据库到另一个服务器成为可能，例如：

pg_dump -h host1 dbname | psql -h host2 dbname

注意：

pg_dump 产生的转储是相对于 template0。这意味着在 templatel 中加入的任何语言、过程等都会被 pg dump 转储。结果是，如果在恢复时使用的是一个自定义的 template1，你必须从 template0 创建一个空的数据库，正如上面的例子所示。

一旦完成恢复，在每个数据库上运行ANALYZE是明智的举动，这样优化器就有有用的统计数据了。

（3）使用pg_dumpall

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

pg_dumpall > dumpfile

转储的结果可以使用psql恢复：

psql -f dumpfile postgres

实际上，你可以指定恢复到任何已有数据库名，但是如果你正在将转储载入到一个空集簇中则通常要用(postgres)。在恢复一个pg dumpall 转储时常常需要具有数据库超级用户访问权限，因为它需要恢复角色和表空间信息。如果你在使用表空间，请确保转储中的表空间路径适合于新的安装。

pg_dumpall 工作时会发出命令重新创建角色、表空间和空数据库，接着为每一个数据库 pg dump。这意味着每个数据库自身是一致的，但是不同数据库的快照并不同步。

集簇范围的数据可以使用 pg_dumpall 的 --globals-only 选项来单独转储。如果在单个数据库上运行 pg_dump 命令，上述做法对于完全备份整个集簇是必需的。

7、远程连接

（1）修改PostgreSQL 监听地址

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

通过dnf 安装的pgsql 配置文件在

/var/lib/pgsql/data/postgresql.conf

通过源码编译安装的pgsql 配置文件在

/usr/local/pgsql/data/postgresql.conf

下面操作以源码编译为例：

更改第60行，取消注释并把localhost 改成*

vim /usr/local/pgsql/data/postgresql.conf

listen_addressess = '*'

重启服务

pg_ctl -D /usr/local/pgsql/data/ -l logfile restart

（2）配置访问权限

默认是只能本地访问 PostgreSQ 的，我们需要在 pg_hba.conf 里面配置

找到IPv4 local connections 这一行，在这一行下面添加

vim /usr/local/pgsql/data/pg_hba.conf 

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：这定义了哪些客户端P地址或 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或password(对于较新版本的 PostgreSQL，建议使用 scram-sha-256)。

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

postgres=# alter user postgres with password 'pwd123';

（3）重启服务

[postgres@bogon ~]$ pg_ctl -D /usr/local/pgsql/data/ -l logfile restart

（4）验证远程连接

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

[postgres@bogon ~]$ psql -h 192.168.10.101
psql (15.4)
Type "help" for help.

postgres=#

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

[postgres@bogon ~]$ psql -h 192.168.10.101
Password for user postgres: 
psql (15.4)
Type "help" for help.

postgres=#

8、重置密码

在PostgreSQL 中如果密码忘记了怎么重置密码

（1）修改配置文件

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

vim /usr/local/pgsql/data/pg_hba.conf 
# IPv4 local connections；

host    all             all             0.0.0.0/0               trust	
    #可以选择添加或者直接修改

（2）重启

[postgres@bogon ~]$ pg_ctl -D /usr/local/pgsql/data/ -l logfile restart

（3）修改密码

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

postgres=# alter user postgres with password '123456';

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

修改完密码后将pg_hba.conf 配置文件改回原本的内容，或者注释掉也行。