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?
5 Answers
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>
If you want to DUMP your inserts into an .sql file:
cdto the location which you want to.sqlfile to be locatedpg_dump --column-inserts --data-only --table=<table> <database> > my_dump.sql
Note the > my_dump.sql command. This will put everything into a sql file named my_dump
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
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
for postgres 12, this worked for me:
pg_dump -d <database> -t <table> > DATA.dump
