Getting PosgreSQL and Rails to work together - StanfordBioinformatics/pulsar_lims GitHub Wiki
Overview
This discussion covers installing PostgreSQL locally and setting up your Rails app to use it. This is useful for running your app locally for testing and development. In production, you shouldn't need to worry about installation and setup of your database; for example, Heroku's Postgres add-on handles all of these details for you.
Some of the instructions below are Mac OS X specific since that is the platform I'm using.
Installing PostgreSQL
Download the installer for your platform from EnterpriseDB. Then run the automated DMG installer.
Update your .bash_profile
I installed PostgreSQL 10 today (May 21, 2018) and the installer put it in /Library/PostgreSQL/10. In this directory is a file called pg_env.sh that contains these contents:
#!/bin/sh
# The script sets environment variables helpful for PostgreSQL
export PATH=/Library/PostgreSQL/10/bin:$PATH
export PGDATA=/Library/PostgreSQL/10/data
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5432
export PGLOCALEDIR=/Library/PostgreSQL/10/share/locale
export MANPATH=$MANPATH:/Library/PostgreSQL/10/share/man
You should source this file in your .bash_profile by adding this line:
source /Library/PostgreSQL/10/pg_env.sh
Now, so that your current terminal environment picks up the new changes, source your .bash_profile (or open a new terminal window/tab):
source ~/.bash_profile
Installing the pg gem
Before going further, check that you are not using the system-wide ruby (the default that came with your Mac). If so, you will likely not be successful in getting the pg gem to talk to your new PostgreSQL installation. That is because the system ruby installation is hard-wired to talk the system wide library file libpq.dylib at /usr/lib/libpq.dylib, which likely will differ from the corresponding library's version in PostgreSQL. On my system, the two versions are different:
>>> readlink /Library/PostgreSQL/10/lib/libpq.dylib
libpq.5.10.dylib
>>> readlink /usr/lib/libpq.dylib
libpq.5.6.dylib
Even though pg will likely install successfully, it won't work properly when you use a Rails command that needs PostgreSQL, such as running a migration. You'd see errors that include the text "rails pg fixup problem" and probably some missing symbols like "symbol '_PQsslAttribute' not found", for example. The solution is to install your own version of ruby. I suggest using rbenv. Install rbenv and then whichever version of Ruby that you need. It's best to install a version that is different from the system installation of Ruby so there is never any confusion - when I installed my own version that was the same as the system Ruby, I noticed that Rails still used the system installation of Ruby and I'm not really sure why. Here is an example of how you can install your Ruby version of choice using rbenv:
# Install Ruby 2.3.7
>>> rbenv install 2.3.7
# List all Ruby versions
>>> rbenv versions
* system
2.3.7 (set by RBENV_VERSION environment variable)
Notice the asterisk next to the system ruby; that means it's set as the current ruby. You can switch to 2.3.7 as follows:
>>> rbenv shell 2.3.7
Since you have a new Ruby installation, you'll need to reinstall all the necessary gems for your Rails application. Change into your application directory where your Gemfile is located. Add to your Gemfile the version of the pg gem that you need if you haven't already done that yet, then run bundle install.
Updating your database configuration in Rails
From within your application's root directory, open up the database file at config/database.yaml. It is good practice to keep your production and development environments as similar as possible, so I recommend using PostgreSQL for all three databases: development, test, and production. Make sure that this file looks something like this:
development:
host: localhost
adapter: postgresql
database: myapp_dev
username: myapp
password: secret
test:
host: localhost
adapter: postgresql
database: myapp_test
username: myapp
password: secret
production:
adapter: postgresql
database: myapp_prod
username: myapp
password: secret
I've given all databases the same password, but that is not an important matter here. It is important that I've added host: localhost to both the test and production databases, otherwise, running your app locally will fail.
Next, you need to create a PostgreSQL user role for the username you chose above, which in this case is "myapp".
Creating a PostgreSQL user role
Switch over to your postgres user and then launch psql:
>>> su - postgres
>>> psql
Enter the following line in the psql interactive terminal:
create role myapp with createdb login password 'secret';
Next, create the databases specified in database.yaml.
Creating the databases
rake db:setup # Create the database, load the schema, and initialize with the seed data
As long as you don't see any errors indicating a connection issue to the PostgreSQL server, then you probably have a successful integration of PostgreSQL with your Rails app.