Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p0Y0J-0006xv-BE for pgsql-general@arkaria.postgresql.org; Thu, 01 Dec 2022 01:04:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p0XzJ-0005n5-Ji for pgsql-general@arkaria.postgresql.org; Thu, 01 Dec 2022 01:03:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p0XzJ-0005mw-9B for pgsql-general@lists.postgresql.org; Thu, 01 Dec 2022 01:03:41 +0000 Received: from mail-qk1-x729.google.com ([2607:f8b0:4864:20::729]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p0XzF-0005vE-Ba for pgsql-general@lists.postgresql.org; Thu, 01 Dec 2022 01:03:40 +0000 Received: by mail-qk1-x729.google.com with SMTP id z17so140200qki.11 for ; Wed, 30 Nov 2022 17:03:36 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:content-language:references:to:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=7qYbGwNsfGK8Gq0xRmMqvr6QavbP/KFKQBO5gDTqWf4=; b=ZtTahPb/SUkr+43N1ckG0l3IyzS731nJLj0KRkvUvU+Na57/7Ry0yCSgHenMFNmXfa DPoWw2I2g2AyPg+oHGuH34jpVT/oBGvZOUQVSZj8BA/Hsa5tXKqd9Tlah5B53xWgzMPH e6+XHGGNYkL+pL8LNHL+K2o3KcNCJwh/ezHKIUiCcBvWZMsUUrVJbR/Brji4lhtkU1dp NBCunHWCGA+4CvrC848v29hF1K+nrf0Eg9KaFQvEUaa/Llp77kMxsC679jsBvKY4N0Zr w4rXL82e7gN8Dxvf6FXVzz+lPM4AHyy1uh26gxZ4JnzPkjlMM6C+/Au4FBztNDeN4xK+ QK0g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:content-language:references:to:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=7qYbGwNsfGK8Gq0xRmMqvr6QavbP/KFKQBO5gDTqWf4=; b=icJfJHbOY+yJMY50XwQ+8OLK92FsOBu9/nssDxLKquFNbuXDAwOWUYQCKcBFYU850F wPASSmAUOBpOiaSfC+Q/t1wepNDG/GSP/BqHKfaLazBBSufnejbvjQnomAsw7BI2ukRk aTc1LReEur/RjkOby7I7Pkv6EdAlvFWPehW+7QoR1lgR+cLYNJeXLvon41eqKnqiMmSN lvJe0WP8Y43Xp6pQvl2bRmb1D7ctwtT9A+IaUsvndabNPayzQOX1QTgZhZL/8c8QTOTG 02vhBZlD3YXuj4F6mo9tP3iYUwhVrejdq9JFa2iayT+//YqlkaWVt4h64PGS9l9ex2Kv gmnw== X-Gm-Message-State: ANoB5pm+DJNpWwnGQvTynLzVPzIUFY/drgaIVju3sp4ldmcYVGZ1PJGD Cem4yIbB2dailMy21YTEqTvXx5nPOZaC3Q== X-Google-Smtp-Source: AA0mqf4CO3CxdhMyF56uQwLS6OxWY0mra82euSd0lRBTX+ywE5SNm/PMzidIFCzJ6WkurffkUjRIIQ== X-Received: by 2002:a05:620a:12fa:b0:6fa:3001:58fb with SMTP id f26-20020a05620a12fa00b006fa300158fbmr46204147qkl.371.1669856614979; Wed, 30 Nov 2022 17:03:34 -0800 (PST) Received: from [192.168.2.100] (pool-72-79-30-4.nwrknj.east.verizon.net. [72.79.30.4]) by smtp.gmail.com with ESMTPSA id a8-20020a05620a16c800b006f3e6933bacsm2165394qkn.113.2022.11.30.17.03.34 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 30 Nov 2022 17:03:34 -0800 (PST) Content-Type: multipart/alternative; boundary="------------qPbgQJ0X22d490coy85LFMBI" Message-ID: <303273b8-6116-23b2-f26d-866e38ef1c0a@gmail.com> Date: Wed, 30 Nov 2022 20:03:33 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.5.0 Subject: Re: postgres large database backup To: pgsql-general@lists.postgresql.org References: <9d50f118-a3dd-565a-41bd-d3056b9b90d6@gmail.com> <592a2c79-9728-e450-ad60-a0df7dd0f214@erven.at> Content-Language: en-US From: Mladen Gogala In-Reply-To: <592a2c79-9728-e450-ad60-a0df7dd0f214@erven.at> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------qPbgQJ0X22d490coy85LFMBI Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 11/30/22 18:19, Hannes Erven wrote: > You could also use a filesystem that can do atomic snapshots - like ZFS. Uh, oh. Not so sure about that. Here is a page from the world of the big O: https://blog.docbert.org/oracle-on-zfs/ However, similar can be said about ZFS. ZFS snapshots will slow down the I/O considerably. I would definitely prefer snapshots done in hardware and not in software.  My favorite file systems, depending on the type of disk, are F2FS and XFS. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com --------------qPbgQJ0X22d490coy85LFMBI Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 11/30/22 18:19, Hannes Erven wrote:
You could also use a filesystem that can do atomic snapshots - like ZFS.

Uh, oh. Not so sure about that. Here is a page from the world of the big O: https://blog.docbert.org/oracle-on-zfs/

However, similar can be said about ZFS. ZFS snapshots will slow down the I/O considerably. I would definitely prefer snapshots done in hardware and not in software.  My favorite file systems, depending on the type of disk, are F2FS and XFS.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
--------------qPbgQJ0X22d490coy85LFMBI--