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

bma · Accepted Answer · 2014-05-21 14:41:32Z

11

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.

edited May 21, 2014 at 14:41

answered Jul 20, 2013 at 2:09

bma

9,8562 gold badges35 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related