Skip site navigation (1) Skip section navigation (2)

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

From: Bret Stern <bret_stern(at)machinemanagement(dot)com>
To: John R Pierce <pierce(at)hogranch(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Backing up through a database connection (not pg_dump)
Date: 2012-03-27 00:37:10
Message-ID: 1332808630.12259.15.camel@fedora13 (view raw or flat)
Thread:
Lists: pgsql-general
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="













In response to

Responses

pgsql-general by date

Next:From: Tim UckunDate: 2012-03-27 00:45:46
Subject: Re: Backing up through a database connection (not pg_dump)
Previous:From: Stephen FrostDate: 2012-03-27 00:17:27
Subject: Re: Backing up through a database connection (not pg_dump)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group