0

i am running postgres on aws and trying to get my head around the "tx wraparound topic".

i executed the following steps to "play" around

  1. take snaptshot of production database (here "our_main_db")
  2. create new aws instance with same parameters as production one
  3. psql into the newly created instance
  4. take current txid, and age(datfrozenxid) of databases. i can see that they are over 100.000.000 everywhere
    -- 100.517.962
    SELECT txid_current();

    -- template0 100516214
    -- rdsadmin  100516214
    -- template1    100516214
    -- postgres     100516214
    -- our_main_db  100516214
    SELECT datname, age(datfrozenxid) FROM pg_database;
  1. execute a "vacuum freeze analyze" on "our_main_db"
     -- 100.517.962
    SELECT txid_current();

    -- template0 100516214
    -- rdsadmin  100516214
    -- template1    100516214
    -- postgres     100516214
    -- our_main_db  148
    SELECT datname, age(datfrozenxid) FROM pg_database;
  1. execute a "vacuum freeze analyze" on "template1", "postgres"
     -- 100.517.962
    SELECT txid_current();

    -- template0 100516214
    -- rdsadmin  100516214
    -- template1    75
    -- postgres     40
    -- our_main_db  148
    SELECT datname, age(datfrozenxid) FROM pg_database;

Now the following questions trouble me

AWS gives me a predefined metric "MaximumUsedTransactionIDs" which i assume is based on the following query.

SELECT max(age(datfrozenxid)) FROM pg_database; w

which leads to "100516214". i cannot (and should not) connect to "rdsadmin" and "template0" to do a manual "vacuum freeze" on them.

as i understand the docs the "autovacuum" process should eventually deal with with these dbs and when they reach a certain threshold "200.000.000" a forded "vacuum freeze" is done by postgres itself.

  1. is this assumption correct?
  2. should i just change the alert threshold to 1.000.000.000 (which is 50% before the "stop the world thing happens") as recommended in the aws docs.
  3. is there a way to force the "freeze" on the admin tables that i cannot connect to? i want to see this metric value going down.
  4. does the "stop the world" happen for the whole postgres instance if one of the databases reaches the 2billion tx id limit or is this done per db inside the postgres instance?
  5. would a "vacuum full" on the "our_main_db" solve the problem or is there still the problem with the other dbs i cannot directly modify which keep the metric value up?

any help highly appreciated thanks and greetings from hamburg marcel

  • 1
    1 yes 2 yes 3 no 4 instance wide 5 what problem? No problem; nothing to solve. – jjanes Apr 4 at 12:37
  • @jjanes thanks for the answer. short and clear :) we don't actually have a problem, i was just wondering how things work and why the overall "SELECT max(age(datfrozenxid)) FROM pg_database;" does not go down. this seems to be related to the "admin dbs...". thanks – niesfisch Apr 4 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.