玩转PostgreSQL（四）：表空间操作及如何备份和恢复数据库

2023-06-0610:13:51数据库教程Comments1,958 views字数 6595阅读模式

本文中，我们会借助示例来学习PostgreSQL表空间的操作，以及如何备份和恢复postgre数据库文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

? PostgreSQL创建表空间

前言: 在本文中，我们将学习如何使用PostgreSQL创建表空间声明。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

?PostgreSQL表空间介绍

表空间是磁盘上PostgreSQL存储包含数据库对象的数据文件的位置，例如，索引,和表格。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

PostgreSQL使用表空间将逻辑名称映射到磁盘上的物理位置。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

PostgreSQL附带两个默认表空间:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

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

表空间允许我们控制PostgreSQL的磁盘布局。使用表空间有两个主要优点:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

  • 首先，如果初始化群集的分区空间不足，我们可以在不同的分区上创建一个新的表空间，并使用它，直到重新配置系统。
  • 其次，我们可以使用统计信息来优化数据库性能。例如，我们可以将频繁访问的索引或表放在执行速度非常快的设备上，例如固态设备，并将包含存档数据的表放在速度较慢的设备上。

?PostgreSQL CREATE TABLESPACE 语法

要创建新的表空间，请使用CREATE TABLESPACE语法如下:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

CREATE TABLESPACE tablespace_name
OWNER user_name
LOCATION directory_path;

表空间的名称不应以pg _开头,因为这些名称是为系统表空间保留的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

默认情况下，表空间的所有者是执行创建表空间语句的用户。要将其他用户分配为表空间的所有者，请在OWNER 关键字后声明用户名称。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

directory_path 是用于表空间的空目录的绝对路径。PostgreSQL系统用户必须拥有此目录权限才能读写数据。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

创建表空间后，我们可以执行CREATE DATABASE,CREATE TABLE 和CREATE INDEX 语句，用于在表空间中存储对象的数据文件。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

?PostgreSQL CREATE TABLE 示例

以下语句使用CREATE TABLESPACE创建一个名为oz_user的表，物理位置位于C:\pgdata\user。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

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

请注意，此语句使用Unix样式的斜杠作为目录路径。在执行命令之前，必须存在C:\pgdata\user 目录。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

要列出当前PostgreSQL数据库服务器中的所有表空间，请使用\db命令:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

\db

Output:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

           List of tablespaces
    Name    |  Owner   |     Location
------------+----------+-------------------
 pg_default | postgres |
 pg_global  | postgres |
 oz_user    | postgres | c:\pgdata\user

db + 命令显示更多信息，例如大小和访问权限:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

                                       List of tablespaces
    Name    |  Owner   |     Location      | Access privileges | Options |  Size   | Description
------------+----------+-------------------+-------------------+---------+---------+-------------
 pg_default | postgres |                   |                   |         | 124 MB  |
 pg_global  | postgres |                   |                   |         | 625 kB  |
 oz_user    | postgres | c:\pgdata\user    |                   |         | 0 bytes |

以下语句创建使用oz_user表空间的 log数据库:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

CREATE DATABASE log 
TABLESPACE oz_user;

TABLESPACE子句指示log数据库将用于存储数据的表空间。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

以下语句创建新表log_time和插入表中的一行:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

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表空间有数据之后，我们可以在psql中使用以下命令查看其信息:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

\db+ oz_user

Output:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

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

?PostgreSQL ALTER TABLESPACE

前言: 在本文中，我们将学习如何使用PostgreSQL ALTER TABLESPACE语句来重命名、更改所有者或设置表空间的参数。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

?ALTER TABLESPACE 语句简介

在创建表空间之后,你可以使用ALTER TABLESPACE更改其定义，代码如下所示:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

ALTER TABLESPACE tablespace_name
action;

操作可以是:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

  • 重命名表空间
  • 更改所有者
  • 设置表空间的参数。

要重命名表空间，请使用ALTER TABLESPACE RENAME TO语句:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

ALTER TABLESPACE tablespace_name 
RENAME TO new_name;

要更改表空间的所有者，请使用ALTER TABLESPACE [tablespace_name] OWNER TO语句:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

