0

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)
David Berg
  • 1,958
  • 1
  • 21
  • 37

1 Answers1

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).

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • pg_restore: options -d/--dbname and -f/--file cannot be used together – David Berg Apr 03 '13 at 08:40
  • @DavidBerg My mistake, fixed – Craig Ringer Apr 03 '13 at 08:41
  • 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 Apr 03 '13 at 08:54
  • 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". – David Berg Apr 03 '13 at 09:04
  • @DavidBerg That's a table name, nothing to do with the database name. Was the new database empty before the restore? – Craig Ringer Apr 03 '13 at 09:26
  • Yes, I truncated all the tables (is it anyway of doing this faster than one table at the time?). You mean that 'auth_group' is a table? I meant the 'A' after 'auth_group'. – David Berg Apr 03 '13 at 09:35
  • @DavidBerg You're restoring a whole DB, not just the data, so *drop* and *recreate* it. This will throw away all the data in the DB. If you don't want to do that you need to use a data-only dump instead. And yes, you can truncate all the tables at once, see how the `TRUNCATE` command takes a comma-separated list of table names? – Craig Ringer Apr 03 '13 at 09:43