There are some cases where you might want to get your own copy of explain.depesz.com. You might not trust me with your explains. You might want to use it without internet access. Or you just want to play with it, and have total control over the site.

Installing, while obvious to me, and recently described by John Poole, is not always 100% clear. So, I decided to write about how to set it up, from scratch.

Basic assumption is simple: I have server, with Ubuntu Server (14.10), and nothing installed, except base system. There is user “explain", which has sudo privileges.

I made my own test case on VirtualBox, installed ubuntu, and while installing set “explain" as the first user to create. The only thing that I selected in the installer was openssh server, so I can actually connect to it.

Having such clean system, what do I do?

For starters I need some packages:

$ sudo apt-get install perl perl-modules libmojolicious-perl libmail-sender-perl libdate-simple-perl libemail-valid-perl libdbi-perl libdbd-pg-perl postgresql nginx git build-essential libexpat-dev

What are these for?

• perl – well, Perl programming language
• perl-modules – base modules for Perl. These should be bundled with perl package, but for some reason they are not.
• libmojolicious-perl – web framework I use
• libmail-sender-perl – library to send mails
• libdate-simple-perl – date handling for easier calculations
• libemail-valid-perl – simple email address validation
• libdbi-perl – DBI is database access library for Perl
• libdbd-pg-perl – PostgreSQL driver for DBI
• postgresql – we need database to store the plans
• nginx – while we could use the app without webserver (there is http-capable webserver within Mojolicious), that would require running the app using root account (so it could bind to low port), or use it on high port, but this makes for ugly urls
• git – to actually download explain.depesz.com
• build-essential – it will add many packages that are required for compiling needed perl modules
• libexpat-dev – headers for Expat library, needed for Perl's XML Parser

Now, we need database, so:

$ sudo -u postgres createuser explain
$ sudo -u postgres createdb -O explain explain

First line creates “explain" account in PostgreSQL, and the second creates database explain owned by explain user.

It is important to name the user and database the same as shell account explain webapp will be running from – it will make for simpler access.

At the moment we need to install one more thing – Pg::Explain library. We'll do it using Perls package manager (well, kinda) – cpan.

After running

$ sudo cpan

you'll be asked:

Would you like to configure as much as possible automatically?

Pressing enter should be sufficient.

After a while, you'll be presented with prompt:

cpan[1]>

At which point you type:

install Pg::Explain

This will take a while.

Afterwards, you can exit cpan shell (exit command) and verify that you have Pg::Explain installed:

explain@ubuntu:~$ perl -MPg::Explain -le 'print $Pg::Explain::VERSION'
0.69

Now we have all the prerequisites. Let's get explain.depesz.com code:

explain@ubuntu:~$ git clone https://gitlab.com/depesz/explain.depesz.com.git

First part of installation – load DB schema:

explain@ubuntu:~$ cd explain.depesz.com/sql/
explain@ubuntu:~/explain.depesz.com/sql$ psql -q -f create.sql
explain@ubuntu:~/explain.depesz.com/sql$ /bin/ls -1 patch-???.sql | sort | xargs -n1 psql -q -f

The third line will simply load all patch-???.sql files, in order.

After this, I can check if everything is in place:

explain@ubuntu:~$ psql -c '\d plans'
                       Table "public.plans"
    Column     |           Type           |       Modifiers        
---------------+--------------------------+------------------------
 id            | text                     | not null
 plan          | text                     | not null
 entered_on    | timestamp with time zone | not null default now()
 is_public     | boolean                  | not null default true
 is_anonymized | boolean                  | not null default false
 title         | text                     | 
 delete_key    | text                     | 
 is_deleted    | boolean                  | not null default false
 added_by      | text                     | 
Indexes:
    "plans_pkey" PRIMARY KEY, btree (id)
    "plan_paging" UNIQUE, btree (added_by, entered_on, id) WHERE is_deleted = false
Foreign-key constraints:
    "plans_added_by_fkey" FOREIGN KEY (added_by) REFERENCES users(username)

OK. Let's configure explain.depesz.com. The configuration file is ~/explain.depesz.com/explain.json.

In there you will need to change:

• database -> dsn: dbi:Pg:database=explain
• database -> username: explain
• secret: some long random string
• hypnotoad -> pid_file: /home/explain/explain.pid

You might also change mail_sender settings – these are for mails sent by contact form. But they depend heavily on environment, so I'll skip it for now.

With this in place, we can test run, by running:

explain@ubuntu:~$ cd ~/explain.depesz.com/
explain@ubuntu:~/explain.depesz.com$ morbo explain.pl

If everything went fine you should see at the bottom:

[Thu Nov 6 06:43:10 2014] [info] Listening at “http://*:3000".
Server available at http://127.0.0.1:3000.

And you can reach it from other machines using the server name (or ip) and port 3000. In my case the virtual server has ip 172.28.173.105, so I can reach the new installation using http://172.28.173.105:3000/.

The best way to check if everything really works (namely – database access) is to go to “history" page. If you get “Sorry, but no results was found in database for this page." – it means you're good, there just aren't any plans in yet. But if you get error – well, you'll have to fix whatever is broken.

You could leave it as is, but url with port number is not nice. Let's fix it.

First, kill (ctrl-c) morbo.

Then, start hypnotoad, like this:

EXPLAIN@ubuntu:~/EXPLAIN.depesz.com$ hypnotoad EXPLAIN.pl > /dev/NULL 2> /dev/NULL &

Hypnotoad is another server that can run Mojolicious apps – one that is dedicated to production environments.

With default config in explain.json, it will listen on port 12004 (port doesn't matter much, but we will need it for nginx config in a moment).

Now we'll change nginx (http server, listening on port 80) config. To do it:

EXPLAIN@ubuntu:~/EXPLAIN.depesz.com$ sudo vim /etc/nginx/sites-enabled/DEFAULT

In there, we can see section:

location / {
        # First attempt to serve request as file, then
        # as directory, then fall back to displaying a 404.
        try_files $uri $uri/ =404;
        # Uncomment to enable naxsi on this location
        # include /etc/nginx/naxsi.rules
}

What we need is to comment out try_files… line, and add proxy information. Like this:

location / {
        # First attempt to serve request as file, then
        # as directory, then fall back to displaying a 404.
        # try_files $uri $uri/ =404;
        # Uncomment to enable naxsi on this location
        # include /etc/nginx/naxsi.rules
        proxy_pass http://127.0.0.1:12004;
}

After the config is saved, we need to restart nginx:

EXPLAIN@ubuntu:~$ sudo service nginx restart

And at this moment, port specification is not needed, I can reach the site using http://172.28.173.105/ – or any name that resolves to IP of this server.

The last bit of work is making sure app will start after reboot.

This can be done by simply editing crontab of user explain:

EXPLAIN@ubuntu:~$ crontab -e

And adding line:

@reboot cd ~/explain.depesz.com; hypnotoad explain.pl 2> /dev/null > /dev/null &

Of course you could add init script to handle starting it, but I found that cronjob @reboot works just as well, at least for my usecases.

That was longer than I expected, but I think it is descriptive enough so that you could use it even if you're not on ubuntu, or not using nginx, or just use anything differently. Hope it helps.