I have a Postgresql database with 3 Schemas (2 user defined and 1 public).
I have script that takes backup of the user defined schema's and then drops the schema's.
Now after doing some operations I want to restore the user defined schemas from the schema backup(schema_backup.out).
When I execute
cat schema_backup.out | psql xx_db_name_xx
I am getting the following error message
ERROR: permission denied for database xx_db_name_xx
ERROR: schema "aaaa" does not exist
ERROR: permission denied for database xx_db_name_xx
ERROR: schema "bbbb" does not exist
ERROR: must be owner of database xx_db_name_xx
When I execute the same command as Admin user its executing just fine.
But, when I execute as a 'xxxxxx' user its failing.
Can someone guide me, what permissions/rights should be added to 'xxxxxx' to restore the schema?
Best Answer
Postgres already answered this for you:
ERROR:
must be owner of database
xx_db_name_xx
.You need to
ALTER
theDATABASE
andSET OWNER
to be the user you're trying to make schema changes as.(See the Postgres manual for information on the
ALTER DATABASE
SQL Statement)Generally it's easier to restore databases as the Postgres superuser - This avoids any permissions issues, and normally ownership/permissions are restored when you restore the database.