Hacker News Reader

I’ve never successfully backed up a single database and restored it on a clean instance of PostgreSQL. It always fails on roles not existing.

If I create all the roles up front then it works. But it’s a hassle compared to SQLServer where I can backup, restore, create a user and link him to the database user. And it’s done.

View on HN

Are you using pg_dump or pg_dumpall? The latter will backup the global objects (like roles) as well.

https://www.postgresql.org/docs/current/app-pg-dumpall.html

An alternative, which I think I borrowed from how Heroku does/did dumps, is 'pg_dump --no-acl --no-owner'.

There is an option to omit roles on backup `--no-owner` https://www.postgresql.org/docs/current/app-pgdump.html

Reply on news.ycombinator.com