Postgres / Postgis - Dump and restore to new server with different user

Question

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

Answer 1

• 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.