Postgresql12 database backup via windows batch

please have someone a working command line for a postgresql12 database backup via windows batch as example?
or hints how your system admin do this kind of backups.

i not understand how this pg_dump gets the permissions as superuser.
https://www.postgresql.org/docs/12/backup-dump.html

in particular, it must have read access to all tables that you want to back up, so in order to back up the entire database you almost always have to run it as a database superuser.

somehow one of 11 Authentication Methods
https://www.postgresql.org/docs/12/client-authentication.html
https://www.postgresql.org/docs/12/auth-methods.html

i found an old wiki entry
https://wiki.postgresql.org/wiki/Automated_Backup_on_Windows

Hi Markus
I use the following script in batch file

@echo off
c:
cd\directory_of_backup

for /f “tokens=1-4 delims=/ " %%i in (”%date%") do (
set dow=%%i
set month=%%j
set day=%%k
set year=%%l
)
set datestr=%month%%day%%year%
echo datestr is %datestr%

set BACKUP_FILE=name_of_backup_%datestr%.backup
echo backup file name is %BACKUP_FILE%
SET PGPASSWORD=password_of_superuser
echo on
bin\pg_dump -h ip_of_databaseserver -p port_of_databaseserver -U username -F c -b -v -f %BACKUP_FILE% name_of_database


Hope this is usefull for you

Eric

1 Like

thank you very much eric.
i will give it our system admin.

memo:
at my pc i got it to work,
i changed the quotation mark back to " (after copy/paste they was wrong) and the delims= to a german date delimiter dot.
ip_of_databaseserver i use 127.0.0.1 or it would need a config entry.
as username i used postgres
port the default 5432

with this script the superuser password is exposed… it’s not good.
I have a script, but for linux or macos, so it won’t work here.

i agree. at least it works.

does the database server service have a build-in scheduler to run this backup tasks and maybe configured via pgAdmin?

You can automate this with the Task Scheduler within Windows server… You can shield the directory where the batchfile resides from other users by making a seperate user. That will also shield the superuser password a bit.

1 Like