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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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