Re: Backing up through a database connection (not pg_dump)

On Mon, 2012-03-26 at 16:16 -0700, John R Pierce wrote:
> On 03/26/12 4:05 PM, Tim Uckun wrote:
> > Is there a way to backup a database or a cluster though a database
> > connection? I mean I want to write some code that connects to the
> > database remotely and then issues a backup command like it would issue
> > any other SQL command. I realize the backups would need to reside on
> > the database server.
>
> there is no backup command in postgres SQL, you could enumerate the
> tables, and use /COPY tablename TO filepath;/ on each table, these files
> would have to be in a directory writable by the postgres server process.
>
> alternately, you could open a shell session on the dbserver and run
> pg_dump there. frankly, this would be preferable.
>
>
>
>
>
> --
> john r pierce N 37, W 122
> santa cruz ca mid-left coast
>
>

pg_dump includes more than just the tables though..doesn't it. Meaning,
pg_dump includes stored procedures, views table structure etc?

COPY won't serve as a backup replacement.

I'd go with John's recommendation of pg_dump

Also, pg_dump can write to remote servers (windows example below)

We're also using libpq to trigger backups using NOTIFY from a client
app.

Here's a .bat file I run on a Windows 2003 box

rem for windows date stamped archive

@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=SKYLINE_%datestr%.backup
echo backup file name is %BACKUP_FILE%
SET PGPASSWORD=your_password
echo on
C:\"Program Files"\"PostgreSQL"\8.3\bin\pg_dump -i -h batchfarm -p
5432 -U postgres -F c -b -v -f %BACKUP_FILE% SKYLINE
C:\"Program Files"\"PostgreSQL"\8.3\bin\pg_dump -i -h batchfarm -p
5432 -U postgres -F c -b -v -f \\Acrm-backup\data\Library\Backup\Skyline
\BackupDB\%BACKUP_FILE% SKYLINE

SET PGPASSWORD="