ALTER TABLESPACE tablespace_name 
OWNER TO new_owner;

以下语句更改表空间的参数:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

ALTER TABLESPACE tablespace_name 
SET parameter_name = value;

?只有超级管理员或表空间所有者可以执行ALTER TABLESPACE声明。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

?PostgreSQL ALTER TABLESPACE 示例

以下语句重命名table1表空间到table2:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

ALTER TABLESPACE table1 
RENAME TO table2;

以下语句更改table2表的所有者从postgres为 oz:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

ALTER TABLESPACE table2 
OWNER to oz;

?PostgreSQL DROP TABLESPACE

前言: 在本文中，我们将学习如何使用PostgreSQL DROP TABLESPACE语句来删除表空间。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

?PostgreSQL DROP TABLESPACE 语句简介

DROP TABLESPACE语句从当前数据库中删除表空间:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

DROP TABLESPACE [IF EXISTS] tablespace_name;

在此语法中，DROP TABLESPACE关键词后指定要删除的表空间。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

当表空间不存在时我们需要使用IF EXISTS，以防postgre报错。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

只有表空间所有者或超级管理员可以执行DROP TABLESPACE删除表空间的语句。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

?PostgreSQL DROP TABLESPACE 示例

首先，创建新表空间命名demo并将其映射到C:\data\demo。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

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

第二，创建新数据库命名demodb并将其表空间设置为demo:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

CREATE DATABASE demodb 
TABLESPACE = demo;

第三，创建新表命名test在dbdemo并设置它的TABLESPACE是demo:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

CREATE TABLE test (
	id serial PRIMARY KEY,
	remark VARCHAR (255) NOT NULL
) TABLESPACE demo;

以下语句返回demo表空间所有对象:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

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

玩转PostgreSQL（四）：表空间操作及如何备份和恢复数据库文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

第四，试着删除test表空间:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

DROP TABLESPACE demo;

PostgreSQL发出错误:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

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

因为demo表空间不为空，不能删除。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

第五，登录到postgres数据库并删除demodb数据库:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

DROP DATABASE demodb;

出了删除数据库,我们还可以将其移动到另一个表空间，例如：通过使用ALTER TABLE 将表移动到pg_default表空间下:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

ALTER DATABASE demodb
SET TABLESPACE = pg_default;

第六，删除test表空间:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

DROP TABLESPACE demo;

这次，成功删除表空间demo。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

在本文中，我们已经学习了如何使用PostgreSQLDROP TABLESPACE删除表空间的方法。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

?PostgreSQL 备份? ? ? ?

前言: 在本文中，我们将学习如何使用pg_dump和pg_dumpall命令备份数据库。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

备份数据库是数据库管理中最关键的任务之一。在备份数据库之前，应考虑以下备份类型:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

  • 全部/部分数据库
  • 数据和结构，或仅结构
  • 恢复时间
  • 恢复性能

PostgreSQL提供了pg_dump和pg_dumpall命令帮助我们轻松有效地备份数据库。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

对于希望查看快速备份数据库的命令的用户，可以参考以下命令:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

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

在下一节中，我们将逐步学习如何备份一个数据库、所有数据库以及仅备份数据库对象。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

如何备份一个数据库

要备份一个数据库，我们可以使用pg_dump命令。pg_dump将所有数据库对象的内容转储到单个文件中。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

首先，进入到PostgreSQL bin文件夹:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

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

第二，执行pg_dump并使用以下选项备份dvdrental数据库到dvdrental.tar文件，存储在C:\pgbackup文件夹。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

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

让我们更详细地研究上面语句。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

-U postgres:  指定要连接到PostgreSQL数据库服务器的用户。在此示例中我们使用了postgres用户。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

-W:  pg_dump 在连接到PostgreSQL数据库服务器之前提示输入密码。点击回车键后, pg_dump 将提示输入postgres 用户密码.文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

-F : 指定可以为以下之一的输出文件格式:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

  • c: 自定义格式的存档文件格式
  • d: 目录格式存档
  • t: tar
  • p: 纯文本SQL脚本文件.

