Should I make logical backupds too from time to time or are physical backups sufficient for recovery in case of emergency in PostgreSQL? What are the advantages of logical backups?

I have heard somewhere that physical backups are not reliable enough without logical ones. But do not know why.

  • 1
    Physical backups are fine. Most bigger installations rely on them. – Laurenz Albe Oct 30 '19 at 14:40

Physical backups can only be restored onto the same version of PostgreSQL running on the same OS and type of hardware. If you are running on obscure hardware or operating system, or ancient versions of PostgreSQL, this can be a problem. If you are not doing those things, it should not be a problem.

  • What constitutes a type of hardware? If it's generic x86_64 then it should run on another x86_64? Motherboard doesn't need to be exactly the same right? – Gherman Oct 30 '19 at 16:22
  • Right, it is not important down the motherboard level. I've never had a problem porting between different Linux x86_64 machines, or virtual machines. You could have a problem with collations if they have different glibc versions, but I generally use C collation, so that isn't a problem I worry about. If you are worried, you can reindex the entire system after a restore, or use the "amcheck" extension to verify the indexes. – jjanes Oct 30 '19 at 17:00

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