Horton

Re: Minimal logical decoding on standbys

tushar <tushar.ahuja@enterprisedb.com>

View thread
From: tushar <tushar.ahuja@enterprisedb.com>
To: Amit Khandekar <amitdkhan.pg@gmail.com>, Andres Freund <andres@anarazel.de>
Cc: Robert Haas <robertmhaas@gmail.com>, Petr Jelinek <petr.jelinek@2ndquadrant.com>, Craig Ringer <craig@2ndquadrant.com>, Petr Jelinek <petr@2ndquadrant.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2019-07-12T09:23:21Z
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.

On 07/10/2019 05:12 PM, Amit Khandekar wrote:
> All right. Will do that in the next patch set. For now, I have quickly
> done the below changes in a single patch again (attached), in order to
> get early comments if any.
Thanks Amit for your patch. i am able to see 1 issues  on Standby server 
- (where  logical replication slot created ) ,
a)size of  pg_wal folder  is NOT decreasing even after firing 
get_changes function
b)pg_wal files are not recycling  and every time it is creating new 
files after firing get_changes function

Here are the detailed steps -
create a directory with the name 'archive_dir' under /tmp (mkdir 
/tmp/archive_dir)
*SR setup -*
*Master*
.)Perform initdb  (./initdb -D master --wal-segsize=2)
.)Open postgresql.conf file and add these below parameters  at the end 
of  file
wal_level='logical'
min_wal_size=4MB
max_wal_size=4MB
hot_standby_feedback = on
archive_mode=on
archive_command='cp %p /tmp/archive_dir/%f'
.)Start the server ( /pg_ctl -D master/ start -l logsM -c )
.)Connect to psql , create physical  slot
     ->SELECT * FROM 
pg_create_physical_replication_slot('decoding_standby');
*Standby - *
.)Perform pg_basebackup ( ./pg_basebackup -D standby/ 
--slot=decoding_standby -R -v)
.)Open postgresql.conf file of standby and add these 2 parameters - at 
the end of file
   port=5555
primary_slot_name = 'decoding_standby'
.)Start the Standby server  ( ./pg_ctl -D standby/ start -l logsS -c )
.)Connect to psql terminal and create logical replication slot
   ->SELECT * from   pg_create_logical_replication_slot('standby', 
'test_decoding');

*MISC steps**-
*.)Connect to master and create table/insert rows ( create table t(n 
int); insert into t (values (1);)
.)Connect to standby and fire get_changes function ( select * from 
pg_logical_slot_get_changes('standby',null,null); )
.)Run pgbench ( ./pgbench -i -s 10 postgres)
.)Check the pg_wal directory size of  STANDBY
[centos@mail-arts bin]$ du -sch standby/pg_wal/
127M    standby/pg_wal/
127M    total
[centos@mail-arts bin]$

.)Connect to standby and fire get_changes function ( select * from 
pg_logical_slot_get_changes('standby',null,null); )
.)Check the pg_wal directory size of STANDBY
[centos@mail-arts bin]$ du -sch standby/pg_wal/
127M    standby/pg_wal/
127M    total
[centos@mail-arts bin]$


.)Restart both master and standby ( ./pg_ctl -D master restart -l logsM 
-c) and (./pg_ctl -D standby restart -l logsS -c )

.)Check the pg_wal directory size of STANDBY
[centos@mail-arts bin]$ du -sch standby/pg_wal/
127M    standby/pg_wal/
127M    total
[centos@mail-arts bin]$

and if we see the pg_wal files ,it is growing rampant and not reusing.

-- 
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company