public inbox for [email protected]help / color / mirror / Atom feed
Large 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 <[email protected]> 2026-05-20 11:23 ` Re: Large backup size of pg_dump Priancka Chatz <[email protected]> 2026-05-20 13:17 ` Re: Large backup size of pg_dump hubert depesz lubaczewski <[email protected]> 2026-05-20 14:15 ` Re: Large backup size of pg_dump Ron Johnson <[email protected]> 2026-05-20 14:48 ` Re: Large backup size of pg_dump Adrian Klaver <[email protected]> 0 siblings, 4 replies; 5+ messages in thread From: Ertan Küçükoglu @ 2026-05-20 07:17 UTC (permalink / raw) To: [email protected] 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 <[email protected]> @ 2026-05-20 11:23 ` Priancka Chatz <[email protected]> 3 siblings, 0 replies; 5+ messages in thread From: Priancka Chatz @ 2026-05-20 11:23 UTC (permalink / raw) To: Ertan Küçükoglu <[email protected]>; +Cc: [email protected] 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 <[email protected]> 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 <[email protected]> @ 2026-05-20 13:17 ` hubert depesz lubaczewski <[email protected]> 3 siblings, 0 replies; 5+ messages in thread From: hubert depesz lubaczewski @ 2026-05-20 13:17 UTC (permalink / raw) To: Ertan Küçükoglu <[email protected]>; +Cc: [email protected] 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 <[email protected]> @ 2026-05-20 14:15 ` Ron Johnson <[email protected]> 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 <[email protected]> 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 <[email protected]> @ 2026-05-20 14:48 ` Adrian Klaver <[email protected]> 3 siblings, 0 replies; 5+ messages in thread From: Adrian Klaver @ 2026-05-20 14:48 UTC (permalink / raw) To: Ertan Küçükoglu <[email protected]>; [email protected] 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 [email protected] ^ 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 <[email protected]> 2026-05-20 11:23 ` Priancka Chatz <[email protected]> 2026-05-20 13:17 ` hubert depesz lubaczewski <[email protected]> 2026-05-20 14:15 ` Ron Johnson <[email protected]> 2026-05-20 14:48 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox