Re: Practice of backups

Michael Graziano wrote:
> On Nov 18, 2009, at 2:45 AM, Julius Tuskenis wrote:
>
>> The question is what user should do backups. Is it good practice to
>> use superuser for that?
>
> If you're doing your backup with pg_dump (on an individual DB) you need
> a DB user who has read access to everything in that DB.
> If you're doing your backup with pg_dumpall (on the whole cluster) you
> need a DB user with read access to everything in the cluster (including
> roles/users), which pretty much demands a superuser...
>
> In either case when I do dumps using pg_dump or pg_dumpall I use a
> superuser account to make sure I don't miss anything.
>
>
>> If not - is there an easy way to let some backup user to access whole
>> database without setting permission on every database component
>> (tables, sequences, functions, etc)?
>
> None that I know of from within the database environment, but you can
> grab a copy of the data directory off the filesystem.  Note that this
> requires stopping the DB server though, as a backup grabbed while the DB
> is running may have issues.

Not if you have enabled PITR and tell the database that you do so:
http://www.postgresql.org/docs/8.4/static/continuous-archiving.html

Works excellent..

Jesper