1

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.

I've also tried restarting postgresql after attempting the restore...it's a no go.

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?

EDIT 2

I've also tried 

 psql -f prod.sql -U postgres -h 1.1.1.1

but it still didn't work As a test I tried restoring the dump file on another new server... same results. So I checked and saw that in the dump file, there is no CREATE DATABASE command. I'm thinking that the original command to dump the file is incorrect...

Improve this question
4
  • Should you explicitly provide the host IP address like this? psql -U postgres -h 1.1.1.1 -d databasename
    – difley
    Commented Jun 16, 2014 at 20:42
  • @difley - thanks for the comment. I've tried that too. I'll update the post. but I also found some other interesting stuff. check out edit 1.
    – dot
    Commented Jun 16, 2014 at 20:45
  • 1
    In your command for restoring, you don't specify which DB to use. In this case, psql connects to the database named as the user, in this case postgres. Your objects are created there.
    – András Váczi
    Commented Jun 16, 2014 at 20:59
  • dezso, i just checked and it looks like you're absolutely right. It's created everything under the postgres database. so since there is no CREATE DATABASE command in the dump file, should I create the database manually, or perhaps it's better to fix the dump command so it does create the db for me??
    – dot
    Commented Jun 17, 2014 at 17:04

1 Answer 1

Reset to default
0

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
Improve this answer

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.