Using pg_dump to only get insert statements from one table within database

Question

I'm looking for a way to get all rows as INSERT statements from one specific table within a database using pg_dump in PostgreSQL.

E.g., I have table A and all rows in table A I need as INSERT statements, it should also dump those statements to a file.

Is this possible?

score 342 · Accepted Answer · edited Dec 05 '12 at 12:56

342

if version < 8.4.0

pg_dump -D -t <table> <database>

Add -a before the -t if you only want the INSERTs, without the CREATE TABLE etc to set up the table in the first place.

version >= 8.4.0

pg_dump --column-inserts --data-only --table=<table> <database>

edited Dec 05 '12 at 12:56

dexter

13,365
5
39
56

answered May 18 '10 at 14:14

psmears

26,070
4
40
48

70

The -d and -D options were removed from PostgreSQL 8.4 (see 8.4.0 release notes). You must now use the "long" names: pg_dump --column-inserts --data-only --table=

– Matthew Wood May 18 '10 at 14:49
1

The `-d`, `-a`, and `-t` short versions are still present, though. Checked with PG11. – demisx Aug 23 '19 at 15:47
`--inserts` is another option; it restores slightly faster but can't tolerate column order changes – Andy Mar 29 '20 at 05:46
You can check postgres version by running the query `SELECT version();` – Tms91 Feb 10 '22 at 09:49

James111 · Answer 2 · 2023-01-30T04:28:24.563

57

If you want to DUMP your inserts into an .sql file:

cd to the location where you want the .sql file to be located
Run the following command: pg_dump --column-inserts --data-only --table=<table> <database> > my_dump.sql

Note that the > my_dump.sql part of this command will put everything into an .sql file named my_dump

edited Jan 30 '23 at 04:28

answered Jan 20 '17 at 01:51

James111

15,378
15
78
121

score 8 · Answer 3 · answered Mar 26 '19 at 11:32

8

just in case you are using a remote access and want to dump all database data, you can use:

pg_dump -a -h your_host -U your_user -W -Fc your_database > DATA.dump

it will create a dump with all database data and use

pg_restore -a -h your_host -U your_user -W -Fc your_database < DATA.dump

to insert the same data in your data base considering you have the same structure

answered Mar 26 '19 at 11:32

Lenon Tolfo

359
4
9

For pg_restore, I had to add a -d for database: -d your_database < mydump.dump – Harlin Dec 22 '22 at 17:20

Pipo · Answer 4 · 2021-01-13T21:34:40.027

Put into a script I like something like that:

#!/bin/bash
set -o xtrace # remove me after debug
TABLE=some_table_name
DB_NAME=prod_database

BASE_DIR=/var/backups/someDir
LOCATION="${BASE_DIR}/myApp_$(date +%Y%m%d_%H%M%S)"
FNAME="${LOCATION}_${DB_NAME}_${TABLE}.sql"

# Create backups directory if not exists
if [[ ! -e $BASE_DIR ]];then
       mkdir $BASE_DIR
       chown -R postgres:postgres $BASE_DIR
fi

sudo -H -u postgres pg_dump --column-inserts --data-only --table=$TABLE $DB_NAME > $FNAME
sudo gzip $FNAME

score 3 · Answer 5 · answered Nov 12 '21 at 04:16

3

for postgres 12, this worked for me:

pg_dump -d <database> -t <table> > DATA.dump

answered Nov 12 '21 at 04:16

suhailvs

20,182
14
100
98

Using pg_dump to only get insert statements from one table within database

5 Answers5

Linked