Re: WAL for backup

What is the version postgresql you are using? What is the transaction
volume you have got? Have you tried to run with minimal wal_level and
assert the number of wal files generated during your activity? 300mb is
considerably very small storage space for production/archive disk.

You may want to consider provisioning a separate server which has backups
and archives.

You can stream archives using pg_receivexlog and take backup using
pg_basebackup.

pg_receivexlog works same as streaming and will not wait for WAL to be
archived and hence your RPO can be more closer to point of crash.

All this would work with lot of ifs and buts. Hence its is imp to know your
version and OS platform.

Since the over head of having hot_standby over having archive is hardly
noticeable you may want to use hot_standby instead.

Regards
Sameer

PS: Sent from my Mobile device. Pls ignore typo n abb
On 30 Jul 2014 01:18, "Shreesha" <shreesha1988(at)gmail(dot)com> 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.
>