How to take automatic back in postgres database in windwos

Question

0.00/5 (No votes)

See more:

Hi am using below code to take automatic postgres database

@ECHO OFF
@setlocal enableextensions
@cd /d "%~dp0"

SET PGPATH=C:\"Program Files"\PostgreSQL\11\bin\
SET SVPATH=E:\
SET PRJDB=postgres
SET DBUSR=postgres
FOR /F "TOKENS=1,2,3 DELIMS=/ " %%i IN ('DATE /T') DO SET d=%%i-%%j-%%k
FOR /F "TOKENS=1,2,3 DELIMS=: " %%i IN ('TIME /T') DO SET t=%%i%%j%%k

SET DBDUMP=%PRJDB%_%d%_%t%.sql
@ECHO OFF
%PGPATH%pg_dump -h localhost -p 5432 -U postgres %PRJDB% > %SVPATH%%DBDUMP%

echo Backup Taken Complete %SVPATH%%DBDUMP%
pause

But when i run this from task scheduler it is asking to enter password but i want to execute without asking password,password should be palced in the code itself,please give me the solution..

After executing from task scheduler it is generating .sql back file that file am trying to restore into postgres database but is showing below error:

pg_restore: [archiver] input file does not appear to be a valid archive

What I have tried:

After executing from task scheduler it is generating .sql back file that file am trying to restore into postgres database but is showing below error:

<pre>pg_restore: [archiver] input file does not appear to be a valid archive

Posted 25-Jan-20 23:53pm

Accuracy-Global

Updated 26-Jan-20 20:19pm

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Answer 1 · 2020-01-26T00:13:00

Solution 1

According to PostgreSQL: Documentation: 9.1: pg_dump[^], you can use the -w option (or --no-passsword) to never issue a password prompt.

You may then have to supply a .pgpass file instead: Pgpass - PostgreSQL wiki[^]

Posted 26-Jan-20 0:13am

phil.o

Comments

Accuracy-Global 26-Jan-20 23:54pm

Hi colud you please add your code into my code and send me,i will tryit

RickZeeland · Answer 2 · 2020-01-26T00:51:00

Solution 2

See: Automated Backup on Windows - PostgreSQL wiki[^]

@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=<NameOfTheFile>_%datestr%.backup
   echo backup file name is %BACKUP_FILE%
   SET PGPASSWORD=<PassWord>
   echo on
   bin\pg_dump -h <HostName> -p 5432 -U <UserName> -F c -b -v -f %BACKUP_FILE% <DATABASENAME>

Posted 26-Jan-20 0:51am

RickZeeland

Comments

Accuracy-Global 26-Jan-20 23:57pm

Not working for me,

@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=E:\test_%datestr%.backup
echo backup file name is %BACKUP_FILE%
SET PGPASSWORD=postgres
echo on
bin\pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f %BACKUP_FILE% postgres

I replaced my credentials can you please do if required any modifications

Accuracy-Global 27-Jan-20 0:56am

I tried this one also facing same issue,could you please help on this

RickZeeland · Answer 3 · 2020-01-26T20:20:00

Solution 3

I test it with PostgreSQL 9.5 like this, and it worked first time. Be sure to run as administrator:

@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=test_%datestr%.backup
   echo backup file name is %BACKUP_FILE%
   SET PGPASSWORD=postgres
   echo on
   "C:\Program Files\PostgreSQL\9.5\bin\pg_dump" -h 127.0.0.1 -p 5432 -U postgres -F c -b -v -f %BACKUP_FILE% mytestdb

Posted 26-Jan-20 20:20pm

RickZeeland

Comments

Accuracy-Global 27-Jan-20 6:44am

Hi now am able to execute the above code and successfully executed and generated .backup file and am able to restore into postgres database...

But every time .backup file is not generated its overridding on the same file but we need to create every time new once when execute it,please gave suggestions

RickZeeland 27-Jan-20 12:42pm

Then you could include the time, see: https://stackoverflow.com/questions/1192476/format-date-and-time-in-a-windows-batch-script