Re: oldest xmin is far in the past :: BUT xmin is not available in system

Other details:

postgres=> select version();
version
-------------------------------------------------------------------------------------------------------------
PostgreSQL 13.5 on aarch64-unknown-linux-gnu, compiled by
aarch64-unknown-linux-gnu-gcc (GCC) 7.4.0, 64-bit
(1 row)

postgres=> select aurora_version();
aurora_version
----------------
13.5.1
(1 row)

postgres=> \l+

List
of databases

Name | Owner | Encoding | Collate | Ctype | Access
privileges | Size | Tablespace | Description

-------------------+----------+----------+-------------+-------------+-----------------------+-----------+------------+--------------------------------------------

Postgres | root | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/root
+| 361 GB | pg_default |

| | | | |
root=CTc/root +| | |

| | | | |
pmm=CTc/root | | |

Test | root | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
| 8391 kB | pg_default | default administrative connection
database

rdsadmin | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
rdsadmin=CTc/rdsadmin | No Access | pg_default |

template0 | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/rdsadmin +| 16 MB | pg_default | unmodifiable empty database

| | | | |
rdsadmin=CTc/rdsadmin | | |

template1 | root | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
root=CTc/root +| 8215 kB | pg_default | default template for new
databases

| | | | |
=c/root | | |

(5 rows)

executing the vacuum on the entire cluster is also giving the same HINTS
and WARNING's

WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or
drop stale replication slots.
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or
drop stale replication slots.
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or
drop stale replication slots.
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or
drop stale replication slots.
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or
drop stale replication slots.
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or
drop stale replication slots.
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or
drop stale replication slots.
WARNING: oldest xmin is far in the past

Regards,
BK

On Tue, Apr 19, 2022 at 11:36 AM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

>
>
> On Monday, April 18, 2022, bhargav kamineni <kbn98406(at)gmail(dot)com> wrote:
>
>> Hi Team,
>>
>> It seems vacuum is behaving somewhat weird on postgres database ,
>> observing below HINTS on the vacuum logs
>>
>> WARNING: oldest xmin is far in the past
>>
>> HINT: Close open transactions soon to avoid wraparound problems.
>>
>> You might also need to commit or roll back old prepared transactions, or
>> drop stale replication slots.
>>
>>
>> What version?
>
> What other databases are present?
>
> Others can give better (more detailed/nuanced) guidance but if you can
> just start vacuuming every table in every database manually, you probably
> should just do that. Vacuum freeze specifically.
>
> David J.
>
>