? 在此示例中，我们使用-F t将输出文件指定为tar文件。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

user: 是要备份的数据库的名称。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

> C:\pgbackup\user.tar 是输出备份文件路径。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

如何备份所有数据库

要备份所有数据库，我们可以运行单个pg_dump上面的命令按顺序排列，如果要加快备份过程，则可以并行执行。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

  • 首先，从psql中，使用命令\list列出集群中所有可用的数据库
  • 第二，使用pg_dump备份每个数据库，如上节所示。

除了pg_dump程序，PostgreSQL还为我们提供pg_dumpall命令允许我们一次备份所有数据库。但是，由于以下原因，不建议使用此命令:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

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

如果你有充分的理由要使用pg_dumpall备份所有数据库，以下是命令:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

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

pg_dumpall语法类似pg_dump语法。此命令省略-W避免为每个单独的数据库输入密码。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

如何备份数据库对象

有时，我们只想备份数据库对象，而不是在测试阶段有用的数据，而不想将测试数据移动到实时系统。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

要备份所有数据库中的对象，包括角色、表空间、数据库、schema、表、索引、触发器、函数、约束、视图、所有者和权限，请使用以下命令:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

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

如果只想备份角色，请使用以下命令:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

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

如果要备份表空间，使用以下命令:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

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

PostgreSQL还原数据库

前言: 在本文中，我们将学习如何使用pg_restore和psql命令还原数据库。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

在还原数据库之前，需要终止与该数据库的所有连接并准备备份文件。在PostgreSQL中，可以通过两种方式还原数据库:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

  • 使用psql恢复由pg_dump和pg_dumpall命令生成的纯SQL脚本文件。
  • 使用pg_restore恢复由pg_dump命令创建的tar文件和目录格式。

如何使用psql还原数据库

psql命令允许你pg_dump,pg_dumpall恢复生成的SQL脚本文或任何其他命令生成的兼容备份文件。通过使用psql命令，我们可以在转储文件中执行整个脚本。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

要还原完整备份并忽略还原过程中发生的任何错误，请使用以下命令:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

psql -U username -f backupfile.sql

如果要在出现错误时停止还原数据库，请添加--set ON_ERROR_STOP=on option：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

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

如果备份特定数据库中的对象，则可以使用以下命令还原它们:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

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

如何使用pg_restore还原数据库

除了psql命令，你可以使用pg_restore命令还原pg_dump或pg_dumpall命令备份的数据库。与pg_restore命令相同，我们可以使用多种选项来恢复数据库，例如:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

  • pg_restore允许我们执行并行恢复，使用-j以指定要还原的线程数。每个线程同时恢复一个单独的表，这大大加快了恢复进程。目前，pg_restore仅支持自定义文件格式的此选项。
  • pg_restore还允许我们还原包含完整数据库的备份文件中的特定数据库对象。
  • pg_restore可以使用旧版本的数据库备份并将其还原到新版本。

让我们创建新数据库命名newdb用于与pg_restore命令。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

CREATE DATABASE newdb;

使用pg_dump命令在PostgreSQL备份数据库生成的tar文件格式文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

我们使用 pg_restore恢复newdb数据库文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

pg_restore --dbname=newdb --verbose C:\pgbackup\db.tar

如果还原与备份数据库相同的数据库，则可以使用以下命令:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

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

从PostgreSQL 9.2开始，我们可以使用-- section 仅恢复表结构的选项。这允许我们使用新数据库作为创建其他数据库的模板。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

首先，创建一个名为 testdb的数据库.文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

CREATE DATABASE testdb;

第二，从备份文件testdb.tar修复表结构，使用以下命令:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

>pg_restore --dbname=testdb --section=pre-data  C:\pgbackup\testdb.tar

? 相关阅读文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html

作者：只注视着你
来源：稀土掘金
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45061.html
  • 本站内容整理自互联网，仅提供信息存储空间服务，以方便学习之用。如对文章、图片、字体等版权有疑问，请在下方留言，管理员看到后，将第一时间进行处理。
  • 转载请务必保留本文链接：https://www.cainiaoxueyuan.com/sjk/45061.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定