Thread: Backing up through a database connection (not pg_dump)
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.
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. > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
* 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
Attachment
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/
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