Hera
Hera

Reputation: 21

schema does not exist error when using pg_dump and psql

I am trying to migrate files over from one remote database- Scratxh to another remote database. I am using pg_dump and psql to download an .sql file then using psql to recreate the table in the new database - SourceData. I want to copy the table only. I used -t to indicate this, but I still get these errors :

ERROR schema public does not exist ERROR permission denied to set session authorization.

These are the commands I used.

pg_dump -t table -d Scratch -U me -h host.com > table.sql

psql -d SourceData -U me -h host.com < table.sql

I know that psql command uses the .sql text file to recreate the table, so I tried editing this file to get rid of any mentions of the schema 'public'.

It didn't help. I got the same error.

Has anyone else encountered this?

Upvotes: 2

Views: 2682

Answers (1)

user3517317
user3517317

Reputation: 379

It is not clear from your comment when the error happens. I will assume it happens on the second command. In that case, the first error shown might be because the second database is not ready to receive the data, i.e.: the SQL contains INSERT statement to a table that doesn't exist yet in SourceData.

You need to create the table in the new database before being able to import data into it.

If you pg_dump the entire database, you would probably not encounter this exact problem.

Upvotes: 1

Related Questions