熊猫学猿--greenplum备份

因为我的greenplum很多数据表都是从其他库同步过来，不需要进行全库备份，所以就使用定时更新需要备份的文件，然后使用gpbackup进行备份

获取需要备份的数据表名称

/home/gpadmin/backup_lxcs_data.py

import pandas as pd
import sys
from sqlalchemy import create_engine

def main():
    postgresql_config = {
        'host': '192.168.200.201',
        'port': 5432,
        'database': 'lxcs_data',
        'username': 'gpadmin',
        'password': 'qaz123...'
    }

    db = create_engine(
        'postgresql+psycopg2://{username}:{password}@{host}:{port}/{database}'.format(**postgresql_config))

    sql="""select concat('public.',tablename) as tablename from pg_tables where schemaname = 'public' and (tablename   ~'^(gp_)'  or tablename   ~'^(import_)' or tablename ~'^(tmp_)')  """

    data = pd.read_sql_query(sql, db)

    fileObject = open('/home/gpadmin/include_file.txt', 'w')
    for t in data.tablename.unique():
        fileObject.write(t)
        fileObject.write('\n')

    print('finish')

if __name__ == '__main__':
    try:
        main()
        sys.exit(0)
    except Exception as e:
        print(e)
        sys.exit(1)

在数仓服务器使用crontab执行

0 6 * * * root /home/gpadmin/back_lxcs_file.sh

#!/bin/bash
work_path='/home/gpadmin'
/root/miniconda3/bin/python ${work_path}/backup_lxcs_data.py

备份指定列表

gpbackup --dbname lxcs_data --include-table-file /home/gpadmin/include_file.txt

文件备份位置   /data/master/gpseg-1/backups

数据恢复

gprestore --timestamp 20171103152558 --redirect-db test

查看备份信息

gpbackup_manager list-backups