Postgres / Postgis - Dump and restore to new server with different user
postgis, postgresql
Solution
- pg_dump with the `--no-owner` flag (see `pg_dump --help`)
- Create the new db with the new owner `CREATE DATABASE foo OWNER pooper;`,
- Load via `psql -U pooper -d database_name -f database_name.sql`.
Problem
I search for a while to find this answer but with no luck. The situation: I have Postgresql currently running on my production environment. I am preparing to scale my database and move it to a large server instance. I made the mistake of setting up the initial database with the postgres user who has all permissions, and I would like the new database to be controlled by a custom user I have created. ie The current database's owner is `postgres`, and I want the new database owner to be `pooper`. To dump, I am running: `pg_dump -d database_name > database_name.sql` To restore on separate machine, I am running: `psql database_name < database_name.sql` If the user is the same, ie both `postgres`, then it will work just fine, but when switching users, my app does not load correctly. Is there a secret to the madness. Nothing stood out to me. My system: - Debian Wheezy - Postgresql 9.1 - Postgis Extension