Re: Practice of backups

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.

Some rough suggestions on how to implement it with minimum impact on
your users:

From a Filesystem Snapshot:
Stop your DB, snapshot the filesystem (mksnap_ffs on FreeBSD, not sure
of a Linux equivalent), restart your DB.
Mount the snapshot somewhere & back up the data directory. (Obviously
get rid of the snapshot when you're done)
You're only down for a few seconds here -- the time for a DB restart
plus the time for a snapshot.

From a Slave of some kind:
Stop the slave, back up the data directory, restart the slave.
This is my current method. It works well, and the master server is
never down so users see zero service disruption. As a bonus, it means
you have a slave server ready to go if your master blows up.

Hope that's helpful :)

-MG