Skip site navigation (1) Skip section navigation (2)

Re: Practice of backups

From: Michael Graziano <michael(dot)graziano(at)premierheart(dot)com>
To: Julius Tuskenis <julius(at)nsoft(dot)lt>
Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Practice of backups
Date: 2009-11-19 17:17:24
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
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 :)


In response to


pgsql-admin by date

Next:From: Palaniappan ThiyagarajanDate: 2009-11-19 18:25:57
Subject: Replication solution
Previous:From: Shruthi ADate: 2009-11-19 06:01:57
Subject: Re: Can i write a time-trigger?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group