pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jim Nasby <jim.nasby@gmail.com>
To: Yongye Serkfem <yserkfem@gmail.com>
To: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: pg_dump Running Slow
Date: Tue, 16 Jan 2024 14:54:10 -0600
Message-ID: <f1c35dd1-9b17-45a9-80f9-57c154ef7c9a@gmail.com> (raw)
In-Reply-To: <CAAu_hGs6quKDNwyQnMHq0XsuLU3L9VBXzBoNDJMk2ORM-MLK+g@mail.gmail.com>
References: <CAAu_hGs6quKDNwyQnMHq0XsuLU3L9VBXzBoNDJMk2ORM-MLK+g@mail.gmail.com>

On 1/16/24 12:55 PM, Yongye Serkfem wrote:
> Hello Engineers,
> I trust you are all doing well. I need help on how to improve the speed 
> of pg_dump. I took a pg_dump on a 1TB database, which took almost a 
> whole day. I used this command: "pg_dump -U postgres -d dynamic -f 
> /backups/." Also, how do I check on the progression of the process?

The advantage to pg_dump is that it's very simple and you'd have to work 
really hard to end up with a backup that won't restore. As such, I 
*always* recommend that it be *part* of your disaster recovery plan. It 
also directly supports only restoring one table.

The flip-side is that all you get from pg_dump is (in one form or 
another) just a bunch of SQL that you'll then have to execute to 
restore. That process can be quite slow for anything but a very small 
cluster. (And as you've seen, pg_dump itself can be pretty slow.) While 
--jobs allows parallelizing that work, it's still a very expensive process.

For clusters of any significant size you'll also want to look at some 
form of binary backup (aka: PITR) [1]. Since that operates directly at 
the OS level it's much faster than pg_dump on large clusters. The 
downside is there are lots of subtle ways to set it up incorrectly, 
resulting in backups that won't restore. Instead of trying to manage it 
by hand, I strongly recommend using a tool that's meant for managing 
Postgres binary backups. pgBackRest[2] and barman[3] are two popular 
choices for that.

Most importantly: ALWAYS TEST YOUR BACKUPS. As I mentioned, it's pretty 
hard to screw up pg_dump, but you don't want to be trying to figure out 
how to restore in the middle of a disaster. Given all the subtlties 
involved with PITR I would actually recommend you test restoring *every* 
base backup you take, especially if you're not using one of the tools to 
manage it.

BTW, one of the biggest advantages to those tools is that a lot of 
people use them, so any bugs (in the tool or in Postgres itself) are 
more likely to be found and eliminated. It's also a big advantage for 
cloud providers (as well as tools like PGO[4]): with many thousands of 
instances running regular backups it's harder for bugs to remain hidden.

1: https://www.postgresql.org/docs/current/continuous-archiving.html
2: https://pgbackrest.org/
3: https://pgbarman.org/
4: https://github.com/CrunchyData/postgres-operator
-- 
Jim Nasby, Data Architect, Austin TX






view thread (3+ messages)

Message-ID: <f1c35dd1-9b17-45a9-80f9-57c154ef7c9a@gmail.com>
Permalink:  ../f1c35dd1-9b17-45a9-80f9-57c154ef7c9a@gmail.com/
Also on:    postgresql.org/message-id/f1c35dd1-9b17-45a9-80f9-57c154ef7c9a@gmail.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: jim.nasby@gmail.com, yserkfem@gmail.com
  Subject: Re: pg_dump Running Slow
  In-Reply-To: <f1c35dd1-9b17-45a9-80f9-57c154ef7c9a@gmail.com>
  References: <CAAu_hGs6quKDNwyQnMHq0XsuLU3L9VBXzBoNDJMk2ORM-MLK+g@mail.gmail.com> <f1c35dd1-9b17-45a9-80f9-57c154ef7c9a@gmail.com>

* 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