Calling psql pg_dump command in a batch script

Question

I am trying to call a pg_dump command in a batch file. First I get all the table names and then loop every table and execute pg_dump command. It has to be probably something like that but I get an error as "syntax error":

for %%T in (psql -U postgres -w -d test_db -t -c "SELECT table_name FROM
information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'")
do pg_dump -t %%T -U postgres test_db -w -f "C:\Users\mtuna\Documents\dumpfiles\%%T.sql"
done;

Any help would be appreciated.

How are you storing the password ?

Houari
– Houari

2014-01-29 18:29:49 +00:00
Commented Jan 29, 2014 at 18:29 — Houari
– Houari, Commented Jan 29, 2014 at 18:29

Houari · Accepted Answer · 2014-01-29 19:01:09Z

2

Here is a solution:

@echo off
SET TableListeFile=C:\Users\mtuna\Documents\dumpfiles\database_list.txt

REM Saveing all tables name of database test_db on a temp file: database_list.txt  
psql -U postgres  -d test_db -t -c "SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'" -o "%TableListeFile%"

REM Loop on liste tables name:
FOR /F "tokens=*" %%I IN (%TableListeFile%) DO (
REM Dump each table on file
pg_dump  -U postgres -h localhost -t %%I test_db > "C:\Users\mtuna\Documents\dumpfiles\%%I.sql"
)
REM Delete temp file
del /Q %TableListeFile%

It will prompt you for password input for every dump. If you don't want to be promted, you can use the Pgpass File.

Hope that helps.

Houari.

edited Jan 29, 2014 at 19:01

answered Jan 29, 2014 at 18:38

Houari

5,6513 gold badges33 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Craig Ringer Over a year ago

Whoever invented the for /F syntax in cmd.exe scripting ... oh, I dislike them so.

mctuna Over a year ago

Great, that was what I was looking for but the problem is the table names have whitespaces like "ID: 00070246 CH: 0001 ExportEinstellungen". So normally "public.\"ID: 00070246 CH: 0001 ExportEinstellungen\"" had to work but "public.\"%%I\"" does not work. Do you have any idea?

mctuna Over a year ago

I got an error in german, could be translated as, Syntax error by file name, directory name or drive letter

mctuna Over a year ago

Everything is perfect now, I got rid of those whitespaces with substring command as follows: %variable:~3,8%_%variable:~8,8%

Ram Pukar · Accepted Answer · 2017-04-04 12:04:46Z

Backup: batch_file_backup.bat

@echo off
SET PGPATH="E:\PostgreSQL\9.5\bin\pg_dump.exe"
SET PGPASSWORD=admin
%PGPATH% -h 127.0.0.1 -p 5432  -U postgres -F c -b -v -f  C:\Users\Pukar\Downloads\backupfile\2017-04-04.backup database_name

Bat File Backup Run PHP Code:

$batchfile_path = "E:/xampp/htdocs/yig2016/ybase/main_app/bizlayer/protected/batch_file_backup.bat";
$WshShell       = new COM("WScript.Shell");
$exec           = $WshShell->Run($batchfile_path, 0, false);

Restore:batch_file_restore.bat

@echo off
SET PGPATH="E:\PostgreSQL\9.5\bin\pg_restore.exe"
SET PGPASSWORD=admin
%PGPATH% -h 127.0.0.1 -p 5432 -U postgres -d database_name -v C:\Users\Pukar\Downloads\backupfile\2017-04-04.backup

Bat File Restore Run PHP Code:

$batchfile_path = 
E:/xampp/htdocs/yig2016/ybase/main_app/bizlayer/protected/batch_file_restore.bat";
$WshShell       = new COM("WScript.Shell");
$exec           = $WshShell->Run($batchfile_path, 0, false);

References: http://www.somelesson.blogspot.com/2017/04/postgresql-backup-and-restore.html

Collectives™ on Stack Overflow

Calling psql pg_dump command in a batch script

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related