本文中，我们会借助示例来学习PostgreSQL表空间的操作，以及如何备份和恢复postgre数据库
? PostgreSQL创建表空间
前言: 在本文中，我们将学习如何使用PostgreSQL创建表空间声明。
?PostgreSQL表空间介绍
表空间是磁盘上PostgreSQL存储包含数据库对象的数据文件的位置，例如，索引,和表格。
PostgreSQL使用表空间将逻辑名称映射到磁盘上的物理位置。
PostgreSQL附带两个默认表空间:
pg_default表空间存储用户数据。pg_global表空间存储全局数据。
表空间允许我们控制PostgreSQL的磁盘布局。使用表空间有两个主要优点:
- 首先，如果初始化群集的分区空间不足，我们可以在不同的分区上创建一个新的表空间，并使用它，直到重新配置系统。
- 其次，我们可以使用统计信息来优化数据库性能。例如，我们可以将频繁访问的索引或表放在执行速度非常快的设备上，例如固态设备，并将包含存档数据的表放在速度较慢的设备上。
?PostgreSQL CREATE TABLESPACE 语法
要创建新的表空间，请使用CREATE TABLESPACE语法如下:
CREATE TABLESPACE tablespace_name
OWNER user_name
LOCATION directory_path;
表空间的名称不应以pg _开头,因为这些名称是为系统表空间保留的。
默认情况下，表空间的所有者是执行创建表空间语句的用户。要将其他用户分配为表空间的所有者，请在OWNER 关键字后声明用户名称。
directory_path 是用于表空间的空目录的绝对路径。PostgreSQL系统用户必须拥有此目录权限才能读写数据。
创建表空间后，我们可以执行CREATE DATABASE,CREATE TABLE 和CREATE INDEX 语句，用于在表空间中存储对象的数据文件。
?PostgreSQL CREATE TABLE 示例
以下语句使用CREATE TABLESPACE创建一个名为oz_user的表，物理位置位于C:pgdatauser。
CREATE TABLESPACE oz_user
LOCATION 'C:pgdatauser';
请注意，此语句使用Unix样式的斜杠作为目录路径。在执行命令之前，必须存在C:pgdatauser 目录。
要列出当前PostgreSQL数据库服务器中的所有表空间，请使用db命令:
db
Output:
List of tablespaces
Name | Owner | Location
------------+----------+-------------------
pg_default | postgres |
pg_global | postgres |
oz_user | postgres | c:pgdatauser
db + 命令显示更多信息，例如大小和访问权限:
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:pgdatauser | | | 0 bytes |
以下语句创建使用oz_user表空间的 log数据库:
CREATE DATABASE log
TABLESPACE oz_user;
TABLESPACE子句指示log数据库将用于存储数据的表空间。
以下语句创建新表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表空间有数据之后，我们可以在psql中使用以下命令查看其信息:
db+ oz_user
Output:
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+-------------------+-------------------+---------+---------+-------------
oz_user | postgres | c:pgdatauser | | | 8033 kB |
(1 row)
?PostgreSQL ALTER TABLESPACE
前言: 在本文中，我们将学习如何使用PostgreSQL ALTER TABLESPACE语句来重命名、更改所有者或设置表空间的参数。
?ALTER TABLESPACE 语句简介
在创建表空间之后,你可以使用ALTER TABLESPACE更改其定义，代码如下所示:
ALTER TABLESPACE tablespace_name
action;
操作可以是:
- 重命名表空间
- 更改所有者
- 设置表空间的参数。
要重命名表空间，请使用ALTER TABLESPACE RENAME TO语句:
ALTER TABLESPACE tablespace_name
RENAME TO new_name;
要更改表空间的所有者，请使用ALTER TABLESPACE [tablespace_name] OWNER TO语句:
ALTER TABLESPACE tablespace_name
OWNER TO new_owner;
以下语句更改表空间的参数:
ALTER TABLESPACE tablespace_name
SET parameter_name = value;
?只有超级管理员或表空间所有者可以执行
ALTER TABLESPACE声明。
?PostgreSQL ALTER TABLESPACE 示例
以下语句重命名table1表空间到table2:
ALTER TABLESPACE table1
RENAME TO table2;
以下语句更改table2表的所有者从postgres为 oz:
ALTER TABLESPACE table2
OWNER to oz;
?PostgreSQL DROP TABLESPACE
前言: 在本文中，我们将学习如何使用PostgreSQL DROP TABLESPACE语句来删除表空间。
?PostgreSQL DROP TABLESPACE 语句简介
DROP TABLESPACE语句从当前数据库中删除表空间:
DROP TABLESPACE [IF EXISTS] tablespace_name;
在此语法中，DROP TABLESPACE关键词后指定要删除的表空间。
当表空间不存在时我们需要使用IF EXISTS，以防postgre报错。
只有表空间所有者或超级管理员可以执行DROP TABLESPACE删除表空间的语句。
?PostgreSQL DROP TABLESPACE 示例
首先，创建新表空间命名demo并将其映射到C:datademo。
CREATE TABLESPACE demo
LOCATION 'C:/data/demo';
第二，创建新数据库命名demodb并将其表空间设置为demo:
CREATE DATABASE demodb
TABLESPACE = demo;
第三，创建新表命名test在dbdemo并设置它的TABLESPACE是demo:
CREATE TABLE test (
id serial PRIMARY KEY,
remark VARCHAR (255) NOT NULL
) TABLESPACE demo;
以下语句返回demo表空间所有对象:
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数据库:
DROP DATABASE demodb;
出了删除数据库,我们还可以将其移动到另一个表空间，例如：通过使用ALTER TABLE 将表移动到pg_default表空间下:
ALTER DATABASE demodb
SET TABLESPACE = pg_default;
第六，删除test表空间:
DROP TABLESPACE demo;
这次，成功删除表空间demo。
在本文中，我们已经学习了如何使用PostgreSQLDROP TABLESPACE删除表空间的方法。
?PostgreSQL 备份? ? ? ?
前言: 在本文中，我们将学习如何使用pg_dump和pg_dumpall命令备份数据库。
备份数据库是数据库管理中最关键的任务之一。在备份数据库之前，应考虑以下备份类型:
- 全部/部分数据库
- 数据和结构，或仅结构
- 恢复时间
- 恢复性能
PostgreSQL提供了pg_dump和pg_dumpall命令帮助我们轻松有效地备份数据库。
对于希望查看快速备份数据库的命令的用户，可以参考以下命令:
pg_dump -U username -W -F t database_name > c:backup_file.tar
在下一节中，我们将逐步学习如何备份一个数据库、所有数据库以及仅备份数据库对象。
如何备份一个数据库
要备份一个数据库，我们可以使用pg_dump命令。pg_dump将所有数据库对象的内容转储到单个文件中。
首先，进入到PostgreSQL bin文件夹:
C:>cd C:Program FilesPostgreSQL12bin
第二，执行pg_dump并使用以下选项备份dvdrental数据库到dvdrental.tar文件，存储在C:pgbackup文件夹。
pg_dump -U postgres -W -F t user > C:pgbackupuser.tar
让我们更详细地研究上面语句。
-U postgres: 指定要连接到PostgreSQL数据库服务器的用户。在此示例中我们使用了postgres用户。
-W: pg_dump 在连接到PostgreSQL数据库服务器之前提示输入密码。点击回车键后, pg_dump 将提示输入postgres 用户密码.
-F : 指定可以为以下之一的输出文件格式:
c: 自定义格式的存档文件格式d: 目录格式存档t: tarp: 纯文本SQL脚本文件.
? 在此示例中，我们使用
-F t将输出文件指定为tar文件。
user: 是要备份的数据库的名称。
> C:pgbackupuser.tar 是输出备份文件路径。
如何备份所有数据库
要备份所有数据库，我们可以运行单个pg_dump上面的命令按顺序排列，如果要加快备份过程，则可以并行执行。
- 首先，从psql中，使用命令
list列出集群中所有可用的数据库 - 第二，使用
pg_dump备份每个数据库，如上节所示。
除了pg_dump程序，PostgreSQL还为我们提供pg_dumpall命令允许我们一次备份所有数据库。但是，由于以下原因，不建议使用此命令:
pg_dumpall程序将所有数据库一个接一个地导出到单个脚本文件中，这将阻止我们执行单个还原。如果以这种方式备份所有数据库，则还原过程将花费更多时间。- 转储所有数据库的处理时间比每个单独的数据库要长，因此我们不知道每个数据库的哪个转储需要多长时间。
如果你有充分的理由要使用pg_dumpall备份所有数据库，以下是命令:
pg_dumpall -U postgres > C:pgbackupall.sql
pg_dumpall语法类似pg_dump语法。此命令省略-W避免为每个单独的数据库输入密码。
如何备份数据库对象
有时，我们只想备份数据库对象，而不是在测试阶段有用的数据，而不想将测试数据移动到实时系统。
要备份所有数据库中的对象，包括角色、表空间、数据库、schema、表、索引、触发器、函数、约束、视图、所有者和权限，请使用以下命令:
pg_dumpall --schema-only > C:pgdumpalldbobjects.sql
如果只想备份角色，请使用以下命令:
pg_dumpall --roles-only > C:pgdumpallroles.sql
如果要备份表空间，使用以下命令:
pg_dumpall --tablespaces-only > C:pgdumpalltablespaces.sql
PostgreSQL还原数据库
前言: 在本文中，我们将学习如何使用pg_restore和psql命令还原数据库。
在还原数据库之前，需要终止与该数据库的所有连接并准备备份文件。在PostgreSQL中，可以通过两种方式还原数据库:
- 使用
psql恢复由pg_dump和pg_dumpall命令生成的纯SQL脚本文件。 - 使用
pg_restore恢复由pg_dump命令创建的tar文件和目录格式。
如何使用psql还原数据库
psql命令允许你pg_dump,pg_dumpall恢复生成的SQL脚本文或任何其他命令生成的兼容备份文件。通过使用psql命令，我们可以在转储文件中执行整个脚本。
要还原完整备份并忽略还原过程中发生的任何错误，请使用以下命令:
psql -U username -f backupfile.sql
如果要在出现错误时停止还原数据库，请添加--set ON_ERROR_STOP=on option：
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_dumpall命令备份的数据库。与pg_restore命令相同，我们可以使用多种选项来恢复数据库，例如:
pg_restore允许我们执行并行恢复，使用-j以指定要还原的线程数。每个线程同时恢复一个单独的表，这大大加快了恢复进程。目前，pg_restore仅支持自定义文件格式的此选项。pg_restore还允许我们还原包含完整数据库的备份文件中的特定数据库对象。pg_restore可以使用旧版本的数据库备份并将其还原到新版本。
让我们创建新数据库命名newdb用于与pg_restore命令。
CREATE DATABASE newdb;
使用pg_dump命令在PostgreSQL备份数据库生成的tar文件格式
我们使用 pg_restore恢复newdb数据库
pg_restore --dbname=newdb --verbose C:pgbackupdb.tar
如果还原与备份数据库相同的数据库，则可以使用以下命令:
pg_restore --dbname=db --create --verbose c:pgbackupdb.tar
从PostgreSQL 9.2开始，我们可以使用-- section 仅恢复表结构的选项。这允许我们使用新数据库作为创建其他数据库的模板。
首先，创建一个名为 testdb的数据库.
CREATE DATABASE testdb;
第二，从备份文件testdb.tar修复表结构，使用以下命令:
>pg_restore --dbname=testdb --section=pre-data C:pgbackuptestdb.tar
? 相关阅读
- www.postgresql.org/docs/curren… –
pg_dump命令官方文档。- www.postgresql.org/docs/curren… –
pg_restore命令官方文档
