Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id B828B9FB2C9 for ; Mon, 5 Feb 2007 16:23:02 -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 99969-08 for ; Mon, 5 Feb 2007 16:22:56 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id A67F59FA50C for ; Mon, 5 Feb 2007 16:22:58 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from threephase.globalherald.net (oh-67-76-158-149.sta.embarqhsd.net [67.76.158.149]) by svr4.postgresql.org (Postfix) with ESMTP id A09A35AF93D for ; Mon, 5 Feb 2007 20:22:47 +0000 (GMT) Received: from [192.168.2.155] (unknown [192.168.2.155]) by threephase.globalherald.net (Postfix) with ESMTP id 87AB61C296; Mon, 5 Feb 2007 10:29:36 -0500 (EST) Date: Mon, 5 Feb 2007 15:18:00 -0500 (EST) From: Joshua Kramer X-X-Sender: josh@localhost.localdomain To: Shoaib Mir Cc: pgsql-admin@postgresql.org Subject: Re: Backup Strategies? In-Reply-To: Message-ID: References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.324 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200702/61 X-Sequence-Number: 24526 Thanks for the hint, Shoaib. My question - and I should have phrased this more carefully - related to the security of the various backup tactics. Is it more secure to have a "backup user" with read permissions on all databases, or is it more secure to have a dedicated Unix backup user - with the .pgpass file in the home directory and all. If someone can, by way of cracking, get in to the account of the backup Unix user, then the postgres user's database account is also surrendered. If I have a Postgres backup user (with read only permissions on all DB's), then even if someone got into the unix account of the backup user, all they could do is read DB data (versus delete or write over stuff). Essentially it's the diffrenece between being *really* secure and *really really* secure. On Sat, 3 Feb 2007, Shoaib Mir wrote: > A cron job can always do the job using pg_dump/pg_dumpall, in case you need > the incremental backup you can opt for PITR WAL based archive logs... > > -- > Shoaib Mir > EnterpriseDB (www.enterprisedb.com) > > On 2/3/07, 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 >> >