问题[pg-dump](dba)

有人能帮助我吗？:)
我有 2 台服务器，想要在独占实例中拆分数据库，所以我将我的数据库 1 逐 1 迁移到第二台服务器
Postgres 版本 11.16 两台服务器
所以这是我在 s1 上的命令
pg_dump db1 > /tmp/db1.dump
然后我在我的 s2 上恢复： 然后
psql -p 5432 db1 < /tmp/db1.dump
我得到这个错误
error: invalid value for parameter "default_tablestapce": "tbs_db1_index"
DETAIL: Tablespace "tbs_db1_index" does not exist

, s1 上的 db1 位置与 s2 上的位置不同，这可能是问题所在，因为我在 s1 上没有错误或任何问题，我的数据库很好
此外，恢复过程不会停止或中断它仍在进行中（我的数据库很大)
PS，是的，我可以直接执行此操作，而无需通过此命令转储文件
pg_dump --no-owner db1 | psql -p 5432 -v ON_ERROR_STOP=1 -h 192.168.1.2 -U postgres db1
但也不起作用

pg_dump 和 pg_restore 将 search_path 设置为空字符串，如果有函数调用公共模式中的函数而没有明确指定模式名称（即：uuid_generate_v4()vs public.uuid_generate_v4()），这将失败。

如何强制pg_restore使用指定的 search_path，例如public？

当我尝试将转储文件恢复到另一个（本地）PostgreSQL 服务器（在 Debian GNU/Linux 10.10 上运行）时，出现以下错误：

pg_restore: error: could not execute query: ERROR:  option "locale" not recognized

因为它确实有以下行：

CREATE DATABASE "REMOTE_DB" WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'en_GB.UTF-8';

是的，我知道PostgreSQL 12 CREATE DATABASE命令没有LOCALE选项。

我不明白的是：

• 我在本地服务器上使用的 PostgreSQL 本身的版本报告为 12.7
• pg_dump我在本地服务器上使用的版本报告为 12.7
• pg_restore我在本地服务器上使用的版本报告为 12.7
• 我使用其创建的转储的远程 PostgreSQL 数据库pg_dump是版本 12.7，而不是 13。

让我一一验证：

我的本地服务器：

