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.
https://gist.github.com/matthewlehner/3091458
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 …
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