PG数据库预防事务年龄过大处理

最新推荐文章于 2024-07-29 22:51:22 发布

岳麓丹枫001

最新推荐文章于 2024-07-29 22:51:22 发布

阅读量1.5k

点赞数

分类专栏： PostgreSQL

本文链接：https://blog.csdn.net/yueludanfeng/article/details/95635162

版权

PostgreSQL 专栏收录该内容

157 篇文章 6 订阅

订阅专栏

PG数据库预防事务年龄过大处理

PSQL_BIN_DIR=/home/postgres/pgsql/bin
$PSQL_BIN_DIR/psql -d postgres -U postgres  -q -At -c "select datname from pg_database where datname not in('template0','template1','postgres') order by datname;"  >${DBNAMEFILE}
while read -r line
do
	DB_NAME=${line}
	$PSQL_BIN_DIR/psql   -U postgres   -d ${DB_NAME} -q -At -c "select relname  from pg_class where  age(relfrozenxid) >= 1000000000 and age(relfrozenxid) <> 2147483647 and relkind='r' "  > ${old_age_tables}
	
	for line in `cat ${old_age_tables}`
	do
		table_name="`echo $line`"
		echo  "${table_name} is being freezed ! ! ! " >>  ${AUTOVACUUM_LOGFILE}
		if [ -n "${table_name}" ];then
	$PSQL_BIN_DIR/psql -d ${DB_NAME} -U postgres -c "\d $table_name"
	if [ $? -eq 0 ];then
			$PSQL_BIN_DIR/vacuumdb  -z  -e -v  -F --username=$DBUSER -h ${MY_PGHOST} -t ${table_name} -d ${DB_NAME} >>${AUTOVACUUM_LOGFILE}    2>&1
	fi
		fi
	done

done < ${DBNAMEFILE}