vCenter backup & restore script

vCenter Postgre DB yedeğini almak için aşağıdaki adımlar izlenebilir. Script içeriklerinde bir değişiklik yapmanıza gerek yoktur. Sarı alanları kopyalayıp bir editöre yapıştırmanız ve kaydetmeniz yeterli olacaktır.

backup_lin.py script içeriği

#!/usr/bin/env python

import argparse

from os import path, remove, close

from subprocess import call, Popen, PIPE

POSTG_BIN_DIR = "/opt/vmware/vpostgres/current/bin/"

PSQL = path.join(POSTG_BIN_DIR, "psql")

PG_DUMP = path.join(POSTG_BIN_DIR, "pg_dump")

parser = argparse.ArgumentParser()

parser.add_argument('-f',

action="store",

dest="backup_file",

required=True)

results = parser.parse_args()

fileOut = open("/tmp/backup_out.log", "a+")

fileError = open("/tmp/backup_err.log", "a+")

cmd = [PG_DUMP, "-a", "-Fc", "--disable-triggers", "-b", "-U", "postgres", "-f", results.backup_file, "VCDB"]

ret = call(cmd, stdout=fileOut, stderr=fileError)

fileOut.close()

fileError.close()

print ("Backup completed successfully.")

python script dosyasına yetki veriyoruz. chmod 700 /tmp/backup_lin.py sonrasında python backup_lin.py -f /tmp/vcsa_postgres_VCDB_12022020.bak konutu ile DB yedeklernir. Sarı ile işaretli dizilimde kırmızı puntolar ile yazılan kısım sizin vereceğiniz bir isimlendirme olabilir. Backup completed successfully. Yazısını gördükten sonra yedeğinizin alındığını anlayabilirsiniz.

işlerin yolunda gitmediğini düşünerek birde restore için script oluşturulalım. Bu script’in adıda restore_lin.py olsun. Script Içeriği aşağıdaki gibi olmalı.

restore_lin.py script içeriği

#!/usr/bin/env python

import argparse

import time

import sys

from os import path, remove, close

from subprocess import call, Popen, PIPE

VPOSTGRES_FOLDER = "/opt/vmware/vpostgres/current/"

PSQL = path.join(VPOSTGRES_FOLDER, "bin", "psql")

PG_RESTORE = path.join(VPOSTGRES_FOLDER, "bin", "pg_restore")

constrfilename = "create_constr" + time.strftime("%Y%m%d%H%M%S%m") + ".sql"

indexfilename = "create_index" + time.strftime("%Y%m%d%H%M%S%m") + ".sql"

CREATE_INDEXES = ("/tmp/" + indexfilename)

CREATE_CONSTRAINTS = ("/tmp/" + constrfilename)

def psqlFile(filePath, userName = "postgres", database = "VCDB"):

return [PSQL, "-U", userName, "-d", database, "-q", "-f", filePath]

def psqlQuery(query, userName = "postgres", database = "VCDB"):

return [PSQL, "-U", userName, "-d", database, "-q", "-t", "-c", query]

def execPsqlDynamicSql(sql):

'''

Executes dynamic sql query which is generated by the input

@sql: input for dynamic query

'''

pipe1 = Popen(psqlQuery(sql), stdout = PIPE)

cmd2 = [PSQL, "-U", "postgres", "-d", "VCDB"]

pipe2 = Popen(cmd2, stdin = pipe1.stdout, stdout = PIPE)

pipe1.stdout.close()

pipe2.communicate()

pipe2.stdout.close()

def preImportScripts(schemaName):

'''

VCDB index and constraint drop and create into files.

When completed drop all indexes and constraints.

This step is required for faster import.

@schemaName: vPostgres VCDB schema name

'''

sqlTblsp = """

CASE

WHEN c.relname LIKE 'vpx_alarm%'

THEN 'SET default_tablespace = alarm; '

WHEN c.relname LIKE 'vpx_event%'

THEN 'SET default_tablespace = event; '

WHEN c.relname LIKE 'vpx_task%'

THEN 'SET default_tablespace = task;'

WHEN c.relname LIKE 'vpx_hist_stat1%'

THEN 'SET default_tablespace = hs1;'

WHEN c.relname LIKE 'vpx_hist_stat2%'

THEN 'SET default_tablespace = hs2;'

WHEN c.relname LIKE 'vpx_hist_stat3%'

THEN 'SET default_tablespace = hs3;'

WHEN c.relname LIKE 'vpx_hist_stat4%'

THEN 'SET default_tablespace = hs4;'

ELSE ''

END

"""

sqlResetTblsp = """

CASE

WHEN c.relname LIKE 'vpx_alarm%'

OR c.relname LIKE 'vpx_event%'

OR c.relname LIKE 'vpx_task%'

OR c.relname LIKE 'vpx_hist_stat%'

THEN 'RESET default_tablespace;'

ELSE ''

END

"""

