3 ms·

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 conne

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.

mistrial9 6y ago
> 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
- philliphaydon 6y ago
  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.
  - jeffdavis 6y ago
    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 https://www.postgresql.org/docs/current/app-pg-dumpall.html
    - stilist 6y ago
      An alternative, which I think I borrowed from how Heroku does/did dumps, is 'pg_dump --no-acl --no-owner'.
  - AlfeG 6y ago
    There is an option to omit roles on backup `--no-owner` https://www.postgresql.org/docs/current/app-pgdump.html https://www.postgresql.org/docs/current/app-pgdump.html
Tostino 6y ago
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 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.
- philliphaydon 6y ago
  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.

mistrial9 6y ago

> 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

philliphaydon 6y ago
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.
- jeffdavis 6y ago
  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 https://www.postgresql.org/docs/current/app-pg-dumpall.html
  - stilist 6y ago
    An alternative, which I think I borrowed from how Heroku does/did dumps, is 'pg_dump --no-acl --no-owner'.
- AlfeG 6y ago
  There is an option to omit roles on backup `--no-owner` https://www.postgresql.org/docs/current/app-pgdump.html https://www.postgresql.org/docs/current/app-pgdump.html

Tostino 6y ago

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

philliphaydon 6y ago
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.