1

I have a backup and WAL files, I like update my backup with WAL files. Anyone know how to do it?

Thanks

  • Are you asking how to combine an existing basebackup and WAL segments to do a Point in Time Recovery? Or are you trying to combine a pg_dump file and WAL segments? – Kassandry Sep 14 '15 at 22:49
  • @Kassandry I ask how to combine an existing basebackup and WAL segments to do a Point in Time Recovery. I have a DB and every day I make a backup (pg_dump).Would I like to incremental backup and restore when developing would combine backup via pg_dump and WAL files. – Marcos Sep 15 '15 at 14:22
3

You cannot restore a pg_dump (via psql or pg_restore) then update it with WAL archives. It just isn't possible, sorry.

WAL archives are block-level records of changes to individual relation files. They only make sense when applied to an identical filesystem-level copy of the original database as created from a pg_basebackup, via rsync, etc.

If you try to apply WAL archives to a DB that's restored from a pg_dump they'll be rejected because as far as PostgreSQL is concerned they're different, unrelated databases. At the block/filesystem level they are; the blocks are different, the relation IDs are different, etc. The WAL archives make no sense when applied to this database, even though at the logical level the row data looks the same.

You must use a pg_basebackup copy to restore WAL, and it only works if you have a complete and uninterrupted sequence of WAL from the moment the base backup was taken until the final segment.

  • Thanks @Craig Ringer. I have a backup (made pg_dump) and would like restore by incremental backup. Do you know how I do it? – Marcos Sep 15 '15 at 14:28
  • 1
    @Marcos what exactly do you mean by "restore by incremental backup"? If you want to restore a pg_dump backup then apply wal archives on top to update it, you can't. Not possible. Like fuelling your petrol car with electricity. Will not work. – Craig Ringer Sep 15 '15 at 14:34
  • Thanks @Craig Ringer. Is there a way to make incremental backup in postgresql, that can be restored on another server? – Marcos Sep 15 '15 at 14:38
  • @Marcos Yes. Use pg_basebackup then WAL archiving. Read the PITR and continuous archiving chapter of the manual. There is not currently any feature for incremental or differential backups via pg_dump. Some differential support is being added to pg_basebackup at the moment but isn't ready yet. – Craig Ringer Sep 15 '15 at 14:41
  • sorry. My backup generated by pg_dump have 200MB everyday. The directory with logs (WAL) has 8GB, 4GB everyday will be generated logs (16MB every 5 minutes), it is advantageous to keep the full backup everyday or implement incremental backup? Thanks! @Craig Ringer. – Marcos Sep 15 '15 at 17:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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