CODEWITHSUNDEEP
postgresqlpostgis
Jahkobi Digital
Jahkobi Digital

Reputation: 728

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

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:

Upvotes: 3

Views: 4033

Answers (1)

bma
bma

Reputation: 9796

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

Upvotes: 11

Related Questions