Writing WAL files

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

Writing WAL files

Robert Inder
I am moving a database from PSQL 9 (!) on CentOS 6 to PSQL 12 on CentOS 7

I have a pair of servers -- one live, one standby.
The live server defines an archive_command as "rsync...." to shift WAL files to the standby server,
The standby server uses "pg_standby" to monitor and process incoming WAL files.
I believe this is all very vanilla, and indeed changes made in the live database are duly shipped to the standby.

BUT...

One of the things I like about the old PGSQL 9 setup is that it generates and ships a WAL file every few minutes, even if nothing has happened in the database.
I find it re-assuring to be able to see WAL files arriving and being processed without problem even when the live system was idle. 

But I cannot get PGSQL 12 to do this.  It only writes (and thus ships) WAL files when something happens in the database.
If the database is idle, it simply does not write any WAL files.

I thought I would get WAL files written from an idle database if, in postgresql.conf, I set "archive_timeout" to 120.

And I've tried setting "checkpoint_timeout" to 90s,

But to no avail.  No WAL files are written unless the database changes.

So what am I missing?  How CAN I get postgresql 12 to write "unnecessary" WAL files every couple of minutes?

Robert.

--
Robert Inder,                                    0131 229 1052 / 07808 492 213
Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
                                           Interactions speak louder than words
Reply | Threaded
Open this post in threaded view
|

Re: Writing WAL files

Adrian Klaver-4
On 10/4/20 9:54 AM, Robert Inder wrote:
> I am moving a database from PSQL 9 (!) on CentOS 6 to PSQL 12 on CentOS 7

It would help to know what the x in 9.x is? Before version 10 of
Postgres, the second number denoted a major version.

>
> I have a pair of servers -- one live, one standby.
> The live server defines an archive_command as "rsync...." to shift WAL
> files to the standby server,
> The standby server uses "pg_standby" to monitor and process incoming WAL
> files.
> I believe this is all very vanilla, and indeed changes made in the live
> database are duly shipped to the standby.
>
> BUT...
>
> One of the things I like about the old PGSQL 9 setup is that it
> generates and ships a WAL file every few minutes, even if nothing has
> happened in the database.
> I find it re-assuring to be able to see WAL files arriving and being
> processed without problem even when the live system was idle.
>
> But I cannot get PGSQL 12 to do this.  It only writes (and thus ships)
> WAL files when something happens in the database.
> If the database is idle, it simply does not write any WAL files.
>
> I thought I would get WAL files written from an idle database if, in
> postgresql.conf, I set "archive_timeout" to 120.
>
> And I've tried setting "checkpoint_timeout" to 90s,
>
> But to no avail.  No WAL files are written unless the database changes.
>
> So what am I missing?  How CAN I get postgresql 12 to write
> "unnecessary" WAL files every couple of minutes?
>
> Robert.
>
> --
> Robert Inder,                                    0131 229 1052 / 07808
> 492 213
> Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
> Registered in Scotland, Company no. SC 150689
>                                             Interactions speak louder
> than words


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Writing WAL files

Robert Inder


On Sun, 4 Oct 2020 at 18:01, Adrian Klaver <[hidden email]> wrote:
On 10/4/20 9:54 AM, Robert Inder wrote:
> I am moving a database from PSQL 9 (!) on CentOS 6 to PSQL 12 on CentOS 7

It would help to know what the x in 9.x is? Before version 10 of
Postgres, the second number denoted a major version.

9.4.
Moving to 12.4.

 

>
> I have a pair of servers -- one live, one standby.
> The live server defines an archive_command as "rsync...." to shift WAL
> files to the standby server,
> The standby server uses "pg_standby" to monitor and process incoming WAL
> files.
> I believe this is all very vanilla, and indeed changes made in the live
> database are duly shipped to the standby.
>
> BUT...
>
> One of the things I like about the old PGSQL 9 setup is that it
> generates and ships a WAL file every few minutes, even if nothing has
> happened in the database.
> I find it re-assuring to be able to see WAL files arriving and being
> processed without problem even when the live system was idle.
>
> But I cannot get PGSQL 12 to do this.  It only writes (and thus ships)
> WAL files when something happens in the database.
> If the database is idle, it simply does not write any WAL files.
>
> I thought I would get WAL files written from an idle database if, in
> postgresql.conf, I set "archive_timeout" to 120.
>
> And I've tried setting "checkpoint_timeout" to 90s,
>
> But to no avail.  No WAL files are written unless the database changes.
>
> So what am I missing?  How CAN I get postgresql 12 to write
> "unnecessary" WAL files every couple of minutes?
>
> Robert.
>
> --
> Robert Inder,                                    0131 229 1052 / 07808
> 492 213
> Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
> Registered in Scotland, Company no. SC 150689
>                                             Interactions speak louder
> than words


--
Adrian Klaver
[hidden email]




--
Robert Inder,                                    0131 229 1052 / 07808 492 213
Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
                                           Interactions speak louder than words
Reply | Threaded
Open this post in threaded view
|

Re: Writing WAL files

Adrian Klaver-4
On 10/4/20 10:30 AM, Robert Inder wrote:

