2

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.

Improve this question
2
  • 1
    Don't use INSERT statements for your dumps (unless you have only a small amount of data). COPY is virtually always (much) faster. Furthermore, as I understand it, you need to use the --clean option of either pg_dump or pg_restore, depending on which dump format you choose. It will throw away your tables before recreating them, so you need to do a full backup (not data-only).
    – András Váczi
    Commented Nov 24, 2015 at 10:47
  • Thanks @dezso. If you post this as an answer to my question. I will check it and post details of what I did.
    – Pamela Cook - LightBe Corp
    Commented Nov 24, 2015 at 16:21

1 Answer 1

Reset to default
5

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.

Improve this answer
1
  • 1
    Thanks so much!! This is what I did: I changed my backups by removing the --inserts clause. The databases reload a whole lot faster. For my purposes I usually want a mirror image of my production database on my development server. I was able to easily use the pg_dump SQL files from my production server to rebuild the production and development databases on my development server. This makes this process a whole lot easier. I guess pg_admin has bit the dust for me other than a quick way to view my databases or quickly drop them.
    – Pamela Cook - LightBe Corp
    Commented Nov 25, 2015 at 14:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.