利用Python脚本备份服务器上所有PostgreSQL数据库

#! /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 = "agm43gadsg"
db_default = "postgres"
backup_path = "/usr/local/pgsql/dba/exp"
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
    conn = psycopg2.connect(host=db_host, port=db_port, user=db_user, password=db_password, database=db_default)
    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
    for database in databases:
        db = str(database).replace(‘[‘, ‘‘).replace(‘]‘, ‘‘)
        try:
            os.system("/usr/local/pgsql/bin/pg_dump --verbose --create {0} | gzip > {2}/{0}_{1}_sql.gz".format(db, backup_day, backup_path))
        except BaseException as e:
            print(e)
        else:
            print("{0} backup finished...".format(db))


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