It is possible to force periodically switch xlog?
I would like to know if there is any parameter I can set to force postgres to switch xlog periodically, even if it not reach the 16MB size?
Thanks,
Edson.
Hi Edson,
You can switch by using below command below PG 10.
Select pg_switch_xlog();
And there is no parameter present to switch it.
Regards,
Abdul Sayeed
On Thursday, April 2, 2020, Edson Richter <edsonrichter@hotmail.com> wrote:
I would like to know if there is any parameter I can set to force postgres
to switch xlog periodically, even if it not reach the 16MB size?Thanks,
Edson.
--
Thanks & Regards,
Abdul Sayeed
PostgreSQL DBA
Postgres Professional Certified
Skype: abdul.sayeed24
On 2020-Apr-02, Abdul Sayeed wrote:
Hi Edson,
You can switch by using below command below PG 10.
Select pg_switch_xlog();
And there is no parameter present to switch it.
IIRC archive_timeout does precisely that.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
________________________________
De: Alvaro Herrera <alvherre@2ndquadrant.com>
Enviado: quarta-feira, 1 de abril de 2020 19:55
Para: Abdul Sayeed <abdulsayeed24@gmail.com>
Cc: Edson Richter <edsonrichter@hotmail.com>; pgsql-general <pgsql-general@postgresql.org>
Assunto: Re: It is possible to force periodically switch xlog?
On 2020-Apr-02, Abdul Sayeed wrote:
Hi Edson,
You can switch by using below command below PG 10.
Select pg_switch_xlog();
And there is no parameter present to switch it.
IIRC archive_timeout does precisely that.
I would assume that "archive_mode" must be "on" or "always" - but I do have "archive_mode=off".
Regards,
Edson
--
Álvaro Herrera https://nam03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.2ndquadrant.com%2F&amp;data=02%7C01%7C%7Cbcfb62c8875b46399dcd08d7d68fbab1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637213785102248653&amp;sdata=6AHDGdb6Ommbv%2FdFImDVFJvsJ4eomP7jWWMlJjiOgvg%3D&amp;reserved=0
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 4/1/20 7:19 PM, Edson Richter wrote:
IIRC archive_timeout does precisely that.
I would assume that "archive_mode" must be "on" or "always" - but I do
have "archive_mode=off".
I'm not sure what the point of switching the WAL segment would be with
archive_mode=off.
What are you trying to accomplish?
Regards,
--
-David
david@pgmasters.net
At Wed, 1 Apr 2020 21:09:20 -0400, David Steele <david@pgmasters.net> wrote in
On 4/1/20 7:19 PM, Edson Richter wrote:
IIRC archive_timeout does precisely that.
I would assume that "archive_mode" must be "on" or "always" - but I do
have "archive_mode=off".I'm not sure what the point of switching the WAL segment would be with
archive_mode=off.What are you trying to accomplish?
Also I'm a bit curious about that, but anyway archive_timeout works
even with archive_mode=off. The name looks being a bit off of its
substance.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
________________________________
De: David Steele <david@pgmasters.net>
Enviado: quarta-feira, 1 de abril de 2020 22:09
Para: Edson Richter <edsonrichter@hotmail.com>; pgsql-general <pgsql-general@postgresql.org>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Assunto: Re: It is possible to force periodically switch xlog?
On 4/1/20 7:19 PM, Edson Richter wrote:
IIRC archive_timeout does precisely that.
I would assume that "archive_mode" must be "on" or "always" - but I do
have "archive_mode=off".
I'm not sure what the point of switching the WAL segment would be with
archive_mode=off.
What are you trying to accomplish?
Actually, I do have a stand by server I wish to update a bit more frequently in low traffic servers.
Regards,
Regards,
--
-David
david@pgmasters.net
________________________________
De: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Enviado: quarta-feira, 1 de abril de 2020 22:22
Para: david@pgmasters.net <david@pgmasters.net>
Cc: edsonrichter@hotmail.com <edsonrichter@hotmail.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>; alvherre@2ndquadrant.com <alvherre@2ndquadrant.com>
Assunto: Re: It is possible to force periodically switch xlog?
At Wed, 1 Apr 2020 21:09:20 -0400, David Steele <david@pgmasters.net> wrote in
On 4/1/20 7:19 PM, Edson Richter wrote:
IIRC archive_timeout does precisely that.
I would assume that "archive_mode" must be "on" or "always" - but I do
have "archive_mode=off".I'm not sure what the point of switching the WAL segment would be with
archive_mode=off.What are you trying to accomplish?
Also I'm a bit curious about that, but anyway archive_timeout works
even with archive_mode=off. The name looks being a bit off of its
substance.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
Good to know!
But meanwhile I've wrote a small bash script that executs pg_switch_xlog() and scheduled it in Crond.
Regards,
Edson
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://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
________________________________
De: Alvaro Herrera <alvherre@2ndquadrant.com>
Enviado: quarta-feira, 1 de abril de 2020 23:38
Para: Edson Richter <edsonrichter@hotmail.com>
Cc: pgsql-general <pgsql-general@postgresql.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