Sfrgttk

How can saying a song's name be a copyright violation?

Is there a hemisphere-neutral way of specifying a season?

How could indestructible materials be used in power generation?

Extract rows of a table, that include less than x NULLs

How can I deal with my CEO asking me to hire someone with a higher salary than me, a co-founder?

A category-like structure without composition?

Assassin's bullet with mercury

Is there an expression that means doing something right before you will need it rather than doing it in case you might need it?

Why can't we play rap on piano?

Do scales need to be in alphabetical order?

Why are the 737's rear doors unusable in a water landing?

How do I deal with an unproductive colleague in a small company?

If human space travel is limited by the G force vulnerability, is there a way to counter G forces?

How would I stat a creature to be immune to everything but the Magic Missile spell? (just for fun)

Why do bosons tend to occupy the same state?

What about the virus in 12 Monkeys?

How can I prevent hyper evolved versions of regular creatures from wiping out their cousins?

Venezuelan girlfriend wants to travel the USA to be with me. What is the process?

Forgetting the musical notes while performing in concert

Why doesn't using multiple commands with a || or && conditional work?

Should I cover my bicycle overnight while bikepacking?

How dangerous is XSS?

Why no variance term in Bayesian logistic regression?

Size of subfigure fitting its content (tikzpicture)

Postgres TX Wraparound - confused

About “Transaction ID Wraparound”PostgreSQL 7.4 data corruptionRestore postgres data/tablespace to new tablespace (at new mount point)?PostgreSQL continuous archiving - use snapshot for base backup on AWS?What's causing Postgres to freeze?When is Postgres autovacuum executedPostgres not using the index even when rows returned is 5% of the tableDoes cancelling an (AUTO)VACUUM process in PostgreSQL make all the work done useless?Postgres Transaction ID Wraparound and AutovacuumAvoid Vacuum (to prevent wraparound) on read-only databases

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;

5. 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;

5. 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

postgresql vacuum autovacuum

share|improve this question

asked 16 mins ago

niesfischniesfisch

1

New contributor

niesfisch is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

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;

5. 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;

5. 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

postgresql vacuum autovacuum

share|improve this question

asked 16 mins ago

niesfischniesfisch

1

New contributor

niesfisch is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

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;

5. 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;

5. 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

postgresql vacuum autovacuum

share|improve this question

asked 16 mins ago

niesfischniesfisch

1

New contributor

niesfisch is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

    -- 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;

     -- 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;

     -- 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;

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

share|improve this question

asked 16 mins ago

niesfischniesfisch

1

New contributor

niesfisch is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked 16 mins ago

niesfischniesfisch

1

New contributor

niesfisch is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 16 mins ago

niesfischniesfisch

1

New contributor

niesfisch is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 16 mins ago

niesfischniesfisch

1