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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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="
From | Date | Subject | |
---|---|---|---|
Next Message | Tim Uckun | 2012-03-27 00:45:46 | Re: Backing up through a database connection (not pg_dump) |
Previous Message | Stephen Frost | 2012-03-27 00:17:27 | Re: Backing up through a database connection (not pg_dump) |