Thread: Practice of backups

Hello, admins

I'd like to ask what is general practice for doing backups?  Currently
we are using cron (on Linux server). The question is what user should do
backups. Is it good practice to use superuser for that? 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)?

--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050

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

Friends,

I am looking for Postgres replication solution for 8.3.x DB.

Any open source software available for replication?

Anybody has good step by step doc for warm standby setup?

Any other suggestion to achieve replication is appreciated.


Thanks
Palani

Palaniappan Thiyagarajan <pthiyagarajan@cashedge.com> wrote:

> I am looking for Postgres replication solution for 8.3.x DB.
>
> Any open source software available for replication?
>
> Anybody has good step by step doc for warm standby setup?
>
> Any other suggestion to achieve replication is appreciated.

You might want to start with these pages:

http://wiki.postgresql.org/wiki/Replication%2C_Clustering%2C_and_Connection_Pooling

http://www.postgresql.org/docs/8.3/interactive/warm-standby.html

-Kevin

check this
http://symmetricds.codehaus.org/

I just began playing with that one


> Date: Thu, 19 Nov 2009 12:39:04 -0600
> From: Kevin.Grittner@wicourts.gov
> To: pthiyagarajan@cashedge.com; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Replication solution
>
> Palaniappan Thiyagarajan <pthiyagarajan@cashedge.com> wrote:
>
> > I am looking for Postgres replication solution for 8.3.x DB.
> >
> > Any open source software available for replication?
> >
> > Anybody has good step by step doc for warm standby setup?
> >
> > Any other suggestion to achieve replication is appreciated.
>
> You might want to start with these pages:
>
> http://wiki.postgresql.org/wiki/Replication%2C_Clustering%2C_and_Connection_Pooling
>
> http://www.postgresql.org/docs/8.3/interactive/warm-standby.html
>
> -Kevin
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

On Thu, 2009-11-19 at 18:53 +0000, Julio Leyva wrote:
> check this
> http://symmetricds.codehaus.org/
>
> I just began playing with that one
>
>
> > Date: Thu, 19 Nov 2009 12:39:04 -0600
> > From: Kevin.Grittner@wicourts.gov
> > To: pthiyagarajan@cashedge.com; pgsql-admin@postgresql.org
> > Subject: Re: [ADMIN] Replication solution
> >
> > Palaniappan Thiyagarajan <pthiyagarajan@cashedge.com> wrote:
> >
> > > I am looking for Postgres replication solution for 8.3.x DB.

https://projects.commandprompt.com/public/replicator
http://www.slony.info
http://www.bucardo.org
http://pgfoundry.org/projects/skytools/

> >
> > > Any open source software available for replication?
> > >
> > > Anybody has good step by step doc for warm standby setup?
> > >
> > > Any other suggestion to achieve replication is appreciated.
> >
> > You might want to start with these pages:
> >
> > http://wiki.postgresql.org/wiki/Replication%2C_Clustering%
> 2C_and_Connection_Pooling
> >
> > http://www.postgresql.org/docs/8.3/interactive/warm-standby.html
> >
> > -Kevin
> >
> > --
> > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-admin


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander

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

On Thu, 2009-11-19 at 18:25 +0000, Palaniappan Thiyagarajan wrote:
>
> I am looking for Postgres replication solution for 8.3.x DB.
>
> Any open source software available for replication?

http://wiki.postgresql.org/wiki/Replication%2C_Clustering%
2C_and_Connection_Pooling

> Anybody has good step by step doc for warm standby setup?

https://projects.commandprompt.com/public/pitrtools

This BSD-licensed software includes a nice document for setting up a
warm standby.
--
Devrim GÜNDÜZ, RHCE
Command Prompt - http://www.CommandPrompt.com
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz

On Nov 19, 2009, at 1:58 PM, Jesper Krogh wrote:

> Michael Graziano wrote:
>> 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..

That's true - I forgot about pg_start_backup / pg_stop_backup (which
is sad considering I use them every time I roll a new slave...)

Doing the backup that way avoids the outage in my filesystem
snapshotting example and isn't dependent on OS-level capabilities so
it's definitely a better way to go.

-MG

On Thu, 2009-11-19 at 18:53 +0000, Julio Leyva wrote:
> check this
> http://symmetricds.codehaus.org/
>
> I just began playing with that one
>
>
> > Date: Thu, 19 Nov 2009 12:39:04 -0600
> > From: Kevin.Grittner@wicourts.gov
> > To: pthiyagarajan@cashedge.com; pgsql-admin@postgresql.org
> > Subject: Re: [ADMIN] Replication solution
> >
> > Palaniappan Thiyagarajan <pthiyagarajan@cashedge.com> wrote:
> >
> > > I am looking for Postgres replication solution for 8.3.x DB.

https://projects.commandprompt.com/public/replicator
http://www.slony.info
http://www.bucardo.org
http://pgfoundry.org/projects/skytools/

> >
> > > Any open source software available for replication?
> > >
> > > Anybody has good step by step doc for warm standby setup?
> > >
> > > Any other suggestion to achieve replication is appreciated.
> >
> > You might want to start with these pages:
> >
> > http://wiki.postgresql.org/wiki/Replication%2C_Clustering%
> 2C_and_Connection_Pooling
> >
> > http://www.postgresql.org/docs/8.3/interactive/warm-standby.html
> >
> > -Kevin
> >
> > --
> > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-admin


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander

Thank you guys for advice. It's nice to have someone with experience around.

--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050

"Joshua D. Drake" <jd@commandprompt.com> writes:
> http://pgfoundry.org/projects/skytools/

See also: http://wiki.postgresql.org/wiki/Londiste_Tutorial

Regards,
--
dim

We are currently using Slony for our client's database that has million of rows on some tables and 12GB of raw data.

As much of a pain as slony can be to setup and get working on a large
data set, it is pretty solid and reliable for us too.  Our DB is about
100G or so.

On Sun, Nov 29, 2009 at 9:09 PM, Mark Guadalupe
<mark.guadalupe@gmail.com> wrote:
> We are currently using Slony for our client's database that has million of
> rows on some tables and 12GB of raw data.
> So far so good, we set it up as a service and using a watchdog script to
> monitor and notify us in case the service fails.
>
> On Fri, Nov 27, 2009 at 12:19 AM, Dimitri Fontaine <dfontaine@hi-media.com>
> wrote:
>>
>> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> > http://pgfoundry.org/projects/skytools/
>>
>> See also: http://wiki.postgresql.org/wiki/Londiste_Tutorial
>>
>> Regards,
>> --
>> dim
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>
>
>
> --
> ------------------------------------------------
>
> "Every memorable act in the history of the world is a triumph of enthusiasm.
> Nothing great was ever achieved without it because it gives any challenge or
> any occupation, no matter how frightening or difficult, a new meaning.
> Without enthusiasm you are doomed to a life of mediocrity but with it you
> can accomplish miracles."
>
> Og Mandino 1923-1996, Speaker and Author
>
> ------------------------------------------------
>



--
When fascism comes to America, it will be intolerance sold as diversity.