本文为大家带来PostgreSQL表空间操作和postgre数据库备份恢复的示例

？ PostgreSQL 创建表空间

前言：在本文中，我们将学习如何使用 PostgreSQL 创建表空间声明。

？ PostgreSQL 表空间简介

表空间是磁盘上 PostgreSQL 存储包含数据库对象（例如索引和表）的数据文件的位置。

PostgreSQL 使用表空间将逻辑名称映射到物理磁盘位置。

PostgreSQL 有两个默认表空间：

• pg_default 用于存储用户数据的表空间。
• pg_global表空间存储全局数据。

表空间允许我们控制 PostgreSQL 的磁盘布局。使用表空间有两个主要优点：

• 首先，如果初始化集群的分区没有空间，我们可以在另一个分区上创建新的表空间并使用它，直到系统重新配置为止。
• 其次，我们可以利用统计数据来优化数据库性能。例如，我们可以将经常使用的索引或表放置在运行速度非常快的设备（例如固态设备）上，并将包含归档数据的表放置在运行速度较慢的设备上。

？PostgreSQL CREATE TABLESPACE 语法

要创建新表空间，请使用命令 CREATE TABLESPACE表名称 ♿ 不应该是以下❀：page _ 以 开头，因为这些名称是为系统表空间保留的。

默认情况下，表空间所有者是执行语句创建表空间的用户。要将其他用户指定为表空间所有者，请在关键字 OWNER 后声明用户名。

directory_path 是表空间中空目录的绝对路径。 PostgreSQL 用户必须具有读写此目录中数据的权限。

创建表空间后，我们可以运行命令CREATE DATABASE、CREATE TABLE 和♿♿♿♿。 CREATE INDEX语句用于在表空间中存储对象数据文件。

?PostgreSQL CREATE TABLE 示例

以下语句使用 CREATE TABLESPACE 创建名为 oz_user 的表：♿♿gp, oz_user, data\user 。

CREATE TABLESPACE oz_user 
LOCATION 'C:\pgdata\user';

请注意，此语句使用 Unix 风格的反斜杠作为目录路径。在执行该命令之前，目录 C:\pgdata\user 必须存在。 › 以下语句使用 oz_user 表空间创建日志数据库：

CREATE DATABASE log 
TABLESPACE oz_user;

TABLESPACE该语句显示日志数据库用于存储数据的表空间。

以下语句创建一个新表log_time并向表中添加一行：

CREATE TABLE log_time (
    log_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
    date DATE, 
    data_id INT
);

INSERT INTO log_time(date, data_id)
VALUES('2022-10-23',1);

命令oz_user❝我们可以使用表中的以下数据。它的信息：

\db+ oz_user

输出：

    Name    |  Owner   |     Location      | Access privileges | Options |  Size   | Description
------------+----------+-------------------+-------------------+---------+---------+-------------
 oz_user    | postgres | c:\pgdata\user    |                   |         | 8033 kB |
(1 row)

？PostgreSQL Altertablespace

preface：在本文中，我们将学习使用acepostgre♓♓lessqls语句来重命名，更改所有者，或设置表空间参数。啊？更改表空间语句配置文件

创建表空间后，可以使用命令 change tablespace 来更改其定义，代码如下：

ALTER TABLESPACE tablespace_name
action;

操作可以是： 操作可以是：重命名表空间

要重命名表空间，请使用命令 ALTER TABLESPACE RENAME TO 语句：

ALTER TABLESPACE tablespace_name 
RENAME TO new_name;

要更改表空间的所有者，请使用命令 ‶ALTER。

ALTER TABLESPACE tablespace_name 
OWNER TO new_owner;

以下语句更改表空间参数：

ALTER TABLESPACE tablespace_name 
SET parameter_name = value;

？只有主管理员或表空间所有者可以执行ALTER TABLESPACE。

?PostgreSQL ALTER TABLESPACE 示例

以下语句重命名下面的 table1 表空间 ♓

更改表 table1 所有者的语句

