如何通过批处理文件中的pg_dump备份所有数据库-Postgres [英] How to backup all db by pg_dump in batch file - postgres

问题描述

我尝试从postgres备份所有数据库，但我不知道如何在循环中执行此操作。
pg_dumpall对我来说不是一个好主意，因为我获得了超过1k的DB，并且它将增长。

I try to backup all DB from postgres, but i dont know how to do that in the loop. pg_dumpall isnt good idea for me, couse i got over 1k DB and it will be grown.

@echo off

set BASELOG=LOG
set BACKUPDIR=C:/postgres/backups/
set BACKUPDIR2=C:\postgres\backups
set PGHOST=localhost
set PGUSER=postgres
set PGBIN="C:/Program Files/PostgreSQL/9.3/bin/"
set FILELOG=log.txt

set BACKUPDIRDATE="%BACKUPDIR%%date%/"
for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
 set dow=%%i
 set month=%%j
 set day=%%k
 set year=%%l
)
if not exist %BACKUPDIRDATE% mkdir %BACKUPDIRDATE%

(
echo Backup start %date%  %time%
%PGBIN%pg_dump -w -i -h %PGHOST% -U %PGUSER% -F c -b -v -f     "%BACKUPDIRDATE%%LOG%.compressed" %BASELOG%
echo End of backup %BASELOG%

FORFILES /p %BACKUPDIR2% /s /D -7 /C "cmd /c rd /S /Q @path"
echo Files are deleted
 )>> %BACKUPDIRDATE%%FILELOG% 2>&1

有人可以解释一下如何获取所有列表吗DB，然后将它们循环？

Can someone explain me how to get list of all DB and then loop them?

我尝试了％PGBIN％psql / list，但这不起作用

I tried %PGBIN%psql /list but this doesnt work

推荐答案

请重新检查股票，我是即时进行的。我不记得pg_dump上的-w是什么。
这样，您将为每个数据库获取一个文件。
作为建议，在谈论Windows上的文件夹路径时，请用\ /代替
抱歉。

Please, re-check the scrip, i do it on the fly. I don't remember what is the -w on pg_dump. In this way you will obtain a file for each db. And as a suggestion, use \ instead / when speak about folder paths on Windows Excuse my english.

@echo off

set BASELOG=LOG
set BACKUPDIR=C:/postgres/backups/
set BACKUPDIR2=C:\postgres\backups
set PGHOST=localhost
set PGUSER=postgres
set PGBIN="C:/Program Files/PostgreSQL/9.3/bin/"
set FILELOG=log.txt

set BACKUPDIRDATE="%BACKUPDIR%%date%/"
for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
 set dow=%%i
 set month=%%j
 set day=%%k
 set year=%%l
)
if not exist %BACKUPDIRDATE% mkdir %BACKUPDIRDATE%

(

if exist dbs.lst del dbs.lst
psql -h %PGHOST% -p 5432 -U %PGUSER% -c "SELECT datname FROM pg_database WHERE datistemplate = false;" -o "dbs.lst"

for /f %%a IN (dbs.lst) DO (

    echo Backup start %date%  %time%
    %PGBIN%pg_dump -w -i -h %PGHOST% -U %PGUSER% -F c -b -v -f "%BACKUPDIRDATE%-%%a.compressed" %%a
    echo End of backup %BASELOG%

)

FORFILES /p %BACKUPDIR2% /s /D -7 /C "cmd /c rd /S /Q @path"
echo Files are deleted
 )>> %BACKUPDIRDATE%%FILELOG% 2>&1

这篇关于如何通过批处理文件中的pg_dump备份所有数据库-Postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！