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!