public inbox for pgsql-general@postgresql.org
help / color / mirror / Atom feedLarge backup size of pg_dump
5+ messages / 5 participants
[nested] [flat]
* Large backup size of pg_dump
@ 2026-05-20 07:17 Ertan Küçükoglu <ertan.kucukoglu@gmail.com>
2026-05-20 11:23 ` Re: Large backup size of pg_dump Priancka Chatz <pc9926@gmail.com>
2026-05-20 13:17 ` Re: Large backup size of pg_dump hubert depesz lubaczewski <depesz@depesz.com>
2026-05-20 14:15 ` Re: Large backup size of pg_dump Ron Johnson <ronljohnsonjr@gmail.com>
2026-05-20 14:48 ` Re: Large backup size of pg_dump Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 4 replies; 5+ messages in thread
From: Ertan Küçükoglu @ 2026-05-20 07:17 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hello,
I am using PostgreSQL 18.4 x64 on Windows Server 2022. There is a very
small single database in the cluster.
There are hourly pg_dump backups scheduled and database backup size is
around 10GB.
command line is like below
pg_dump.exe -p 5432 -U dbuser --exclude-table=app -F p -b -c -f "hourly.bak"
When I check the cluster directory size it is 4.1 GB.
Database has one BLOB saved in a single record and it is 16MB in size and
that is in the "app" table which is excluded from the backup file.
I didn't understand about 2.5 times bigger backup sizes than the total
cluster size. I do not know what to check either. Is there a way for me to
make the hourly backup size smaller?
Thanks & Regards,
Ertan
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Large backup size of pg_dump
2026-05-20 07:17 Large backup size of pg_dump Ertan Küçükoglu <ertan.kucukoglu@gmail.com>
@ 2026-05-20 11:23 ` Priancka Chatz <pc9926@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Priancka Chatz @ 2026-05-20 11:23 UTC (permalink / raw)
To: Ertan Küçükoglu <ertan.kucukoglu@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
When you store large objects, the actual data resides on pg_largeobject
table (https://www.postgresql.org/docs/current/catalog-pg-largeobject.html).
So your "app" table might not be the only thing to exclude in your dump.
Regards,
Priyanka Chatterjee
On Wed, May 20, 2026 at 9:18 AM Ertan Küçükoglu <ertan.kucukoglu@gmail.com>
wrote:
> Hello,
>
> I am using PostgreSQL 18.4 x64 on Windows Server 2022. There is a very
> small single database in the cluster.
>
> There are hourly pg_dump backups scheduled and database backup size is
> around 10GB.
>
> command line is like below
> pg_dump.exe -p 5432 -U dbuser --exclude-table=app -F p -b -c -f
> "hourly.bak"
>
> When I check the cluster directory size it is 4.1 GB.
>
> Database has one BLOB saved in a single record and it is 16MB in size and
> that is in the "app" table which is excluded from the backup file.
>
> I didn't understand about 2.5 times bigger backup sizes than the total
> cluster size. I do not know what to check either. Is there a way for me to
> make the hourly backup size smaller?
>
> Thanks & Regards,
> Ertan
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Large backup size of pg_dump
2026-05-20 07:17 Large backup size of pg_dump Ertan Küçükoglu <ertan.kucukoglu@gmail.com>
@ 2026-05-20 13:17 ` hubert depesz lubaczewski <depesz@depesz.com>
3 siblings, 0 replies; 5+ messages in thread
From: hubert depesz lubaczewski @ 2026-05-20 13:17 UTC (permalink / raw)
To: Ertan Küçükoglu <ertan.kucukoglu@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On Wed, May 20, 2026 at 10:17:57AM +0300, Ertan Küçükoglu wrote:
> Hello,
>
> I am using PostgreSQL 18.4 x64 on Windows Server 2022. There is a very
> small single database in the cluster.
>
> There are hourly pg_dump backups scheduled and database backup size is
> around 10GB.
1. pg_dump is not the best choice for backups.
2. When using pg_dump, use at least -Fd, and -jX to make the dumps work
in parallel
3. Check what is using the most space in dump, and compare it with db
4. What exactly do you mean by "BLOB"? What is the actual datatype of
the field?
5. What is `\l+ your_db_name` output from psql?
> I didn't understand about 2.5 times bigger backup sizes than the total
> cluster size. I do not know what to check either. Is there a way for me to
> make the hourly backup size smaller?
Consider compressing it? Or use some backup tool that handles
incremental/differential backups, like, for example, backrest.
depesz
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Large backup size of pg_dump
2026-05-20 07:17 Large backup size of pg_dump Ertan Küçükoglu <ertan.kucukoglu@gmail.com>
@ 2026-05-20 14:15 ` Ron Johnson <ronljohnsonjr@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Ron Johnson @ 2026-05-20 14:15 UTC (permalink / raw)
To: pgsql-general
On Wed, May 20, 2026 at 3:18 AM Ertan Küçükoglu <ertan.kucukoglu@gmail.com>
wrote:
> Hello,
>
> I am using PostgreSQL 18.4 x64 on Windows Server 2022. There is a very
> small single database in the cluster.
>
> There are hourly pg_dump backups scheduled and database backup size is
> around 10GB.
>
> command line is like below
> pg_dump.exe -p 5432 -U dbuser --exclude-table=app -F p -b -c -f
> "hourly.bak"
>
1. Note that -Fp generates plain SQL files.
2. Where are you specifying the database name? Or is everything going into
"postgres"?
3. No need to specify the default port 5432.
> When I check the cluster directory size it is 4.1 GB.
>
> Database has one BLOB saved in a single record and it is 16MB in size and
> that is in the "app" table which is excluded from the backup file.
>
Is 16MB *that* big?
> I didn't understand about 2.5 times bigger backup sizes than the total
> cluster size. I do not know what to check either. Is there a way for me to
> make the hourly backup size smaller?
>
Taking full backups every hour is suboptimal.
But if you *must*, then
pg_dump -Fp --compress=zstd $db > ${db}.sql.zst
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Large backup size of pg_dump
2026-05-20 07:17 Large backup size of pg_dump Ertan Küçükoglu <ertan.kucukoglu@gmail.com>
@ 2026-05-20 14:48 ` Adrian Klaver <adrian.klaver@aklaver.com>
3 siblings, 0 replies; 5+ messages in thread
From: Adrian Klaver @ 2026-05-20 14:48 UTC (permalink / raw)
To: Ertan Küçükoglu <ertan.kucukoglu@gmail.com>; pgsql-general@lists.postgresql.org
On 5/20/26 12:17 AM, Ertan Küçükoglu wrote:
> Hello,
>
> I am using PostgreSQL 18.4 x64 on Windows Server 2022. There is a very
> small single database in the cluster.
>
> There are hourly pg_dump backups scheduled and database backup size is
> around 10GB.
>
> command line is like below
> pg_dump.exe -p 5432 -U dbuser --exclude-table=app -F p -b -c -f "hourly.bak"
>
> When I check the cluster directory size it is 4.1 GB.
>
> Database has one BLOB saved in a single record and it is 16MB in size
> and that is in the "app" table which is excluded from the backup file.
>
> I didn't understand about 2.5 times bigger backup sizes than the total
> cluster size. I do not know what to check either. Is there a way for me
> to make the hourly backup size smaller?
Because you are using a plain text dump. The data is stored in an
optimized binary form in the cluster files, when you ask for it to be
plain text it 'expands' to be represented as text. Use something like
this -Fc, which will compress the file produced. The handy part is that
on restoring you can restore all or part of the file, with the caveat
that for a partial restore it needs to make logical sense. In other
words restoring a child table without it's parent will not work.
>
> Thanks & Regards,
> Ertan
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-05-20 14:48 UTC | newest]
Thread overview: 5+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-20 07:17 Large backup size of pg_dump Ertan Küçükoglu <ertan.kucukoglu@gmail.com>
2026-05-20 11:23 ` Priancka Chatz <pc9926@gmail.com>
2026-05-20 13:17 ` hubert depesz lubaczewski <depesz@depesz.com>
2026-05-20 14:15 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-05-20 14:48 ` Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox