I've built a script to perform a physical backup of a Postgresql 9.0.8 database by following the "Standalone hot physical database backup" recipe in the PostgreSQL 9 Administration Cookbook (Riggs/Krosing), but I adapted it for Windows Server 2008 R2.
For the recipe's step #4 which uses rsync to copy all of the data files (excluding pg_xlog directory) I am using robocopy.exe (since rsync is a *nix utility and I am using Windows). The problem is that often, one of the files cannot be copied and results in "Access is denied". Copying the file by hand fails with "Access is denied" long after the backup script has failed - so this is not some intermittent issue that can be re-tried. The file can be copied only after restarting the PostgreSQL process. It's always a different file. Last nite it was %PGDATADIR%\5432\base\24609\38122 .
I'd like to hear if you've experienced this and what you did to fix this. I'm considering:
- Restart the PostgreSQL server right before backup (I admit this is a hack)
- Using some sort of utility that can copy open files such as VSHADOW, DISKSHADOW and hobocopy (note: not robocopy)
- Maybe there is some way to instruct PostgreSQL to release all locks?
- [added] see below - looks like adding regular "vacuum"ing eliminates the symptom