Published on May 21st, 2017 by peter
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.
Database: MySQL 14.14 (Dist 5.7.18) → PostgreSQL 9.6.2
Backend: PHP 5.x → nodejs 6.x
Dev environment: macOS Sierra
Host environment: Ubuntu Linux 14.x
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!
- Postgres.app docs
- Heroku PostgreSQL local setup including installation locations and uninstallation instructions
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:
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
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.
- clone https://github.com/AnatolyUss/nmig
- install nmig →
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 <firstname.lastname@example.org> --[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.
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
LoadModule proxy_module libexec/apache2/mod_proxy.so
LoadModule proxy_http_module libexec/apache2/mod_proxy_http.so
Create dedicated vhost
/etc/apache2/extra/httpd-vhosts.conf create a new vhost entry
<VirtualHost *:80> ServerAdmin email@example.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