I'm trying to import a schema-dump into PostgreSQL with 'psql -U username -W dbname < migrations/schema.psql', the Schema is imported partly, but the console throws errors like "ERROR: permission denied for relation table_name" and "ERROR: relation table_name does not exist".
I've created the database like this: "createdb -O username dbname"
There are only 7 tables to import, and it breaks with just importing 3 of them.
Anybody a hint, what to do?
If the backup was in the "custom" format (-Fc), you could use pg_restore instead so you can tell it to not apply ownership changes:
pg_restore -U username -W --no-owner --dbname=dbname migrations/schema.psql
All the objects will created with "username" as the owner.
Barring that, try to grep out the ALTER OWNER and SET SESSION AUTHORIZATION commands into new file (or through send grep output via pipe to psql). Those commands should always be on a single line in the plain-text output format.
Sometimes this kind of problem is caused by case-sensitivity issues. PostgreSQL folds to lowercase all unquoted identifiers; if the tables are created with quoted names containing uppercase letters, later commands that don't quote the names may fail to find the table.
The permission errors may be related to the same thing, or it may be something else entirely. Hard to tell without seeing the failing commands.
As I work on my websites, I get that error all the time because I'll be creating a table as me, table that needs to be accessed by the Apache/PHP user that connects later.
There is a table named pg_class that defines your tables. This includes a column named relowner. Changing that with the correct number will give you the correct ownership.
I have details on this page:
http://linux.m2osw.com/table_owner_in_postgresql
The ALTER OWNER ... may be a better solution that does the same thing, although in older versions of PostgreSQL it did not exist!
