Hacker News Reader

Comment by philliphaydon | Hacker News Reader

The only thing I want for PostgreSQL is a better backup/restore. SqlServer is so easy. You backup a database and restore it and never have any issues. You connect the users in the instance to the users in the database and away you go.

PostgreSQL will allow a backup and then epic fail on the restore if you don’t have users and roles etc pre defined.

It’s the hardest thing to achieve in PostgreSQL.

View on HN

Yeah, that's because you're using the wrong tool for the job. You are using pg_dump on a single database, or set of databases. Use pg_dumpall: https://www.postgresql.org/docs/current/app-pg-dumpall.html

For an actual backup solution, not one off stuff, use a tool like pgbackrest. It's just way better.

I’m pretty sure I’ve tried dumpall before but honestly cannot remember but you’re right do use dump.

I’ll give dumpall a go today. I do still feel this could be improved in PostgreSQL because there’s sooo many blog posts who suggest dump and none of them work.

> epic fail on the restore if you don’t have users and roles

?? in my lonely research world, the PG backup defaults and restore behavior have always been gentle and predictable. I generally backup per-database, never by cluster

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.

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