agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19647: Difference in pg_basebackup behaviour between PostgreSQL <= 16 and >= 17 with pgactive extension
7+ messages / 3 participants
[nested] [flat]
* BUG #19647: Difference in pg_basebackup behaviour between PostgreSQL <= 16 and >= 17 with pgactive extension
@ 2026-08-30 17:08 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 7+ messages in thread
From: PG Bug reporting form @ 2026-08-30 17:08 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: edwin.polkerman@splendiddata.com
The following bug has been logged on the website:
Bug reference: 19647
Logged by: Edwin Polkerman
Email address: edwin.polkerman@splendiddata.com
PostgreSQL version: 15.19
Operating system: Linux (See details)
Description:
Operating system:
I have tested this on SUSE15 SP7 x86_64 and ppc64le, RHEL8/9 x86_64 and
ppc64le, RHEL10 x86_64, Rocky Linux 8/9/10 x86_64 and every time having the
same results.
The start situation is a postgresql instance (data checksums on) with
extension pgactive (version 2.1.8) installed in a database (CREATE EXTENSION
pgactive;), in "shared_preload_libraries" and added to new parameter
"output_plugin_libraries". Following step is to take an base backup with
pg_basebackup.
In PostgreSQL versions 18.6 and 17.11 this succeeds without any problem.
However in PostgreSQL versions 16.15, 15.19 and 14.24 the backup process
fails.
FAIL SITUATION:
postgres=# select version();
version
--------------------------------------------------------------------------------------
PostgreSQL 15.19 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux)
14.3.0, 64-bit
(1 row)
postgres=# select name, setting, category, short_desc from pg_settings where
name='data_checksums';
name | setting | category |
short_desc
----------------+---------+----------------+--------------------------------------------------------------
data_checksums | on | Preset Options | Shows whether data checksums
are turned on for this cluster.
(1 row)
For database app extension pgactive is installed and configured:
postgres=# \c app
app=# SELECT * from pgactive.pgactive_nodes;
-[ RECORD 1
]------+------------------------------------------------------------------------
node_sysid | 7679811947370996481
node_timeline | 0
node_dboid | 16669
node_status | r
node_name | endpoint1-app
node_dsn | user_mapping=splendid
pgactive_foreign_server=pgactive_server_endpoint1
node_init_from_dsn |
node_read_only | f
node_seq_id | 1
-[ RECORD 2
]------+------------------------------------------------------------------------
node_sysid | 7679811972576347006
node_timeline | 0
node_dboid | 16386
node_status | r
node_name | endpoint2-app
node_dsn | user_mapping=splendid
pgactive_foreign_server=pgactive_server_endpoint2
node_init_from_dsn | user_mapping=splendid
pgactive_foreign_server=pgactive_server_endpoint1
node_read_only | f
node_seq_id | 2
Creating an base backup:
> pg_basebackup -p 5442 -D /basebackup/backup/15/pg_basebackup_pg15pgactive
-X s -P -v -R
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/44000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_14961"
pg_basebackup: error: COPY stream ended before last file was finished617_vm)
pg_basebackup: removing data directory
"/basebackup/backup/15/pg_basebackup_pg15pgactive"
Logging from the database server shows following:
2026-08-30 16:31:45 CEST pid:14641 xid:0 ip: LOG: checkpoint starting:
force wait
2026-08-30 16:31:45 CEST pid:14641 xid:0 ip: LOG: checkpoint complete:
wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled;
write=0.003 s, sync=0.001 s, total=0.009 s; sync files=0, longest=0.000 s,
average=0.000 s; distance=16383 kB, estimate=16383 kB
2026-08-30 16:31:48 CEST [unknown] postgres pid:14959 xid:0 ip:[local]
ERROR: invalid segment number 0 in file "pgactive.stat"
2026-08-30 16:31:48 CEST [unknown] postgres pid:14959 xid:0 ip:[local]
STATEMENT: BASE_BACKUP ( LABEL 'pg_basebackup base backup', PROGRESS,
WAIT 0, MANIFEST 'yes', TARGET 'client')
2026-08-30 16:31:48 CEST [unknown] postgres pid:14961 xid:0 ip:[local] LOG:
unexpected EOF on standby connection
2026-08-30 16:31:48 CEST [unknown] postgres pid:14961 xid:0 ip:[local]
STATEMENT: START_REPLICATION SLOT "pg_basebackup_14961" 0/44000000 TIMELINE
1
The ERROR message shows file pgactive.stat, which is probably placed by the
pgactive extension in the "global" directory in PGDATA
Stopped the server and run pg_checksum to verify:
> pg_checksums -c /var/pgpure/postgres/15/cluster2/
pg_checksums: error: invalid segment number 0 in file name
"/var/pgpure/postgres/18/cluster2//global/pgactive.stat"
As said, this behaviour is seen for 16.15, 15.19 and 14.24.
GOOD SITUATION:
postgres=# select version();
version
-------------------------------------------------------------------------------------
PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux)
14.3.0, 64-bit
(1 row)
postgres=# select name, setting, category, short_desc from pg_settings where
name='data_checksums';
name | setting | category |
short_desc
----------------+---------+----------------+--------------------------------------------------------------
data_checksums | on | Preset Options | Shows whether data checksums
are turned on for this cluster.
(1 row)
For database app extension pgactive is installed and configured:
postgres=# \c app
app=# SELECT * from pgactive.pgactive_nodes;
-[ RECORD 1
]------+------------------------------------------------------------------------
node_sysid | 7678809857720063909
node_timeline | 0
node_dboid | 16702
node_status | r
node_name | endpoint1-app
node_dsn | user_mapping=splendid
pgactive_foreign_server=pgactive_server_endpoint1
node_init_from_dsn |
node_read_only | f
node_seq_id | 1
-[ RECORD 2
]------+------------------------------------------------------------------------
node_sysid | 7678809889904381387
node_timeline | 0
node_dboid | 16386
node_status | r
node_name | endpoint2-app
node_dsn | user_mapping=splendid
pgactive_foreign_server=pgactive_server_endpoint2
node_init_from_dsn | user_mapping=splendid
pgactive_foreign_server=pgactive_server_endpoint1
node_read_only | f
node_seq_id | 2
Creating an base backup:
pg_basebackup -p 5442 -D /basebackup/backup/18/pg_basebackup_pg18pgactive -X
s -P -v -R
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/55000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_141732"
372246/372246 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/55000120
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
For PostgreSQL 18.6 and 17.11 the backup completes without any problems.
The strange thing is that also here pg_checksums reports about file
pgactive.stat???
> pg_checksums -c /var/pgpure/postgres/18/cluster2/
pg_checksums: error: invalid segment number 0 in file name
"/var/pgpure/postgres/15/cluster2//global/pgactive.stat"
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19647: Difference in pg_basebackup behaviour between PostgreSQL <= 16 and >= 17 with pgactive extension
@ 2026-09-02 18:51 Jacob Champion <jacob.champion@enterprisedb.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 7+ messages in thread
From: Jacob Champion @ 2026-09-02 18:51 UTC (permalink / raw)
To: edwin.polkerman@splendiddata.com; +Cc: pgsql-bugs@lists.postgresql.org
On Tue, Sep 1, 2026 at 6:16 AM PG Bug reporting form
<noreply@postgresql.org> wrote:
> For PostgreSQL 18.6 and 17.11 the backup completes without any problems.
Do the global/ directories for the 17 and 18 clusters contain a
pgactive.stat file?
Without knowing anything about pgactive in particular (it's a
third-party extension)... It sure looks like that extension, or else
something related to it, has put a random file into the DB internals
in a way that won't pass our checksum verification. It needs to go
somewhere else.
--Jacob
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19647: Difference in pg_basebackup behaviour between PostgreSQL <= 16 and >= 17 with pgactive extension
@ 2026-09-02 20:43 Edwin Polkerman <edwin.polkerman@splendiddata.com>
parent: Jacob Champion <jacob.champion@enterprisedb.com>
0 siblings, 1 reply; 7+ messages in thread
From: Edwin Polkerman @ 2026-09-02 20:43 UTC (permalink / raw)
To: Jacob Champion <jacob.champion@enterprisedb.com>; +Cc: pgsql-bugs@lists.postgresql.org
Hi Jacob,
Yes, the 18.6 and 17.11 global/ directory contains this file as well.
Doing a pg_checksums on the data directory gives exactly the same result
as for the PostgreSQL 16.15, 15.19 and 14.24:
pg_checksums -c /var/pgpure/postgres/18/cluster2/
pg_checksums: error: invalid segment number 0 in file name
"/var/pgpure/postgres/18/cluster2//global/pgactive.stat"
The file pgactive.stat is placed in the globals directory when the
database server is stopped / started after installation of the extension
and setup of the replication group. I think it is used for internal
bookkeeping for replication statussen with the other node(s) in the
replication group when the node is shutdown.
Because of the difference in behaviour of pg_basebackup what dazzled me,
it made me decide to create the issue in the pgql-bugs mailing list.
Please let me know if it is not PostgreSQL related and if it should be
created in github at AWS/pgactive
Kind regards,
Edwin
On 9/2/26 20:51, Jacob Champion wrote:
> On Tue, Sep 1, 2026 at 6:16 AM PG Bug reporting form
> <noreply@postgresql.org> wrote:
>> For PostgreSQL 18.6 and 17.11 the backup completes without any problems.
> Do the global/ directories for the 17 and 18 clusters contain a
> pgactive.stat file?
>
> Without knowing anything about pgactive in particular (it's a
> third-party extension)... It sure looks like that extension, or else
> something related to it, has put a random file into the DB internals
> in a way that won't pass our checksum verification. It needs to go
> somewhere else.
>
> --Jacob
--
Splendid Data Nederland B.V.
Binnenhof 62A
1412 LC NAARDEN
+31 85 773 19 99
+31 6 5118 8231
*Follow us on LinkedIn <https://www.linkedin.com/company/3327997/admin/>*
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19647: Difference in pg_basebackup behaviour between PostgreSQL <= 16 and >= 17 with pgactive extension
@ 2026-09-02 21:19 Jacob Champion <jacob.champion@enterprisedb.com>
parent: Edwin Polkerman <edwin.polkerman@splendiddata.com>
0 siblings, 1 reply; 7+ messages in thread
From: Jacob Champion @ 2026-09-02 21:19 UTC (permalink / raw)
To: Edwin Polkerman <edwin.polkerman@splendiddata.com>; +Cc: pgsql-bugs@lists.postgresql.org
Hi Edwin,
On Wed, Sep 2, 2026 at 1:43 PM Edwin Polkerman
<edwin.polkerman@splendiddata.com> wrote:
> Yes, the 18.6 and 17.11 global/ directory contains this file as well.
> [...]
> Because of the difference in behaviour of pg_basebackup what dazzled me, it made me decide to create the issue in the pgql-bugs mailing list. Please let me know if it is not PostgreSQL related and if it should be created in github at AWS/pgactive
It looks like the behavior change in pg_basebackup is by design [1], as of PG17.
I think that's separate from the general question of "do our checksum
tools handle arbitrary third-party files in arbitrary places", and I
think the answer to that is still "no". There was briefly some
discussion on allowing that, IIRC -- but as far as I know, it hasn't
actually been established as a supported feature? (It is clearly not
supported for PG16 and before, so I think a trip to the extension's
GitHub Issues is probably in your future either way.)
It's possible that today's pg_checksums behavior was an oversight,
because pg_checksums.c says
/*
* List of files excluded from checksum validation.
*
* Note: this list should be kept in sync with what basebackup.c
includes.
*/
and it sure seems like that list is no longer "in sync". But
personally, I don't think that's a backportable change, rather than an
enhancement request for future versions.
Thanks,
--Jacob
[1] https://postgr.es/c/025584a16
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19647: Difference in pg_basebackup behaviour between PostgreSQL <= 16 and >= 17 with pgactive extension
@ 2026-09-03 11:55 Edwin Polkerman <edwin.polkerman@splendiddata.com>
parent: Jacob Champion <jacob.champion@enterprisedb.com>
0 siblings, 1 reply; 7+ messages in thread
From: Edwin Polkerman @ 2026-09-03 11:55 UTC (permalink / raw)
To: Jacob Champion <jacob.champion@enterprisedb.com>; +Cc: pgsql-bugs@lists.postgresql.org
Hi Jacob,
I think I understand it...
Keeps me only wondering why the behaviour change in pg_basebackup is not
implemented for PG14 to 16 as well.
Now you can have a situation in which
a) pg_basebackup and pg_checksums 'complain' consistent about checksum
issues in data location
b) pg_basebackup ignores checksum issues on files not owned/managed by
postgresql but pg_checksums will still complain about them in the data
location.
Or is that what you mean for 'b)' below, an enhancement request for
pg_checksums to treat checksums the way pg_basebackup does (pg >= 17)?
> It's possible that today's pg_checksums behavior was an oversight,
> because pg_checksums.c says
>
> /*
> * List of files excluded from checksum validation.
> *
> * Note: this list should be kept in sync with what basebackup.c
> includes.
> */
>
> and it sure seems like that list is no longer "in sync". But
> personally, I don't think that's a backportable change, rather than an
> enhancement request for future versions.
I will file a bug report for this issue in github for the third-party
extension pgactive. Please let me know if you want me to report an
enhancement request or thet you take care about that
Kind regards,
Edwin
On 9/2/26 23:19, Jacob Champion wrote:
> Hi Edwin,
>
> On Wed, Sep 2, 2026 at 1:43 PM Edwin Polkerman
> <edwin.polkerman@splendiddata.com> wrote:
>> Yes, the 18.6 and 17.11 global/ directory contains this file as well.
>> [...]
>> Because of the difference in behaviour of pg_basebackup what dazzled me, it made me decide to create the issue in the pgql-bugs mailing list. Please let me know if it is not PostgreSQL related and if it should be created in github at AWS/pgactive
> It looks like the behavior change in pg_basebackup is by design [1], as of PG17.
>
> I think that's separate from the general question of "do our checksum
> tools handle arbitrary third-party files in arbitrary places", and I
> think the answer to that is still "no". There was briefly some
> discussion on allowing that, IIRC -- but as far as I know, it hasn't
> actually been established as a supported feature? (It is clearly not
> supported for PG16 and before, so I think a trip to the extension's
> GitHub Issues is probably in your future either way.)
>
> It's possible that today's pg_checksums behavior was an oversight,
> because pg_checksums.c says
>
> /*
> * List of files excluded from checksum validation.
> *
> * Note: this list should be kept in sync with what basebackup.c
> includes.
> */
>
> and it sure seems like that list is no longer "in sync". But
> personally, I don't think that's a backportable change, rather than an
> enhancement request for future versions.
>
> Thanks,
> --Jacob
>
> [1]https://postgr.es/c/025584a16
--
Splendid Data Nederland B.V.
Binnenhof 62A
1412 LC NAARDEN
+31 85 773 19 99
+31 6 5118 8231
*Follow us on LinkedIn <https://www.linkedin.com/company/3327997/admin/>*
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19647: Difference in pg_basebackup behaviour between PostgreSQL <= 16 and >= 17 with pgactive extension
@ 2026-09-03 16:49 Jacob Champion <jacob.champion@enterprisedb.com>
parent: Edwin Polkerman <edwin.polkerman@splendiddata.com>
0 siblings, 1 reply; 7+ messages in thread
From: Jacob Champion @ 2026-09-03 16:49 UTC (permalink / raw)
To: Edwin Polkerman <edwin.polkerman@splendiddata.com>; +Cc: pgsql-bugs@lists.postgresql.org
On Thu, Sep 3, 2026 at 4:55 AM Edwin Polkerman
<edwin.polkerman@splendiddata.com> wrote:
> Keeps me only wondering why the behaviour change in pg_basebackup is not implemented for PG14 to 16 as well.
We try to avoid big behavior changes in minor version updates.
> b) pg_basebackup ignores checksum issues on files not owned/managed by postgresql but pg_checksums will still complain about them in the data location.
>
> Or is that what you mean for 'b)' below, an enhancement request for pg_checksums to treat checksums the way pg_basebackup does (pg >= 17)?
Correct.
> I will file a bug report for this issue in github for the third-party extension pgactive. Please let me know if you want me to report an enhancement request or thet you take care about that
I'm happy to shepherd the conversation over to -hackers, and will do
that shortly. (I'm probably not the right person to drive it.)
--Jacob
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19647: Difference in pg_basebackup behaviour between PostgreSQL <= 16 and >= 17 with pgactive extension
@ 2026-09-03 19:35 Edwin Polkerman <edwin.polkerman@splendiddata.com>
parent: Jacob Champion <jacob.champion@enterprisedb.com>
0 siblings, 0 replies; 7+ messages in thread
From: Edwin Polkerman @ 2026-09-03 19:35 UTC (permalink / raw)
To: Jacob Champion <jacob.champion@enterprisedb.com>; +Cc: pgsql-bugs@lists.postgresql.org
Hi Jacob,
> We try to avoid big behavior changes in minor version updates.
Ah of course, that 's quite understandable... PG17 was the new major but
the others were minor releases :-)
Thank you very much for opening the enhancement request in -hackers!!
Kind regards,
Edwin
On 9/3/26 18:49, Jacob Champion wrote:
> On Thu, Sep 3, 2026 at 4:55 AM Edwin Polkerman
> <edwin.polkerman@splendiddata.com> wrote:
>> Keeps me only wondering why the behaviour change in pg_basebackup is not implemented for PG14 to 16 as well.
> We try to avoid big behavior changes in minor version updates.
>
>> b) pg_basebackup ignores checksum issues on files not owned/managed by postgresql but pg_checksums will still complain about them in the data location.
>>
>> Or is that what you mean for 'b)' below, an enhancement request for pg_checksums to treat checksums the way pg_basebackup does (pg >= 17)?
> Correct.
>
>> I will file a bug report for this issue in github for the third-party extension pgactive. Please let me know if you want me to report an enhancement request or thet you take care about that
> I'm happy to shepherd the conversation over to -hackers, and will do
> that shortly. (I'm probably not the right person to drive it.)
>
> --Jacob
--
Splendid Data Nederland B.V.
Binnenhof 62A
1412 LC NAARDEN
+31 85 773 19 99
+31 6 5118 8231
*Follow us on LinkedIn <https://www.linkedin.com/company/3327997/admin/>*
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-09-03 19:35 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-30 17:08 BUG #19647: Difference in pg_basebackup behaviour between PostgreSQL <= 16 and >= 17 with pgactive extension PG Bug reporting form <noreply@postgresql.org>
2026-09-02 18:51 ` Jacob Champion <jacob.champion@enterprisedb.com>
2026-09-02 20:43 ` Edwin Polkerman <edwin.polkerman@splendiddata.com>
2026-09-02 21:19 ` Jacob Champion <jacob.champion@enterprisedb.com>
2026-09-03 11:55 ` Edwin Polkerman <edwin.polkerman@splendiddata.com>
2026-09-03 16:49 ` Jacob Champion <jacob.champion@enterprisedb.com>
2026-09-03 19:35 ` Edwin Polkerman <edwin.polkerman@splendiddata.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox