PostgreSQL – How to Load Data-Only Dump Created with pg_dump

pg-dumppgadminpostgresql-9.4

I have two PostgreSQL databases on my development server, one I use for development and another for production where I test my web applications. When I was using an earlier version of PostgreSQL I was able to easily create a TAR dump file of my prod database in pgAdmin and use that same TAR file to restore my development database after selection the option to clean the database first. A while back I upgraded pgAdmin to the version (1.20.0 I think) that is supposed to work with PostgreSQL 9.4. For whatever reason I cannot restore files in that version. I have tried TAR and SQL but the restore button is always protected.

I decided to use pg_dump and create a SQL file with only insert statements for my production database data. What I want to do is replicate what I was able to do in pgAdmin: Clean my development database and load my pg_dump SQL file into it.

I've search but so far I have not found any examples of how to do this in pgAdmin or in the terminal.

Best Answer

If you are happy with recreating the tables upon restore (and I see no reason why one should not be happy :), then you could use the --clean option:

-c --clean

Output commands to clean (drop) database objects prior to outputting the commands for creating them. (Unless --if-exists is also specified, restore might generate some harmless error messages, if any objects were not present in the destination database.)

This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore.

Use it with pg_dump when creating plaintext dumps, or with pg_restore when using any other format. In both cases, you will need a full dump, as a data-only one would lack the information for creating the tables.

Note: don't use INSERT statements in your dumps (--inserts does this in pg_dump). It is almost invariably slower than relying on the default COPY functionality.

Related Question