template0数据库默认是不允许连接的,可以查看pg_database看对应字段,如下:
datallowconn字段为f,不允许连接
gpadmin=# select * from pg_database where datname='template0';
-[ RECORD 1 ]-+---------------------------------
datname | template0
datdba | 10
encoding | 6
datistemplate | t
datallowconn | f
datconnlimit | -1
datlastsysoid | 10898
datfrozenxid | 52795542
dattablespace | 1663
datconfig |
datacl | {=c/gpadmin,gpadmin=CTc/gpadmin}
但是由于版本问题,template0的年龄一直未回收,所以只能手动回收年龄了
回收年龄步骤如下:
1.新建一个SQL文件,放入修改数据字典的语句和vacuum freeze语句,最后改回数据字典
cat vacuum_tmp0.sql
set allow_system_table_mods='DML';
update pg_database set datallowconn='t' where datname='template0';
\c template0
vacuum freeze ;
\c postgres gpadmin
set allow_system_table_mods='DML';
update pg_database set datallowconn='f' where datname='template0';
2.shell脚本调用以上sql文件循环执行
/home/gpadmin/segment_hosts_file里面存放了GP的每个segment_host,循环执行即可
cat vacuum_tmp0db.sh
#!/bin/bash
source /home/gpadmin/.bash_profile
for host in `cat /home/gpadmin/segment_hosts_file`;do
PGOPTIONS="-c gp_session_role=utility" psql -h ${host} -p 40000 -d gpadmin -f vacuum_tmp0.sql
PGOPTIONS="-c gp_session_role=utility" psql -h ${host} -p 40001 -d gpadmin -f vacuum_tmp0.sql
PGOPTIONS="-c gp_session_role=utility" psql -h ${host} -p 40002 -d gpadmin -f vacuum_tmp0.sql
PGOPTIONS="-c gp_session_role=utility" psql -h ${host} -p 40003 -d gpadmin -f vacuum_tmp0.sql
done
echo "The all segment's template0 db have be vacuumed"