MIME-Version: 1.0
References: 
 <CAH2i4yfdgWf5N+ofPineFW2a97cFT0F9b255HmcEDJ06dc=huA@mail.gmail.com>
In-Reply-To: 
 <CAH2i4yfdgWf5N+ofPineFW2a97cFT0F9b255HmcEDJ06dc=huA@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 20 May 2026 10:15:46 -0400
Message-ID: 
 <CANzqJaBzoCe5DHjP0o=jgb4qJvAH1gP2vpTX+QJOUniSdyy7rw@mail.gmail.com>
Subject: Re: Large backup size of pg_dump
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003d42590652406f38"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaBzoCe5DHjP0o%3Djgb4qJvAH1gP2vpTX%2BQJOUniSdyy7rw%40mail.gmail.com>
Precedence: bulk

--0000000000003d42590652406f38
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, May 20, 2026 at 3:18=E2=80=AFAM Ertan K=C3=BC=C3=A7=C3=BCkoglu <ert=
an.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=3Dapp -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 t=
o
> make the hourly backup size smaller?
>

Taking full backups every hour is suboptimal.

But if you *must*, then
pg_dump -Fp  --compress=3Dzstd  $db > ${db}.sql.zst

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--0000000000003d42590652406f38
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, May 20, 2026 at 3:18=E2=80=AFAM E=
rtan K=C3=BC=C3=A7=C3=BCkoglu &lt;<a href=3D"mailto:ertan.kucukoglu@gmail.c=
om">ertan.kucukoglu@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote=
 gmail_quote_container"><blockquote class=3D"gmail_quote" style=3D"margin:0=
px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><=
div dir=3D"ltr">Hello,<div><br></div><div>I am using PostgreSQL 18.4 x64 on=
 Windows=C2=A0Server 2022. There is a very small single database in the clu=
ster.</div><div><br></div><div>There are hourly pg_dump backups scheduled a=
nd database backup size is around 10GB.</div><div><br></div><div>command li=
ne is like below</div><div>pg_dump.exe -p 5432 -U dbuser --exclude-table=3D=
app -F p -b -c -f &quot;hourly.bak&quot;</div></div></blockquote><div><br><=
/div><div>1. Note that -Fp generates plain SQL files.</div><div>2. Where ar=
e you specifying the database name?=C2=A0 Or is everything going into &quot=
;postgres&quot;?</div><div>3. No need to specify the default port 5432.</di=
v><div><br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px =
0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=
=3D"ltr"><div><br></div><div>When I check the cluster directory size it is =
4.1 GB.</div><div><br></div><div>Database has one BLOB saved in a single re=
cord and it is 16MB in size and that is in the &quot;app&quot; table which =
is excluded from the backup file.</div></div></blockquote><div><br></div><d=
iv>Is 16MB <i>that</i> big?=C2=A0</div><div>=C2=A0</div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>I didn&#39;t underst=
and about 2.5 times bigger backup sizes than the total cluster size. I do n=
ot know what to check either. Is there a way for me to make the hourly back=
up size smaller?</div></div></blockquote><div><br></div><div>Taking full ba=
ckups every hour is suboptimal.</div><div><br></div><div>But if you <i>must=
</i>, then</div><div>pg_dump -Fp=C2=A0

--compress=3Dzstd=C2=A0 $db &gt; ${db}.sql.zst</div><div><br></div></div><s=
pan class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=
=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter=
 sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redact=
ed&gt; lobster!</div></div></div></div></div></div>

--0000000000003d42590652406f38--