1

I noticed that PGDATA folder changes during an upgrade (which is normal as I use the pg_upgrade --links command), but I don't know the best way to backup up my cluster before the upgrade in order to restore it in case of failure.

AFAIK, the several ways to backup my cluster are:

  • pg_dumpall
  • pg_basebackupp
  • tar

I tested pg_dumpall and pg_basebackup, but I've seen that after doing an upgrade and restore, the size and files of PGDATA changed (like the WAL folder for example).

Is this normal and which method is recommended to backup/restore cluster ?

2 Answers 2

1

AFAIK, the several ways to backup my cluster is : pg_dumpall, pg_basebackupp or tar.

Also, filesystem instantaneous snapshots if your filesystem/OS has this feature.

For small/medium databases it's probably OK to use any of these methods.

Choose the method which you feel most comfortable with.

With tar/cp backups remember to shutdown cluster before doing the backup.

Logical backups (pg_dump + pg_dumpall) have extra advantage of being portable (to other Pg versions). But the restore is a multi-step process, with Fs/directory it's just a plain replacement of contents.

I tested pg_dumpall and pg_basebackup but I've seen that after doing an upgrade and restore, the size and files of PGDATA did changed (like WAL folder for example). Is it normal

Yes, completely normal. I mean - if the difference is 100-fold then probably something fishy is going on, but it's normal that after dump/restore the on-disk size is smaller.

0

How do you normally backup? The best way may be to do it the way you are already accustomed to.

The problem with pg_dumpall is that, unless you take steps to forbid other connections, the database can keep on changing and those changes won't be in the dump file. That is also the problem with pg_basebackup, unless you couple it with a WAL archive.

The problem with tar is that you have to take the database down in order to use it, and that much down time might be undesirable. (Unless you are using tar as part of an online backup, but then that is essentially the same as pg_basebackup and has the same problem)

I routinely use pg_basebackup with a WAL archive. So then it is very simple, you just shutdown the database cleanly, and verify that the archival of the last WAL file succeeded. To minimize how long it takes to restore, you might want to take a fresh pg_basebackup shortly before the attempted upgrade, so that less WAL needs to replayed in the unlikely event you need to restore.

1
  • Thank you for the answer. Normally I backup clusters using pg_dumpall before upgrade, but I was wondering in case of rollback if this was the good backup method, as somes files (in PGDATA) (not just databases) will change when upgrading. As it is recommended in PostgreSQL doc, I need to restore a backup before restart the old cluster (in case rollback), but for me, using pg_dumpall will just restore databases stored in PGDATA/base but not all other files that has been changed (like in PGDATA/global, ...).
    – Mat_
    Commented Mar 13, 2020 at 11:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.