public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nitin Jadhav <[email protected]>
To: Matthias van de Meent <[email protected]>
Cc: Bharath Rupireddy <[email protected]>
Cc: Ashutosh Sharma <[email protected]>
Cc: Julien Rouhaud <[email protected]>
Cc: Bruce Momjian <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Magnus Hagander <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Report checkpoint progress with pg_stat_progress_checkpoint (was: Report checkpoint progress in server logs)
Date: Wed, 2 Mar 2022 16:45:20 +0530
Message-ID: <CAMm1aWYWmqys2O7UekQ0=ZSWSpG9h2qoEW7w46yiWM0eO1Lu7g@mail.gmail.com> (raw)
In-Reply-To: <CAMm1aWbuJLEnAjT4_1s8eArmZq=H7oi0Jv=J2GnRpBhkbC_PVw@mail.gmail.com>
References: <CALj2ACUGWAC56yqhVOisNPXhewfRdeHG4NGTOK_sOStL=7yP+w@mail.gmail.com>
<CAMm1aWY_wThAxmhC_7mkFsYHcNP2_JQLAKJEoY5EcHBi0Q42QQ@mail.gmail.com>
<CAEze2WhfmEWDKcFx8jhcSFhbSoP7ettdYiikNSsVWCNpe5ME0g@mail.gmail.com>
<CAMm1aWYsfgO91mXxy7kzrDcML8nuY3Ab4w9RnSjZ_mr6=Y-hwQ@mail.gmail.com>
<20220217110515.zssjnez4lq2lmrdq@jrouhaud>
<CAMm1aWZ1YY6n8T=0OkwQJJdua7fpkJdKr1fRqfTSrJb1ME9Q5g@mail.gmail.com>
<20220217172724.uhs2v3mgio7y6ga5@jrouhaud>
<CAMm1aWbjzP9g9ebkYqqJvNAW4jh6wP0maKW2Ozcv8PLVu76cFA@mail.gmail.com>
<20220218074351.wgfosjt5lql2vjbr@jrouhaud>
<CAMm1aWadyUhb89DEfgrs2j5i2u--O06x6oTp0EEhgGHtnw-SXA@mail.gmail.com>
<20220219053215.r4wqatln5awklecf@jrouhaud>
<CAMm1aWZzPVpLs7k5sQU4DObvh6YpOhjibcq2ykDrC_n25jeJNA@mail.gmail.com>
<CAMm1aWZ07VYUPpvmV9Ck_VX-o+xH=hEBs-67scd_Us6U6FKbHA@mail.gmail.com>
<CAE9k0Png9tjW5M7wjMKzEoOqkHS-VToP9yC2pr=HX7aQ0yQRNA@mail.gmail.com>
<CAMm1aWa8p=o60y1Z71bjAjWH8XH2Y+kZ6cNjQ21uK2_i65soOQ@mail.gmail.com>
<CALj2ACUNnqY7oc5C5yGS1ZbHV1RtQgPGiMkPTm989czCDwag6g@mail.gmail.com>
<CAEze2Wh_+P9Hs4RQ=tc=70rdB5h0omGx5Kz-BLhsJPRnKQ+cWw@mail.gmail.com>
<CAMm1aWbuJLEnAjT4_1s8eArmZq=H7oi0Jv=J2GnRpBhkbC_PVw@mail.gmail.com>
Thanks for reviewing.
> > > I suggested upthread to store the starting timeline instead. This way you can
> > > deduce whether it's a restartpoint or a checkpoint, but you can also deduce
> > > other information, like what was the starting WAL.
> >
> > I don't understand why we need the timeline here to just determine
> > whether it's a restartpoint or checkpoint.
>
> I'm not saying it's necessary, I'm saying that for the same space usage we can
> store something a bit more useful. If no one cares about having the starting
> timeline available for no extra cost then sure, let's just store the kind
> directly.
Fixed.
> 2) Can't we just have these checks inside CASE-WHEN-THEN-ELSE blocks
> directly instead of new function pg_stat_get_progress_checkpoint_kind?
> + snprintf(ckpt_kind, MAXPGPATH, "%s%s%s%s%s%s%s%s%s",
> + (flags == 0) ? "unknown" : "",
> + (flags & CHECKPOINT_IS_SHUTDOWN) ? "shutdown " : "",
> + (flags & CHECKPOINT_END_OF_RECOVERY) ? "end-of-recovery " : "",
> + (flags & CHECKPOINT_IMMEDIATE) ? "immediate " : "",
> + (flags & CHECKPOINT_FORCE) ? "force " : "",
> + (flags & CHECKPOINT_WAIT) ? "wait " : "",
> + (flags & CHECKPOINT_CAUSE_XLOG) ? "wal " : "",
> + (flags & CHECKPOINT_CAUSE_TIME) ? "time " : "",
> + (flags & CHECKPOINT_FLUSH_ALL) ? "flush-all" : "");
Fixed.
---
> 5) Do we need a special phase for this checkpoint operation? I'm not
> sure in which cases it will take a long time, but it looks like
> there's a wait loop here.
> vxids = GetVirtualXIDsDelayingChkpt(&nvxids);
> if (nvxids > 0)
> {
> do
> {
> pg_usleep(10000L); /* wait for 10 msec */
> } while (HaveVirtualXIDsDelayingChkpt(vxids, nvxids));
> }
Yes. It is better to add a separate phase here.
---
> Also, how about special phases for SyncPostCheckpoint(),
> SyncPreCheckpoint(), InvalidateObsoleteReplicationSlots(),
> PreallocXlogFiles() (it currently pre-allocates only 1 WAL file, but
> it might be increase in future (?)), TruncateSUBTRANS()?
SyncPreCheckpoint() is just incrementing a counter and
PreallocXlogFiles() currently pre-allocates only 1 WAL file. I feel
there is no need to add any phases for these as of now. We can add in
the future if necessary. Added phases for SyncPostCheckpoint(),
InvalidateObsoleteReplicationSlots() and TruncateSUBTRANS().
---
> 6) SLRU (Simple LRU) isn't a phase here, you can just say
> PROGRESS_CHECKPOINT_PHASE_PREDICATE_LOCK_PAGES.
> +
> + pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
> + PROGRESS_CHECKPOINT_PHASE_SLRU_PAGES);
> CheckPointPredicate();
>
> And :s/checkpointing SLRU pages/checkpointing predicate lock pages
>+ WHEN 9 THEN 'checkpointing SLRU pages'
Fixed.
---
> 7) :s/PROGRESS_CHECKPOINT_PHASE_FILE_SYNC/PROGRESS_CHECKPOINT_PHASE_PROCESS_FILE_SYNC_REQUESTS
I feel PROGRESS_CHECKPOINT_PHASE_FILE_SYNC is a better option here as
it describes the purpose in less words.
> And :s/WHEN 11 THEN 'performing sync requests'/WHEN 11 THEN
> 'processing file sync requests'
Fixed.
---
> 8) :s/Finalizing/finalizing
> + WHEN 14 THEN 'Finalizing'
Fixed.
---
> 9) :s/checkpointing snapshots/checkpointing logical replication snapshot files
> + WHEN 3 THEN 'checkpointing snapshots'
> :s/checkpointing logical rewrite mappings/checkpointing logical
> replication rewrite mapping files
> + WHEN 4 THEN 'checkpointing logical rewrite mappings'
Fixed.
---
> 10) I'm not sure if it's discussed, how about adding the number of
> snapshot/mapping files so far the checkpoint has processed in file
> processing while loops of
> CheckPointSnapBuild/CheckPointLogicalRewriteHeap? Sometimes, there can
> be many logical snapshot or mapping files and users may be interested
> in knowing the so-far-processed-file-count.
I had thought about this while sharing the v1 patch and mentioned my
views upthread. I feel it won't give meaningful progress information
(It can be treated as statistics). Hence not included. Thoughts?
> > > As mentioned upthread, there can be multiple backends that request a
> > > checkpoint, so unless we want to store an array of pid we should store a number
> > > of backend that are waiting for a new checkpoint.
> >
> > Yeah, you are right. Let's not go that path and store an array of
> > pids. I don't see a strong use-case with the pid of the process
> > requesting checkpoint. If required, we can add it later once the
> > pg_stat_progress_checkpoint view gets in.
>
> I don't think that's really necessary to give the pid list.
>
> If you requested a new checkpoint, it doesn't matter if it's only your backend
> that triggered it, another backend or a few other dozen, the result will be the
> same and you have the information that the request has been seen. We could
> store just a bool for that but having a number instead also gives a bit more
> information and may allow you to detect some broken logic on your client code
> if it keeps increasing.
It's a good metric to show in the view but the information is not
readily available. Additional code is required to calculate the number
of requests. Is it worth doing that? I feel this can be added later if
required.
Please find the v4 patch attached and share your thoughts.
Thanks & Regards,
Nitin Jadhav
On Tue, Mar 1, 2022 at 2:27 PM Nitin Jadhav
<[email protected]> wrote:
>
> > > 3) Why do we need this extra calculation for start_lsn?
> > > Do you ever see a negative LSN or something here?
> > > + ('0/0'::pg_lsn + (
> > > + CASE
> > > + WHEN (s.param3 < 0) THEN pow((2)::numeric, (64)::numeric)
> > > + ELSE (0)::numeric
> > > + END + (s.param3)::numeric)) AS start_lsn,
> >
> > Yes: LSN can take up all of an uint64; whereas the pgstat column is a
> > bigint type; thus the signed int64. This cast is OK as it wraps
> > around, but that means we have to take care to correctly display the
> > LSN when it is > 0x7FFF_FFFF_FFFF_FFFF; which is what we do here using
> > the special-casing for negative values.
>
> Yes. The extra calculation is required here as we are storing unit64
> value in the variable of type int64. When we convert uint64 to int64
> then the bit pattern is preserved (so no data is lost). The high-order
> bit becomes the sign bit and if the sign bit is set, both the sign and
> magnitude of the value changes. To safely get the actual uint64 value
> whatever was assigned, we need the above calculations.
>
> > > 4) Can't you use timestamptz_in(to_char(s.param4)) instead of
> > > pg_stat_get_progress_checkpoint_start_time? I don't quite understand
> > > the reasoning for having this function and it's named as *checkpoint*
> > > when it doesn't do anything specific to the checkpoint at all?
> >
> > I hadn't thought of using the types' inout functions, but it looks
> > like timestamp IO functions use a formatted timestring, which won't
> > work with the epoch-based timestamp stored in the view.
>
> There is a variation of to_timestamp() which takes UNIX epoch (float8)
> as an argument and converts it to timestamptz but we cannot directly
> call this function with S.param4.
>
> TimestampTz
> GetCurrentTimestamp(void)
> {
> TimestampTz result;
> struct timeval tp;
>
> gettimeofday(&tp, NULL);
>
> result = (TimestampTz) tp.tv_sec -
> ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY);
> result = (result * USECS_PER_SEC) + tp.tv_usec;
>
> return result;
> }
>
> S.param4 contains the output of the above function
> (GetCurrentTimestamp()) which returns Postgres epoch but the
> to_timestamp() expects UNIX epoch as input. So some calculation is
> required here. I feel the SQL 'to_timestamp(946684800 +
> (S.param4::float / 1000000)) AS start_time' works fine. The value
> '946684800' is equal to ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) *
> SECS_PER_DAY). I am not sure whether it is good practice to use this
> way. Kindly share your thoughts.
>
> Thanks & Regards,
> Nitin Jadhav
>
> On Mon, Feb 28, 2022 at 6:40 PM Matthias van de Meent
> <[email protected]> wrote:
> >
> > On Sun, 27 Feb 2022 at 16:14, Bharath Rupireddy
> > <[email protected]> wrote:
> > > 3) Why do we need this extra calculation for start_lsn?
> > > Do you ever see a negative LSN or something here?
> > > + ('0/0'::pg_lsn + (
> > > + CASE
> > > + WHEN (s.param3 < 0) THEN pow((2)::numeric, (64)::numeric)
> > > + ELSE (0)::numeric
> > > + END + (s.param3)::numeric)) AS start_lsn,
> >
> > Yes: LSN can take up all of an uint64; whereas the pgstat column is a
> > bigint type; thus the signed int64. This cast is OK as it wraps
> > around, but that means we have to take care to correctly display the
> > LSN when it is > 0x7FFF_FFFF_FFFF_FFFF; which is what we do here using
> > the special-casing for negative values.
> >
> > As to whether it is reasonable: Generating 16GB of wal every second
> > (2^34 bytes /sec) is probably not impossible (cpu <> memory bandwidth
> > has been > 20GB/sec for a while); and that leaves you 2^29 seconds of
> > database runtime; or about 17 years. Seeing that a cluster can be
> > `pg_upgrade`d (which doesn't reset cluster LSN) since PG 9.0 from at
> > least version PG 8.4.0 (2009) (and through pg_migrator, from 8.3.0)),
> > we can assume that clusters hitting LSN=2^63 will be a reasonable
> > possibility within the next few years. As the lifespan of a PG release
> > is about 5 years, it doesn't seem impossible that there will be actual
> > clusters that are going to hit this naturally in the lifespan of PG15.
> >
> > It is also possible that someone fat-fingers pg_resetwal; and creates
> > a cluster with LSN >= 2^63; resulting in negative values in the
> > s.param3 field. Not likely, but we can force such situations; and as
> > such we should handle that gracefully.
> >
> > > 4) Can't you use timestamptz_in(to_char(s.param4)) instead of
> > > pg_stat_get_progress_checkpoint_start_time? I don't quite understand
> > > the reasoning for having this function and it's named as *checkpoint*
> > > when it doesn't do anything specific to the checkpoint at all?
> >
> > I hadn't thought of using the types' inout functions, but it looks
> > like timestamp IO functions use a formatted timestring, which won't
> > work with the epoch-based timestamp stored in the view.
> >
> > > Having 3 unnecessary functions that aren't useful to the users at all
> > > in proc.dat will simply eatup the function oids IMO. Hence, I suggest
> > > let's try to do without extra functions.
> >
> > I agree that (1) could be simplified, or at least fully expressed in
> > SQL without exposing too many internals. If we're fine with exposing
> > internals like flags and type layouts, then (2), and arguably (4), can
> > be expressed in SQL as well.
> >
> > -Matthias
Attachments:
[application/octet-stream] v4-0001-pg_stat_progress_checkpoint-view.patch (34.9K, ../CAMm1aWYWmqys2O7UekQ0=ZSWSpG9h2qoEW7w46yiWM0eO1Lu7g@mail.gmail.com/2-v4-0001-pg_stat_progress_checkpoint-view.patch)
download | inline diff:
From a3ff5c7b194d1128ab0272e546913baeafc04a0c Mon Sep 17 00:00:00 2001
From: Nitin Jadhav <[email protected]>
Date: Wed, 2 Mar 2022 11:06:57 +0000
Subject: [PATCH] pg_stat_progress_checkpoint view
---
doc/src/sgml/monitoring.sgml | 384 +++++++++++++++++++++++++++
doc/src/sgml/ref/checkpoint.sgml | 6 +
doc/src/sgml/wal.sgml | 5 +-
src/backend/access/transam/xlog.c | 98 +++++++
src/backend/catalog/system_views.sql | 50 ++++
src/backend/storage/buffer/bufmgr.c | 7 +
src/backend/storage/sync/sync.c | 6 +
src/backend/utils/adt/pgstatfuncs.c | 2 +
src/include/commands/progress.h | 36 +++
src/include/utils/backend_progress.h | 3 +-
src/test/regress/expected/rules.out | 76 ++++++
11 files changed, 671 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 9fb62fec8e..8d7c8ffc92 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -401,6 +401,13 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
See <xref linkend='copy-progress-reporting'/>.
</entry>
</row>
+
+ <row>
+ <entry><structname>pg_stat_progress_checkpoint</structname><indexterm><primary>pg_stat_progress_checkpoint</primary></indexterm></entry>
+ <entry>One row only, showing the progress of the checkpoint.
+ See <xref linkend='checkpoint-progress-reporting'/>.
+ </entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -6844,6 +6851,383 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
</table>
</sect2>
+ <sect2 id="checkpoint-progress-reporting">
+ <title>Checkpoint Progress Reporting</title>
+
+ <indexterm>
+ <primary>pg_stat_progress_checkpoint</primary>
+ </indexterm>
+
+ <para>
+ Whenever the checkpoint operation is running, the
+ <structname>pg_stat_progress_checkpoint</structname> view will contain a
+ single row indicating the progress of the checkpoint. The tables below
+ describe the information that will be reported and provide information about
+ how to interpret it.
+ </para>
+
+ <table id="pg-stat-progress-checkpoint-view" xreflabel="pg_stat_progress_checkpoint">
+ <title><structname>pg_stat_progress_checkpoint</structname> View</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pid</structfield> <type>integer</type>
+ </para>
+ <para>
+ Process ID of the checkpointer process.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>type</structfield> <type>text</type>
+ </para>
+ <para>
+ Type of checkpoint. See <xref linkend="checkpoint-types"/>.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>kind</structfield> <type>text</type>
+ </para>
+ <para>
+ Kind of checkpoint. See <xref linkend="checkpoint-kinds"/>.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>start_lsn</structfield> <type>text</type>
+ </para>
+ <para>
+ The checkpoint start location.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>start_time</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Elapsed time of the checkpoint.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>phase</structfield> <type>text</type>
+ </para>
+ <para>
+ Current processing phase. See <xref linkend="checkpoint-phases"/>.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers_total</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Total number of buffers to be written. This is estimated and reported
+ as of the beginning of buffer write operation.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers_processed</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of buffers processed. This counter increases when the targeted
+ buffer is processed. This number will eventually become equal to
+ <literal>buffers_total</literal> when the checkpoint is
+ complete.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers_written</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of buffers written. This counter only advances when the targeted
+ buffers is written. Note that some of the buffers are processed but may
+ not required to be written. So this count will always be less than or
+ equal to <literal>buffers_total</literal>.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>files_total</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Total number of files to be synced. This is estimated and reported as of
+ the beginning of sync operation.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>files_synced</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of files synced. This counter advances when the targeted file is
+ synced. This number will eventually become equal to
+ <literal>files_total</literal> when the checkpoint is complete.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <table id="checkpoint-types">
+ <title>Checkpoint types</title>
+ <tgroup cols="2">
+ <colspec colname="col1" colwidth="1*"/>
+ <colspec colname="col2" colwidth="2*"/>
+ <thead>
+ <row>
+ <entry>Types</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>checkpoint</literal></entry>
+ <entry>
+ The current operation is checkpoint.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>restartpoint</literal></entry>
+ <entry>
+ The current operation is restartpoint.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <table id="checkpoint-kinds">
+ <title>Checkpoint kinds</title>
+ <tgroup cols="2">
+ <colspec colname="col1" colwidth="1*"/>
+ <colspec colname="col2" colwidth="2*"/>
+ <thead>
+ <row>
+ <entry>Kinds</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>shutdown</literal></entry>
+ <entry>
+ The checkpoint is for shutdown.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>end-of-recovery</literal></entry>
+ <entry>
+ The checkpoint is for end-of-recovery.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>immediate</literal></entry>
+ <entry>
+ The checkpoint is happens without delays.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>force</literal></entry>
+ <entry>
+ The checkpoint is started because some operation forced a checkpoint.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>flush all</literal></entry>
+ <entry>
+ The checkpoint flushes all pages, including those belonging to unlogged
+ tables.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>wait</literal></entry>
+ <entry>
+ Wait for completion before returning.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>requested</literal></entry>
+ <entry>
+ The checkpoint request has been made.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>wal</literal></entry>
+ <entry>
+ The checkpoint is started because <literal>max_wal_size</literal> is
+ reached.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>time</literal></entry>
+ <entry>
+ The checkpoint is started because <literal>checkpoint_timeout</literal>
+ expired.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <table id="checkpoint-phases">
+ <title>Checkpoint phases</title>
+ <tgroup cols="2">
+ <colspec colname="col1" colwidth="1*"/>
+ <colspec colname="col2" colwidth="2*"/>
+ <thead>
+ <row>
+ <entry>Phase</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>initializing</literal></entry>
+ <entry>
+ The checkpointer process is preparing to begin the checkpoint operation.
+ This phase is expected to be very brief.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>getting virtual transaction IDs</literal></entry>
+ <entry>
+ The checkpointer process is getting the virtual transaction IDs that
+ are delaying the checkpoint.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing replication slots</literal></entry>
+ <entry>
+ The checkpointer process is currently flushing all the replication slots
+ to disk.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing logical replication snapshot files</literal></entry>
+ <entry>
+ The checkpointer process is currently removing all the serialized
+ snapshot files that are not required anymore.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing logical rewrite mapping files</literal></entry>
+ <entry>
+ The checkpointer process is currently removing/flushing the logical
+ rewrite mapping files.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing commit log pages</literal></entry>
+ <entry>
+ The checkpointer process is currently writing commit log pages to disk.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing commit time stamp pages</literal></entry>
+ <entry>
+ The checkpointer process is currently writing commit time stamp pages to
+ disk.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing subtransaction pages</literal></entry>
+ <entry>
+ The checkpointer process is currently writing subtransaction pages to disk.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing multixact pages</literal></entry>
+ <entry>
+ The checkpointer process is currently writing multixact pages to disk.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing predicate lock pages</literal></entry>
+ <entry>
+ The checkpointer process is currently writing predicate lock pages to disk.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing buffers</literal></entry>
+ <entry>
+ The checkpointer process is currently writing buffers to disk.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>processing file sync requests</literal></entry>
+ <entry>
+ The checkpointer process is currently processing file sync requests.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>performing two phase checkpoint</literal></entry>
+ <entry>
+ The checkpointer process is currently performing two phase checkpoint.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>performing post checkpoint cleanup</literal></entry>
+ <entry>
+ The checkpointer process is currently performing post checkpoint cleanup.
+ It removes any lingering files that can be safely removed.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>invalidating replication slots</literal></entry>
+ <entry>
+ The checkpointer process is currently invalidating replication slots.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>recycling old XLOG files</literal></entry>
+ <entry>
+ The checkpointer process is currently recycling old XLOG files.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>truncating subtransactions</literal></entry>
+ <entry>
+ The checkpointer process is currently removing the subtransaction
+ segments.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>finalizing</literal></entry>
+ <entry>
+ The checkpointer process is finalizing the checkpoint operation.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect2>
+
</sect1>
<sect1 id="dynamic-trace">
diff --git a/doc/src/sgml/ref/checkpoint.sgml b/doc/src/sgml/ref/checkpoint.sgml
index 1cebc03d15..a88c76533a 100644
--- a/doc/src/sgml/ref/checkpoint.sgml
+++ b/doc/src/sgml/ref/checkpoint.sgml
@@ -56,6 +56,12 @@ CHECKPOINT
the <link linkend="predefined-roles-table"><literal>pg_checkpointer</literal></link>
role can call <command>CHECKPOINT</command>.
</para>
+
+ <para>
+ The checkpointer process running the checkpoint will report its progress
+ in the <structname>pg_stat_progress_checkpoint</structname> view. See
+ <xref linkend="checkpoint-progress-reporting"/> for details.
+ </para>
</refsect1>
<refsect1>
diff --git a/doc/src/sgml/wal.sgml b/doc/src/sgml/wal.sgml
index 2bb27a8468..a75d1d63d0 100644
--- a/doc/src/sgml/wal.sgml
+++ b/doc/src/sgml/wal.sgml
@@ -530,7 +530,10 @@
adjust the <xref linkend="guc-archive-timeout"/> parameter rather than the
checkpoint parameters.)
It is also possible to force a checkpoint by using the SQL
- command <command>CHECKPOINT</command>.
+ command <command>CHECKPOINT</command>. The checkpointer process running the
+ checkpoint will report its progress in the
+ <structname>pg_stat_progress_checkpoint</structname> view. See
+ <xref linkend="checkpoint-progress-reporting"/> for details.
</para>
<para>
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 0d2bd7a357..9591f2cecc 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -65,6 +65,7 @@
#include "catalog/catversion.h"
#include "catalog/pg_control.h"
#include "catalog/pg_database.h"
+#include "commands/progress.h"
#include "common/controldata_utils.h"
#include "executor/instrument.h"
#include "miscadmin.h"
@@ -719,6 +720,8 @@ static void WALInsertLockAcquireExclusive(void);
static void WALInsertLockRelease(void);
static void WALInsertLockUpdateInsertingAt(XLogRecPtr insertingAt);
+static void checkpoint_progress_start(int flags, int type);
+
/*
* Insert an XLOG record represented by an already-constructed chain of data
* chunks. This is a low-level routine; to construct the WAL record header
@@ -6296,6 +6299,9 @@ CreateCheckPoint(int flags)
MemSet(&CheckpointStats, 0, sizeof(CheckpointStats));
CheckpointStats.ckpt_start_t = GetCurrentTimestamp();
+ /* Prepare to report progress of the checkpoint. */
+ checkpoint_progress_start(flags, PROGRESS_CHECKPOINT_TYPE_CHECKPOINT);
+
/*
* Use a critical section to force system panic if we have trouble.
*/
@@ -6394,6 +6400,7 @@ CreateCheckPoint(int flags)
curInsert += SizeOfXLogShortPHD;
}
checkPoint.redo = curInsert;
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_LSN, checkPoint.redo);
/*
* Here we update the shared RedoRecPtr for future XLogInsert calls; this
@@ -6501,6 +6508,8 @@ CreateCheckPoint(int flags)
* and we will correctly flush the update below. So we cannot miss any
* xacts we need to wait for.
*/
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_GET_VIRTUAL_TRANSACTION_IDS);
vxids = GetVirtualXIDsDelayingChkpt(&nvxids);
if (nvxids > 0)
{
@@ -6604,6 +6613,8 @@ CreateCheckPoint(int flags)
/*
* Let smgr do post-checkpoint cleanup (eg, deleting old files).
*/
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_POST_CHECKPOINT_CLEANUP);
SyncPostCheckpoint();
/*
@@ -6619,6 +6630,9 @@ CreateCheckPoint(int flags)
*/
XLByteToSeg(RedoRecPtr, _logSegNo, wal_segment_size);
KeepLogSeg(recptr, &_logSegNo);
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_INVALIDATE_REPLI_SLOTS);
+
if (InvalidateObsoleteReplicationSlots(_logSegNo))
{
/*
@@ -6629,6 +6643,8 @@ CreateCheckPoint(int flags)
KeepLogSeg(recptr, &_logSegNo);
}
_logSegNo--;
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_OLD_XLOG_RECYCLE);
RemoveOldXlogFiles(_logSegNo, RedoRecPtr, recptr,
checkPoint.ThisTimeLineID);
@@ -6647,11 +6663,21 @@ CreateCheckPoint(int flags)
* StartupSUBTRANS hasn't been called yet.
*/
if (!RecoveryInProgress())
+ {
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_TRUNCATE_SUBTRANS);
TruncateSUBTRANS(GetOldestTransactionIdConsideredRunning());
+ }
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_FINALIZE);
/* Real work is done; log and update stats. */
LogCheckpointEnd(false);
+ /* Stop reporting progress of the checkpoint. */
+ pgstat_progress_end_command();
+
/* Reset the process title */
update_checkpoint_display(flags, false, true);
@@ -6808,29 +6834,60 @@ static void
CheckPointGuts(XLogRecPtr checkPointRedo, int flags)
{
CheckPointRelationMap();
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_REPLI_SLOTS);
CheckPointReplicationSlots();
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_SNAPSHOTS);
CheckPointSnapBuild();
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_LOGICAL_REWRITE_MAPPINGS);
CheckPointLogicalRewriteHeap();
CheckPointReplicationOrigin();
/* Write out all dirty data in SLRUs and the main buffer pool */
TRACE_POSTGRESQL_BUFFER_CHECKPOINT_START(flags);
CheckpointStats.ckpt_write_t = GetCurrentTimestamp();
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_CLOG_PAGES);
CheckPointCLOG();
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_COMMITTS_PAGES);
CheckPointCommitTs();
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_SUBTRANS_PAGES);
CheckPointSUBTRANS();
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_MULTIXACT_PAGES);
CheckPointMultiXact();
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_PREDICATE_LOCK_PAGES);
CheckPointPredicate();
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_BUFFERS);
CheckPointBuffers(flags);
/* Perform all queued up fsyncs */
TRACE_POSTGRESQL_BUFFER_CHECKPOINT_SYNC_START();
CheckpointStats.ckpt_sync_t = GetCurrentTimestamp();
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_FILE_SYNC);
ProcessSyncRequests();
CheckpointStats.ckpt_sync_end_t = GetCurrentTimestamp();
TRACE_POSTGRESQL_BUFFER_CHECKPOINT_DONE();
/* We deliberately delay 2PC checkpointing as long as possible */
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_TWO_PHASE);
CheckPointTwoPhase(checkPointRedo);
}
@@ -6977,6 +7034,9 @@ CreateRestartPoint(int flags)
MemSet(&CheckpointStats, 0, sizeof(CheckpointStats));
CheckpointStats.ckpt_start_t = GetCurrentTimestamp();
+ /* Prepare to report progress of the restartpoint. */
+ checkpoint_progress_start(flags, PROGRESS_CHECKPOINT_TYPE_RESTARTPOINT);
+
if (log_checkpoints)
LogCheckpointStart(flags, true);
@@ -7051,6 +7111,9 @@ CreateRestartPoint(int flags)
replayPtr = GetXLogReplayRecPtr(&replayTLI);
endptr = (receivePtr < replayPtr) ? replayPtr : receivePtr;
KeepLogSeg(endptr, &_logSegNo);
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_INVALIDATE_REPLI_SLOTS);
+
if (InvalidateObsoleteReplicationSlots(_logSegNo))
{
/*
@@ -7077,6 +7140,8 @@ CreateRestartPoint(int flags)
if (!RecoveryInProgress())
replayTLI = XLogCtl->InsertTimeLineID;
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_OLD_XLOG_RECYCLE);
RemoveOldXlogFiles(_logSegNo, RedoRecPtr, endptr, replayTLI);
/*
@@ -7093,11 +7158,20 @@ CreateRestartPoint(int flags)
* this because StartupSUBTRANS hasn't been called yet.
*/
if (EnableHotStandby)
+ {
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_TRUNCATE_SUBTRANS);
TruncateSUBTRANS(GetOldestTransactionIdConsideredRunning());
+ }
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_FINALIZE);
/* Real work is done; log and update stats. */
LogCheckpointEnd(true);
+ /* Stop reporting progress of the restartpoint. */
+ pgstat_progress_end_command();
+
/* Reset the process title */
update_checkpoint_display(flags, true, true);
@@ -9197,3 +9271,27 @@ SetWalWriterSleeping(bool sleeping)
XLogCtl->WalWriterSleeping = sleeping;
SpinLockRelease(&XLogCtl->info_lck);
}
+
+/*
+ * Start reporting progress of the checkpoint.
+ */
+static void
+checkpoint_progress_start(int flags, int type)
+{
+ const int index[] = {
+ PROGRESS_CHECKPOINT_TYPE,
+ PROGRESS_CHECKPOINT_KIND,
+ PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_START_TIMESTAMP
+ };
+ int64 val[4];
+
+ pgstat_progress_start_command(PROGRESS_COMMAND_CHECKPOINT, InvalidOid);
+
+ val[0] = type;
+ val[1] = flags;
+ val[2] = PROGRESS_CHECKPOINT_PHASE_INIT;
+ val[3] = CheckpointStats.ckpt_start_t;
+
+ pgstat_progress_update_multi_param(4, index, val);
+}
\ No newline at end of file
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 40b7bca5a9..1e5b6e995f 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1273,3 +1273,53 @@ CREATE VIEW pg_stat_subscription_stats AS
ss.stats_reset
FROM pg_subscription as s,
pg_stat_get_subscription_stats(s.oid) as ss;
+
+CREATE VIEW pg_stat_progress_checkpoint AS
+ SELECT
+ S.pid AS pid,
+ CASE S.param1 WHEN 1 THEN 'checkpoint'
+ WHEN 2 THEN 'restartpoint'
+ END AS type,
+ ( CASE WHEN (S.param2 & 1) > 0 THEN 'shutdown ' ELSE '' END ||
+ CASE WHEN (S.param2 & 2) > 0 THEN 'end-of-recovery ' ELSE '' END ||
+ CASE WHEN (S.param2 & 4) > 0 THEN 'immediate ' ELSE '' END ||
+ CASE WHEN (S.param2 & 8) > 0 THEN 'force ' ELSE '' END ||
+ CASE WHEN (S.param2 & 16) > 0 THEN 'flush-all ' ELSE '' END ||
+ CASE WHEN (S.param2 & 32) > 0 THEN 'wait ' ELSE '' END ||
+ CASE WHEN (S.param2 & 64) > 0 THEN 'requested ' ELSE '' END ||
+ CASE WHEN (S.param2 & 128) > 0 THEN 'wal ' ELSE '' END ||
+ CASE WHEN (S.param2 & 256) > 0 THEN 'time ' ELSE '' END
+ ) AS kind,
+ ( '0/0'::pg_lsn +
+ ((CASE
+ WHEN S.param3 < 0 THEN pow(2::numeric, 64::numeric)::numeric
+ ELSE 0::numeric
+ END) +
+ S.param3::numeric)
+ ) AS start_lsn,
+ to_timestamp(946684800 + (S.param4::float8 / 1000000)) AS start_time,
+ CASE S.param5 WHEN 1 THEN 'initializing'
+ WHEN 2 THEN 'getting virtual transaction IDs'
+ WHEN 3 THEN 'checkpointing replication slots'
+ WHEN 4 THEN 'checkpointing logical replication snapshot files'
+ WHEN 5 THEN 'checkpointing logical rewrite mapping files'
+ WHEN 6 THEN 'checkpointing commit log pages'
+ WHEN 7 THEN 'checkpointing commit time stamp pages'
+ WHEN 8 THEN 'checkpointing subtransaction pages'
+ WHEN 9 THEN 'checkpointing multixact pages'
+ WHEN 10 THEN 'checkpointing predicate lock pages'
+ WHEN 11 THEN 'checkpointing buffers'
+ WHEN 12 THEN 'processing file sync requests'
+ WHEN 13 THEN 'performing two phase checkpoint'
+ WHEN 14 THEN 'performing post checkpoint cleanup'
+ WHEN 15 THEN 'invalidating replication slots'
+ WHEN 16 THEN 'recycling old XLOG files'
+ WHEN 17 THEN 'truncating subtransactions'
+ WHEN 18 THEN 'finalizing'
+ END AS phase,
+ S.param6 AS buffers_total,
+ S.param7 AS buffers_processed,
+ S.param8 AS buffers_written,
+ S.param9 AS files_total,
+ S.param10 AS files_synced
+ FROM pg_stat_get_progress_info('CHECKPOINT') AS S;
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index f5459c68f8..9663035d7a 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -38,6 +38,7 @@
#include "access/xlogutils.h"
#include "catalog/catalog.h"
#include "catalog/storage.h"
+#include "commands/progress.h"
#include "executor/instrument.h"
#include "lib/binaryheap.h"
#include "miscadmin.h"
@@ -2012,6 +2013,8 @@ BufferSync(int flags)
WritebackContextInit(&wb_context, &checkpoint_flush_after);
TRACE_POSTGRESQL_BUFFER_SYNC_START(NBuffers, num_to_scan);
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_BUFFERS_TOTAL,
+ num_to_scan);
/*
* Sort buffers that need to be written to reduce the likelihood of random
@@ -2129,6 +2132,8 @@ BufferSync(int flags)
bufHdr = GetBufferDescriptor(buf_id);
num_processed++;
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_BUFFERS_PROCESSED,
+ num_processed);
/*
* We don't need to acquire the lock here, because we're only looking
@@ -2149,6 +2154,8 @@ BufferSync(int flags)
TRACE_POSTGRESQL_BUFFER_SYNC_WRITTEN(buf_id);
PendingCheckpointerStats.m_buf_written_checkpoints++;
num_written++;
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_BUFFERS_WRITTEN,
+ num_written);
}
}
diff --git a/src/backend/storage/sync/sync.c b/src/backend/storage/sync/sync.c
index e161d57761..638d3eb781 100644
--- a/src/backend/storage/sync/sync.c
+++ b/src/backend/storage/sync/sync.c
@@ -23,6 +23,7 @@
#include "access/multixact.h"
#include "access/xlog.h"
#include "access/xlogutils.h"
+#include "commands/progress.h"
#include "commands/tablespace.h"
#include "miscadmin.h"
#include "pgstat.h"
@@ -356,6 +357,9 @@ ProcessSyncRequests(void)
/* Now scan the hashtable for fsync requests to process */
absorb_counter = FSYNCS_PER_ABSORB;
hash_seq_init(&hstat, pendingOps);
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_FILES_TOTAL,
+ hash_get_num_entries(pendingOps));
+
while ((entry = (PendingFsyncEntry *) hash_seq_search(&hstat)) != NULL)
{
int failures;
@@ -419,6 +423,8 @@ ProcessSyncRequests(void)
longest = elapsed;
total_elapsed += elapsed;
processed++;
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_FILES_SYNCED,
+ processed);
if (log_checkpoints)
elog(DEBUG1, "checkpoint sync: number=%d file=%s time=%.3f ms",
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index fd993d0d5f..95df730415 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -494,6 +494,8 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
cmdtype = PROGRESS_COMMAND_BASEBACKUP;
else if (pg_strcasecmp(cmd, "COPY") == 0)
cmdtype = PROGRESS_COMMAND_COPY;
+ else if (pg_strcasecmp(cmd, "CHECKPOINT") == 0)
+ cmdtype = PROGRESS_COMMAND_CHECKPOINT;
else
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
diff --git a/src/include/commands/progress.h b/src/include/commands/progress.h
index a28938caf4..7064026bf1 100644
--- a/src/include/commands/progress.h
+++ b/src/include/commands/progress.h
@@ -151,4 +151,40 @@
#define PROGRESS_COPY_TYPE_PIPE 3
#define PROGRESS_COPY_TYPE_CALLBACK 4
+/* Progress parameters for checkpoint */
+#define PROGRESS_CHECKPOINT_TYPE 0
+#define PROGRESS_CHECKPOINT_KIND 1
+#define PROGRESS_CHECKPOINT_LSN 2
+#define PROGRESS_CHECKPOINT_START_TIMESTAMP 3
+#define PROGRESS_CHECKPOINT_PHASE 4
+#define PROGRESS_CHECKPOINT_BUFFERS_TOTAL 5
+#define PROGRESS_CHECKPOINT_BUFFERS_PROCESSED 6
+#define PROGRESS_CHECKPOINT_BUFFERS_WRITTEN 7
+#define PROGRESS_CHECKPOINT_FILES_TOTAL 8
+#define PROGRESS_CHECKPOINT_FILES_SYNCED 9
+
+/* Types of checkpoint (as advertised via PROGRESS_CHECKPOINT_TYPE) */
+#define PROGRESS_CHECKPOINT_TYPE_CHECKPOINT 1
+#define PROGRESS_CHECKPOINT_TYPE_RESTARTPOINT 2
+
+/* Phases of checkpoint (as advertised via PROGRESS_CHECKPOINT_PHASE) */
+#define PROGRESS_CHECKPOINT_PHASE_INIT 1
+#define PROGRESS_CHECKPOINT_PHASE_GET_VIRTUAL_TRANSACTION_IDS 2
+#define PROGRESS_CHECKPOINT_PHASE_REPLI_SLOTS 3
+#define PROGRESS_CHECKPOINT_PHASE_SNAPSHOTS 4
+#define PROGRESS_CHECKPOINT_PHASE_LOGICAL_REWRITE_MAPPINGS 5
+#define PROGRESS_CHECKPOINT_PHASE_CLOG_PAGES 6
+#define PROGRESS_CHECKPOINT_PHASE_COMMITTS_PAGES 7
+#define PROGRESS_CHECKPOINT_PHASE_SUBTRANS_PAGES 8
+#define PROGRESS_CHECKPOINT_PHASE_MULTIXACT_PAGES 9
+#define PROGRESS_CHECKPOINT_PHASE_PREDICATE_LOCK_PAGES 10
+#define PROGRESS_CHECKPOINT_PHASE_BUFFERS 11
+#define PROGRESS_CHECKPOINT_PHASE_FILE_SYNC 12
+#define PROGRESS_CHECKPOINT_PHASE_TWO_PHASE 13
+#define PROGRESS_CHECKPOINT_PHASE_POST_CHECKPOINT_CLEANUP 14
+#define PROGRESS_CHECKPOINT_PHASE_INVALIDATE_REPLI_SLOTS 15
+#define PROGRESS_CHECKPOINT_PHASE_OLD_XLOG_RECYCLE 16
+#define PROGRESS_CHECKPOINT_PHASE_TRUNCATE_SUBTRANS 17
+#define PROGRESS_CHECKPOINT_PHASE_FINALIZE 18
+
#endif
diff --git a/src/include/utils/backend_progress.h b/src/include/utils/backend_progress.h
index 47bf8029b0..02d51fb948 100644
--- a/src/include/utils/backend_progress.h
+++ b/src/include/utils/backend_progress.h
@@ -27,7 +27,8 @@ typedef enum ProgressCommandType
PROGRESS_COMMAND_CLUSTER,
PROGRESS_COMMAND_CREATE_INDEX,
PROGRESS_COMMAND_BASEBACKUP,
- PROGRESS_COMMAND_COPY
+ PROGRESS_COMMAND_COPY,
+ PROGRESS_COMMAND_CHECKPOINT
} ProgressCommandType;
#define PGSTAT_NUM_PROGRESS_PARAM 20
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index ac468568a1..11626a4200 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1897,6 +1897,82 @@ pg_stat_progress_basebackup| SELECT s.pid,
s.param4 AS tablespaces_total,
s.param5 AS tablespaces_streamed
FROM pg_stat_get_progress_info('BASEBACKUP'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20);
+pg_stat_progress_checkpoint| SELECT s.pid,
+ CASE s.param1
+ WHEN 1 THEN 'checkpoint'::text
+ WHEN 2 THEN 'restartpoint'::text
+ ELSE NULL::text
+ END AS type,
+ ((((((((
+ CASE
+ WHEN ((s.param2 & (1)::bigint) > 0) THEN 'shutdown '::text
+ ELSE ''::text
+ END ||
+ CASE
+ WHEN ((s.param2 & (2)::bigint) > 0) THEN 'end-of-recovery '::text
+ ELSE ''::text
+ END) ||
+ CASE
+ WHEN ((s.param2 & (4)::bigint) > 0) THEN 'immediate '::text
+ ELSE ''::text
+ END) ||
+ CASE
+ WHEN ((s.param2 & (8)::bigint) > 0) THEN 'force '::text
+ ELSE ''::text
+ END) ||
+ CASE
+ WHEN ((s.param2 & (16)::bigint) > 0) THEN 'flush-all '::text
+ ELSE ''::text
+ END) ||
+ CASE
+ WHEN ((s.param2 & (32)::bigint) > 0) THEN 'wait '::text
+ ELSE ''::text
+ END) ||
+ CASE
+ WHEN ((s.param2 & (64)::bigint) > 0) THEN 'requested '::text
+ ELSE ''::text
+ END) ||
+ CASE
+ WHEN ((s.param2 & (128)::bigint) > 0) THEN 'wal '::text
+ ELSE ''::text
+ END) ||
+ CASE
+ WHEN ((s.param2 & (256)::bigint) > 0) THEN 'time '::text
+ ELSE ''::text
+ END) AS kind,
+ ('0/0'::pg_lsn + (
+ CASE
+ WHEN (s.param3 < 0) THEN pow((2)::numeric, (64)::numeric)
+ ELSE (0)::numeric
+ END + (s.param3)::numeric)) AS start_lsn,
+ to_timestamp(((946684800)::double precision + ((s.param4)::double precision / (1000000)::double precision))) AS start_time,
+ CASE s.param5
+ WHEN 1 THEN 'initializing'::text
+ WHEN 2 THEN 'getting virtual transaction IDs'::text
+ WHEN 3 THEN 'checkpointing replication slots'::text
+ WHEN 4 THEN 'checkpointing logical replication snapshot files'::text
+ WHEN 5 THEN 'checkpointing logical rewrite mapping files'::text
+ WHEN 6 THEN 'checkpointing commit log pages'::text
+ WHEN 7 THEN 'checkpointing commit time stamp pages'::text
+ WHEN 8 THEN 'checkpointing subtransaction pages'::text
+ WHEN 9 THEN 'checkpointing multixact pages'::text
+ WHEN 10 THEN 'checkpointing predicate lock pages'::text
+ WHEN 11 THEN 'checkpointing buffers'::text
+ WHEN 12 THEN 'processing file sync requests'::text
+ WHEN 13 THEN 'performing two phase checkpoint'::text
+ WHEN 14 THEN 'performing post checkpoint cleanup'::text
+ WHEN 15 THEN 'invalidating replication slots'::text
+ WHEN 16 THEN 'recycling old XLOG files'::text
+ WHEN 17 THEN 'truncating subtransactions'::text
+ WHEN 18 THEN 'finalizing'::text
+ ELSE NULL::text
+ END AS phase,
+ s.param6 AS buffers_total,
+ s.param7 AS buffers_processed,
+ s.param8 AS buffers_written,
+ s.param9 AS files_total,
+ s.param10 AS files_synced
+ FROM pg_stat_get_progress_info('CHECKPOINT'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20);
pg_stat_progress_cluster| SELECT s.pid,
s.datid,
d.datname,
--
2.25.1
view thread (74+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Report checkpoint progress with pg_stat_progress_checkpoint (was: Report checkpoint progress in server logs)
In-Reply-To: <CAMm1aWYWmqys2O7UekQ0=ZSWSpG9h2qoEW7w46yiWM0eO1Lu7g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox