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 1p0SQ9-0002TK-9D for pgsql-general@arkaria.postgresql.org; Wed, 30 Nov 2022 19:07:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p0SQ7-0003rz-Cw for pgsql-general@arkaria.postgresql.org; Wed, 30 Nov 2022 19:06:59 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p0SQ6-0003ri-NV for pgsql-general@lists.postgresql.org; Wed, 30 Nov 2022 19:06:58 +0000 Received: from mail-oi1-x22f.google.com ([2607:f8b0:4864:20::22f]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p0SQ4-0002aZ-Ae for pgsql-general@lists.postgresql.org; Wed, 30 Nov 2022 19:06:57 +0000 Received: by mail-oi1-x22f.google.com with SMTP id t62so19768531oib.12 for ; Wed, 30 Nov 2022 11:06:56 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=+LexIniJuwP/1/uBvKZw6L+En3qZpem5dwlQctM8zlc=; b=iA+lw+n+2HThZhJda4ARhT+wD72ipG5wYpWcwYagh/2FXvIU0HBjtoC5QqJpsMUlmL pYADxgfnV+LeH7Qj/DZdV7i6dmPRTrr2JgY1yx3/QDYTs/IHjetNDgSx9j4pNPGpIt8H Wance/rf9pfriEQ7RzyhY8tzV4/Nj4ttdYPNr5mtEYhza5FvO0MbEWmtnf9x8nqBx94l NcjY8+YjNCxRazEK71C8crzf9jGfijGYTmdAgDeKdJeVm75bYquywO7jMMk8JZiDMei3 BOX5W4sQUFXTAHD1HGGaizFG6bjlKkdFKJMCKO1st6jVYotgWMnXLSm7lt/A63ddVFQr U/IA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=+LexIniJuwP/1/uBvKZw6L+En3qZpem5dwlQctM8zlc=; b=YfDLIl4iuRo0Pv0mT56cWxGqFk88vodJw8NTNL7nakM0XMi9YLlHIqX401u4FL6Xyc 14z25HVlzLYNiqRCFtHh5h8nc4SYFm0nhk84ZpYC9vmrjg/6Thy7bXL+9C1LLc0p+iPm jd4r6rP7fWKjRyIaLug8zGrM6D3f0sNhoW0Q+fuyaup95kK/+P6pBvKG4O5mkqbrWnpL XBj3OE8LWk+ei/IXF5xfXdsGvUMJfSzAvgW1knfutmUbybXPkEcziyRFiB6MfgZXi6UO IME5RSVFEzMyaKlsKF/881ol8AqEucOV+J0LZxpGTNDKEb2XJUG1EWuBP/rawsPghMXq RJHw== X-Gm-Message-State: ANoB5pmGDkJjgUkT8tUkGXPzN0GJixsAn4nnLkoK4+CDhvG7G3gsmn2F OReJXVZNLyD7gby7CYHiL+dN/Q8wmm3haA== X-Google-Smtp-Source: AA0mqf5ZW8t2HMOqJ8wwDshdKvWvx6Z5AWaHn18du6/9BMKbPJEb2hNEmzmxJWD/E4iwE9IbcFeZNA== X-Received: by 2002:aca:1308:0:b0:35a:79fd:fc9f with SMTP id e8-20020aca1308000000b0035a79fdfc9fmr21137934oii.246.1669835215238; Wed, 30 Nov 2022 11:06:55 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id s34-20020a056870612200b0013d9bd4ad2esm1601923oae.12.2022.11.30.11.06.54 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 30 Nov 2022 11:06:54 -0800 (PST) Content-Type: multipart/alternative; boundary="------------EnL0ACA5eIPDB10cD00CIPMo" Message-ID: Date: Wed, 30 Nov 2022 13:06:53 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: postgres large database backup Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: 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. --------------EnL0ACA5eIPDB10cD00CIPMo Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 11/30/22 11:41, Vijaykumar Jain wrote: > > On Wed, Nov 30, 2022, 9:10 PM Atul Kumar wrote: > > Hi, > > I have a 10TB database running on postgres 11 version running on > centos 7 "on premises", I need to schedule the backup of this database > in a faster way. > > The scheduled backup will be used for PITR purposes. > > So please let me know how I should do it in a quicker backup for my > 10TB database ? Is there any tool to take backups and subsequently > incremental backups in a faster way and restore it for PITR in a > faster way when required. > > What should be the exact approach for scheduling such backups so that > it can be restored in a faster way ? > > > > We had a 96 cpu, 385gb ram, nvme storage and 10g network baremetal server. > We used pgbackrest for full backup. > It supports pitr and differential backup. > > 28tb db took 2.5 hours for backup on remote storage, and restore from the > remote storage  took 3.5 hours when immediately restored (a lot of time is > later due to wal replay to catch up) Another vote for pgbackrest.  It supports AES encryption, and multiple forms of compression. -- Angular momentum makes the world go 'round. --------------EnL0ACA5eIPDB10cD00CIPMo Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 11/30/22 11:41, Vijaykumar Jain wrote:

On Wed, Nov 30, 2022, 9:10 PM Atul Kumar <akumar14871@gmail.com> wrote:
Hi, 

I have a 10TB database running on postgres 11 version running on centos 7 "on premises", I need to schedule the backup of this database in a faster way.

The scheduled backup will be used for PITR purposes.

So please let me know how I should do it in a quicker backup for my 10TB database ? Is there any tool to take backups and subsequently incremental backups in a faster way and restore it for PITR in a faster way when required.

What should be the exact approach for scheduling such backups so that it can be restored in a faster way ?


We had a 96 cpu, 385gb ram, nvme storage and 10g network baremetal server.
We used pgbackrest for full backup.
It supports pitr and differential backup.

28tb db took 2.5 hours for backup on remote storage, and restore from the remote storage  took 3.5 hours when immediately restored (a lot of time is later due to wal replay to catch up)

Another vote for pgbackrest.  It supports AES encryption, and multiple forms of compression.

--
Angular momentum makes the world go 'round.
--------------EnL0ACA5eIPDB10cD00CIPMo--