>
>
> On Sun, 4 Oct 2020 at 18:01, Adrian Klaver <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     On 10/4/20 9:54 AM, Robert Inder wrote:
>      > I am moving a database from PSQL 9 (!) on CentOS 6 to PSQL 12 on
>     CentOS 7
>
>     It would help to know what the x in 9.x is? Before version 10 of
>     Postgres, the second number denoted a major version.
>
>
> 9.4.
> Moving to 12.4.

Well I'm going to say it has to do with this:

https://www.postgresql.org/docs/10/release-10.html

E.15.3.1.9.1. Write-Ahead Log (WAL)
Prevent unnecessary checkpoints and WAL archiving on otherwise-idle
systems (Michael Paquier)

Because that is when this:

". (Increasing checkpoint_timeout will reduce unnecessary checkpoints on
an idle system.)"

disappeared from the archive_timeout docs:

https://www.postgresql.org/docs/9.6/runtime-config-wal.html

vs

https://www.postgresql.org/docs/10/runtime-config-wal.html

Someone else will have to fill in the details.


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Writing WAL files

Adrian Klaver-4
In reply to this post by Robert Inder
On 10/4/20 10:30 AM, Robert Inder wrote:

>
>
> On Sun, 4 Oct 2020 at 18:01, Adrian Klaver <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     On 10/4/20 9:54 AM, Robert Inder wrote:
>      > I am moving a database from PSQL 9 (!) on CentOS 6 to PSQL 12 on
>     CentOS 7
>
>     It would help to know what the x in 9.x is? Before version 10 of
>     Postgres, the second number denoted a major version.
>
>
> 9.4.
> Moving to 12.4.
>

Should have added to previous post:

Did you restart the server after you made the changes?


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Writing WAL files

Álvaro Herrera
In reply to this post by Robert Inder
Hello Robert

On 2020-Oct-04, Robert Inder wrote:

> One of the things I like about the old PGSQL 9 setup is that it generates
> and ships a WAL file every few minutes, even if nothing has happened in the
> database.
> I find it re-assuring to be able to see WAL files arriving and being
> processed without problem even when the live system was idle.
>
> But I cannot get PGSQL 12 to do this.  It only writes (and thus ships) WAL
> files when something happens in the database.
> If the database is idle, it simply does not write any WAL files.

This is on purpose; archiving WAL files that contain nothing is pure
wastage of good electrons.  I suggest that in PG12 you can monitor the
"lag" of a standby server more directly by looking at columns write_lag,
flush_lag, replay_lag in the pg_stat_replication view.  (You'll need to
change your configuration so that it uses streaming replication instead
of pg_standby and rsync, but that's far more convenient so it's a good
change anyway.)


Reply | Threaded
Open this post in threaded view
|

Re: Writing WAL files

Robert Inder


On Sun, 4 Oct 2020 at 20:52, Alvaro Herrera <[hidden email]> wrote:

This is on purpose; archiving WAL files that contain nothing is pure
wastage of good electrons. 
Seriously?  Oh, holy ****
 
I suggest that in PG12 you can monitor the
"lag" of a standby server more directly by looking at columns write_lag,
flush_lag, replay_lag in the pg_stat_replication view. 

And are those things updated when there are no changes to the master database?
If so, can anyone make the case that continually checking and updating them (how often?) wastes fewer electrons than shipping an empty file every few minutes?

Or are they only measured when something is updated?

If I upgrade/install/reconfigure/restart something, I want to know that I haven't broken the sync.

Will looking at the replay_lag (where?  master?  standby?) tell me that the sync is still good?
Or will they capture the last sync. operation, and so only tell me what I need to know if I do some kind of database operation?

And if I have to do some kind of database operation, I may as well stick wiht the current arrangement,
since that operation would force a WAL file transfer anyway...

(You'll need to
change your configuration so that it uses streaming replication instead
of pg_standby and rsync, but that's far more convenient so it's a good
change anyway.)

Maybe, but it's forcing me to spend time understanding stuff that I really don't want to know about.

Robert.

--
Robert Inder,                                    0131 229 1052 / 07808 492 213
Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
                                           Interactions speak louder than words
Reply | Threaded
Open this post in threaded view
|

Re: Writing WAL files

David G Johnston
On Sunday, October 4, 2020, Robert Inder <[hidden email]> wrote:
than shipping an empty file every few minutes? 

The file is not empty.  We’re talking 16 megabytes in a default setup...

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Writing WAL files

Michael Lewis
In reply to this post by Robert Inder

I suggest that in PG12 you can monitor the
"lag" of a standby server more directly by looking at columns write_lag,
flush_lag, replay_lag in the pg_stat_replication view. 

And are those things updated when there are no changes to the master database?
If so, can anyone make the case that continually checking and updating them (how often?) wastes fewer electrons than shipping an empty file every few minutes?

Or are they only measured when something is updated?

If you setup a scripted process to update a single row with a timestamptz on the source/primary every minute, then you have a very simple consistent change and also a way to check on the replica what is current time vs last_scripted_update_time if you will and know the approx lag. It would seem like a simple albeit hacky solution to you wanting a file every X minutes regardless of server activity.

