Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Postgres already answered this for you: ERROR:must be owner of databasexx_db_name_xx.

You need to ALTER the DATABASE and SET 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.

share|improve this answer
    
Hi Thankyou for the details, I am trying to using psql and get it it done something like cat xxx.out | psql -d "xx_db_name_xx" -U "db_user" and its failing. –  Trip Jan 11 '12 at 20:25
    
@Trip If you haven't already done so you should probably spend some quality time with the Backup & Restore section of the Postgres documentation –  voretaq7 Jan 11 '12 at 20:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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