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

问题描述：

我需要从 PostgreSQL 数据库的多个表中提取 SQL 文件.到目前为止，这是我想出的:

I need to extract SQL files from multiple tables of a PostgreSQL database. This is what I've come up with so far:

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

然而，如您所见，所有以前缀 thr 开头的表都被导出到一个统一的文件 (db_dump.sql).我总共有将近 90 个表可以从中提取 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.

我该怎么做?提前致谢.

How can I do it? Thanks in advance.

答

如果您乐于对表格列表进行硬编码，但只是希望每个表格都在不同的文件中，您可以使用 shell 脚本循环来运行多次执行 pg_dump 命令，每次循环替换表名:

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:

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:这种方法可以扩展为通过 psql 运行查询并将结果提供给循环而不是硬编码列表来动态获取表列表:

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:

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 将一次遍历它们.

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.

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

相关推荐