PostgreSQL - 将每个表转储到不同的文件中

Question

我需要从一个PostgreSQL数据库的多个表中提取SQL文件。目前我想到的方法如下：

pg_dump -t 'thr_*' -s dbName -U userName > /home/anik/psqlTest/db_dump.sql

然而，正如您所看到的，以前缀thr开头的所有表都被导出到一个统一的文件（db_dump.sql）中。我总共有近90个表需要提取SQL，因此必须将数据存储到单独的文件中。

请问我该怎么做？提前感谢您的帮助。

- QuestionEverything

---

你需要解释一下为什么你需要90个不同的文件 - 是为了导出到MySQL，还是进行部分备份？如果你试图做备份/导出，那么IMSoP的答案不能保证每个表的快照相同。 - Richard Huxton

@RichardHuxton 很好的观点，我没有考虑到非原子性。我想你可以从数据库中创建一个单独的“自定义”备份，然后使用 pg_restore 从中提取各个表。 - IMSoP

@RichardHuxton，如果您想使用单独的文件设置代码存储库，以便在进行更改并将其保存到单独的文件时，无需手动从完整转储中拆分项目，则通常需要执行此操作。 - BilliD

Answer 1

如果您愿意硬编码表格列表，但只是希望每个表格在不同的文件中，您可以使用一个shell脚本循环来运行pg_dump命令多次，在循环期间每次替换表格名称。

for table in table1 table2 table3 etc;
do pg_dump -t $table -U userName dbName > /home/anik/psqlTest/db_dump_dir/$table.sql;
done;

编辑：这种方法可以通过运行psql查询并将结果馈入循环中来动态获取表列表，而不是使用硬编码的列表：

for table in $(psql -U userName -d dbName -t -c "Select table_name From information_schema.tables Where table_type='BASE TABLE' and table_name like 'thr_%'");
do pg_dump -t $table -U userName dbName > /home/anik/psqlTest/db_dump_dir/$table.sql;
done;

这里的psql -t -c "SQL"运行SQL并输出结果，没有头部或页脚；由于只选择了一列，每行输出中都会有一个表名，被$(command)捕获，你的shell将逐个循环遍历它们。

- IMSoP

---

我将在表名参数中使用通配符。硬编码90个表名！那时候我就成老头了。 :) - QuestionEverything

@HasanIqbalAnik 或者您可以从shell脚本中查询表列表，然后循环遍历该列表来访问数据库。 - Ihor Romanchenko

@HasanIqbalAnik 我想你可能会这么说，这就是为什么我在回答开始时加了一个警告语句。正如Igor建议的那样，我已经编辑了我的答案，先查询数据库获取列表。 - IMSoP

警告：此方法将把每个表格保存在不同的时间状态下，因为脚本需要一定时间进行循环。如果备份期间数据库发生更改，则可能导致不一致状态。因此，请确保备份期间不要更改数据库！ - rubo77

Answer 2

自从2011年9月PostgreSQL 9.1版本以来，备份时可以使用目录格式输出。另外，在两个版本/两年后的PostgreSQL 9.3中，使用--jobs/-j参数可以更有效地并行备份每个对象。

但我不理解你在原问题中使用的-s选项，它只会导出对象定义（模式），而没有数据。如果想要数据，应该使用-a（仅数据）或不使用任何选项（模式+数据）。

因此，为了备份以'th'开头的dbName数据库中的所有对象（表...）到dbName_objects/目录中，并使用10个并发进程（增加服务器负载）： pg_dump -Fd -f dbName_objects -j 10 -t 'thr_*' -U userName dbName （如果需要数据或对象模式，则还可以使用-a / -s选项）

备份完成后，该目录将包含一个toc.dat文件（列出所有对象的目录）和每个对象的一个压缩文件（.dat.gz）。

每个文件都以其对象编号命名，可以使用以下pg_restore命令检索列表： pg_restore --list -Fd dbName_objects/ | grep 'TABLE DATA'

如果要以非压缩的形式（原始SQL）保存每个文件，请使用以下命令： pg_dump --data-only --compress=0 --format=directory --file=dbName_objects --jobs=10 --table='thr_*' --username=userName --dbname=dbName

- Cyril Chaboisseau

---

运行这个程序只会给我 toc.dat 文件，目录中没有其他文件... - Rafs

你具体运行了哪个命令？表格是否在特定路径中？请提供更多上下文信息。 - Cyril Chaboisseau

我尝试了这两个命令，但我对后者更感兴趣，即不压缩以获取纯SQL文件的命令。我没有使用“table”参数，因为我想要转储所有数据库对象“--schema-only”。 - Rafs

我认为我找到了问题所在：--schema-only不会生成多个文件，而是生成一个包含数据库定义的文件... - Rafs

Answer 3

（声望不够，无法评论正确的帖子） 我使用了您的脚本，并进行了一些更正和修改，可能对其他人有用：

#!/bin/bash

# Config:
DB=rezopilotdatabase
U=postgres
# tablename searchpattern, if you want all tables enter "":
P=""
# directory to dump files without trailing slash:
DIR=~/psql_db_dump_dir

mkdir -p $DIR
TABLES="$(psql -d $DB -U $U -t -c "SELECT table_name FROM 
information_schema.tables WHERE table_type='BASE TABLE' AND table_name 
LIKE '%$P%' ORDER BY table_name")"
for table in $TABLES; do
  echo backup $table ...
  pg_dump $DB -U $U -w -t $table > $DIR/$table.sql;
done;
echo done

我认为您在pg_dumb命令中忘记添加$DB变量，我添加了一个-w选项，对于自动化脚本来说，最好不要有密码提示，所以我创建了一个~/.pgpass文件并将我的密码保存在其中。我还给出了用户名，以便命令知道在.pgpass中获取哪个密码。希望这能帮助到某些人。

- Rousseau Alban

---

很好。你只需要在某个地方包含模式名称即可。 - Otheus

我不得不进行一些微调，但这个脚本非常好用。谢谢。 - Jeff Bluemel

Answer 4

这个bash脚本将针对每个表格进行备份，并生成一个文件：

#!/bin/bash

# Config:
DB=dbName
U=userName
# tablename searchpattern, if you want all tables enter "":
P=""
# directory to dump files without trailing slash:
DIR=~/psql_db_dump_dir

mkdir -p $DIR
AUTH="-d $DB -U $U"
TABLES="$(psql $AUTH -t -c "SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_name LIKE '%$P%' ORDER BY table_name")"
for table in $TABLES; do
  echo backup $table ...
  pg_dump $AUTH -t $table > $DIR/$table.sql;
done;
echo done

- rubo77