Message-ID: <47C24B7E.6030007@internode.com.au>
Date: Mon, 25 Feb 2008 15:30:46 +1030
From: Shilpa Sudhakar <ssudhakar@internode.com.au>
User-Agent: Thunderbird 1.5.0.14 (Windows/20071210)
MIME-Version: 1.0
To: Vishal Arora <aroravishal22@hotmail.com>
CC: pgsql-admin@postgresql.org
Subject: Re: WAL archiving
References: <47BE07ED.1070802@internode.com.au>
	<BLU110-W165FBE7261A87F71FBB522A91D0@phx.gbl>
	<47C1FC77.6070507@internode.com.au>
	<BLU110-W2A3EBB9AB3FEC6A30E758A9180@phx.gbl>
In-Reply-To: <BLU110-W2A3EBB9AB3FEC6A30E758A9180@phx.gbl>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Thanks a lot Vishal for the info.

I have one more query postgres automatically cleans the WAL files 
present in the pg_xlog directory....right?
So instead of me creating a separate folder as i've done in my current 
settings (wal_log), should i need to change the archive_command to point 
to pg_xlog directly.

In this way i can avoid a separate process of deleting old files.

Please let me know if my understanding is right.

Thanks and Regards,
Shilpa


Vishal Arora wrote:
>
>
>
>
>
> ------------------------------------------------------------------------
>
> > Date: Mon, 25 Feb 2008 09:53:35 +1030
> > From: ssudhakar@internode.com.au
> > To: aroravishal22@hotmail.com
> > Subject: Re: [ADMIN] WAL archiving
> >
> > Hi Vishal,
> >
> > Below is the setup in the postgresql.conf file
> >
> > fsync = true # turns forced synchronization on or off
> > wal_sync_method = fsync # the default varies across platforms:
> > #wal_buffers = 8 # min 4, 8KB each
> > #commit_delay = 0 # range 0-100000, in microseconds
> > #commit_siblings = 5 # range 1-1000
> >
> > # - Checkpoints -
> >
> > checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
> > checkpoint_timeout = 300 # range 30-3600, in seconds
> > checkpoint_warning = 30 # 0 is off, in seconds
> >
> > # - Archiving -
> >
> > archive_command = 'cp -i %p /usr/local/var/pgsql/data/wal_log/%f'
> > # command to use to archive a logfile segment
> >
> > Since this is the test system i've put the wal_log folder in the data
> > folder itself. But, later on i'll be moving this to a different disk.
> >
> > We have thousands and thousands of records that get inserted and 
> updated
> > so the wal logs would be increasing in a large rate. I was thinking 
> if i
> > want to delete the wal logs after a period of time, do i take a base
> > backup again and then delete those old files?
>
> Not required actually. Once you have base backup and Archived WAL in 
> place, you need not take a base backup again. In case of a crash you 
> just have to restore from the base backup and the server would start 
> in restore mode. In restore.conf file you have to specify the 
> restore_command - which would point towards the archived WAL files. 
> Yes Once the WAL has been archived you can delete the old WAL files, 
> which is also done by the postgres itself. It replaces the old(not 
> required) WAL files with the new one.
>
> > Thanks and Regards,
> > Shilpa
> >
> >
> > Vishal Arora wrote:
> > >
> > > Hi Shilpa,
> > >
> > > You need not take base backup again and again. You have WAL 
> Archiveing
> > > in place, that is enough to restore the database. BTW, what archiving
> > > command are you using for WAL Archiving in you postgresql.conf.
> > >
> > > - Vishal
> > >
> > > 
> ------------------------------------------------------------------------
> > >
> > > > Date: Fri, 22 Feb 2008 09:53:25 +1030
> > > > From: ssudhakar@internode.com.au
> > > > To: pgsql-admin@postgresql.org
> > > > Subject: [ADMIN] WAL archiving
> > > >
> > > > Hi All,
> > > >
> > > > I am new to postgres and have been slowly learning the concepts.
> > > >
> > > > Regarding WAL archiving, we first take a base backup and then 
> save all
> > > > the wal logs for PITR.
> > > >
> > > > Both the base backup and wal logs are stored in another disk.
> > > >
> > > > Since the wal logs keep increasing, do we take the base backup 
> every
> > > now
> > > > and then so that we can delete the old log files?
> > > > How often do we take a base filesystem backup keeping in mind 
> that our
> > > > systems are 24 x 7.
> > > >
> > > > Any suggestions are appreciated.
> > > >
> > > > Thanks and Regards,
> > > > Shilpa
> > > >
> > > >
> > > > ---------------------------(end of 
> broadcast)---------------------------
> > > > TIP 9: In versions below 8.0, the planner will ignore your desire to
> > > > choose an index scan if your joining column's datatypes do not
> > > > match
> > >
> > >
> > > 
> ------------------------------------------------------------------------
> > > It's about getting married. Click here! Try it!
> > > <http://ss1.richmedia.in/recurl.asp?pid=201>
> >
>
>
> ------------------------------------------------------------------------
> Post free auto ads on Yello Classifieds now! Try it now! 
> <http://ss1.richmedia.in/recurl.asp?pid=255>