Re: Large backup size of pg_dump

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(dot)klaver(at)aklaver(dot)com