I'm running the following command to create a dump of all database on my production server:
prodserver:/# pg_dumpall -U postgres -h 126.96.36.199 > prod.sql
on my development box, I also take a backup by doing:
devserver:/# pg_dumpall -U postgres -h 188.8.131.52 > 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.
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?
I've also tried
psql -f prod.sql -U postgres -h 184.108.40.206
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...