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
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