RE: It is possible to force periodically switch xlog?

________________________________
De: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Enviado: quarta-feira, 1 de abril de 2020 23:38
Para: Edson Richter <edsonrichter(at)hotmail(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Assunto: Re: It is possible to force periodically switch xlog?

On 2020-Apr-02, Edson Richter wrote:

> Actually, I do have a stand by server I wish to update a bit more
> frequently in low traffic servers.

It is better to use streaming replication for that. Using the WAL
archive for that is an archaic mechanism now.

--
Álvaro Herrera https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.2ndQuadrant.com%2F&amp;data=02%7C01%7C%7Cfc4cfe08909e429dc12f08d7d6af0184%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637213919432611092&amp;sdata=y5CpqDka4uAADykZjBOZHoTBOLPiO%2FADqAazhOnXgsQ%3D&amp;reserved=0
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Actually, I do use streaming 🙂
Our scenario is a bit more complex than ordinay wal archive or streaming.
With this setup we are able to backup 1.5 TB of data in less than 12 hours even with geographically distributted servers, and we have production.

1) We do have streaming from production to report server (geographically distributted)
2) Wal archive is setup from standby server to backup server (same location)
3) From time-to-time, we do barman backups from standby server (same location)
4) Twice a week, we restore every single database in a backup server to test backups.

Besides non standard, this setup is working really well for our needs.

But not everthing is shine like gold, and sometimes, for low traffic servers, barman complains not all wal segments were received.
Then we need to manually execute pg_switch_xlog at master, and after a "barman check-database" at backup server: this is what we would like to automate.
And it went well with a bash script and cron. At least, for 40 databases it is working really well.

Regards,

Edson