CREATE TABLE log_time (
    log_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
    date DATE, 
    data_id INT
);

INSERT INTO log_time(date, data_id)
VALUES('2022-10-23',1);

postgres 到 oz:

ALTER TABLESPACE table2 
OWNER to oz;

?PostgreSQL❓♷❓›：在本文中我们将学习使用 PostgreSQL DROP TABLESPACE

？PostgreSQL DROP TABLESPACE 语句介绍

DROP TABLESPACE 命令从当前数据库中删除一个表空间： ♿LESS 在这个语法中❓P❓ 指定要删除的表空间后删除关键词。

如果没有表空间，我们需要使用IF EXISTS这样postgre就不会报错。

只有表空间所有者或超级管理员才能执行DROP TABLESPACE删除表空间。 S？ PostgreSQL 删除表空间示例

首先，创建一个名为 Demo 的新表空间，并将其映射到 c: \ data \ ♓♓ ‿ ♓。

CREATE TABLESPACE demo 
LOCATION 'C:/data/demo';

其次，创建一个名为 demodb 的新数据库，并将其表空间设置为 demo♿，Th。 test 位于 dbdemo 并将其 TABLESPACE 设置在 de 上莫♻♹♹♻♹♻♻♻♻♻。:

SELECT
	ts.spcname,
	cl.relname
FROM
	pg_class cl
JOIN pg_tablespace ts 
    ON cl.reltablespace = ts.oid
WHERE
	ts.spcname = 'demo';

第四，尝试删除test表空间：

DROP TABLESPACE demo;

PostgreSQL给出错误：

[Err] ERROR: tablespace "demo" is not empty

因为

demo 不能为空。

第五，登录postgres并删除demodb

CREATE TABLE log_time (
    log_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
    date DATE, 
    data_id INT
);

INSERT INTO log_time(date, data_id)
VALUES('2022-10-23',1);

demodb

CREATE TABLE log_time (
    log_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
    date DATE, 
    data_id INT
);

INSERT INTO log_time(date, data_id)
VALUES('2022-10-23',1);

demodb 除了删除数据库 还将其移动到另一个桌面表面，例如：通过 使用按键 ALTER TABLE 移动表pg_default测试桌面：

DROP TABLESPACE demo;

本次表删除成功空白演示。

在本文中，我们学习了如何使用 PostgreSQL 删除表空间DROP TABLESPACE。

？PostgreSQL 备份？ ？ ？ ?

前言：在本文中，我们将学习如何使用命令pg_dump和❿❿

数据库备份是数据库管理中最关键的任务之一。执行数据库备份之前应考虑以下备份类型：

