WAL Archiving and base backup

[Issa Gorissen]

Hello all,

I've been upgrading some of our PostgreSQL instances from 9.x to 12.x 
and we're using a master/slave setup based on continuous archiving.


I can read on 
https://www.postgresql.org/docs/12/continuous-archiving.html 25.3.3.2. 
Making an Exclusive Low-Level Backup that the exclusive method is 
deprecated.


But then this leads me to the following problem:

- Non-Exclusive Low-Level Backup requires that the functions 
pg_start_backup() and pg_stop_backup() be called in the *same* 
connection to the database


- we use scripts to automate the creation of the backup and it's 
deployment on the slave, after a server maintenance for example


- the example given in 25.3.6.1. Standalone Hot Backups still uses the 
exclusive (deprecated) method


So I have this question, how to script the making of base backup for 
transfer on the slave server when the two SQL functions must be called 
in the same connection, in Bash for example; is this doable?


Thanks for any pointers.

Regards
--
Issa

Re: WAL Archiving and base backup

[Issa Gorissen]

Thx a lot. I thought about it but was not so sure about having a complex 
script (compared to the very simple version when using the exclusive 
backup - but this this is deprecated...).


I will test your option with the simpler version and post it back to it 
can maybe land in PostgreSQL documentation.


Regards

Re: WAL Archiving and base backup

[Issa Gorissen]

On 1/16/22 01:23, Mladen Gogala wrote:

On 1/14/22 16:00, David G. Johnston wrote:
I still don't really understand what is so great about it.  About its 
only redeeming feature is a declaration that "it is in core" and that 
newcomers can just default to it without thinking.  I'd rather just 
play favorites and write "use pgbackrest" in our documentation.  Or 
some hybrid approach where we don't just pick one but instead guide 
people to the community solutions that are out there.  I don't think I 
really want the people responsible for core to spend time on writing 
end-user backup tooling. Their time is much more valuably spent 
working on the core product.


David J.

Well, the "without thinking" part of your post can be rephrased as "ease 
of use". Do database administrators really need to think about which 
backup software to use? What kind of knowledge will such an evaluation 
provide? All commercial databases have some form of backup software 
included into the core database. After all, backup and restore are 
extremely important functions which IMHO should be provided along with 
the database software.



--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com




Just to avoid any misunderstanding. I am perfectly happy using the 
backup/restore with pg_dump and we use it for at least a decade with 
success for our need of backups.


My question is really in the context of WAL archiving and preparing a 
slave instance with a base 'non-exclusive' backup from a script (to be 
precise, on reboot of the server running the slave instance) since the 
exclusive way is deprecated.


Thx for all your valuable comments
--
Issa