Re: WAL for backup - Mailing list pgsql-novice

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.