CODEWITHSUNDEEP
sqldatabasepostgresqlsql-insertpg-dump
Elitmiar
Elitmiar

Reputation: 36829

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

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?

Upvotes: 195

Views: 248277

Answers (7)

Isuru Sandunika
Isuru Sandunika

Reputation: 1

To get the data set as insert commands without root user - using different user

pg_dump -U -a --column-inserts --data-only > backup.sql

data will be backed up into a backup.sql file

Upvotes: -1

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

For example, you can export only the data of apple database of the user(role) john to backup.sql as shown below. *My answer explains how to export both schema and data:

pg_dump -U john -a apple > backup.sql

Or:

pg_dump -U john --data-only apple > backup.sql

And, you can export only the data of apple database of the user(role) john to backup.sql with only INSERT statement which has column names as shown below:

pg_dump -U john -a --column-inserts apple > backup.sql

And, you can export only the data of apple database of the user(role) john to backup.sql with only INSERT statement which doesn't have column names as shown below:

pg_dump -U john -a --inserts apple > backup.sql

And, you can export only the data of the specific tables person and animal of apple database of the user(role) john to backup.sql with only INSERT statement which has column names as shown below. *Specifying multiple tables are available in one command:

pg_dump -U john -a --column-inserts -t person -t animal apple > backup.sql

Or:

pg_dump -U john -a --column-inserts --table=person --table=animal apple > backup.sql

Or:

pg_dump -U john -a --column-inserts --table person --table animal apple > backup.sql

Lastly for example, you export only the data of the specific tables person and animal of apple database of the user(role) john to backup.sql with only INSERT statement which has column names as shown below:

pg_dump -U john -a --column-inserts -t person -t animal apple > backup.sql

Then, you can import backup.sql to orange database of the user(role) john with psql which must be used to import non-archive files as shown below. *You have to create orange database and the schema otherwise there is error and my answer explains how to create a database and my answer explains how to import schema and data:

psql -U john -f backup.sql orange

And, for example, you export and archive(-Fc) only the data of the specific tables person and animal of apple database of the user(role) john to backup.sql with only INSERT statement which has column names as shown below. *My answer explains how to export and archive a database:

pg_dump -U john -Fc -a --column-inserts -t person -t animal apple > backup.sql

Then, you can import archive backup.sql to orange database of the user(role) john with pg_restore which must be used to import archive files as shown below. *You have to create orange database and the schema otherwise there is error and my answer explains how to create a database and my answer explains how to import archive files:

pg_restore -U john -d orange < backup.sql

Upvotes: 0

James111
James111

Reputation: 15903

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

  1. cd to the location where you want the .sql file to be located
  2. 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

Upvotes: 61

suhailvs
suhailvs

Reputation: 21680

for postgres 12, this worked for me:

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

Upvotes: 4

Pipo
Pipo

Reputation: 5073

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

Upvotes: 3

Lenon Tolfo
Lenon Tolfo

Reputation: 387

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

Upvotes: 9

psmears
psmears

Reputation: 27990

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>

Upvotes: 371

Related Questions