I'm writing some batch scripts for doing incremental backups of a PostgreSQL cluster on a Windows Server.
I copied the Data folder to a different folder, ran my backup scripts, stopped the service, deleted the Data folder, and tried recovering the database from the WAL files and such.
This didn't work, because i copied the wrong log files, and i couldn't get the service started again, so i tried copying back in the original Data folder, but i still can't start the service.
The first script i ran called:
pg_basebackup -Fp -D %BACKUPDIR%\full_%CURRENTDATE%
This was the only line which actually ended up interacting with the data, but not the original Data folder, which i copied beforehand.
When trying to start the service again i get the following error message:
The postgresql-x64-10 - PostgreSQL Server 10 service on Local Computer started and then stopped.
Some services stop automatically if they are not in use by other services or programs.
I have gotten this before, when making a typo in the conf file, so i'm guessing that's just the standard error message for when something is missing.
Found out that i had to redo the folder permissions.
This is done the following way:
5. Change permissions for the new data directory For the new data-dictionary folder: Right-click on it and click Properties. Under the Security Tab click “Edit...” and then “Add...”. Type “Network Service” and then click “Check Names”, make sure it has Modify and Full Control permissions and then click OK. Equally important PostgreSQL needs to be able to “see” the data-directory (see my ServerFault.StackEx question), i.e. it needs to have read access to the parent directories above it. So Right-click on the pg_db folder and under the Security Permissions add Network Services again, but this time it only needs Read & Execute as well as List folder contents permissions.
The full post is a nice checklist to go through, for anyone else facing similar issues: https://radumas.info/blog/tutorial/2016/08/08/Migrating-PostgreSQL-Data-Directory-Windows.html