trying to restore a production postgresql db into dev box

Question

I'm running the following command to create a dump of all database on my production server:

prodserver:/# pg_dumpall -U postgres -h 1.1.1.1 > prod.sql

on my development box, I also take a backup by doing:

devserver:/# pg_dumpall -U postgres -h 1.1.1.1 > dev.sql

Then I try to restore the production database on to development like so:

devserver:/# psql -f prod.sql -U postgres

It runs with no errors. And it appears to be creating tables /import data. I know because if I run it a second time, I get messages that certain data already exists / relationships already exist.

But when I log in like so:

psql -U postgres -d databasename

the data looks the same as the old dev data. I tried the following with the production data supposedly restored:

select count(*) from widgets; 

I compare that with the count returned when I'm using the development database and they are the same. It looks like the database wasn't restored properly.

Do I have to:

• a) delete the dev databsae before I restore production?
• b) restart postgresql?

I've been using http://www.postgresql.org/docs/9.1/static/app-pg-dumpall.html as a guide... I was referring to the examples at the bottom of the page.

Any suggestions on what I might be doing wrong would be appreciated.

Thanks.

EDIT 1

I also checked the following:

I opened up the prod.sql file and found insert statements like:

INSERT INTO widgets_to_wiget_groups VALUES (363, 15);

And then I did a select statement on the database to see if I had this entry in the table but I don't... maybe it's restoring it but under the context of another user?

Answer 1

I had to run

CREATE DATABASE databasename;

to manually create the db, and then changed the restore command to:

devserver:/# psql -f prod.sql -U postgres -d databasename