La migrazione di un database PostgreSQL da Heroku a DigitalOcean permette di ridurre i costi server sfruttando i Database Cluster di Digital Ocean. In questa guida si presuppone l’obiettivo di ottenere un unico cluster contenente diversi database di diversi applicativi su Heroku.
SCARICARE UN BACKUP DEL DATABASE DA HEROKU
Per prima cosa è necessario scaricare un backup del database PostgreSQL da Heroku in un file locale .sql. Il download può essere fatto con qualsiasi client SQL (esempio TablePlus).
CREARE UN NUOVO DATABASE E UN NUOVO UTENTE SU DIGITAL OCEAN
Dal pannello di Digital Ocean creare un nuovo database e un nuovo utente all’interno del cluster. Assicurarsi che utente e database abbiano lo stesso nome (esempio: se il progetto si chiama “Gestionale Masotti” allora chiamare sia il database che l’utente “gestionale-masotti”).
IMPORTARE IL DATABASE DI BACKUP SU DIGITAL OCEAN
Una volta creato il database e l’utente, connettersi con un client SQL ed importare il backup effettuato al primo step.
Al termine dell’import sarà necessario effettuare un reset di tutti gli indici auto-incrementali delle tabelle (in modo di ripristinare l’uso degli indici utilizzati dal database su Heroku). Per fare ciò in maniera automatica utilizzare il seguente script Python connettendosi al nuovo database su Digital Ocean:
# pip3 install psycopg2
import psycopg2
conn = psycopg2.connect(
host="CAMBIAMI",
database="CAMBIAMI",
user="CAMBIAMI",
password="CAMBIAMI",
port="CAMBIAMI",
sslmode="CAMBIAMI"
)
cur = conn.cursor()
query_table_names = "SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE';"
cur.execute(query_table_names)
table_names = cur.fetchall()
cur.close()
for table_name in table_names:
cur = conn.cursor()
try:
query_reset_id_counter = "SELECT setval('{}_id_seq', (SELECT MAX(id) + 1 FROM {}));".format(table_name[0], table_name[0])
cur.execute(query_reset_id_counter)
conn.commit()
print("Reset id counter for table {}".format(table_name[0]))
except Exception as e:
conn.rollback()
print(e)
finally:
cur.close()
conn.close()
AGGIUNTARE I PERMESSI UTENTE SUL CLUSTER
Di default un nuovo utente sul cluster ha tutti i permessi admin, e quindi la possibilità di accedere e modificare qualsiasi database del cluster. Per mettere il sistema in sicurezza sarebbe meglio fare in modo che ad ogni utente sia permesso accedere e modificare solo il database con il suo stesso nome. Per fare ciò in maniera automatica utilizzare il seguente script Python connettendosi al database di default con utente di default su Digital Ocean:
# pip3 install psycopg2
import psycopg2
conn = psycopg2.connect(
user = 'CAMBIAMI',
password = 'CAMBIAMI',
host = 'CAMBIAMI',
port = 'CAMBIAMI',
database = 'CAMBIAMI',
sslmode = 'CAMBIAMI'
)
excluded_db_names = ['postgres', 'template0', 'template1', '_dodb', 'defaultdb']
cur = conn.cursor()
query_database_names = "SELECT datname FROM pg_database;"
cur.execute(query_database_names)
database_names = cur.fetchall()
cur.close()
for database_name in database_names:
db_name = database_name[0]
if db_name in excluded_db_names:
continue
# check user exists with same db name
query_user_exists = "SELECT 1 FROM pg_roles WHERE rolname='{}';".format(db_name)
cur = conn.cursor()
cur.execute(query_user_exists)
user_exists = cur.fetchone()
cur.close()
if user_exists == None:
print("User {} does not exist".format(db_name))
continue
# revoke all privileges to user to all databases
for database_revoke_name in database_names:
db_revoke_name = database_revoke_name[0]
query_revoke_privileges = "REVOKE ALL PRIVILEGES ON DATABASE \"{}\" FROM \"{}\";".format(db_revoke_name, db_name)
cur = conn.cursor()
cur.execute(query_revoke_privileges)
conn.commit()
print("Revoked privileges for user {} to database {}".format(db_name, db_revoke_name))
cur.close()
# add privileges to user to only it's database
query_grant_privileges = "GRANT ALL PRIVILEGES ON DATABASE \"{}\" TO \"{}\";".format(db_name, db_name)
cur = conn.cursor()
cur.execute(query_grant_privileges)
conn.commit()
print("Granted privileges for user {} to database {}".format(db_name, db_name))
cur.close()
conn.close()
SOSTITUIRE LA CONNESSIONE SU HEROKU
Per concludere il tutto basta modificare la connessione al database su Heroku. Di solito questa è impostata tramite variabile d’ambiente “DATABASE_URL”. Per questo motivo bisogna eliminare il database PostgreSQL installato come risorsa e successivamente aggiungere la variabile d’ambiente con l’url di connessione a DigitalOcean.