pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWAL archiving
11+ messages / 5 participants
[nested] [flat]
* WAL archiving
@ 2008-02-21 23:23 Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-02-22 07:35 ` Re: WAL archiving Vladimir Rusinov <vladimir@greenmice.info>
2008-02-24 23:24 ` Re: WAL archiving Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-02-25 01:02 ` Re: WAL archiving Dawid Kuroczko <qnex42@gmail.com>
2008-02-25 04:57 ` Re: WAL archiving Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-02-25 05:00 ` Re: WAL archiving Shilpa Sudhakar <ssudhakar@internode.com.au>
0 siblings, 5 replies; 11+ messages in thread
From: Shilpa Sudhakar @ 2008-02-21 23:23 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: WAL archiving
2008-02-21 23:23 WAL archiving Shilpa Sudhakar <ssudhakar@internode.com.au>
@ 2008-02-22 07:35 ` Vladimir Rusinov <vladimir@greenmice.info>
4 siblings, 0 replies; 11+ messages in thread
From: Vladimir Rusinov @ 2008-02-22 07:35 UTC (permalink / raw)
To: Shilpa Sudhakar <ssudhakar@internode.com.au>; +Cc: pgsql-admin
On 2/22/08, Shilpa Sudhakar <ssudhakar@internode.com.au> wrote:
>
> 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.
Not exactly.
1. adjust acrhive_command to save wal's to backup dir
2. execute SELECT pg_start_backup('lable')
3. copy database files
4. execute SELECT pg_stop_backup()
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?
Yes, but do not delete old files until the new backup was successfully
finished.
How often do we take a base filesystem backup keeping in mind that our
> systems are 24 x 7.
It depends on your diskspace & load.
Any suggestions are appreciated.
Read the manual. It's pretty good & explains almost everything.
--
Vladimir Rusinov
Voronezh, Russia
UNIX Admin @ Murano Software
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: WAL archiving
2008-02-21 23:23 WAL archiving Shilpa Sudhakar <ssudhakar@internode.com.au>
@ 2008-02-24 23:24 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-02-25 04:06 ` Re: WAL archiving Vishal Arora <aroravishal22@hotmail.com>
4 siblings, 1 reply; 11+ messages in thread
From: Shilpa Sudhakar @ 2008-02-24 23:24 UTC (permalink / raw)
To: Vishal Arora <aroravishal22@hotmail.com>; +Cc: pgsql-admin
Shilpa Sudhakar wrote:
> 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?
>
> 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;
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: WAL archiving
2008-02-21 23:23 WAL archiving Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-02-24 23:24 ` Re: WAL archiving Shilpa Sudhakar <ssudhakar@internode.com.au>
@ 2008-02-25 04:06 ` Vishal Arora <aroravishal22@hotmail.com>
2008-02-25 05:01 ` Re: WAL archiving Shilpa Sudhakar <ssudhakar@internode.com.au>
0 siblings, 1 reply; 11+ messages in thread
From: Vishal Arora @ 2008-02-25 04:06 UTC (permalink / raw)
To: Shilpa Sudhakar <ssudhakar@internode.com.au>; +Cc: pgsql-admin
You can have a warm standby system in place. Pls check this link - http://archives.postgresql.org/sydpug/2006-10/msg00001.php
> Date: Mon, 25 Feb 2008 09:54:34 +1030> From: ssudhakar@internode.com.au> To: aroravishal22@hotmail.com> CC: pgsql-admin@postgresql.org> Subject: Re: [ADMIN] WAL archiving> > Shilpa Sudhakar wrote:> > 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?> >> > 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>; >> >> > > ---------------------------(end of broadcast)---------------------------> TIP 7: You can help support the PostgreSQL project by donating at> > http://www.postgresql.org/about/donate
_________________________________________________________________
Post ads for free - to sell, rent or even buy.www.yello.in
http://ss1.richmedia.in/recurl.asp?pid=186=
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: WAL archiving
2008-02-21 23:23 WAL archiving Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-02-24 23:24 ` Re: WAL archiving Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-02-25 04:06 ` Re: WAL archiving Vishal Arora <aroravishal22@hotmail.com>
@ 2008-02-25 05:01 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
0 siblings, 0 replies; 11+ messages in thread
From: Shilpa Sudhakar @ 2008-02-25 05:01 UTC (permalink / raw)
To: Vishal Arora <aroravishal22@hotmail.com>; +Cc: pgsql-admin
Thanks for the link Vishal
Regards,
Shilpa
Vishal Arora wrote:
>
> You can have a warm standby system in place. Pls check this link -
> http://archives.postgresql.org/sydpug/2006-10/msg00001.php
>
>
>
>
> ------------------------------------------------------------------------
>
> > Date: Mon, 25 Feb 2008 09:54:34 +1030
> > From: ssudhakar@internode.com.au
> > To: aroravishal22@hotmail.com
> > CC: pgsql-admin@postgresql.org
> > Subject: Re: [ADMIN] WAL archiving
> >
> > Shilpa Sudhakar wrote:
> > > 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?
> > >
> > > 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;
> > >
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 7: You can help support the PostgreSQL project by donating at
> >
> > http://www.postgresql.org/about/donate
>
>
> ------------------------------------------------------------------------
> Post free auto ads on Yello Classifieds now! Try it now!
> <http://ss1.richmedia.in/recurl.asp?pid=255;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: WAL archiving
2008-02-21 23:23 WAL archiving Shilpa Sudhakar <ssudhakar@internode.com.au>
@ 2008-02-25 01:02 ` Dawid Kuroczko <qnex42@gmail.com>
4 siblings, 0 replies; 11+ messages in thread
From: Dawid Kuroczko @ 2008-02-25 01:02 UTC (permalink / raw)
To: pgsql-admin
On Fri, Feb 22, 2008 at 12:23 AM, Shilpa Sudhakar
<ssudhakar@internode.com.au> wrote:
> 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.
Depends on your workload and needs.
Say, if you have a policy on having ability to "go back in time" for
at least 30 days, then
you must keep last base backup that was done before 30 days and all
the log files since
then.
But your primary concern should be probably recovery time after
failure (your whole machine
just fried ;)). What you need to do is restore last backup and reply
all archival WAL logs
since then. And assume worst possible situation (the machine fried
just before full backup),
for a weekly backups it means 7 days worth of WAL-log reply.
Now, if your database is DML-heavy recovery won't be very fast, so you
might want to take
base backups more frequently. Or better -- set up a standby server
which will constantly
reply the backups.
Regards,
Dawid Kuroczko
PS: And before setting a policy, do try test recovery and note down
how much time it took.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: WAL archiving
2008-02-21 23:23 WAL archiving Shilpa Sudhakar <ssudhakar@internode.com.au>
@ 2008-02-25 04:57 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
4 siblings, 0 replies; 11+ messages in thread
From: Shilpa Sudhakar @ 2008-02-25 04:57 UTC (permalink / raw)
To: Dawid Kuroczko <qnex42@gmail.com>; +Cc: pgsql-admin
Thanks loads Dawid,
I'll test the process on the TEST box and note down the time it takes.
Dawid Kuroczko wrote:
> On Fri, Feb 22, 2008 at 12:23 AM, Shilpa Sudhakar
> <ssudhakar@internode.com.au> wrote:
>
>> 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.
>>
>
> Depends on your workload and needs.
>
> Say, if you have a policy on having ability to "go back in time" for
> at least 30 days, then
> you must keep last base backup that was done before 30 days and all
> the log files since
> then.
>
> But your primary concern should be probably recovery time after
> failure (your whole machine
> just fried ;)). What you need to do is restore last backup and reply
> all archival WAL logs
> since then. And assume worst possible situation (the machine fried
> just before full backup),
> for a weekly backups it means 7 days worth of WAL-log reply.
>
> Now, if your database is DML-heavy recovery won't be very fast, so you
> might want to take
> base backups more frequently. Or better -- set up a standby server
> which will constantly
> reply the backups.
>
> Regards,
> Dawid Kuroczko
>
> PS: And before setting a policy, do try test recovery and note down
> how much time it took.
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: WAL archiving
2008-02-21 23:23 WAL archiving Shilpa Sudhakar <ssudhakar@internode.com.au>
@ 2008-02-25 05:00 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-02-25 06:00 ` Re: WAL archiving Vishal Arora <aroravishal22@hotmail.com>
4 siblings, 1 reply; 11+ messages in thread
From: Shilpa Sudhakar @ 2008-02-25 05:00 UTC (permalink / raw)
To: Vishal Arora <aroravishal22@hotmail.com>; +Cc: pgsql-admin
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;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: WAL archiving
2008-02-21 23:23 WAL archiving Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-02-25 05:00 ` Re: WAL archiving Shilpa Sudhakar <ssudhakar@internode.com.au>
@ 2008-02-25 06:00 ` Vishal Arora <aroravishal22@hotmail.com>
2008-02-25 06:23 ` Re: WAL archiving Shilpa Sudhakar <ssudhakar@internode.com.au>
0 siblings, 1 reply; 11+ messages in thread
From: Vishal Arora @ 2008-02-25 06:00 UTC (permalink / raw)
To: Shilpa Sudhakar <ssudhakar@internode.com.au>; +Cc: pgsql-admin
> Date: Mon, 25 Feb 2008 15:30:46 +1030> From: ssudhakar@internode.com.au> To: aroravishal22@hotmail.com> CC: pgsql-admin@postgresql.org> Subject: Re: [ADMIN] 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@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>; > > ---------------------------(end of broadcast)---------------------------> TIP 6: explain analyze is your friend
_________________________________________________________________
Post free property ads on Yello Classifieds now! www.yello.in
http://ss1.richmedia.in/recurl.asp?pid=221=
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: WAL archiving
2008-02-21 23:23 WAL archiving Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-02-25 05:00 ` Re: WAL archiving Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-02-25 06:00 ` Re: WAL archiving Vishal Arora <aroravishal22@hotmail.com>
@ 2008-02-25 06:23 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
0 siblings, 0 replies; 11+ messages in thread
From: Shilpa Sudhakar @ 2008-02-25 06:23 UTC (permalink / raw)
To: Vishal Arora <aroravishal22@hotmail.com>; +Cc: pgsql-admin
Thanks Vishal. I got it now.
Will test the archiving process.
Vishal Arora wrote:
>
>
>
>
>
> ------------------------------------------------------------------------
>
> > Date: Mon, 25 Feb 2008 15:30:46 +1030
> > From: ssudhakar@internode.com.au
> > To: aroravishal22@hotmail.com
> > CC: pgsql-admin@postgresql.org
> > Subject: Re: [ADMIN] 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@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;
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
>
>
> ------------------------------------------------------------------------
> Detailed profiles 4 marriage! Only at Shaadi.com Try it!
> <http://ss1.richmedia.in/recurl.asp?pid=107;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: WAL archiving
@ 2008-02-25 06:38 Praveen Kumar (TUV) <praveen.k@renaissance-it.com>
0 siblings, 0 replies; 11+ messages in thread
From: Praveen Kumar \(TUV\) @ 2008-02-25 06:38 UTC (permalink / raw)
To: Vishal Arora <aroravishal22@hotmail.com>; Shilpa Sudhakar <ssudhakar@internode.com.au>; +Cc: pgsql-admin
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@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Vishal Arora
Sent: Monday, February 25, 2008 11:30 AM
To: Shilpa Sudhakar
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] WAL archiving
_____
> Date: Mon, 25 Feb 2008 15:30:46 +1030
> From: ssudhakar@internode.com.au
> To: aroravishal22@hotmail.com
> CC: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] 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@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;
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 6: explain analyze is your friend
_____
Detailed profiles 4 marriage! Only at Shaadi.com Try it!
<http://ss1.richmedia.in/recurl.asp?pid=107;
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2008-02-25 06:38 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-21 23:23 WAL archiving Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-02-22 07:35 ` Vladimir Rusinov <vladimir@greenmice.info>
2008-02-24 23:24 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-02-25 04:06 ` Vishal Arora <aroravishal22@hotmail.com>
2008-02-25 05:01 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-02-25 01:02 ` Dawid Kuroczko <qnex42@gmail.com>
2008-02-25 04:57 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-02-25 05:00 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-02-25 06:00 ` Vishal Arora <aroravishal22@hotmail.com>
2008-02-25 06:23 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-02-25 06:38 Re: WAL archiving Praveen Kumar (TUV) <praveen.k@renaissance-it.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox