Re: WAL for backup - Mailing list pgsql-novice

On 30 Jul 2014 02:04, "Shreesha" <shreesha1988@gmail.com> wrote:
>
> Thank you David for the quick response. Appreciate it!
> I am currently running PostgreSQL version 9.3.4.
>

Saw your response now... Both the tools I mentioned will work in v9.3. And if you do a clean shutdown all pending wals will be streamed before shutdown is complete.

> Actually, when I said 300MB, I was referring to the archive system space. So like you suggested, I might have to run pg_basebackup more frequently. 
> So are you saying that every time after I run pg_basebackup, if I do pg_archivecleanup then can I ensure data consistency and reduce the archive storage space?
>
>
> On Tue, Jul 29, 2014 at 10:41 AM, David G Johnston <david.g.johnston@gmail.com> wrote:
>>
>> shreesha21 wrote
>> > Hey All,
>> > I am new to the postgreSQL and need some clarifications from your end.
>> >
>> > I am running a standalone PostgreSQL server and I want to have the data
>> > backed up for consistency and system availability. Upon exploring options
>> > available in postgresql, I think continuous archival and PITR suits for my
>> > system. But this method requires archive mode to be enabled which comes
>> > with resource requirements like space/memory etc.
>> > The following are my concerns:
>> > 1. Currently, our system is backing up the database data before restart
>> > and
>> > periodically for every 3 hours. Is there any option in postgresql to do
>> > the
>> > archive periodically, say for every 3 hours?
>> > 2. If there is a system reboot and if postgreSQL server was running in the
>> > system, Will I be ensured from postgreSQL that all the WAL logs archived
>> > so
>> > far contains all the necessary data that was committed before system
>> > reboot?
>> > 3. As I am running standalone server, I want to set the
>> > wal_level='archive'
>> > and not 'hot standby'. Am I right in my understanding here? (I saw couple
>> > of examples over the internet and they are using 'hot standby' as the
>> > wal_level. So wanted to clarify.)
>> >
>> > There are some constraints in my system as well.
>> > 4. The hard disk space in my system is minimal (around 300 MB). So I can't
>> > keep more archive files in my system as it may run out of space. I know
>> > that each archive files are of 16MB of size. So I want to keep it less. Is
>> > there any mechanism which can be used to cleanup the archive files
>> > periodically while the server is running, and ensure data consistency?
>> > 5. The system's performance is a concern. So I wanted to know whether by
>> > enabling the archiving option, will PostgreSQL show any delays in query
>> > execution and result retrieval?
>> >
>> > Sorry if I am bombarding many questions here. It would be great if any of
>> > you could answer each of my questions mentioned above.
>> > Eagerly looking forward for your response.
>> >
>> > Thanks in advance!
>> >
>> > Regards,
>> > Shreesha.
>>
>> You should indicate what version of PostgreSQL you are using.
>>
>> 1) The archives will occur as the files fill up.  The files themselves are
>> continually written.  In archive modes additional data is always written.
>> So, basically no, you cannot guarantee the archive will occur no more
>> frequently than every 3 hours.
>>
>> 2) If the database is shutdown cleanly I am almost positive you are
>> guaranteed a WAL archive
>>
>> 3) Some additional info is captured for hot standby mode but for offline
>> PITR either one will work.
>>
>> 4) Not totally fluent on this point but once a file has been archived
>> off-system it can and will be re-used so the main issue is if the archive
>> fails for some reason and you fill up before you can resolve the issue.
>>
>> 5) You will be writing a little more data to disk than in a non-backup setup
>> and you will periodically eat network bandwidth transferring the WAL files
>> to another system.  I doubt either hit is significant enough to forgo the
>> benefit of PITR.
>>
>>
>> I would probably be very concerned about only having 300MB on the production
>> system (I assume you have considerably more on the target archive storage
>> location).  If it is the archive system you are referring to you would have
>> to run pg_basebackup more frequently to minimize the overall archive storage
>> space but it has the added benefit of making recovery occur very quickly.
>>
>> David J.
>>
>>
>>
>>
>>
>>
>>
>> --
>> View this message in context: http://postgresql.1045698.n5.nabble.com/WAL-for-backup-tp5813194p5813197.html
>> Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
>>
>>
>> --
>> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-novice
>
>
>
>
> --
> ~Shreesha.