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

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?

postgresqljpajdbc


Solution 1:[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 db-schema.sql dbname
pg_restore -a -d dbname -Fc full.dump

That is my $0.02. Hope it helps.

Solution 2:[2]

I encountered this problem. Then I realized that I forgot to install postgis extension.

Don't forget to install the extensions you use.

Solution 3:[3]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Ali Davut
Solution 3 Dean Schulze

Related Questions