维护定期回收空间任务

背景介绍

由于数据的增删改查操作，会产生一些临时文件和日志文件等，这些文件会占用一定的磁盘空间。如果不及时清理，可能会导致磁盘空间不足，影响系统的正常运行。因此，需要定期进行空间回收任务，以保证系统的稳定运行。

维护回收空间任务

空间回收任务主要包括以下几个方面：

1. 清理临时文件：在数据库的操作过程中，可能会产生一些临时文件，如事务日志、备份文件等。这些文件在完成其使命后，需要及时被清理。

2. 回收已删除的数据：在数据库中删除数据时，实际上只是标记了该数据为已删除，但并未真正从磁盘上删除。因此，需要定期进行空间回收，将这些已被标记为删除的数据真正从磁盘上删除。

3. 优化表结构：对于一些大型的表，可以通过优化表结构来节省磁盘空间。例如，可以考虑使用分区表、索引等方式来提高数据访问效率，同时也可以减少磁盘空间的占用。

4. 定期备份：定期对数据库进行备份，不仅可以防止数据丢失，还可以通过删除旧的备份来释放磁盘空间。

以下为两种不同场景下如何维护回收空间任务。

不锁表回收脏数据

在进行数据更新或者删除操作时不锁定整个表，而是只对需要修改的部分进行锁定。这种做法可能会导致一些脏数据产生，优点是可以提高系统的并发性能，减少用户等待时间等。具体方式如下：

• 命令：连接每个数据库，以数据库的所有者身份登录，执行VACUUM命令。

• 频率：

  • 如果有大批量实时更新的情况（即不断执行INSERT VALUES、UPDATE、DELETE等操作），建议每天执行一次，或每周至少一次。

  • 如果更新是每天一次批量进行的，建议每周执行一次，或不要超过一个月执行一次。

• 对系统影响：不会锁表，表可以正常读写。会导致CPU、I/O使用率增加，可能影响查询的性能。

• 执行方法：

  • 可以使用pg_cron插件创建定时任务执行VACUUM，具体内容，请参见pg_cron。

  • 可以使用如下的Linux Shell脚本文件连接数据库，作为crontab定期任务来执行。

  #!/bin/bash
  export PGHOST=myinst.gpdb.rds.tbsite.net
  export PGPORT=3432
  export PGUSER=myuser
  export PGPASSWORD=mypass
  #do not echo command, just get a list of db
  dblist=`psql -d postgres -c "copy (select datname from pg_stat_database) to stdout"`
  for db in $dblist ; do
      #skip the system databases
      if [[ $db == template0 ]] ||  [[ $db == template1 ]] || [[ $db == postgres ]] || [[ $db == gpdb ]] ; then
          continue
      fi
      echo processing $db
      #vacuum all tables (catalog tables/user tables)
      psql -d $db -e -a -c "VACUUM;"
  done

维护窗口回收脏数据

在业务暂停的维护窗口，可以回收脏数据并释放脏数据所占的空间。具体方式如下：

• 命令：连接每个数据库，以数据库的所有者身份登录（需要对所有操作对象有所有者权限）。

  1. 执行REINDEX SYSTEM <database name>。

  2. 对每张数据表，执行VACUUM FULL <table name>，对列存表还需要执行REINDEX TABLE <table name>。

  3. 对于系统表（包括pg_class, pg_attribute, pg_index等），当有频繁建删表，建删索引等操作时，也建议执行 VACUUM FULL <table name>进行定期维护。注意：该操作需要业务停止访问数据库。

• 频率：至少每周执行一次。如果每天会更新几乎所有数据，需要每天做一次。

• 对系统影响：会对正在进行VACUUM FULL或REINDEX的表进行锁定，无法读写。会导致CPU、I/O使用率增加。

• 执行方法：

  • 可以使用pg_cron插件创建定时任务执行VACUUM FULL和REINDEX，具体内容，请参见pg_cron。

  • 可以使用如下的Linux Shell脚本文件，作为crontab定期任务来执行。

#!/bin/bash
export PGHOST=myinst.gpdb.rds.tbsite.net
export PGPORT=3432
export PGUSER=myuser
export PGPASSWORD=mypass
#do not echo command, just get a list of db
dblist=`psql -d postgres -c "copy (select datname from pg_stat_database) to stdout"`
for db in $dblist ; do
    #skip system databases
    if [[ $db == template0 ]] ||  [[ $db == template1 ]] || [[ $db == postgres ]] || [[ $db == gpdb ]] ; then
        continue
    fi
    echo processing db "$db"
    #do a normal vacuum
    psql -d $db -e -a -c "VACUUM;"
    #reindex system tables firstly
    psql -d $db -e -a -c "REINDEX SYSTEM $db;"
    #use a temp file to store the table list, which could be vary large
    cp /dev/null tables.txt
    #query out only the normal user tables, excluding partitions of parent tables
    psql -d $db -c "copy (select '\"'||tables.schemaname||'\".' || '\"'||tables.tablename||'\"' from (select nspname as schemaname, relname as tablename from pg_catalog.pg_class, pg_catalog.pg_namespace, pg_catalog.pg_roles where pg_class.relnamespace = pg_namespace.oid and pg_namespace.nspowner = pg_roles.oid and pg_class.relkind='r' and (pg_namespace.nspname = 'public' or pg_roles.rolsuper = 'false' ) ) as tables(schemaname, tablename) left join pg_catalog.pg_partitions on pg_partitions.partitionschemaname=tables.schemaname and pg_partitions.partitiontablename=tables.tablename where pg_partitions.partitiontablename is null) to stdout;" > tables.txt
    while read line; do
        #some table name may contain the $ sign, so escape it
        line=`echo $line |sed 's/\\\$/\\\\\\\$/g'`
        echo processing table "$line"
        #vacuum full this table, which will lock the table
        psql -d $db -e -a -c "VACUUM FULL $line;"
        #reindex the table to reclaim index space
        psql -d $db -e -a -c "REINDEX TABLE $line;"
    done <tables.txt
done