Horton

Re: Minimal logical decoding on standbys

Bertrand Drouvot <bertranddrouvot.pg@gmail.com>

View thread
From: "Drouvot, Bertrand" <bertranddrouvot.pg@gmail.com>
To: Andres Freund <andres@anarazel.de>
Cc: Amit Kapila <amit.kapila16@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Jeff Davis <pgsql@j-davis.com>, Robert Haas <robertmhaas@gmail.com>, Thomas Munro <thomas.munro@gmail.com>, Ibrar Ahmed <ibrar.ahmad@gmail.com>, Amit Khandekar <amitdkhan.pg@gmail.com>, fabriziomello@gmail.com, tushar <tushar.ahuja@enterprisedb.com>, Rahila Syed <rahila.syed@2ndquadrant.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2023-04-07T20:58:19Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Reduce the log level in 035_standby_logical_decoding.pl.

  2. 035_standby_logical_decoding: Add missing waits for replication

  3. For cascading replication, wake physical and logical walsenders separately

  4. Handle logical slot conflicts on standby

  5. Support invalidating replication slots due to horizon and wal_level

  6. Prevent use of invalidated logical slot in CreateDecodingContext()

  7. Replace replication slot's invalidated_at LSN with an enum

  8. Pass down table relation into more index relation functions

  9. Assert only valid flag bits are passed to visibilitymap_set()

  10. Remove unused _bt_delitems_delete() argument.

  11. Add xl_btree_delete optimization.

Attachments

Hi,

On 4/7/23 8:24 PM, Drouvot, Bertrand wrote:
> Hi,
> 
> On 4/7/23 5:47 PM, Andres Freund wrote:
>> Hi,
>>
>>>> - write a test that invalidated logical slots do not lead to retaining WAL
>>>
>>> I'm not sure how to do that since pg_switch_wal() and friends can't be executed on
>>> a standby.
>>
>> You can do it on the primary and wait for the records to have been applied.
>>
> 
> Thanks, will give it a try in a couple of hours.

I looked at it but I think we'd also need things like pg_walfile_name() on the standby but is not allowed.

>> Is this patchset sufficient to subscribe to a publication on a physical
>> standby, assuming the publication is created on the primary? If so, we should
>> have at least a minimal test. If not, we should note that restriction
>> explicitly.
> 
> I gave it a try and it does work.
> 
> "
> node3 subscribes to node2 (standby).
> Insert done in node1 (primary) where the publication is created => node3 see the changes.
> "
> 
> I started to create the TAP test but currently stuck as the "create subscription" waits for a checkpoint/pg_log_standby_snapshot() on the primary.
> 
> So, trying to make use of things like:
> 
> "my %psql_subscriber = ('stdin' => '', 'stdout' => '');
> $psql_subscriber{run} =
>    $node_subscriber->background_psql('postgres', \$psql_subscriber{stdin},
>      \$psql_subscriber{stdout},
>      $psql_timeout);
> $psql_subscriber{stdout} = '';
> "
> 
> But in vain so far...
> 

please find attached sub_in_progress.patch that "should work" but "does not" because
the wait_for_subscription_sync() call produces:

"
error running SQL: 'psql:<stdin>:1: ERROR:  recovery is in progress
HINT:  WAL control functions cannot be executed during recovery.'
while running 'psql -XAtq -d port=61441 host=/tmp/45dt3wqs2p dbname='postgres' -f - -v ON_ERROR_STOP=1' with sql 'SELECT pg_current_wal_lsn()'
"

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com