/usr/bin/pg_dump -U ${zabbix_user} ${zabbix_dbname} | bzip2 -c > zabbix_pgsql_backup_20170309.dump.bz2

PGPASSWORD=xxxx  /usr/bin/psql -U zabbix

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a;

DELETE FROM history WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
DELETE FROM history_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
DELETE FROM history_str WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
DELETE FROM history_text WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
DELETE FROM history_log WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
DELETE FROM trends WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
DELETE FROM trends_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

# trends_uint为表名
VACUUM (VERBOSE,ANALYZE) trends_uint;

SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;

# 显示Schema的搜索路径
SHOW search_path;

# 从上面的输出可以看出，默认没有partitions，我们加入，如果不加入，在drop table的时候，就会提示does not exist
SET search_path TO "$user",partitions,public;

# 可以drop了，CASCADE的作用是把关联的触发条件删除，这触发条件，其实就是什么数据应该插入此分区的，现在已经是2017了，自然不会有2015的数据需要插入，所以可以放心删除
drop table history_uint_2015_01_11 CASCADE;

grep _2015_ zabbix_table_size.txt |awk -F\| '{print "drop table" $3 "CASCADE;"}'