Before the Shadow Play

Tag: postgres

  • Necessary steps to get the GitLab Rails template app running on OS X

    The GitLab template is a great way to get started on a new Rails app – it sets up most of what you need simply by checking out the code, but there are a few things you need to do if you’re starting fresh on a
    new Mac.

    The template app assumes you’ll be using Postgres for your database. I recommend sticking with this; if you grow your app past the proof-of-concept step, you’re going to want it configured for a robust and responsive  database. I love SQlite, but it’s not that much harder to go ahead and get Postgres running, so let’s do that instead of downgrading the template.

    If you have Homebrew installed, skip down to the brew install postgres section. If you don’t, run the following command:

    /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

    Wait for a while for that to finish, and Homebrew will be installed. Once that’s done you can move on to installing and configuring Postgres:

    brew update
brew install postgres
initdb /usr/local/var/postgres -E utf8

    Now you need to configure your databases. The initdb above sets up your user ID as a root user to start, so you can now add the application’s user and databases. (Substitute an appropriate username for yourappdev).

    psql postgres
create role yourappdev with login password '...some password here...';
alter user yourappdev createdb;

    Now exit psql (^D will do) and log back in again as the yourappdev user to create the development and test databases for your Rails app. If you set a password, psql will prompt you for it. (If you forgot it, your root-level user created when you installed Postgres can still log in without a password at the moment.)

    psql postgres -U yourappdev
create database yourapp_development;
create database yourapp_test;

    Securing the production database

    You now need to create the database that you’ll run in production. Remember this is the one with the important user data in it that you don’t want to lose, so we want to create a whole separate username that will be the creator of the database and give it a good strong password that you record in your password manager. (If you don’t have a password manager, get one! It’s way safer than writing it down on a sticky and putting it in an envelope.)

    psql postgres
create role yourapp with login password 'a very strong password please';
alter user yourapp createdb;
^D
psql postgres -U yourapp
create database yourapp

    You’re now ready to work on your Rails app. When you want to run the production version, you’ll need to set the DATABASE_URL environment variable like this:

    DATABASE_URL=”postgres://yourapp:strongpassword@localhost/yourapp”

    Further deployment issues are beyond the scope of this post.

  • Postgres array_to_string() and array_agg() to decouple your interface

    Let’s say you’ve got a collection of job data for a set of users that looks like this, and you want to create a nice summary of it to be displayed, with a count of how many jobs are in each category for each user, all on one line.

     │id │ user_id │ job │ status    │
 ├───┼─────────┼─────┼───────────┤
 │ 1 │ 12      │ 1   │ Completed │
 │ 2 │ 12      │ 2   │ Cancelled │
 │ 3 │ 14      │ 3   │ Ready     │
 │ 5 │ 14      │ 4   │ Completed │
 │ 6 │ 14      │ 4   │ Completed │
 │ 7 │ 14      │ 4   │ Cancelled │
 ...

    Here’s  the report of summarized statuses of jobs for each user that you want.

    │ user_id │ summary                           │
├─────────┼───────────────────────────────────┤
│ 12      | 1 Cancelled, 1 Completed          │
│ 14      | 1 Cancelled, 2 Completed, 1 Ready │

    I’ll show you how it’s possible to provide this solely with a Postgres SELECT.

    (more…)

  • “PG::UnableToSend: SSL error: sslv3 alert bad record mac”: not Postgres, not SSL

    If you see this error in the build log for your Rails app (we were getting it during our Cucumber tests):

    PG::UnableToSend: SSL error: sslv3 alert bad record mac
    : ...SQL query...

    then what you have is probably not an SSL problem, and probably not a Postgres problem. You should not turn off the SSL connection to Postgres, because more than likely not SSL causing the problem, and it’s not (directly) Postgres either, so don’t disable SSL in Postgres.

    What it most likely is, especially if it’s intermittent, is Postgres dropping a connection and then Rails trying to reuse it. This is still not Postgres’s fault.

    In my particular case, it turned out my app was using Puma to serve the app, and when you’ve got Puma in there, you need to properly configure it to work with Postgres by setting up a connection pool, like this:

    workers Integer(ENV['WEB_CONCURRENCY'] || 2)
threads_count = Integer(ENV['MAX_THREADS'] || 1)
threads threads_count, threads_count

preload_app!

rackup DefaultRackup
port ENV['PORT'] || 3000
environment ENV['RACK_ENV'] || 'development'

on_worker_boot do
  # Worker specific setup for Rails 4.1+
  ActiveRecord::Base.establish_connection
end

on_worker_shutdown do
  ActiveRecord::Base.connection.disconnect!
end

    This is a minimal configuration; we’re probably going to want to add more workers later. The important part is the on_worker_boot block. This tells ActiveRecord that it’s time for a new connection pool for this worker. I’ve cut the number of threads to one, as connections cannot be shared across threads.

    Once I had this in place, our build passed and all seems good. We may have to add some more workers once we go to production, but that’s a simple matter of updating the Ansible playbook to bump WEB_CONCURRENCY to an acceptable level.

    Why didn’t this show up until today? Probably because we hadn’t gotten our application’s test suite up to the point that we used up all our connections and had some drop on us. We’re still early on in our development and are just starting to hit the database a fair amount.

    I’m posting this because I searched for hours today for a solution that wasn’t just “turn off your security and it’ll be fine” – which wouldn’t have actually fixed the issue anyway.

    [Edited 11/24/15 to add on_worker_shutdown block.]