Postgres Incremental Backup Stopped Working

I have a remote postgres server on a Linux host Ubuntu 14.04 that was backing up regularly using the backup script mentioned here.

I now notice that the backup script initiates but does not complete its task.

I get the folder created with the date and inside are files that have zero mb size and end in “in_progress”

Nothing has changed on the server that I personally have done. Any ideas on how I might get this going again?

I did a pg_dump remotely using pgAdmin and all worked fine copying everything to my local computer. I was able to create an instance of the database locally with all the data.

Did something get corrupted? I restarted the server, restarted postgres - still no backups.

Did you check the destination filesystem is not full?

Did you tried the script from terminal to read output and possible errors?

just for the records, I use this shell script for postgres backup and it works fine.

Just double-checked. It has 1.4TB free.

This is interesting. I created a postgres user (backup_user) just to do backups. What happens is when you add a table or sequence, you have to give permission to the user to access the table or sequence - otherwise, everything just fails.

Maybe I should have just used the user postgres - who knew?

nice but windows only software …

You may want to change the default permissions. See:

Great you were able to resolve the issue with permissions.

Let me add a comment on the technique you are using: While still a quite common approach, please be aware that pg_dumpall -g plus a bunch of pg_dump -C (-s) is not (!) equivalent to pg_dumpall (-s). There are corner-cases that are only included if you do a full pg_dumpall (I gave some examples in a comment to this blog-post). This has been the case for quite some time and thankfully there has started some recent work on the issues, hopefully for PostgreSQL 11. Quick advise: if you don’t need per-database backups (e.g. if you only have one) just use pg_dumpall which is easier anyhow.

If you want to implement more complex backup schemes then just scheduled dumps w/o rolling your on scripts, you might want to have a look to Barman