PostgreSQL - dump each table into a different file

Question

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

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.

Pinero

You need to explain why you want 90 different files - export to MySQL, partial backup? If you're trying to do a backup/export then IMSoP's answer won't guarantee the same snapshot for each table. – Brandes

@RichardHuxton Good point, I hadn't thought of the non-atomicity. I suppose you could instead create a single "custom" backup from the DB and then extract the individual tables from that using pg_restore. – Cy

@RichardHuxton this is often required if you want to setup your code repository with individual files so you dont have to manually split items off the full dump as you make changes and save them to individual files. – Lyric

Answer 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:

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:

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.

Cy

I will be using a wildcard in table name parameter. Hard coding 90 table names! I would be an old man by then. :) – Pinero

@HasanIqbalAnik Alternatively you can query the DB from shell script for the list of the tables and then loop through the list. – Glebe

@HasanIqbalAnik I thought you might say that, which is why I added the caveat at the beginning of the answer. I've edited my answer to query the DB for the list first, as Igor suggests. – Cy

WARNING: This approach will store each table in a state of a different time, since the script will take time for the loop. This can lead to an inconsistent state, if there are changes in the database during the backup. So make sure, the database is not altered during the backup! – Filmer

Answer 2

Since version 9.1 of PostgreSQL (Sept. 2011), one can use the directory format output when doing backups

and 2 versions/2 years after (PostgreSQL 9.3), the --jobs/-j makes it even more efficient to backup every single objects in parallel

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.

if you want the data, you shall not use -s but rather -a (data-only) or no option to have schema+data

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) :

pg_dump -Fd -f dbName_objects -j 10 -t 'thr_*' -U userName dbName

(you can also use the -a/-s if you want the data or the schema of the objects)

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

each file is named after it's object number, and you can retrieve the list with the following pg_restore command:

pg_restore --list -Fd dbName_objects/ | grep 'TABLE DATA'

in order to have each file not compressed (in raw SQL)

pg_dump --data-only --compress=0 --format=directory --file=dbName_objects --jobs=10 --table='thr_*' --username=userName --dbname=dbName

Beastings

Running this gives me toc.dat only, with no other files in the directory... – Archaeozoic

which command did you run exactly? are the tables in a specific path? Please give more info of the context – Beastings

I tried both commands but I am interested in the latter, i.e., the one without compression to get plain SQL files. I didn't use the table parameter because I want to dump all DB objects --schema-only. – Archaeozoic

I think I found the problem: --schema-only won't produce several files, but one file containing the database definition... – Archaeozoic

Answer 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.

Anselmi

Nice. You just need to include the schema name somewhere. – Globefish

I had to make a few tweaks, but this script works great. Thank you. – Spoiler

Answer 4

This bash script will do a 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 $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

Filmer