Come migrare un database PostgreSQL da Heroku a Digital Ocean

Pubblicato da Gregorio Galante | ultima modifica Marzo 29, 2022

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.