25

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?

3 Answers 3

Reset to default
47

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.

5
  • 1
    I am moving from 8.3 to 8.4. I'll change my backup scripts to use your more comprehensive approach. Thanks for the response.
    – Dean Schulze
    Commented Aug 21, 2010 at 16:36
  • 2
    I would add verbose flag(-v) to pg_restore for large database. pg_restore -a -v -d dbname -Fc full.dump
    – green0range
    Commented Mar 27, 2014 at 20:50
  • 2
    What's the point of dumping the schema twice (in plaintext and compressed) if you're going to pg_restore -a anyway? Why not pg_dump -Fc -a -v -f data.dump, for example? Or skip the schema step altogether, and just do globals in one step and everything else in the other?
    – Air
    Commented Apr 13, 2015 at 16:56
  • 1
    In the restore block, on the second line, I assume that filename should be 'db-schema.sql'?
    – Dave Munger
    Commented Jan 24, 2016 at 7:13
  • Best answer so far, was facing many issues to restore my database with incomplete answers.
    – Kenny Aires
    Commented Sep 5, 2021 at 20:50
6

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

Don't forget to install the extensions you use.

1
  • 2
    Many thanks man. It was the issue i struggled with for hours. Didn't even know about postgis existance, i'm new to postgre world. Just installed postgis from repo and all worked.
    – Doctor Coder
    Commented Aug 11, 2017 at 9:22
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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.