1. It looks four template0 databases exist
============================================================
$ psql -l | grep template0
template0 | postgres | UTF8 | C | C | =c/postgres +
template0 | postgres | UTF8 | C | C | =c/postgres +
template0 | postgres | UTF8 | C | C | =c/postgres +
template0 | postgres | UTF8 | C | C | =c/postgres +
============================================================
These have same dataid.
============================================================
postgres=# SELECT datid, datname FROM pg_stat_database where datname = 'template0';
datid | datname
-------+-----------
12772 | template0
12772 | template0
12772 | template0
12772 | template0
(4 rows)
============================================================
2. Xid of template0 keeps growing
============================================================
postgres=# SELECT datname, age(datfrozenxid) FROM pg_database order by age desc;
datname | age
------------------------------------------------+-----------
template0 | 198431852
template0 | 198431852
template0 | 198431852
template0 | 50480024
template1 | 45629585
============================================================
At this moment, the maximum age value of all databases is template0.
The age value keeps growing.
One of 4 template0 is young (504080024). Other three template0s are still old.
3. Can not freeze xid of template0
To reset xid of template0, I did vacuum full/ vacuum freeze to template0. But,
the age of three template0 did not change. Only of of 4 template0 had successfully
changed the age young.
============================================================
$ psql template1
template1=# UPDATE pg_database SET datallowconn = TRUE where datname = 'template0';
UPDATE 4
template1=# \c template0
template0=# VACUUM FREEZE ANALYZE ;
VACUUM
template0=# VACUUM FULL ;
VACUUM
template0=# \c template1
template1=# UPDATE pg_database SET datallowconn = FALSE where datname = 'template0';
UPDATE 4
============================================================
I run the commands above. But, I could not change the age of three template0 databases.
[My idea to fix this]
If I don't do anything about this, I think our production service will be down because it exceeds the limit of xid.
I guess if I drop all template0 and create template0 again, then everything gets back normal.
But I am not quite sure if my approach is right.
I would appreciate any suggestion/comments.
Best regards,
Kazuaki Fujikura