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

[bhargav kamineni <kbn98406@xxxxxxxxx>]

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.g.johnston@xxxxxxxxx> wrote:

On Monday, April 18, 2022, bhargav kamineni <kbn98406@xxxxxxxxx> 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.