• 完整/部分数据库
• 数据和结构或仅结构
• 恢复时间
• SQL 提供❓❓恢复。 pg_dump 命令和pg_dumpall帮助我们轻松高效地备份数据库。

  想要查看快速备份数据库的命令的用户，可以查看以下命令：

  pg_dump -U username -W -F t database_name > c:\backup_file.tar
  

  下一节我们将一步步学习如何备份数据库、所有数据库、仅数据库对象。

  如何备份数据库

  我们可以使用命令pg_dump来备份数据库。 pg_dump 将所有数据库对象的内容转储到一个文件中。

  首先，转到 PostgreSQL 转储文件夹：

  C:>cd C:\Program Files\PostgreSQL\12\bin
  

  其次，运行 pg_dump 并使用以下选项来备份 dd。 数据库dvdrental.tar文件存储在文件夹C:\pgbackup。

  pg_dump -U postgres -W -F t user > C:\pgbackup\user.tar
  

  让我们更详细地研究一下上面的陈述。

  -U postgres：指定连接PostgreSQL数据库服务器的用户。在此示例中，我们使用用户 postgres。

  -W：pg_dump 在连接到 PostgreSQL 数据库服务器之前要求输入密码。点击回车后，会提示pg_dump输入postgres用户的密码。

  -F ：指定输出文件格式，可以是以下之一：

  • c ：自定义格式存档文件格式 ♿❓ 不是 ： tar ：纯文本 SQL 脚本文件。

  ？在此示例中，我们使用 -F t 将输出文件设置为 tar 文件。

  user：是要备份的数据库名称。

  > C:\pgbackup\user.tar 是输出备份文件路径。备份所有数据库。

  • 首先，在 psql 中使用命令 \list，列出集群中的所有数据库
  • 其次，使用命令
  • 其次，使用命令 ❓ 列出每个数据库如上一节所示。

  除了程序pg_dump之外，PostgreSQL还立即为我们提供了pg_dumpall命令。不建议使用此命令，原因如下：

  • pg_dumpall 该程序将所有数据库依次导出到一个脚本文件中，这会阻止我们执行单次恢复。如果所有数据库都以这种方式备份，恢复过程将花费更多时间。
  • 处理所有数据库的转储比处理每个单独的数据库花费的时间更长，因此我们不知道每个数据库转储需要多长时间。

  如果您有充分的理由使用 pg_dumpall 备份所有数据库，请使用以下命令：

  pg_dumpall -U postgres > C:\pgbackup\all.sql
  

  pg_dumpall 类似于 ♓❓ pg_dump语法。此命令省略 -W，以避免为每个单独的数据库输入密码。

  如何备份数据库对象

  有时我们只想备份数据库对象而不是数据，这在测试阶段很有用，而无需将测试数据移至实时系统。

  要备份数据库中的所有对象（包括角色、表空间、数据库、模式、表、索引、触发器、函数、约束、视图、所有者和权限），请使用以下命令：

  pg_dumpall --schema-only > C:\pgdump\alldbobjects.sql
  

  如果需要要备份角色，请使用以下命令：

  pg_dumpall --roles-only > C:\pgdump\allroles.sql
  

  如果要备份表空间，请使用以下命令：

  pg_dumpall --tablespaces-only > C:\pgdump\alltablespaces.sql
  

  PostgreSQL 恢复数据库

  前言 ：在本文中，我们将学习如何用它。 pg_restore 命令 和 psql ♿ 恢复数据库 。

  恢复数据库之前，必须断开与数据库的所有连接并准备备份文件。可以通过两种方式在 PostgreSQL 中恢复数据库：

  • 使用 psql 恢复 pg_dump 和♓♓– _dumpall 通过命令创建的清理 SQL 脚本文件。
  • 使用命令pg_restore恢复由命令pg_dump

  ❝创建的tar文件和目录格式。

如何使用 psql 恢复数据库

psql 该命令允许 pg_dump

♓♓_mp 恢复由任何其他命令创建的 SQL 脚本或兼容的备份文件。使用命令 psql 我们可以运行转储文件中的整个脚本。 ？ ：

psql -U username --set ON_ERROR_STOP=on -f backupfile

如果备份了特定数据库中的对象，可以使用以下命令恢复它们：

psql -U username -d database_name -f objects.sql

如何恢复数据库 pg_restore

除了命令 ❝♓psql 还可以使用命令 pg_restore 命令 恢复由 pg_dump 或 或 pg_ 命令备份的数据库。和命令pg_restore一样，我们可以使用不同的方式来恢复数据库，例如：

• pg_restore-允许我们执行并行恢复

  ❓❓ ❓❓ 指定要恢复哪些线程。每个线程同时恢复一个单独的表，这显着加快了恢复过程。目前，pg_restore 仅支持自定义文件格式的此选项。

• pg_restore还允许我们从包含整个数据库的备份文件中恢复某些数据库对象。
• pg_restore可以对旧版本的数据库进行备份并将其恢复到较新的版本。

我们创建一个名为newdb的新数据库，它可以与命令pg_restore一起使用。

CREATE DATABASE newdb;

使用命令pg_dump生成tar文件格式到PostgreSQL♿❓❓❓❓ pg_restore

pg_restore --dbname=db --create --verbose c:\pgbackup\db.tar