Message-ID: <1517234620.2622.41.camel@cybertec.at>
Subject: Re: deduplicating backup of multiple pg_dump dumps
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Egor Duda <egor.duda@gmail.com>, pgsql-admin@lists.postgresql.org
Date: Mon, 29 Jan 2018 15:03:40 +0100
In-Reply-To: <5f4d2bdd-e0b6-35e7-501d-e37e5e41a92f@gmail.com>
References: <5f4d2bdd-e0b6-35e7-501d-e37e5e41a92f@gmail.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

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