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

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 thr are 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.

我怎么做呢?提前谢谢。

4 个解决方案

#1

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_dump command 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 SQL and 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并输出没有页眉或页脚的结果;由于只选择了一列，因此在$(命令)捕获的输出的每一行上都将有一个表名，您的shell将一次循环一次。

#2

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

#3

(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中获取哪个密码)希望有一天这能帮助到某人。

#4

Since version 9.1 of PostgreSQL (Sept. 2011), one can use the directory format output 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选项，它只转储对象定义(schema)，而不转储数据。

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开头的所有对象(表…)，以及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(所有对象的内容表)和一个压缩格式的每个对象(. data .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=目录—file=dbName_objects—job =10—table='thr_*'—用户名=用户名-dbname= dbname。

#1