To backup only one scheme from multiple schema, you can use the following script:
- Create a dump of database with specific schema.
- Connect to server and create a new database.
- Restore the dump of the database to the newly created database.
from subprocess import PIPE,Popen
import subprocess
username = user_name
password = password
host = localhost
port= 5432
database = dummy_test
schema = 'public'
dest_file = /tmp/test.sql
#Dump of specific schema
try:
process = subprocess.Popen(
['pg_dump',
'--dbname=postgresql://{}:{}@{}:{}/{}'.format(username, password, host, port, database),
'-n', schema,
'-Fc',
'-f', dest_file,
'-v'],
stdout=subprocess.PIPE
)
output = process.communicate()[0]
except Exception as e:
print(e)
#connection to the database
try:
con = psycopg2.connect(dbname='postgres', port=port,
user=username, host=host,
password=password)
except Exception as e:
print(e)
new_database = 'test_new'
con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cur = con.cursor()
try:
cur.execute("DROP DATABASE {} ;".format(new_database))
except Exception as e:
print('DB does not exist, nothing to drop')
#create new database
cur.execute("CREATE DATABASE {} ;".format(new_database))
cur.execute("GRANT ALL PRIVILEGES ON DATABASE {} TO {} ;".format(new_database, username))
#restore backup dump into new database
try:
backup_file = dest_file
create_process = subprocess.Popen(
['pg_restore',
'--no-owner',
'--dbname=postgresql://{}:{}@{}:{}/{}'.format(username,
password,
host,
port, new_database),
'-v',
backup_file],
stdout=subprocess.PIPE
)
output = create_process.communicate()[0]
except Exception as e:
print(e)