2

I want to create a postgresql database backup with a .bat file on Windows

I tried with this code:

@echo off
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=odoo_%datestr%.backup
echo backup file name is %BACKUP_FILE%
SET PGPASSWORD=openpgpwd
echo on
bin\pg_dump -i -h localhost -p 5432 -U openpg -F c -b -v -f %BACKUP_FILE% formation

But I received nothing.

And with this code:

@echo off
SET PG_BIN="C:\Program Files\OpenERP 7.0-20150818\PostgreSQL\bin\pg_dump.exe"
SET PG_HOST=localhost
SET PG_PORT=5432
SET PG_DATABASE=formation
SET PG_USER=openpg
SET PG_PASSWORD=openpgpwd
SET PG_PATCH=D:\odoo
SET FECHAYHORA=%date:/=%-%time:-0,8%
SET FECHAYHORA=%FECHAYHORA::=-%
SET FECHAYHORA=%FECHAYHORA: =0%
SET PG_FILENAME=%PG_PATH%\%PG_DATABASE%-%FECHAYHORA%.sql
%PG_BIN% - i -h %PG_HOST% -p %PG_PORT% -U %PG_USER% %PG_DATABASE% > %PG_FILENAME%

I have the same problem.

I want to use it later in the Windows Task Scheduler.

Improve this question
4
  • 1
    What exactly is your problem? "But I received nothing" is not a good problem description Commented Jan 21, 2017 at 14:17
  • 2
    PG_PATCH and PG_PATH looks suspicious... Commented Jan 21, 2017 at 14:24
  • think you The problem is that the code does not work Does not create a backup Commented Jan 21, 2017 at 15:50
  • think JOANOLO I have to replace PG_PATCH to PG_PATH But it creates a file zero KO Commented Jan 21, 2017 at 15:54

3 Answers 3

Reset to default
4

This answer was provided by the author:

I think this is the solution for all:

@echo Backup database  %PG_PATH%%PG_FILENAME%
@echo off
SET PG_BIN="C:\Program Files\PostgreSQL\bin\pg_dump.exe"
SET PG_HOST=localhost
SET PG_PORT=5432
SET PG_DATABASE=motor_8
SET PG_USER=openpg
SET PG_PASSWORD=openpgpwd
SET PG_PATH=C:\OEM
SET FECHAYHORA=%date:/=%-%time:-0,8%
SET FECHAYHORA=%FECHAYHORA::=-%
SET FECHAYHORA=%FECHAYHORA: =0%
SET PG_FILENAME=%PG_PATH%\%PG_DATABASE%_%d%_%t%.sql

%PG_BIN% -h %PG_HOST% -p %PG_PORT% -U %PG_USER% %PG_DATABASE% > %PG_FILENAME%

@echo Backup Taken Complete %PG_PATH%%PG_FILENAME%
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

when I am running this script on double click on .bat file every time it is asking for password in command prompt window. How I can remove password authentication ?
I have added this "local all all trust" in my pg_hba.conf file then I run my script so it will not asking for a password but creating .sql file with 0kb with no data
2

It is hardly surprising that your scripts don't work, because they contain typos all over. I don't claim that I found them all, but here is a list of those that I did spot:

First script:

  • There is no option -i for pg_dump.

Second script:

  • There is a bogus space in -i, but since that option doesn't exist and will cause an error, it should be removed.

  • You set PG_PATCH and reference PG_PATH.

  • The environment variable PG_PASSWORD is not recognized by libpq. Remove the underscore.

You can figure out all this if you read the error messages you undoubtedly get. You should also always include such error messages in questions like this.

Improve this answer

3 Comments

Thank you for your answer is what you can copy the correct code
Hi I solve this problem and i correct the question for a new solution think for all
It would be better if you wrote an answer instead of editing the question. That way everybody can understand easily that it is an answer.
0

Modifying Laurenz Albe's code, here's how to back up an entire server using the "pg_dumpall.exe" program (documentation here). This is for PostgreSQL 15, you may have to modify the "PG_BIN" line to match the location of your "pg_dumpall.exe" file.

@echo Backing up server...
@echo off
SET PG_BIN="C:\Program Files\PostgreSQL\15\bin\pg_dumpall.exe"
SET PG_HOST=YOUR_HOST
SET PG_PORT=YOUR_PORT
SET PG_USER=YOUR_USER
SET PG_PATH=YOUR_DESTINATION
SET PG_FILENAME=%PG_PATH%\PostgreSQL_server_back_up.sql

%PG_BIN% -h %PG_HOST% -p %PG_PORT% -U %PG_USER% > %PG_FILENAME%

@echo Backup complete. Path: %PG_PATH%%PG_FILENAME%

A password isn't included in the above code, you'll need to use the pgpass.conf file (documentation here) to store the user name(s) and password(s) you'll be using.

Example file location:

C:\Users\bradl\AppData\Roaming\postgresql\pgpass.conf

Example file contents:

localhost:5432:*:YOUR_ADMIN_USER:YOUR_ADMIN_PASSWORD
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.