We have PostgreSQL 9.6 databases and a dailytask running a pg_dump for all databases, though these backups are getting "large" at this point.

My database was 900MB, then I tried clearing it by deleting old history which is not necessary anymore and after that ran VACUUM FULL. The statistics in pgAdmin say that the database is now only 30MB.

When I run the pg_dump command manually through a Command prompt, it creates a file of 22MB. When I run my daily task (windows task scheduler) it still creates a backup file of 1GB.

What am I missing at this point?

  • Do you have long running transactions? Is autovacuum running correctly? But to boot: did you compare the two backup files? What are the differences in them?? – Patrick Mevzek May 9 '18 at 15:13
  • Something is wrong with you daily task. It is hard to debug it without seeing it. Is your daily task connecting to the database you think it is? Are there any differences between the pg_dump run manually and one run in your daily task? – jjanes May 9 '18 at 16:19
  • Hi both of you. I just let my daily batch run during the night and apparently it worked just fine now with small backup files in the end. I think I was too fast in complaining/questioning. Though I can tell that I copied the line from the batch file to do the manual check and probably it added content instead of recreating the file. So that was my problem. – TimVK May 10 '18 at 10:11

Check if your pg_dump does not add contents to the existing file instead of replacing it with a new one. That was my mistake.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.