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,
| Ümit GÖRÜR Profesyonel Hizmetler | Professional Services |
Yıldız Teknik Üniversitesi Davutpaşa Kampüsü Teknoloji Geliştirme Bölgesi Çiftehavuzlar Mahallesi Eski Londra Asfaltı Caddesi No: 151/1E D2 Blok Ofis No: 1B-10 Esenler - İstanbul, TÜRKİYE T: +90 (212) 393 00 90 | F: +90 (212) 393 00 91 www.aktekbilisim.com | |