Backing up through a database connection (not pg_dump)

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

fwiw we run db_dump locally, compress the resulting file and scp or
rsync it to the remote server.

On Tue, Mar 27, 2012 at 1:00 PM, David Boreham <david_list@boreham.org> wrote:

fwiw we run db_dump locally, compress the resulting file and scp or rsync it
to the remote server.

I wanted to see if I can do that without running pg_dump on the remote
server. That would involve connecting to the server via ssh and I want
to see if there is a way to avoid that.

Cheers.

* Tim Uckun (timuckun@gmail.com) wrote:

On Tue, Mar 27, 2012 at 1:00 PM, David Boreham <david_list@boreham.org> wrote:

fwiw we run db_dump locally, compress the resulting file and scp or rsync it
to the remote server.

I wanted to see if I can do that without running pg_dump on the remote
server. That would involve connecting to the server via ssh and I want
to see if there is a way to avoid that.

Well, sure, run pg_dump on the local system and have it connect to the
remote server.. The result would end up on the local system, of course.

Thanks,

Stephen

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="

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

Do you have an example of how this is done?

At 01:05 27/03/2012, 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.

You can use a stored procedure with this pl
http://plsh.projects.postgresql.org/ , like this:

CREATE FUNCTION dump_db(text, text) RETURNS text AS '
#!/bin/sh
pg_dump $1 > $2
' LANGUAGE plsh;

Note that you must CREATE LANGUAGE first, $1 is db_name, $2 is file
name and check for write permissions of $2. Be careful and check your
security because any shell script will run as postgres user. Don't
know if it works with 9.x+ versions of postgres.

HTH

On Tue, Mar 27, 2012 at 12:05:00PM +1300, 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.

Like this:
http://www.depesz.com/2011/01/24/waiting-for-9-1-pg_basebackup/ ?

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/