Published on May 21st, 2017 by peter

The background

I was seeking a free database to host the demo of a small project and after some disappointing trials elsewhere I rediscovered Heroku which I had great experience with in the past. However, this decision meant that I had to migrate my MySQL database to postgresql. I also decided to port the php backend to nodejs at the same time.

Installing PostgreSQL

The two seemingly most common way to install PostgreSQL on macOS are via homebrew or using Postgres.app. Initially I installed the app (pre-packaged binary with standard installer) as recommended by Heroku - my target deployment environment. However I used pgloader to migrate MySQL, which is very simple to install via homebrew (and very complex without). pgloader - naturally - has psql as its dependency, so I ended up with two simultaneous installation. They seem to work without clashes thus far, but I will have to be careful with upgrades!

References

Enable the CLI

If psql is installed as the Postgres.app, then CLI needs to be enabled separately.

sudo mkdir -p /etc/paths.d && echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp

Start a new terminal to verify that the changes were applied.

$ which psql
/Applications/Postgres.app/Contents/Versions/latest/bin/psql

Finally, check the configuration:

pg_config --configure

Creating self-signed certificate

Applications typically communicate with PostgreSQL via SSL, which require appropriate certificates. See https://www.postgresql.org/docs/9.6/static/ssl-tcp.html and look for the header 18.9.3. Creating a Self-signed Certificate for instructions which is copied below (between the horizontal lines) as reference.


openssl req -new -text -out server.req

Fill out the information that openssl asks for. Make sure you enter the local host name as "Common Name"; the challenge password can be left blank. The program will generate a key that is passphrase protected; it will not accept a passphrase that is less than four characters long. To remove the passphrase (as you must if you want automatic start-up of the server), run the commands:

openssl rsa -in privkey.pem -out server.key
rm privkey.pem

Enter the old passphrase to unlock the existing key. Now do:

openssl req -x509 -in server.req -text -key server.key -out server.crt

to turn the certificate into a self-signed certificate and to copy the key and certificate to where the server will look for them. Finally do:

chmod og-rwx server.key

because the server will reject the file if its permissions are more liberal than this. For more details on how to create your server private key and certificate, refer to the OpenSSL documentation.

A self-signed certificate can be used for testing, but a certificate signed by a certificate authority (CA) (either one of the global CAs or a local one) should be used in production so that clients can verify the server's identity. If all the clients are local to the organisation, using a local CA is recommended.


Migrate MySQL to PostgreSQL

Read https://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL before proceeding. I will present two options. Both produced similar results, in fact I wish I did a diff on them, but I didn't. Anyhow, I ended up rolling with option 1.

Option 1: pgloader

pgloader appears to be the go to tool for the task, however its installation - according to the repo - is pretty complex. It fails to mention that it can be installed via homebrew too, which in contrast takes care of everything, removing huge amounts of pain form this process.

$ brew search pgloader
    If you meant "pgloader" specifically:
    It was migrated from caskroom/cask to homebrew/core.
    You can access it again by running:
    brew tap homebrew/core
$ brew tap homebrew/core
$ brew install pgloader
$ pgloader --version
    pgloader version "3.3.2"
    compiled with SBCL 1.3.14
Migrate data

To migrate a MySQL database to psql, simply create a target psql database and give the source and target db details as arguments to pgloader. The output will be something similar to this:

$ createdb psql_db_name
$ pgloader mysql://username:password@host/mysql_db_name postgresql:///psql_db_name

    2017-05-02T06:01:37.015000+01:00 LOG Main logs in '/private/tmp/pgloader/pgloader.log'
    2017-05-02T06:01:37.017000+01:00 LOG Data errors in '/private/tmp/pgloader/'
                    table name       read   imported     errors      total time       read      write
    --------------------------  ---------  ---------  ---------  --------------  ---------  ---------
               fetch meta data          2          2          0          0.162s
              Create SQL Types          0          0          0          0.003s
                 Create tables          2          2          0          0.022s
                Set Table OIDs          1          1          0          0.000s
    --------------------------  ---------  ---------  ---------  --------------  ---------  ---------
    "your_db_table_name_here"         215        215          0          0.015s     0.014s    0.015s
    --------------------------  ---------  ---------  ---------  --------------  ---------  ---------
       COPY Threads Completion          3          3          0          0.086s
        Index Build Completion          1          1          0          0.165s
                Create Indexes          1          1          0          0.007s
               Reset Sequences          0          1          0          0.053s
                  Primary Keys          1          1          0          0.002s
           Create Foreign Keys          0          0          0          0.000s
               Create Triggers          0          0          0          0.005s
              Install Comments          0          0          0          0.000s
    --------------------------  ---------  ---------  ---------  --------------  ---------  ---------
             Total import time        215        215          0          0.839s     0.014s    0.015s

Option 2: nmig

I also did a test migration using a nodejs library nmig.

  1. clone https://github.com/AnatolyUss/nmig
  2. install nmig → npm install
  3. update config.json with particulars
  4. run node nmig.js, which will present the below screen.

Note the input for "proceed" must be a capital Y in order for the program to proceed.


    /\_  |\  /\/\ /\___
    |  \ | |\ | | | __
    | |\\| || | | | \_ \
    | | \| || | | |__/ |
    \|   \/ /_|/______/

    NMIG - the database migration tool
    Copyright (C) 2016 - present, Anatoly Khaytovich <anatolyuss@gmail.com>

    --[boot] Configuration has been just loaded.
    --[boot] NMIG is ready to start.
    --[boot] Proceed? [Y/n]
Y

Deploy to Heroku

It's best to follow the Heroku docs. I only deviate from it for the items detailed below.

Troubleshooting

Dynamic port assignment

Running nodejs apps on Heroku - according to their tutorial - require us to explicitly bind the app to port 5000, but on Heroku itself the port is dynamically assigned and made available to the app via process.env.PORT. To make the app portable and work both locally and on Heroku, we can define the local port in a CONFIG file and fetch whichever is available. Thanks for the solution to vibesphere.com.

#!/usr/bin/env node
// when on Heroku, port will be exported to an environment variable
// and available as process.env.PORT
var port = process.env.PORT || CONFIG.port;
app.listen(port);
Deploying branch X as Heroku master

As I ported this project from php to nodejs I started a new node branch. Also note that my origin remote is the bitbucket / github repo. Heroku remote is named heroku. So to deploy the node branch as the master to Heroku, I can push as

git push heroku node:master
Running nodejs server on top of Apache

Heroku's CLI provides a local environment with heroku local, but I run Apache locally for a broad variety of reason (and start it on boot), so I wanted to make this work seamlessly and without needing to stop and start Apache while I work on nodejs / Heroku targeted projects.

NB: This proxy solution is only for local development and creates dev-prod disparity!

Enable Apache proxy module

In /etc/apache2/httpd.conf

  1. uncomment LoadModule proxy_module libexec/apache2/mod_proxy.so
  2. uncomment LoadModule proxy_http_module libexec/apache2/mod_proxy_http.so
Create dedicated vhost

In /etc/apache2/extra/httpd-vhosts.conf create a new vhost entry

<VirtualHost *:80>
    ServerAdmin admin@site.com
    ServerName example.com
    ServerAlias www.example.com

    ProxyRequests off

    <Proxy *>
        Order deny,allow
        Allow from all
    </Proxy>

    <Location ></Location>
        ProxyPass http://localhost:1337/
        ProxyPassReverse http://localhost:1337/
    </Location>
</VirtualHost>

Comments, feedback, ideas are welcome. Ping me on Twitter