Re: WAL for backup - Mailing list pgsql-novice
| From | Sameer Kumar |
|---|---|
| Subject | Re: WAL for backup |
| Date | |
| Msg-id | CADp-Sm73hs+_nUKw47RfqBdkypcupimm0Xkf_0+kX1HFgQ1fNg@mail.gmail.com Whole thread Raw |
| In response to | WAL for backup (Shreesha <shreesha1988@gmail.com>) |
| List | pgsql-novice |
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
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.
pgsql-novice by date: