Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 9B77C9FA37A for ; Tue, 6 Feb 2007 05:34:10 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 09362-05 for ; Tue, 6 Feb 2007 05:34:03 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.238]) by postgresql.org (Postfix) with ESMTP id BAA7D9FA312 for ; Tue, 6 Feb 2007 05:34:06 -0400 (AST) Received: by nz-out-0506.google.com with SMTP id i11so1884491nzh for ; Tue, 06 Feb 2007 01:34:05 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=njVb4pAS9FEmYaMP5pmdP/VnkLTNl0C3/w0JGxEci76zX0kDdu/KDipe9nl302JXVS58klt1lPFYYw/zBwoCX9gBhq0H1AMk0Pk6lzPdl6bvBzGai5n0lrMHH5ShD2kf+FJJ8ALl8yzjdi3el01XV2PGp4PnzBEpiKLRcgcIgwA= Received: by 10.65.200.14 with SMTP id c14mr12453451qbq.1170754445054; Tue, 06 Feb 2007 01:34:05 -0800 (PST) Received: by 10.65.206.17 with HTTP; Tue, 6 Feb 2007 01:34:04 -0800 (PST) Message-ID: Date: Tue, 6 Feb 2007 14:34:04 +0500 From: "Shoaib Mir" To: "Phillip Smith" Subject: Re: Backup Strategies? Cc: pgsql-admin@postgresql.org In-Reply-To: <1170494928.9214.10.camel@phillips-desktop> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1327_31361735.1170754444998" References: <1170494928.9214.10.camel@phillips-desktop> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/76 X-Sequence-Number: 24541 ------=_Part_1327_31361735.1170754444998 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Going with a PostgreSQL backup user (with read only permissions) seems to be a better option for your case.... Anyone else on the list might be able to come up with much better options. -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 2/3/07, Phillip Smith wrote: > > Have you looked at pg_dump and/or pg_dumpall? That would be the more > common way of performing backups. > > I run 2 backup's nightly of our databases: > 1) As part of a backup script, call pg_dumpall as the postgres *nix > users and PG user, then pipe the output to a file on a remote server. > 2) As a cronjob: pg_dumpall > /dev/st0 > > Cheers, > -p > > > On Sat, 2007-02-03 at 01:51 -0500, Joshua Kramer wrote: > > Hello All, > > > > What strategies are people using for automated, script-based backup of > > databases? There are a few I can think of: > > > > 1. Create a "db_backup" unix user and a "db_backup" pgsql user. Grant > > READ access to all objects on all databases for the "db_backup" pgsql > > user. Create a .pgpass file in the home directory of the "db_backup" > unix > > user. Backup as needed with a script run as the "db_backup" unix user. > > > > 2. Create a "db_backup" unix user and repeat above, except using the > > "postgres" db user. > > > > Thanks, > > -Josh > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: Don't 'kill -9' the postmaster > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > ------=_Part_1327_31361735.1170754444998 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Going with a PostgreSQL backup user (with read only permissions) seems to be a better option for your case....

Anyone else on the list might be able to come up with much better options.

--
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)

On 2/3/07, Phillip Smith <fukawi2@iinet.net.au > wrote:
Have you looked at pg_dump and/or pg_dumpall? That would be the more
common way of performing backups.

I run 2 backup's nightly of our databases:
1) As part of a backup script, call pg_dumpall as the postgres *nix
users and PG user, then pipe the output to a file on a remote server.
2) As a cronjob: pg_dumpall > /dev/st0

Cheers,
-p


On Sat, 2007-02-03 at 01:51 -0500, Joshua Kramer wrote:
> Hello All,
>
> What strategies are people using for automated, script-based backup of
> databases?  There are a few I can think of:
>
> 1. Create a "db_backup" unix user and a "db_backup" pgsql user.  Grant
> READ access to all objects on all databases for the "db_backup" pgsql
> user.  Create a .pgpass file in the home directory of the "db_backup" unix
> user.  Backup as needed with a script run as the "db_backup" unix user.
>
> 2. Create a "db_backup" unix user and repeat above, except using the
> "postgres" db user.
>
> Thanks,
> -Josh
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

------=_Part_1327_31361735.1170754444998--