Hello you all,
We use different Windows servers with Postgresql and looking for the best strategy to create daily incremental backups to a (managed) volume. We want to achieve this with scripts and run them daily within the TaskSchedular. Point in time recovery seems to work only for the whole cluster but we want to be able to restore just one database on a cluster witch contains more databases.
It seems to start with WAL files and a full backup is necessary. We don’t want to shutdown a production server so a full backup with pg_basebackup seems to be the way (i could be wrong). We want to minimize the chance of dataloss so archivemode will be set to on (for production environment).
Postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'copy "%p" "D:\\backupfolder\\walfiles\\%f"' # Windows
Does anyone have a (powershell) script that we can use to create the incremental backups on Windows and can point us the right settings to achieve this? Do you keep the first full backup, do you replace them once a week/month and then delete the wall files? I’m wondering how other PostgreSQL on Windows admins perform this. Is a third party tool the only way to perfom such a backup? For the record, at the time being a daily full backup is executed for all individual databases.
For Oracle (rman) we use a Powershell script that backups the database (incremental) with archive logs and delete the local archive files after successful backup and also delete files older then …..The backup server SLA keeps the backups in archive.
Thanks in advance.
Greetings,
Mario
