2

I would like to back up, and then restore, the contents of an entire postgres installtion, including all roles, all databases, everything.

During the restore, the target postgres installation shall be entirely replaced, so that its state is entirely as specified in the backup, no matter what's on the target postgres installation currently.

Existing answers include this and this, but none of them meets my requirements because

  • They use pg_dump, forcing me to list the databases manually. I don't want that. I want all databases.
  • They suggest pg_dumpall + psql, which doesn't work if the target installation already has (some part of) the tables; typical errors include ERROR: role "myuser" cannot be dropped because some objects depend on it, as a result the table is not dropped, and as a result the eventual COPY command importing data fails with e.g. ERROR: duplicate key value violates unique constraint.
  • They suggest copying on-disk data files, which doesn't work when you want to backup e.g. from version 9.5 and restore into version 9.6.

How can you backup, and restore, everything in a postgres installation, with one command each?

Community
  • 1
  • 1
nh2
  • 24,526
  • 11
  • 79
  • 128
  • I recommend barman here is one tutorial for centos https://www.digitalocean.com/community/tutorials/how-to-back-up-restore-and-migrate-postgresql-databases-with-barman-on-centos-7 – Abdel P. Jan 16 '17 at 01:14
  • While barman seems great for a production online backup system of multiple servers, I just want to back up my DB to a file, without setting up a whole standalone backup server solution. – nh2 Jan 16 '17 at 01:26
  • I think you should try this question on http://dba.stackexchange.com/ you may get more appropriate attention on your specific request. – Jorge Campos Jan 16 '17 at 01:43
  • so you want to backup 9.5 database and restore it to 9.6 – Vao Tsun Jan 16 '17 at 08:55

0 Answers0