Re: WAL archiving
От | Shilpa Sudhakar |
---|---|
Тема | Re: WAL archiving |
Дата | |
Msg-id | 47C25EEB.7080602@internode.com.au обсуждение исходный текст |
Ответ на | Re: WAL archiving (Vishal Arora <aroravishal22@hotmail.com>) |
Список | 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>
В списке pgsql-admin по дате отправления: