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

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 (view raw or flat)
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

pgsql-admin by date

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

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