因为我的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