My MaximumUsedTransactionIDs
seems to keep growing indefinitely, but I have
these evidences hinting me it maybe should not be happening that way:
$ PGPASSWORD=$MY_USER_LAND_RDS_SUPER_USER_PASSWORD psql -h $MY_RDS_INSTANCE -d
my_main_data_base -U $MY_USER_LAND_RDS_SUPER_USER
psql (9.5.8, server 9.5.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits:
256, compression: off)
Type "help" for help.
my_main_data_base=> SELECT datname FROM pg_database;
datname
-------------------
template0
rdsadmin
template1
postgres
my_main_data_base
another_data_base
(6 rows)
my_main_data_base=> VACUUM FREEZE; SELECT relname, age(relfrozenxid) AS xid_ag
e FROM pg_class JOIN pg_stat_all_tables USING (relname) ORDER BY xid_age DESC
LIMIT 5;
VACUUM
relname | xid_age
----------------------+---------
pg_toast_1150794 | 63707
a_user_table | 63707
another_user_table | 63707
pg_toast_1223948 | 63707
pg_toast_1223998 | 63707
(5 rows)
my_main_data_base=> \q
$ PGPASSWORD=$MY_USER_LAND_RDS_SUPER_USER_PASSWORD psql -h $MY_RDS_INSTANCE -d
another_data_base -U $MY_USER_LAND_RDS_SUPER_USER
psql (9.5.8, server 9.5.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits:
256, compression: off)
Type "help" for help.
another_data_base=> VACUUM FREEZE; SELECT relname, age(relfrozenxid) AS xid_ag
e FROM pg_class JOIN pg_stat_all_tables USING (relname) ORDER BY xid_age DESC
LIMIT 5;
VACUUM
relname | xid_age
--------------------+---------
pg_toast_2964 | 12350
pg_toast_3592 | 12350
pg_toast_2396 | 12350
pg_db_role_setting | 12350
pg_authid | 12350
(5 rows)
another_data_base=> \q
$ _
$MY_USER_LAND_RDS_SUPER_USER
is what AWS RDS provides me as a superuser.
It is a super user from the RDS point of view, but it is not a superuser
from the PostgreSQL point of view.
I would expect now to have MaximumUsedTransactionIDs
decreased, but it does
not. It is still at about ~80.000.000
.
I have planned that after it reaches 100.000.000
I'll fork a development
version of the RDS instance where I'll make it so AWS boots the autovacuum
process at it.
Questions
-
What is happening with
MaximumUsedTransactionIDs
? Why it does not shrink? -
Will
autovacuum
take care ofMaximumUsedTransactionIDs
once booted? -
Is
autovacuum
irrelevant in my scenario because I run manually unqualified
VACUUM FREEZE
s from time to time on all my user land databases? -
Should I just ignore
MaximumUsedTransactionIDs
? -
Can I somehow connect to database
rdsadmin
toSELECT
age(relfrozenxid)
there?
EDIT, fulfilling your first request, adding…
$ PGPASSWORD=$MY_USER_LAND_RDS_SUPER_USER_PASSWORD psql -h $MY_RDS_INSTANCE -d
my_main_data_base -U $MY_USER_LAND_RDS_SUPER_USER
psql (9.6.6, server 9.5.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits:
256, compression: off)
Type "help" for help.
postgresql=> SELECT datname, datfrozenxid FROM pg_database;
datname | datfrozenxid
-------------------+--------------
template0 | 1822
rdsadmin | 1822
template1 | 1822
postgres | 1822
my_main_data_base | 76628948
another_data_base | 76681257
(6 rows)
postgresql=> \q
$ _
Best Answer
Activate autovacuum in the parameter group and when it hits the configured level (min 100.000.000) it'll start and
MaximumUsedTransactionIDs
will go down drastically.