Pythonスクリプトによるサーバ上のすべてのPostgreSQLデータベースのバックアップ
8679 ワード
スクリプトの内容
生成されたデータベースバックアップ名
#! /usr/local/python3/bin/python
import os
import psycopg2
import time
db_host = "172.16.101.54"
db_port = 5432
db_user = "dbadmin"
db_password = "adwwxsade"
db_default = "postgres"
backup_path = "/usr/local/pgsql/dba/exp"
log_success = "/usr/local/pgsql/dba/exp/log_success.txt"
log_error = "/usr/local/pgsql/dba/exp/log_error.txt"
mail_list = "******"
backup_day = time.strftime("%Y%m%d")
databases = []
# check backup path if exists.
def check_backup_path():
if not os.path.exists(backup_path):
os.mkdir(backup_path)
# get all databases information:
def get_all_databases():
global databases
try:
conn = psycopg2.connect(host=db_host, port=db_port, user=db_user, password=db_password, database=db_default)
except BaseException as e:
with open(log_error, "a", encoding="utf-8") as f:
f.truncate()
f.write(str(e))
os.system("/bin/mailx -s '[Urgent]:Database on {0} connect failed, please check.' {1} < {2}".format(
db_host, mail_list, log_error))
else:
cur = conn.cursor()
cur.execute("select datname from pg_database where datname not in('template0','template1','postgres')")
rows = cur.fetchall()
for row in rows:
databases.append(list(row))
conn.close()
def backup_all_databases():
global databases
try:
for database in databases:
db = str(database).replace('[', '').replace(']', '')
os.system("/usr/local/pgsql/bin/pg_dump --verbose --create {0} | gzip > {2}/{0}_{1}_sql.gz".format(
db, backup_day, backup_path))
with open(log_success, "a", encoding="utf-8") as f:
f.write("Database {0} backup finished...
".format(db))
except BaseException as e:
with open(log_error, "a", encoding="utf-8") as f:
f.truncate()
f.write(str(e))
os.system("/bin/mailx -s '[Urgent]:Database {0} backup failed, please check.' {1}} < {2}".format(
db, mail_list, log_error))
else:
os.system("/bin/mailx -s 'All Database backup finished.' {0} < {1}".format(mail_list, log_success))
check_backup_path()
get_all_databases()
backup_all_databases()
生成されたデータベースバックアップ名
$ ls -lh
total 4.4M
-rw-r--r-- 1 postgres appuser 2.2M Mar 14 11:27 edbstore_20200314_sql.gz
-rw-r--r-- 1 postgres appuser 2.2M Mar 14 11:27 HERE_EU_DATA_20200314_sql.gz
-rw-r--r-- 1 postgres appuser 579 Mar 14 11:27 proxydb_20200314_sql.gz