Re: four template0 databases after vacuum

On 02/06/2016 06:43 PM, Kazuaki Fujikura wrote:
> Hi there,
>
> Version: 9.1.6 running since Dec, 2014
> We have 3 different databases.
>

See comment in line.

> [problem history/background]
>
> Jan 10th, 2016:
> The first problem was autovacuum issue.
> - autovacuum could not finish successfully.
> - I set autovacuum_freeze_max_age to 2 hundreds million.
> - autovacuum immediately finished against the database which age was
> over 2 hundreds million.

Which was?

> - so, autovacuum did not go next database

Which was?

> - I then run "vacuumdb -az" and run vacuum freeze analyze against
> template0 after setting datallowconn to true

Why? template0 is by default read-only there should be nothing happening
it to require vacuuming.

> - I set datallowconn to false
>
> ============================================================
> $ vacuumdb -az
> $ psql template1
> template1=# UPDATE pg_database SET datallowconn = TRUE where datname =
> 'template0';
> UPDATE 1
> template1=# \c template0
> template0=# VACUUM FREEZE ANALYZE ;
> VACUUM
> template0=# \c template1
> template1=# UPDATE pg_database SET datallowconn = FALSE where datname =
> 'template0';
> UPDATE 1
> ============================================================
>
> - At this point, there is one template0 only
>
>
> Jan 30th, 2016:
> After three weeks, I again hit the same issue - autovacuum could not finish.

Not finish on what?

> This time, the age did not reduce with manual vacuum.

Age of what?

> I then run vacuum full to pg_database. The age of pg_database becomes
> minus value.
> Then, autovacuum started again.
>
> ============================================================
> target_db=# SELECT relname, age(relfrozenxid) FROM pg_class WHERE
> relkind = 'r' ;
> relname | age
> ------------------------------------------------+-----------
> pg_database | 219383067
> target_db=# VACUUM FREEZE ;
> VACUUM
> target_db=# SELECT relname, age(relfrozenxid) FROM pg_class WHERE
> relkind = 'r' ;
> relname | age
> ------------------------------------------------+-----------
> pg_database | 219387307
> target_db=# VACUUM FULL ;
> VACUUM
> relname | age
> ------------------------------------------------+-----------
> pg_database | -1861408089
> ============================================================
>
>
>
> Yesterday:
>
> I run the following command to run vacuum full to all pg_database.

This I do not get, how the xid count on pg_database so quickly and to
such an extent that it needs a VACUUM FULL?

Is there a script that is creating and dropping databases rapidly?

> Then, I run vacuum freeze analyze and vacuum full to template0 after
> setting datallowconn.
>
> ============================================================
> $ psql -lt | awk '{print $1}' | grep -v ^$ | grep -v ^\| | while read
> line; do psql ${line} -c "VACUUM FULL pg_database;"; done
> VACUUM ....

What are you trying to do with the above?
I do not think it is a coincidence that the first time the above was run
in this sequence, shortly after 4 template0 databases appear.

>
> $ psql template1
> template1=# UPDATE pg_database SET datallowconn = TRUE where datname =
> 'template0';
> UPDATE 1
> template1=# \c template0
> template0=# VACUUM FULL ;
> VACUUM
> template0=# \c template1
> template1=# UPDATE pg_database SET datallowconn = FALSE where datname =
> 'template0';
> UPDATE 4
> ============================================================
>
> ***Then I HAVE FOUR template0 DATABASES***
>
>
>
>
> [Current problems]
>
> We now have three issues in our production.
>
> 1. It looks four template0 databases exist
> 2. Xid of template0 keeps growing
> 3. Can not freeze xid of template0
>
>
>
> 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
> ============================================================

Can you show?:

SELECT * from pg_database;

If you do not want to show the whole cluster, then at least the
databases involved in this discussion.

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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com