convert-linux-instance-to-psql-server.py

## this script is to setup master postgres database
import random, string, sys, socket, os, json, re
from subprocess import STDOUT, check_call, check_output

try:
    xrange
except NameError:
    xrange = range

def findAndReplace(filePath, findLine, replaceLine):
    s = open(filePath).read()
    s = s.replace(findLine, replaceLine)
    f = open(filePath, 'w')
    f.write(s)
    f.close()

def addLineInFile(filePath, textToInsertInFile):
    with open(filePath, "r+") as file:
        for line in file.readlines():
            if textToInsertInFile in line:
               break
        else:
            file.write(textToInsertInFile+'\n')

def runBashCommand(command):
    command = command.strip()
    exit_code = os.system(command)
    if(exit_code != 0):
        os.system('printbanner '+ str(exit_code))
        os.system('printbanner exited')
        sys.exit()

def getRamSize():
    return int((os.sysconf('SC_PAGE_SIZE') * os.sysconf('SC_PHYS_PAGES'))/(1024.**3)*1024)

def getPostgresConfigFile(url):
    file_path = check_output('psql '+ url +' -t -P format=unaligned  -c "show config_file;"', shell=True)
    if type(file_path)() == b'':
        return file_path.strip().decode("utf-8")

## Creating dj url here
DATABASE = "postgres"
PORT = "5432"
HOST = socket.gethostbyname(socket.gethostname())
USER = "".join( [random.choice(string.ascii_lowercase) for i in xrange(14)] )
PASSWORD = "".join( [random.choice(string.ascii_lowercase + string.digits) for i in xrange(64)] )
NAME = "".join( [random.choice(string.ascii_lowercase) for i in xrange(14)] )
dj_db_url = DATABASE+"://"+USER+":"+PASSWORD+"@"+HOST+":"+PORT+"/"+NAME


## Installing prosgres
runBashCommand('sudo apt-get install wget ca-certificates')
runBashCommand('wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -')
runBashCommand("sudo sh -c 'echo \"deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main\" >> /etc/apt/sources.list.d/pgdg.list'")

runBashCommand('sudo apt-get update -qqy')
runBashCommand('sudo apt-get install postgresql-11 -qqy')

## Creating new user and database and giving permissions
runBashCommand('sudo -u postgres psql -c "CREATE ROLE replicate WITH REPLICATION LOGIN;"')
runBashCommand('''sudo -u postgres psql -c "CREATE USER {} REPLICATION LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD '{}';"'''.format(USER, PASSWORD))
runBashCommand('sudo -u postgres psql -c "ALTER ROLE '+ USER +' SUPERUSER;"')
runBashCommand('sudo -u postgres createdb -O ' + USER + ' '+ NAME)


## change this if DB version changes
config_file_path = "/etc/postgresql/11/main/postgresql.conf" # getPostgresConfigFile(dj_db_url)
hg_hba_file_path = "/etc/postgresql/11/main/pg_hba.conf"

ram = getRamSize()

## Allow All IP
textToInsertInFile =  "host    all             all             0.0.0.0/0               md5\n"
filePath = hg_hba_file_path
addLineInFile(filePath, textToInsertInFile)

## Listen Address
textToFind = "#listen_addresses = 'localhost'"
textToReplace = "listen_addresses = 'localhost,"+ HOST +"'"
filePath = config_file_path
findAndReplace(config_file_path, textToFind, textToReplace)

## Time Zone
textToFind = "timezone = 'Etc/UTC'"
textToReplace = "timezone = 'MST7MDT'"
findAndReplace(config_file_path, textToFind, textToReplace)

## Cache Size
textToFind = "#effective_cache_size = 4GB"
textToReplace = "effective_cache_size = " + str(int(ram*(0.75))) + "MB"
findAndReplace(config_file_path, textToFind, textToReplace)

## shared_buffers is 1/4th of ram is recommended
textToFind = "shared_buffers = 128MB"
textToReplace = "shared_buffers = "+ str(int(ram/4)) +"MB"
findAndReplace(config_file_path, textToFind, textToReplace)

## Random Page Cost
textToFind = "#random_page_cost = 4.0"
textToReplace = "random_page_cost = 4.0"
findAndReplace(config_file_path, textToFind, textToReplace)

# 1-1000; 0 disables prefetching
textToFind = "#effective_io_concurrency = 1"
textToReplace = "effective_io_concurrency = 2"
findAndReplace(config_file_path, textToFind, textToReplace)

# 2621kB per GB is recommended
textToFind = "#work_mem = 4MB"
textToReplace = "work_mem = "+ str(int((ram/1024)*2621)) +"kB"
findAndReplace(config_file_path, textToFind, textToReplace)

runBashCommand('sudo service postgresql restart')
runBashCommand('tail /var/log/postgresql/postgresql-11-main.log')

data = {}
data["URL"] = dj_db_url
data["HOST"] = HOST
data["USER"] = USER
data["PASSWORD"] = PASSWORD
data["NAME"] = NAME


## Writing credentials to file
with open('database_url.json', 'w') as outfile:
    json.dump(data, outfile)

print(dj_db_url)