CODEWITHSUNDEEP
postgresqlbatch-file
mctuna
mctuna

Reputation: 871

Calling psql pg_dump command in a batch script

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.

Upvotes: 0

Views: 6177

Answers (2)

Ram Pukar
Ram Pukar

Reputation: 1621

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

Upvotes: 2

Houari
Houari

Reputation: 5651

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.

Upvotes: 2

Related Questions