By the by, top-posting (reply above all quoted text) is discouraged on these groups.
Reply | Threaded
Open this post in threaded view
|

Re: Writing WAL files

Robert Inder


On Mon, 5 Oct 2020 at 18:29, Michael Lewis <[hidden email]> wrote:

If you setup a scripted process to update a single row with a timestamptz on the source/primary every minute, then you have a very simple consistent change and also a way to check on the replica what is current time vs last_scripted_update_time if you will and know the approx lag. It would seem like a simple albeit hacky solution to you wanting a file every X minutes regardless of server activity.
 
I'd thought of going half way to that, and just have a cron job for
   psql -c 'CHECKPOINT'
which doesn't give me the quantitative indication I'd get from updating a timestamp, but doesn't commit me to know about any particular database, either.

The thing that I find most disconcerting is that the documentation for Version 12 says checkpoint_timeout is
    "Maximum time between automatic WAL checkpoints"

But the change Adrian Klaverd highlighted suggests that this is deliberately no longer the case,
and I am left wondering what it does, in fact do/mean now.


By the by, top-posting (reply above all quoted text) is discouraged on these groups.

Indeed.  Once upon a time, my signature use to be along the lines of


So what?  It's easier for me, so I'll do it!

> > What's wrong with top posting?
> It makes it hard to see comments in context.

Robert.

--
Robert Inder,                                    0131 229 1052 / 07808 492 213
Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
                                           Interactions speak louder than words

Reply | Threaded
Open this post in threaded view
|

Re: Writing WAL files

David G Johnston
On Monday, October 5, 2020, Robert Inder <[hidden email]> wrote:
But the change Adrian Klaverd highlighted suggests that this is deliberately no longer the case,
and I am left wondering what it does, in fact do/mean now.

If no WAL has been written since the previous checkpoint, new checkpoints will be skipped even if checkpoint_timeout has passed. ‘


David J.

Reply | Threaded
Open this post in threaded view
|

Re: Writing WAL files

Peter J. Holzer
In reply to this post by Michael Lewis
On 2020-10-05 11:29:04 -0600, Michael Lewis wrote:
>
>         I suggest that in PG12 you can monitor the
>         "lag" of a standby server more directly by looking at columns
>         write_lag,
>         flush_lag, replay_lag in the pg_stat_replication view. 
>
>
>     And are those things updated when there are no changes to the master
>     database?

Probably not, as there is nothing to replicate, so no new data it
provided.


> If you setup a scripted process to update a single row with a timestamptz on
> the source/primary every minute, then you have a very simple consistent change
> and also a way to check on the replica what is current time vs
> last_scripted_update_time if you will and know the approx lag. It would seem
> like a simple albeit hacky solution to you wanting a file every X minutes
> regardless of server activity.

It also has the advantage that you don't have to wait for the WAL file
to be written. You can just check whether the change appears on the
replicas. About 2 years ago I wrote a Nagios/Icinga check that does
that: Update a timestamp in a table on the master, then connect to all
the replicas and wait for the change to show up on them. It then reports
the lag for each replica and a final status (OK, WARNING, CRITICAL)
based on the maximal lag.

I think I wrote it because the PostgreSQL version we were using at the
time didn't have the lag columns yet, but it does have the advantage of
providing an end to end check (do I really get the correct value?), not
the database's idea of whether replication is working.

(The check is written in Go and buried in a svn repo at work, but I
could publish it if there is interest)

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [hidden email]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Writing WAL files

Cory Nemelka


On Sat, Oct 10, 2020 at 3:41 AM Peter J. Holzer <[hidden email]> wrote:
On 2020-10-05 11:29:04 -0600, Michael Lewis wrote:
>
>         I suggest that in PG12 you can monitor the
>         "lag" of a standby server more directly by looking at columns
>         write_lag,
>         flush_lag, replay_lag in the pg_stat_replication view. 
>
>
>     And are those things updated when there are no changes to the master
>     database?

Probably not, as there is nothing to replicate, so no new data it
provided.


> If you setup a scripted process to update a single row with a timestamptz on
> the source/primary every minute, then you have a very simple consistent change
> and also a way to check on the replica what is current time vs
> last_scripted_update_time if you will and know the approx lag. It would seem
> like a simple albeit hacky solution to you wanting a file every X minutes
> regardless of server activity.

It also has the advantage that you don't have to wait for the WAL file
to be written. You can just check whether the change appears on the
replicas. About 2 years ago I wrote a Nagios/Icinga check that does
that: Update a timestamp in a table on the master, then connect to all
the replicas and wait for the change to show up on them. It then reports
the lag for each replica and a final status (OK, WARNING, CRITICAL)
based on the maximal lag.

I think I wrote it because the PostgreSQL version we were using at the
time didn't have the lag columns yet, but it does have the advantage of
providing an end to end check (do I really get the correct value?), not
the database's idea of whether replication is working.

(The check is written in Go and buried in a svn repo at work, but I
could publish it if there is interest)

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [hidden email]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

I would be interested in the Nagios/Icinga check you wrote.