oldest xmin is far in the past - Postgresql 9.4.4

Question

I have a postgresql 9.4.4 in a production environment. Since 3 weeks, I'm receiving a lot of messages in pg_log:

<:::2020-04-06 23:59:59 BRT::2020-04-06 23:59:55 BRT:72350>|LOG:  automatic vacuum of table "template0.pg_catalog.pg_range": index scans: 0
    pages: 0 removed, 1 remain
    tuples: 0 removed, 6 remain, 0 are dead but not yet removable
    buffer usage: 20 hits, 0 misses, 0 dirtied
    avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
    system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
<:::2020-04-07 00:00:00 BRT::2020-04-06 23:59:59 BRT:72428>|WARNING:  oldest xmin is far in the past
<:::2020-04-07 00:00:00 BRT::2020-04-06 23:59:59 BRT:72428>|HINT:  Close open transactions soon to avoid wraparound problems.

The warnings occurs a lot of times in the day, and it's look like happening on vacuum in each table of template0.pg_catalog.

Searching in other stack overflow questions, I found many answers, but none of them fit my case.

They say it can be 3 things:

1. Idle in transaction running for a long time:

Running select * from pg_stat_activity where state = 'idle in transaction', I get just one connection in this state, and it was opened today.

2. Prepared transactions transactions that have not been closed

Running select * from pg_prepared_xacts;, I got 9 lines and finished everyone with ROLLBACK PREPARED 'gid';. So, I don't have any prepared transactions right now.

3. Unused replication slot

Running select * from pg_replication_slots, I got just one line, and this replication is in use in another server.

slot_name;plugin;slot_type;datoid;database;active;xmin;catalog_xmin;restart_lsn
replica;;physical;;;t;2845561097;;584C/CC6604F0

So, I can't drop this one cause its in use. My two servers are running normally, except for these messages in the log.

My server:
autovacuum = on 
archive_mode = on
wal_keep_segments = 20
max_wal_senders = 3
max_replication_slots = 3
wal_level = hot_standby
hot_standby = on
archive_command = 'test -f %p && cp %p /opt/postgres/archxlog/%f'

My recovery.conf (on the other server, with standby replication):

standby_mode = 'on'
primary_conninfo = 'user= host= port= sslmode=disable sslcompression=1'
primary_slot_name = 'replica'

Thanks for the help!

EDIT:

Searching on google, i found this website: http://micronetinternational.com/index.pl/en/00/https/postgrespro.com/list/thread-id/1556972

They say the problem is related to a substransaction that is pending on streaming.

Looking at the folder pg_subtrans folder, I founded a lot of so old files.

ls -lha | more
total 1,4G
drwx------.  2 postgres postgres  92K Abr 27 10:56 .
drwx------. 19 postgres postgres 4,0K Fev 21 16:38 ..
-rw-------   1 postgres postgres 256K Dez 11 11:07 A99B
-rw-------   1 postgres postgres 256K Dez 11 11:07 A99C
-rw-------   1 postgres postgres 256K Dez 11 11:07 A99D
-rw-------   1 postgres postgres 256K Dez 11 11:07 A99E

My replication is about of February of this year. So, these files are not recent. How is the best way to clear this folder?

Answer 1

For me the issue was caused by a couple of stale 2FA txns in pg_prepared_xacts which were around seven days old. Rolling them back fixed the issue.

Answer 2

I managed to solve the problem;

I stopped the replication and dropped the replication slot. When I did this, the pg_subtrans was cleanned up, and the warning disappeared.

But, I lost my replication and had to start from scratch.

Everything is working normally now.

Thanks guys!