Re: WAL files backup

From: "Eduardo J(dot) Ortega" <ejortegau(at)cable(dot)net(dot)co>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: WAL files backup
Date: 2007-02-15 01:02:21
Message-ID: 200702142002.24377.ejortegau@cable.net.co
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

hi:

You enable WAL archiving by setting an appropiate archive_command in your
postgresql.conf (probably something copying the WAL files somewhere you store
them). You MUST restart postgres after changing this file.

About the WAL file size: sorry, i don't know exactly how to do that. I do know
that it requires that you recompile postgres (or that's what i read
somewhere). There's an alternative, if you switch to version 8.2. I haven't
actually tried it, but it appears that in 8.2, in addition to archiving
everytime the WAL grows to 16 MB, it also archives the WAL records every
certain amount of time that you can configure on postgresql.conf. So you can
store WAL files, say, every 30 or 60 minutes, regardless of how big it is.
This way maybe the DB speed won't be seriuosly affected.

Small question, though. How are you taking your base backup?

Regards,

Eduardo.
On Wednesday 14 February 2007 18:38, pedro noticioso wrote:
> Everyone please excuse the mistake in my previous
> question, there is a lot in my mind.
>
> We are already creating a complete backup every day,
> and would like to have WAL files to restore up to the
> last minute of course.
>
> Acording to
>
> http://www.postgresql.org/docs/8.1/static/runtime-config-wal.html#GUC-ARCHI
>VE-COMMAND
> http://www.postgresql.org/docs/8.1/static/backup-online.html#BACKUP-ARCHIVI
>NG-WAL
>
> I tried a couple of them and got an error messages
> stating that WAL archiving was not activated, so, how
> can I activate it? thanks
>
> these docs explain a lot but are not practical like
> the ones in howtoforge.com for example ;)
>
> In regards to my previous question, I did not check
> what I wrote, what I meant is that the 16*1024*1024
> WAL file size is too big for us because we handle just
> a few KB per day of information, so are interested in
> a smaller WAL file size, perhaps 1024*1024 or even
> less so that we can backup the files in less media
> space, but in the postgresql(at)freenode(dot)net IRC channel
> I was told that I would make the database work slower
> if I make a WAL file too small, what is your take on
> the matter? recomendation? etc?
>
> thanks a lot!
>
>
>
>
> --- "Eduardo J. Ortega" <ejortegau(at)cable(dot)net(dot)co>
>
> wrote:
> > For what i understand, the WAL files only record
> > changes to the database, so
> > each WAL segment is absolutely different from the
> > previous one. As a matter
> > of fact, each WAL file is the incremental backup you
> > want to make (provided
> > you have already taken a base or level 0 backup).
> >
> > On Monday 12 February 2007 13:42, pedro noticioso
> >
> > wrote:
> > > hi there
> > >
> > > I lightly read this
>
> http://www.postgresql.org/docs/8.1/static/backup-online.html
>
> > > and am interested in creating an incremental
> >
> > backup of
> >
> > > WAL files, but my database is small so each of
> >
> > this
> >
> > > WAL files must be almost identical to the previous
> > > one.
> > >
> > > Is there a way to incrementally backup so that it
> > > takes less space?
> > >
> > > thanks!
>
> ___________________________________________________________________________
>
> > >_________ Finding fabulous fares is fun.
> > > Let Yahoo! FareChase search your favorite travel
> >
> > sites to find flight and
> >
> > > hotel bargains.
> >
> > http://farechase.yahoo.com/promo-generic-14795097
> >
> > > ---------------------------(end of
> >
> > broadcast)---------------------------
> >
> > > TIP 1: if posting/reading through Usenet, please
> >
> > send an appropriate
> >
> > > subscribe-nomail command to
> >
> > majordomo(at)postgresql(dot)org so that your
> >
> > > message can get through to the mailing list
> >
> > cleanly
> >
> > --
> > Eduardo J. Ortega - Linux user #222873
> > "No fake - I'm a big fan of konqueror, and I use it
> > for everything." -- Linus
> > Torvalds
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 7: You can help support the PostgreSQL project
> > by donating at
> >
> >
> > http://www.postgresql.org/about/donate
>
> ___________________________________________________________________________
>_________ Looking for earth-friendly autos?
> Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.
> http://autos.yahoo.com/green_center/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

--
Eduardo J. Ortega - Linux user #222873
"No fake - I'm a big fan of konqueror, and I use it for everything." -- Linus
Torvalds

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Jaime Casanova 2007-02-15 05:41:12 Re: tabla pg_user Ayuda......
Previous Message pedro noticioso 2007-02-14 23:38:27 Re: WAL files backup