PostgreSQL - flushes each table to a different file

Question

I need to extract SQL files from several PostgreSQL database tables. This is what I came up with:

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

However, as you can see, all tables starting with the thr prefix are exported to one unified file ( db_dump.sql ). I have almost 90 tables for extracting SQL, so I need to store the data in separate files.

How can i do this? Thanks in advance.

Answer 1

If you are happy to hard-code the list of tables, but just want each of them to be in a different file, you can use the script loop to run the pg_dump several times, substituting the name of each loop in the table:

 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 expanded to dynamically obtain a list of tables by running a query through psql and submitting the results in a loop, rather than as hard code:

 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 displays the results without a header or footer; since only one column is selected, each line of the output file written using $(command) will have a table name, and your shell will cycle through them one at a time.

Answer 2

This bash script will backup with one file per table:

 #!/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 

Answer 3

(not enough reputation to comment on the correct post) I used your script with some corrections and some changes for my own use, may be useful 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 to the pg_dumb command, and I added a -w for the automatic script, it’s better not to have the psw prompt, I think I created ~ / .pgpass with my password in it, I also gave the user the command to find out which password to output to .pgpass) I hope this someday helps someone.