Hello,
If the machine where we have make setup
for warm standby database goes down and we restart this machine again ,then
what will be happen for
Our warm standby database ,will it again
start applying archive log from starting point ( archive log generated after
taking online backup) or from that point where
It has leaved before machine was go down ?
From:
pgsql-admin-owner@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Vishal Arora
Sent: Monday, February 25, 2008
11:30 AM
To: Shilpa Sudhakar
Cc: pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: WAL archiving
> Date: Mon, 25 Feb 2008 15:30:46 +1030
> From: ssudhakar@xxxxxxxxxxxxxxxx
> To: aroravishal22@xxxxxxxxxxx
> CC: pgsql-admin@xxxxxxxxxxxxxx
> Subject: Re: WAL archiving
>
> 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?
YES! it actually recycles the files which are of no further intrest.
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.
NO! In this case it would try to overwrite the existing WAL files. pg_xlog
already contains the WAL files .. whats the point archiving the files at the
same place.
Refer to Chapter 23.3.1 (8.2) of the Manual -
"The archive command should
generally be designed to refuse to overwrite any pre-existing archive file.
This is an important safety feature to preserve the integrity of your archive
in case of administrator error (such as sending the output of two different
servers to the same archive directory). It is advisable to test your proposed
archive command to ensure that it indeed does not overwrite an existing file, and that it returns nonzero status in this case.
"
> 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@xxxxxxxxxxxxxxxx
> > > To: aroravishal22@xxxxxxxxxxx
> > > Subject: Re: 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@xxxxxxxxxxxxxxxx
> > > > > To: pgsql-admin@xxxxxxxxxxxxxx
> > > > > Subject: 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>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
Detailed profiles 4 marriage! Only at Shaadi.com Try it!
