Re: WAL for backup

Thank you David for the quick response. Appreciate it!
I am currently running PostgreSQL version 9.3.4.

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(dot)g(dot)johnston(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>

--
~Shreesha.