Skip to content

Instantly share code, notes, and snippets.

@nathants nathants/pg-util.py
Last active Jun 18, 2018

Embed
What would you like to do?
#!/usr/bin/env python2.7
"""
chunked backup and restore utilities for postgres via s3 with
minimal local disk/ram requirements.
dependencies:
- pip install argh awscli
- have awscli creds configured
"""
from __future__ import print_function, absolute_import
import os
import uuid
import argh
import subprocess
import sys
import time
def check_call(*a):
cmd = ' '.join(map(str, a))
print('run:', cmd, file=sys.stderr)
subprocess.check_call(cmd, shell=True, executable='/bin/bash', stderr=subprocess.STDOUT)
def check_output(*a):
cmd = ' '.join(map(str, a))
print('run:', cmd, file=sys.stderr)
return subprocess.check_output(cmd, shell=True, executable='/bin/bash').strip()
def backup(mb_per_file=100.0, s3_path='s3://bucket/prefix/...'):
"""pg_dumpall | python2.7 pg_util.py backup -l 10000 -s s3://bucket/prefix/todays_backup"""
start = time.time()
size = 0
tmp_path = '/tmp/temp_%s' % uuid.uuid4()
f = open(tmp_path, 'w')
i = 1
for line in sys.stdin:
f.write(line)
size += len(line)
if size / 1024.0 / 1024.0 > mb_per_file:
f.close()
check_call('aws s3 cp', tmp_path, os.path.join(s3_path, '%020d' % i))
f = open(tmp_path, 'w')
size = 0
i += 1
f.close()
check_call('aws s3 cp', tmp_path, os.path.join(s3_path, '%020d' % i))
check_call('rm -f', tmp_path)
print('duration minutes:', int((time.time() - start) / 60), file=sys.stderr)
def restore(s3_path='s3://bucket/prefix/...'):
"""python2.7 pg_util.py restore -s s3://bucket/prefix/todays_backup | psql -f - postgres"""
start = time.time()
tmp_path = '/tmp/temp_%s' % uuid.uuid4()
for key in check_output('aws s3 ls', s3_path.strip('/') + '/', "|awk '{print $NF}'").splitlines():
path = os.path.join(s3_path, key)
check_output('aws s3 cp', path, tmp_path)
with open(tmp_path) as f:
for line in f:
print(line.rstrip())
check_output('rm -f', tmp_path)
print('duration minutes:', int((time.time() - start) / 60), file=sys.stderr)
if __name__ == '__main__':
argh.dispatch_commands([backup, restore])
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.