sqlCreateIndex = """

SELECT %s || pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) || ';' || %s

FROM pg_class c

JOIN pg_index i

ON c.oid = i.indrelid

JOIN pg_class c2

ON i.indexrelid = c2.oid

JOIN pg_namespace n

ON n.oid=c.relnamespace

AND nspname = '%s'

LEFT JOIN pg_catalog.pg_constraint con

ON con.conrelid = i.indrelid

AND con.conindid = i.indexrelid

AND con.contype IN ('p','u','x')

WHERE NOT EXISTS(SELECT 1

FROM pg_constraint

WHERE conname = c2.relname)

""" % (sqlTblsp, sqlResetTblsp, schemaName)

sqlCreateConstr = """

SELECT 'ALTER TABLE ' || c.relname||

' ADD CONSTRAINT ' || con.conname ||

' ' || pg_get_constraintdef(con.oid)||';' ||

CASE WHEN i.indisclustered='t'

THEN ' cluster ' || c.relname|| ' using ' || con.conname || ';'

ELSE ''

END

FROM pg_constraint con

JOIN pg_class c

ON con.conrelid = c.oid

JOIN pg_namespace n

ON n.oid = c.relnamespace

LEFT JOIN pg_index i

ON con.conrelid = i.indrelid

AND con.conindid = i.indexrelid

WHERE n.nspname = '%s'

ORDER BY con.contype DESC

""" % (schemaName)

sqlDropIndex = """

SELECT 'DROP INDEX IF EXISTS ' || c.relname || ' CASCADE;'

FROM pg_class c

LEFT JOIN pg_namespace n

ON n.oid = c.relnamespace

WHERE c.relkind IN ('i','')

AND n.nspname = '%s'

AND pg_table_is_visible(c.oid)

AND NOT EXISTS(SELECT 1

FROM pg_constraint

WHERE conname = c.relname)

""" % (schemaName)

sqlDropConstr = """

SELECT 'ALTER TABLE ' || relname ||

' DROP CONSTRAINT IF EXISTS ' || conname || ' CASCADE;'

FROM pg_constraint con

JOIN pg_class c

ON con.conrelid = c.oid

JOIN pg_namespace n

ON n.oid = c.relnamespace

WHERE n.nspname = '%s'

""" % (schemaName)

sqlTrunc = """

SELECT 'TRUNCATE TABLE ' || c.relname || ' CASCADE;'

FROM pg_class c

JOIN pg_namespace n

ON n.oid = c.relnamespace

WHERE c.relkind = 'r'

AND n.nspname = '%s'

AND pg_table_is_visible(c.oid)

""" % (schemaName)

with open(CREATE_INDEXES, "w") as f:

ret = call(psqlQuery(sqlCreateIndex), stdout=f, stderr=fileError)

print ("Saved Indexes from target DB to file %s." % (CREATE_INDEXES))

with open(CREATE_CONSTRAINTS, "w") as f:

ret = call(psqlQuery(sqlCreateConstr), stdout=f, stderr=fileError)

print ("Saved Constraints from target DB to file %s." %(CREATE_CONSTRAINTS))

execPsqlDynamicSql(sqlDropIndex)

print ("Dropped Indexes in target DB.")

execPsqlDynamicSql(sqlDropConstr)

print ("Dropped Constraints in target DB.")

execPsqlDynamicSql(sqlTrunc)

print ("Truncated all tables in target DB.")

def postImportScripts():

'''

Recreate indexes and constraints after data import.

'''

ret = call(psqlFile(CREATE_INDEXES), stdout=fileOut, stderr=fileError)

print ("Recreated Indexes back in target DB from file %s." % (CREATE_INDEXES))

ret = call(psqlFile(CREATE_CONSTRAINTS), stdout=fileOut, stderr=fileError)

print ("Recreated Constraints back in target DB from file %s." % (CREATE_CONSTRAINTS))

print ("Operation not cancellable. Please wait for it to finish...")

parser = argparse.ArgumentParser()

parser.add_argument('-f',

action="store",

dest="backup_file",

required=True)

results = parser.parse_args()

fileOut = open("/tmp/restore_out.log", "a+")

fileError = open("/tmp/restore_err.log", "a+")

sql = """

SELECT nspname

FROM pg_namespace

WHERE nspname in ('vc', 'vpx')

"""

pipe = Popen(psqlQuery(sql), stdout = PIPE)

schemaName = pipe.communicate()[0].strip()

pipe.stdout.close()

if sys.version_info[0] > 2:

schemaName=schemaName.decode()

fileOut.write("Running pre-import scripts...\n")

preImportScripts(schemaName)

fileOut.write("Pre-import step completed.\n")

cmd = [PG_RESTORE, "-a", "-Fc", "--disable-triggers", "-U", "postgres", "-d", "VCDB", results.backup_file]

ret = call(cmd, stdout=fileOut, stderr=fileError)

fileOut.write("Running post-import scripts...\n")

postImportScripts()

fileOut.write("Post-import step completed.\n")

fileOut.close()

fileError.close()

print ("Restore completed successfully.")

Bu script ‘e çalışmadan önce chmod 700 /tmp/restore_lin.py diyerek yetki vermeliyiz. Restore parameter dizilimi ise python /tmp/restore_lin.py -f tmp/vcsa_postgres_VCDB_12022020.bak şeklinde olmalı.

selamlar,