I have just installed postgres 9.1.6 on a local Ubuntu server. Now I'm trying to restore a database dump from a database on Heroku. The local database is setup like this:
sudo -u postgres psql -c "create user app_user with password 'pass';"
sudo -u postgres psql -c "create database app_production owner app_user;"
Now, when I try to restore the the dump I use the following command:
pg_restore --verbose --schema=public --no-acl --no-owner --jobs=8 --exit-on-error --username=app_user --dbname=app_production /tmp/app_production.dump
Now in psql with \l
to see ownerships I get the following:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------------+-----------+----------+-------------+-------------+-----------------------
app_production | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
As you can see, the ownership of app_production database has now turned from app_user to postgres. I would have expected the owner of the app_production database to still be app_user, so what am I doing wrong?
BTW, The --schema=public
was added, because I was getting a weird error:
"Could not execute query: ERROR: must be owner of extension plpgsql"
Another thing is, that the owner of the dump is the user that the database was having on heroku, which would be something like 'jebf473b73bv73v749b7'
--dbname
option. This will output a SQL script containing all the restore commands instead of actually restoring. Then you can look near the beginning of that output for anyALTER DATABASE
command.