Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eg9zr-0000JX-En for pgsql-admin@arkaria.postgresql.org; Mon, 29 Jan 2018 14:01:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eg9zq-0003F6-F9 for pgsql-admin@arkaria.postgresql.org; Mon, 29 Jan 2018 14:01:18 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eg9zq-0003Ew-4f for pgsql-admin@lists.postgresql.org; Mon, 29 Jan 2018 14:01:18 +0000 Received: from mail-lf0-x234.google.com ([2a00:1450:4010:c07::234]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eg9zj-00088n-AA for pgsql-admin@lists.postgresql.org; Mon, 29 Jan 2018 14:01:16 +0000 Received: by mail-lf0-x234.google.com with SMTP id x196so10100974lfd.12 for ; Mon, 29 Jan 2018 06:01:10 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=to:from:subject:message-id:date:user-agent:mime-version :content-language:content-transfer-encoding; bh=5KgSoQIs6vlVJFxHilvzjefIHV7QdZj5Ovye4a0BhlA=; b=hKa050ug9weQtj9ilFLLAZoTjB/FgoLztYa6fIMxEraAyTmkMtwJ0lRjOrA3SWmGyE FZsKBtHcxXfJGK3bxhSOYtqdam8g0H0oAGk0jqyQrM4c+MvxX+VQeia+39N6/OPk1ROX A7PywMf+o3jnai/4wpwEBOtcJYnn3dcagkzm+Fnddmi2mWz6GwvkVKlGglqNxHUAkI7N s9s20u8EOX3XMGQ0ENLGdCYVX0V4iJXy3HORKPVjwnMYj4V04gHNnUWQmzoD32AiyETJ jhW9PO0LhuOvivbg2af0Xtt1dy+ky7u9drTHm6tIYRfb1oMcE/uM+rXVcJnwK03CruZE nZcA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:to:from:subject:message-id:date:user-agent :mime-version:content-language:content-transfer-encoding; bh=5KgSoQIs6vlVJFxHilvzjefIHV7QdZj5Ovye4a0BhlA=; b=luqWU4GSCt6PcihZyEKCJAVaGeDxp1tClriaGLX5oABYZr3Y5+AECez42UbIUMVbxE eq/1qJjnY5GSrLsWOUoMjsr0yBWLDd7v/FNz/xgT6hyEk8e8TFfa0C//8/zez4Bt20gn UuyW3m5dJP9/zGxSYrVbbznEjO1aHeWqYtjw0ZQ/qC3L7Gx5ZIU8Np8oHto1AKOhZ4RV xnv+kbqFAuBqYYa/G5uO/yv1X53/1Kh/oQGQ1d4U/xlUD8nmAd5XomuZMSiKKAWiis7c eyGTIDy7TdH3F/IRdcmnZMIPY+hTGvrulzLKy67eESs8RE/Qil8TW18JK6MvFuBHH9JV j1+g== X-Gm-Message-State: AKwxytfHBFHyiP+defrI5qfZkuHL2j/+GbNFd1hnFgVtuzHRmpIL8Gle ha5p8I1Z627xgGAC2qABuIHPUobk X-Google-Smtp-Source: AH8x225H5zq38ori4287YlHoNIOm6Zpx1UBLoyHNre25HOKLmVASVWq8xbatwH1xQSQ8iRdEagtp7Q== X-Received: by 10.46.8.1 with SMTP id 1mr7285189lji.147.1517234468083; Mon, 29 Jan 2018 06:01:08 -0800 (PST) Received: from [192.168.177.35] (nat.corpit.ru. [86.62.121.226]) by smtp.gmail.com with ESMTPSA id l74sm2945015lfi.1.2018.01.29.06.01.07 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 29 Jan 2018 06:01:07 -0800 (PST) To: pgsql-admin@lists.postgresql.org From: Egor Duda Subject: deduplicating backup of multiple pg_dump dumps Message-ID: <5f4d2bdd-e0b6-35e7-501d-e37e5e41a92f@gmail.com> Date: Mon, 29 Jan 2018 17:01:06 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.5.2 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Language: ru Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hello! 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? Egor.