I'm writing a batch file for doing incremental backups of a cluster of Postgres Databases.
For doing a full backup, i am using pg_dump and pg_dump_all, which works no problem if i for example do it like this:
@echo off && pushd "%~dp0"
set PGHOST=localhost
set PGPORT=5432
set PGUSER=postgres
set PGPASSWORD=SOMEPSW
set BACKUPDIR=C:\some\path
set CURRENTDATE=%CURRENTDATE:~0,14%
set BACKUPPATH=%BACKUPDIR%\%CURRENTDATE%
pg_dumpall -r -f %BACKUPPATH%\users.backup <---- THIS IS THE LINE I WANT TO REPLACE
popd
pause
exit /b %RETURNCODE%
(I have tried shaving down the example, to make it quick and easy to read, let my know if anything is unclear).
As far as i have understood, i can't use pg_dump and pg_dumpall for incremental backups, so i want to use pg_start_backup and pg_stop_backup as part of my incremental script, and inbetween create zip and move the Data folder.
My issue right now is that i can't call either of them, because they are not recognized commands.
I have tried calling them in the following ways:
1.
pg_start_backup('label', false, false);
This does not work because pg_start_backup is not recognized as an internal or external command.
2.
SELECT pg_start_backup('label', false, false);
This does not work because SELECT is not recognized as an internal or external command.
3.
psql -h %PGHOST% -p %PGPORT% -U %PGUSER% -d %DATABASES% -c "SELECT pg_start_backup('label', false, false);"
psql -h %PGHOST% -p %PGPORT% -U %PGUSER% -d %DATABASES% -c "SELECT * FROM pg_stop_backup(false, true);"
This does not work, because it starts the backup in one context, and tries to stop it in another, hence throwing this error: "ERROR: non-exclusive backup is not in progress".
4.
psql -h %DB_HOST% -p %DB_PORT% -U %DB_USER% -d %DB_NAME% -f test.sql
This DOES work, but i need to put some batch logic between pg_start_backup and pg_stop_backup, and i don't want to have several sql files i need to call.
What is causing my issues, and how can i fix it?
I just found that pg_dump and pg_dumpall are .exe files in the bin folder. Guess that explains why i can call them directly.
But i'm still confused as to how i'm supposed to call pg_start_backup() and pg_stop_backup() then.