> 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 https://gitlab.com/gitlab-com/gl-infra/production/-/issues/8290#note_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 ```