PostgreSQL - 将每个表转储到不同的文件中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18330358/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL - dump each table into a different file
提问by Hasan Iqbal
I need to extract SQL files from multiple tables of a PostgreSQL database. This is what I've come up with so far:
我需要从 PostgreSQL 数据库的多个表中提取 SQL 文件。这是我到目前为止想出的：
pg_dump -t 'thr_*' -s dbName -U userName > /home/anik/psqlTest/db_dump.sql
However, as you see, all the tables that start with the prefix thrare being exported to a single unified file (db_dump.sql). I have almost 90 tables in total to extract SQL from, so it is a must that the data be stored into separate files.
但是，如您所见，所有以前缀开头的表thr都被导出到一个统一文件 ( db_dump.sql)。我总共有将近 90 个表可以从中提取 SQL，因此必须将数据存储到单独的文件中。
How can I do it? Thanks in advance.
我该怎么做？提前致谢。
回答by IMSoP
If you are happy to hard-code the list of tables, but just want each to be in a different file, you could use a shell script loop to run the pg_dumpcommand multiple times, substituting in the table name each time round the loop:
如果您乐于对表列表进行硬编码，但只是希望每个表都在不同的文件中，您可以使用 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;
EDIT: This approach can be extended to get the list of tables dynamically by running a query through psql and feeding the results into the loop instead of a hard-coded list:
编辑：可以扩展此方法以通过 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;
Here psql -t -c "SQL"runs SQLand outputs the results with no header or footer; since there is only one column selected, there will be a table name on each line of the output captured by $(command), and your shell will loop through them one at a time.
这里psql -t -c "SQL"运行SQL并输出没有页眉或页脚的结果；由于只选择了一列，因此在捕获的输出的每一行上都会有一个表名$(command)，并且您的 shell 将一次循环遍历它们。
回答by Cyril Chaboisseau
Since version 9.1 of PostgreSQL (Sept. 2011), one can use the directory formatoutput when doing backups
自 PostgreSQL 9.1 版（2011 年 9 月）起，备份时可以使用目录格式输出
and 2 versions/2 years after (PostgreSQL 9.3), the --jobs/-j makes it even more efficient to backup every single objects in parallel
和 2 个版本/2 年后（PostgreSQL 9.3），--jobs/-j 使并行备份每个对象的效率更高
but what I don't understand in your original question, is that you use the -s option which dumps only the object definitions (schema), not data.
但是我在您的原始问题中不明白的是，您使用了 -s 选项，该选项仅转储对象定义（架构），而不转储数据。
if you want the data, you shall not use -s but rather -a (data-only) or no option to have schema+data
如果您想要数据，则不应使用 -s 而应使用 -a（仅数据）或没有选项以拥有架构+数据
so, to backup all objects (tables...) that begins with 'th' for the database dbName on the directory dbName_objects/ with 10 concurrent jobs/processes (increase load on the server) :
因此，要备份目录 dbName_objects/ 上以 'th' 开头的数据库 dbName 的所有对象（表...），并有 10 个并发作业/进程（增加服务器负载）：
pg_dump -Fd -f dbName_objects -j 10 -t 'thr_*' -U userName dbName
pg_dump -Fd -f dbName_objects -j 10 -t 'thr_*' -U 用户名 dbName
(you can also use the -a/-s if you want the data or the schema of the objects)
（如果需要对象的数据或架构，也可以使用 -a/-s）
as a result the directory will be populated with a toc.dat (table of content of all the objects) and one file per object (.dat.gz) in a compressed form
因此，该目录将填充一个 toc.dat（所有对象的内容表）和一个压缩形式的每个对象文件 (.dat.gz)
each file is named after it's object number, and you can retrieve the list with the following pg_restore command:
每个文件都以其对象编号命名，您可以使用以下 pg_restore 命令检索列表：
pg_restore --list -Fd dbName_objects/ | grep 'TABLE DATA'
pg_restore --list -Fd dbName_objects/ | grep '表格数据'
in order to have each file not compressed (in raw SQL)
为了不压缩每个文件（在原始 SQL 中）
pg_dump --data-only --compress=0 --format=directory --file=dbName_objects --jobs=10 --table='thr_*' --username=userName --dbname=dbName
pg_dump --data-only --compress=0 --format=directory --file=dbName_objects --jobs=10 --table='thr_*' --username=userName --dbname=dbName
回答by Rousseau Alban
(not enough reputation to comment the right post) I used your script with some corrections and some modifications for my own use, may be usefull for others:
（没有足够的声誉来评论正确的帖子）我使用了您的脚本并进行了一些更正和一些修改供我自己使用，可能对其他人有用：
#!/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
(I think you forgot to add $DB in the pg_dumb command, and I added a -w, for an automated script, it is better not to have a psw prompt I guess, for that, I created a ~/.pgpass file with my password in it I also gave the user for the command to know which password to fetch in .pgpass) Hope this helps someone someday.
（我想你忘了在 pg_dumb 命令中添加 $DB，我添加了 -w，对于自动脚本，最好不要有 psw 提示，我猜，为此，我创建了一个 ~/.pgpass 文件我在里面的密码我还给了用户命令来知道要在 .pgpass 中获取哪个密码）希望有一天这对某人有所帮助。
回答by rubo77
This bash script will do a backup with one file per table:
这个 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 -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 $ -t $table > $DIR/$table.sql;
done;
echo done
