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?

Muhammad Dyas Yaskur
  • 6,914
  • 10
  • 48
  • 73
Dean Schulze
  • 9,633
  • 24
  • 100
  • 165

3 Answers3

46

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.

John P
  • 15,035
  • 4
  • 48
  • 56
  • 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 Aug 21 '10 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 Mar 27 '14 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 Apr 13 '15 at 16:56
  • 1
    In the restore block, on the second line, I assume that filename should be 'db-schema.sql'? – Dave Munger Jan 24 '16 at 07:13
  • Best answer so far, was facing many issues to restore my database with incomplete answers. – Kenny Aires Sep 05 '21 at 20:50
5

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

Don't forget to install the extensions you use.

Ali Davut
  • 660
  • 1
  • 9
  • 16
  • 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 Aug 11 '17 at 09: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.

Dean Schulze
  • 9,633
  • 24
  • 100
  • 165