Test: try to reproduce the problem with xact_rollback spike when deleting a logical subscription/publication
In a lower environment, reproduce and investigate the problem with pg_stat_database.xact_rollback spike when logical slot is being dropped (there is no 100% certainty that his is what happened, but the timing of the xact_rollback spike definitely matches the time when the slot was dropped)
Issue gitlab-com/gl-infra/production#8290 (comment 1256441400)
Test environment:
Two servers (source and target):
- CPU: 4
- Memory: 8Gb
- disk: 150Gb
- OS: Ubuntu 20.04.5 LTS
- PostgreSQL version: 12
On both servers:
Install and configure PostgreSQL
# Install PostgreSQL
sudo apt update && sudo apt install -y postgresql-12
# postgresql.conf
mv /etc/postgresql/12/main/postgresql.conf /etc/postgresql/12/main/postgresql.base.conf
cat <<EOF > /etc/postgresql/12/main/postgresql.conf
include 'postgresql.base.conf'
listen_addresses = '*'
max_connections = 500
shared_buffers= 1GB
work_mem = 100MB
maintenance_work_mem = 256MB
effective_cache_size = 4GB
seq_page_cost = 1.0
random_page_cost = 1.1
effective_io_concurrency = 200
checkpoint_timeout = 10min
checkpoint_completion_target = 0.9
max_wal_size = 8GB
min_wal_size = 1GB
wal_level = logical
shared_preload_libraries = 'pg_stat_statements'
EOF
# pg_hba.conf
cat <<EOF > /etc/postgresql/12/main/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# local connections:
local all all trust
# IPv4 connections:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5
# IPv6 connections:
host all all ::1/128 md5
# replication connections:
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
host replication all 0.0.0.0/0 md5
EOF
# Restart PostgreSQL
pg_ctlcluster 12 main stop -m fast && pg_ctlcluster 12 main start
# Set password for postgres
psql -U postgres -c "alter user postgres with password 'postgres-pass'"
# Init Database
psql -U postgres -c "create database test"
pgbench -U postgres -i -s 100 --init-steps=tgpf test
Configure logical replication
# On source: create a publication
psql -U postgres -d test -c "CREATE PUBLICATION logical_replication FOR ALL TABLES"
# On source: create a slot for logical replication
psql -U postgres -d test -c "SELECT * FROM pg_create_logical_replication_slot('logical_replication_slot', 'pgoutput')"
# On target: create a subscription
psql -U postgres -d test -c "CREATE SUBSCRIPTION logical_subscription
CONNECTION 'host=10.1.0.8 port=5432 password=postgres-pass user=postgres dbname=test'
PUBLICATION logical_replication
WITH (copy_data = false, create_slot = false, slot_name = 'logical_replication_slot')"
# On source: Make sure that logical replication is active'
psql -U postgres -d test -c "SELECT active FROM pg_replication_slots WHERE slot_name = 'logical_replication_slot'"
# result
active
--------
t
Edited by Vitaliy Kukharik