$ sudo -u postgres psql -c 'select version();'
                                                     version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.7 (Debian 12.7-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

我的本地pg_dump版本：

$ pg_dump --version
pg_dump (PostgreSQL) 12.7 (Debian 12.7-1.pgdg100+1)

我的本地pg_restore版本：

$ pg_restore --version
pg_restore (PostgreSQL) 12.7 (Debian 12.7-1.pgdg100+1)

让我们检查一下远程数据库的 PostgreSQL 版本：

$ psql --host=REMOTE_HOST_IP_ADDRESS --dbname=REMOTE_DB --username=DB_USER -c 'select version();'
Password for user DB_USER:
                                                             version
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.7 (Debian 12.7-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

这就是我创建转储文件的方式：

$ pg_dump --verbose \
  --create \
  --clean \
  --if-exists \
  --format=custom \
  --compress=5 \
  --host=REMOTE_HOST_IP_ADDRESS --dbname=REMOTE_DB --username=DB_USER \
  --schema=public --table=TABLE_NAME\
  > db.dump

这就是我尝试在运行 PostgreSQL 12 的本地服务器上恢复它的方式：

$ sudo -u postgres \
pg_restore --verbose \
  --create \
  --clean \
  --jobs=8 \
  --format=custom \
  --dbname=postgres \
  db.dump

PostgreSQL 13已安装，但未在此本地服务器上运行：

$ systemctl status [email protected]
● [email protected] - PostgreSQL Cluster 13-main
   Loaded: loaded (/lib/systemd/system/[email protected]; enabled-runtime; vendor preset: enabled)
   Active: failed (Result: protocol) since Thu 2021-06-24 07:59:24 UTC; 1 day 5h ago
  Process: 562 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 13-main start (code=exited, status=1/FAILURE)

PostgreSQL 12正在运行：

$ systemctl status [email protected]
● [email protected] - PostgreSQL Cluster 12-main
   Loaded: loaded (/lib/systemd/system/[email protected]; enabled-runtime; vendor preset: enabled)
   Active: active (running) since Thu 2021-06-24 07:59:26 UTC; 1 day 5h ago
  Process: 556 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 12-main start (code=exited, status=0/SUCCESS)
 Main PID: 595 (postgres)
    Tasks: 9 (limit: 36863)
   Memory: 235.7M
   CGroup: /system.slice/system-postgresql.slice/[email protected]
           ├─595 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf
           ├─678 postgres: 12/main: checkpointer
           ├─679 postgres: 12/main: background writer
           ├─680 postgres: 12/main: walwriter
           ├─681 postgres: 12/main: autovacuum launcher
           ├─682 postgres: 12/main: stats collector
           ├─683 postgres: 12/main: TimescaleDB Background Worker Launcher
           ├─684 postgres: 12/main: logical replication launcher
           └─685 postgres: 12/main: TimescaleDB Background Worker Scheduler

长话短说，我的问题是：

• 为什么我LOCALE在生成的转储文件中得到这个不受支持的选项pg_dump，而 REMOTE PostgreSQL 版本以及本地pg_dump版本报告为12.7而不是 13？

我还应该检查什么？

我备份了托管在 PostgreSQL 的 RDS 实例上的堆栈交换存档数据库，如下所示：

pg_dump stackexchange -h <host> -U postgres -password -o > stack.dmp

...几个小时后，我有了一个不错的 151GB 转储文件。

接下来，我将这只小狗恢复到另一个实例：

psql --set ON_ERROR_STOP=on -h <otherhost> -U postgres -d stackexchange -f stack.dmp

...大约一个小时后，它完成了，没有错误。幸福。

奇怪的是，pgadmin4 没有显示应该创建的模式，也没有显示任何表、视图等。与 psql 连接并发出 \dn 只返回公共模式。

对于傻笑，我查看了数据库的大小，它看起来正确（ish）：

stackexchange=> SELECT pg_size_pretty( pg_database_size('stackexchange') );
 pg_size_pretty
----------------
 145 GB
(1 row)

我用 head 从转储中抓取前 50 行，一切看起来都很好：

--
-- PostgreSQL database dump
--

-- Dumped from database version 11.10
-- Dumped by pg_dump version 11.11

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: stackoverflow; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA stackoverflow;


ALTER SCHEMA stackoverflow OWNER TO postgres;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: posthistory; Type: TABLE; Schema: stackoverflow; Owner: postgres
--

CREATE TABLE stackoverflow.posthistory (
    id integer NOT NULL,
    posthistorytypeid integer,
    postid integer,
    revisionguid text,
    creationdate timestamp without time zone NOT NULL,
    userid integer,
    posttext text,
    jsonfield jsonb
);


ALTER TABLE stackoverflow.posthistory OWNER TO postgres;

所以我有点难过。所有这些东西都正确执行。数据库大小合适……但没有对象。

接下来，我很好奇并尝试重新创建应该已经创建的模式（“stackoverflow”）：

stackexchange=> create schema stackoverflow;

5 分钟后，我在控制台中没有得到任何响应。psql 只是坐在那里。

这听起来对任何人来说都很熟悉吗？我很困惑。关于下一步的想法？

编辑：我在 psql 上做了一个 CTRL-BREAK。结果：

stackexchange=> create schema stackoverflow;

^CCancel request sent
ERROR:  canceling statement due to user request
CONTEXT:  while inserting index tuple (0,12) in relation "pg_namespace_nspname_index"

pg_dump我有一个由PostgreSQL v12 客户端附带的大转储文件，它是自定义格式而不是纯 sql 文本。服务器的版本也是 PostgreSQL v12。

现在我需要将文件重新加载到 PostgreSQL v11 服务器中，我收到一条错误消息：

unrecognized configuration parameter "default_table_access_method"

我猜这个参数是PG12引入的，所以PG11识别不出来，虽然我用的是12版本的pg_restore.

因为文件很大并且是二进制格式，我不能编辑它来手动删除该行。

我pg_restore test_file -f plain.sql用一个测试转储文件对其进行了测试，它看起来像工作，我可以从纯 SQL 文件中删除该行。但真正的转储文件大约是 30GB，从二进制扩展的纯 SQL 文件可能更大（工作数据库占用 300GB 磁盘空间）。

有没有办法，我可以干净地将它重新加载到 PG11 中，只有没有“SET default_table_access_method = heap;”这一行？

其实下面这些都可以忽略，因为都是默认值：

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';

我有一个UTF8数据库，我想使用相同的编码qdb将它备份到一个普通文件中。UTF8我正在使用pg_dump，因为我现在没有pgAdmin工作。但是，我无法pg_dump输出UTF8-encoded 纯 SQL 转储文件，因为这不起作用：

C:\Program Files\PostgreSQL\12\bin> .\pg_dump.exe -U postgres -F p -O -c -C -E UTF8 qdb > d:\qdb.sql

输出转储文件被编码为UCS-2 LE BOM尽管如此，这使得一些德语重音字母显示不正确。我正在使用 Windows 10 和 PostgreSQL 12.4。我们如何才能pg_dump真正转储一个普通的 SQL UTF8 文件？

我们每晚都会为我们的生产数据库创建一个“自定义”格式 (-Fc) 转储文件。它的用途之一是恢复到测试数据库。然后我们运行一些更新查询来匿名数据，但由于有数百万行，这是一项相当耗时的任务。

我的想法是，如果我们使用“目录”格式 (-Fd) 转储，那么我可以运行一些脚本来直接修改该转储中的数据。然后，我们只需在一个步骤中恢复匿名数据，而不是两步（pg_restore+updates）。

但是，我仍然需要自定义转储文件来输入其他进程，并且如果可能的话，我更愿意避免转储两次。所以我想知道是否可以采用自定义格式转储文件并将其转换为目录格式转储。

我有一个使用 PostgreSQL v11 运行的大型数据库，并且磁盘空间非常有限，所以我不得不VACUUM (FULL)频繁地访问数据库（例如每天）。

但是吸尘是一项非常耗时的操作。此外，我经常遇到真空错误消息，例如

ERROR:  canceling autovacuum task
CONTEXT:  automatic vacuum of table ***

幸运的是，服务可以在一天中的特定时间点关闭，所以我可以在关闭数据库后转储数据库，立即恢复并重新启动服务。

此外，我发现“转储/恢复”比“清理”更快。

我的问题：“倾倒/恢复”所做的事情是否包括“吸满”所做的事情？

如果是这样，我可以这样做而不是吸尘。

我创建了一个 postgeSQL 数据库的备份文件：

>pg_dump --dbname=db_test --host=localhost --username=user -F c -f dump_test.out

在 pg_dump 的文档中使用扩展名 .sql 和 pg_dumpall 使用 .out

https://www.postgresql.org/docs/9.2/app-pgdump.html

https://www.postgresql.org/docs/9.2/app-pg-dumpall.html

PostgreSQL 中 pg_dump 中 .out 和 .sql 的扩展名有什么区别？建议是什么，为什么？

使用时文档说明了这一点-t table：

注意当指定 -t 时，pg_dump 不会尝试转储所选表可能依赖的任何其他数据库对象。因此，无法保证特定表转储的结果可以自行成功恢复到干净的数据库中。

但是，当表有一个序列，并且owned by为该序列设置了表时，它被包含在转储中。

不应该根据文档的这一部分排除吗？（如果是这样，为什么序列例外？）

那么什么样的数据库对象将被排除在-t table模式中呢？