From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Egor Duda <egor.duda@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: deduplicating backup of multiple pg_dump dumps
Date: Mon, 29 Jan 2018 15:03:40 +0100
Message-ID: <1517234620.2622.41.camel@cybertec.at> (raw)
In-Reply-To: <5f4d2bdd-e0b6-35e7-501d-e37e5e41a92f@gmail.com>
References: <5f4d2bdd-e0b6-35e7-501d-e37e5e41a92f@gmail.com>
Egor Duda wrote:
> I've recently tried to use borg backup (https://borgbackup.readthedocs.io/) to store multiple
> PostgreSQL database dumps, and encountered a problem. Due to nondeterministic nature of pg_dump it
> reorders data tables rows on each invocation, which breaks borg backup chunking and deduplication
> algorithm.
>
> This means that each next dump in backup almost never reuses data from previous dumps, and so it's
> not possible to store multiple database dumps as efficiently as possible.
>
> I wonder if there's any way to force pg_dump use some predictable ordering of data rows (for
> example, by primary key, where possible) to make dumps more uniform, similar to mysqldump
> --order-by-primary option?
There is no such option.
I think you would be better off with physical backups using "pg_basebackup" if you
want to deduplicate, at least if deduplication is on the block level.
Yours,
Laurenz Albe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, egor.duda@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: deduplicating backup of multiple pg_dump dumps
In-Reply-To: <1517234620.2622.41.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox