Moving a database with pg_dump and psql -U postgres db_name < ... results in "ERROR: relation "table_name" does not exist"

Question

I moved my PostgresQL database from one hard drive to another using

pg_dump -U postgres db_name > db_name.dump

and then

psql -U postgres db_name < db_name.dump

I created the database db_name the same way in both instances. In the new database when I run my Java program with a JPA query (or a JDBC query) I get this error:

"ERROR: relation "table1" does not exist"

The query is:

select count(0) from table1

I know I've got a connection because if I change the password in the connection parameters I get an error.

For some reason in the new PostgresQL instance it thinks that table1 does not exist in the imported schema.

If I change the query to

select count(0) from myschema.table1

Then it complains about permissions:

"ERROR: permission denied for schema myschema"

Why would the permissions be different?

The table table1 exists in myschema because I can see it in the pgAdmin tool. All the rows were imported into the new PostgresQL instance.

When I do a query from Java the combination of pg_dump and psql created a problem.

What do I need to do to solve this issue?

Thanks in advance.

Answer 1

Are you moving to the same version of PostgreSQL? There might be issues if you make a dump with pg_dump 8.3 and try to restore it in Postgresql 8.4. Anyway, assuming that it is the same version try the following:

Dump all global objects, such as users and groups (don't know if they were missing in your dump):

pg_dumpall -g -U postgres > globals.sql

Dump schema of database:

pg_dump -Fp -s -v -f db-schema.sql -U postgres dbname

Dump contents of database:

pg_dump -Fc -v -f full.dump -U postgres dbname

Now restore.

psql -f globals.sql
psql -f schema.sql dbname
pg_restore -a -d dbname -Fc full.dump

That is my $0.02. Hope it helps.

Answer 2

I was able to solve it by changing the database privileges to public CONNECT and the schema privileges for public and postgres = USAGE and CREATE.

My backup scripts apparently didn't preserve the privileges, at least not when moving from 8.3 to 8.4.