datfrozenxid not dropping after vacuum - Mailing list pgsql-general

Hi,

Starting this morning at 0830 local time I noticed that my
datfrozenxid starts moving past the `autovacuum_freeze_max_age` value
of 200000000.   When we encountered this in the past the solution has
been to do one of the following:

1. This is related an error similar to
```
found xmin 2675436435 from before relfrozenxid 321165377
```
Where the solution has been to move the `pg_internal.init` file out of
the way and let Postgresql recreate it.  Or;

2. A long-running transaction.  Typically I'll just find the `idle in
transaction` transactions that have a `query_start` around when my
alarm went off notifying me when `datfrozenxid` breaches
`autovacuum_freeze_max_age`.  Using a query similar to
```
SELECT pid, query_start, datname, usename, state, backend_xmin,
age(backend_xmin)
FROM pg_stat_activity
WHERE state = 'idle in transaction';
```

3. The autovacuum process seemed to be "stuck" on a particular table.
We would kill the pid of the autovacuum process.

The problem is that neither of these solutions have seemed to drop
`datfrozenxid` back down and there is one specific database in this
cluster that's holding onto it.

Using these queries from CrunchyData:

# Show oldest current xid
# WITH max_age AS (
    SELECT 2000000000 as max_old_xid
        , setting AS autovacuum_freeze_max_age
        FROM pg_catalog.pg_settings
        WHERE name = 'autovacuum_freeze_max_age' )
, per_database_stats AS (
    SELECT datname
        , m.max_old_xid::int
        , m.autovacuum_freeze_max_age::int
        , age(d.datfrozenxid) AS oldest_current_xid
    FROM pg_catalog.pg_database d
    JOIN max_age m ON (true)
    WHERE d.datallowconn )
SELECT max(oldest_current_xid) AS oldest_current_xid
    , max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS
percent_towards_wraparound
    , max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float)))
AS percent_towards_emergency_autovac
FROM per_database_stats;

 oldest_current_xid | percent_towards_wraparound |
percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
          230935699 |                         12 |
          115

# Top 8 individual databases
SELECT datname
    , age(datfrozenxid)
    , current_setting('autovacuum_freeze_max_age')
FROM pg_database
ORDER BY 2 DESC LIMIT 8;

          datname          |    age    | current_setting
---------------------------+-----------+-----------------
 siteservice  | 230935699 | 200000000
 coupon-ws           | 217613246 | 200000000
 contextchangestore | 211343280 | 200000000
 template0                 | 210351639 | 200000000
 productmanager | 207876167 | 200000000
 rhistory              | 207876167 | 200000000
 smsservice   | 207876167 | 200000000
 techservice      | 205432524 | 200000000

That top database `siteservice` is the "problematic" one where a
manual vacuum won't coerce it to free up the `datfrozenxid`.

Looking at the tables in that database:

# SELECT c.oid::regclass
    , age(c.relfrozenxid)
    , pg_size_pretty(pg_total_relation_size(c.oid))
FROM pg_class c
JOIN pg_namespace n on c.relnamespace = n.oid
WHERE relkind IN ('r', 't', 'm')
AND n.nspname NOT IN ('pg_toast')
ORDER BY 2 DESC LIMIT 10;

       oid         |    age    | pg_size_pretty
--------------------+-----------+----------------
 pg_database        | 230935699 | 4264 kB
 pg_proc            |  93543215 | 976 kB
 pg_collation       |  93543215 | 560 kB
 pg_attribute       |  93543215 | 600 kB
 pg_shdepend        |  59515320 | 15 MB
 pg_statistic       |  53828900 | 464 kB
 pg_subscription    |  53172718 | 16 kB
 pg_pltemplate      |  53172718 | 56 kB
 pg_authid          |  53172718 | 8616 kB
 pg_db_role_setting |  53172718 | 64 kB

I thought maybe it had to do with my replication slots somehow:

# select slot_name, slot_type, database, active, catalog_xmin,
restart_lsn, confirmed_flush_lsn from pg_replication_slots ;
               slot_name                | slot_type |
database            | active | catalog_xmin |  restart_lsn  |
confirmed_flush_lsn

----------------------------------------+-----------+-------------------------------+--------+--------------+---------------+---------------------
 dbs1db02                               | physical  |
             | f      |              |               |
 dbs1db01                               | physical  |
             | t      |              | 4D25/ACE6EE08 |
 dbs1db03                               | physical  |
             | t      |              | 4D25/ACE6EE08 |
 dbs2db01                               | physical  |
             | t      |              | 4D25/ACE6EE08 |
 debezium_cmanager              | logical   | campaign-manager
     | t      |   2152258063 | 4D25/A421A6C8 | 4D25/ABC18C88
 debezium_rservice        | logical   | retail-content-service
| t      |   2152238060 | 4D25/8EC403B0 | 4D25/A6105DF8
 debezium_partnerservice | logical   | partnerservice | t      |
2152238060 | 4D25/8EC403B0 | 4D25/A5446630
 dbs1_dr                                | physical  |
             | f      |              |               |

So I either restarted the physical standbys or I restarted the logical
connections (Debezium -> Kafka).

I'm also working on a:
# vacuumdb --all --jobs=10 --echo --analyze

But this is still running (and honestly I'm not really hopeful it's
going to do the trick).

My primary, read/write database is Postgresql 10.4 (CentOS 7) while my
standby databases have been patched to 10.17.

Any thoughts on this?

Thanks,Matt