PostgreSQL: How do I backup database with name A and load it to database with name B?

Question

I have two databases on the same server. One named A and one named B. Booth databases have the same structure. I want to empty database B and load it with data from database A. Which is the best way to do this?

I have tried to take backup of database A in plain format. Then open the resulting sql-file and replace every occurence of 'A' with 'B' and then run the sql-script. This worked but I think it should be an easier way to move data from one database to another. Is it?

I use 'pgAdmin III' as my tool, but this is not necessary.

This is my first post here, hope the question is relevant and structured well enough. I tried google first but found it hard to find anyone with the same question.

Thanks in advance! /David

SOLUTION: After help from Craig, this is how I did it

pg_dump -Fc -a -f a.dbbackup A

psql -c 'TRUNCATE table1, table2, ..., tableX CASCADE'

pg_restore dblive.backup -d B -c (not sure if -c was necessary)

Craig Ringer · Accepted Answer · 2013-04-03 08:41:13Z

1

Backup:

pg_dump -Fc -f a.dbbackup

Restore:

psql -c 'CREATE DATABASE b;'
pg_restore --dbname b a.dbbackup

Use the -U, -h etc options as required to connect to the correct host as the correct user with permissions to dump, create and restore the DB. See the docs for psql, pg_dump and pg_restore for more info (they all take the same options for connection control).

edited Apr 3, 2013 at 8:41

answered Apr 3, 2013 at 7:30

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

David Berg Over a year ago

pg_restore: options -d/--dbname and -f/--file cannot be used together

Craig Ringer Over a year ago

@DavidBerg My mistake, fixed

David Berg Over a year ago

No flag was needed for the inputfile. But I don´t know if I can remove the db completly and recreate it like in your example (I asume that is what you mean I should do before the create statement). I have also tried with the flag '-c' to clean the db first, but it does not seem to work because I get 'duplicate key value' errors anyway.

David Berg Over a year ago

I tried: "pg_restore dblive.backup -U user -h localhost -d B -a -c" but I still get 'duplicate key value' errors and it seems like it still tries to change database 'A'. This is from the log: "Error from TOC entry 2029; 0 25266 TABLE DATA auth_group A".

Craig Ringer Over a year ago

@DavidBerg That's a table name, nothing to do with the database name. Was the new database empty before the restore?

|

Collectives™ on Stack Overflow

PostgreSQL: How do I backup database with name A and load it to database with name B?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related