Monday, May 26, 2014

Playing with nginx and postgresql

I've always used Apache as my web server (and now MS IIS :), but have wanted to play with nginx; I was looking for something to do with my Raspberry Pi, so I figured setting up nginx's postgresql module would be fun. I followed the instructions for OpenResty (a 'distribution' of nginx with a bunch of modules configured by default; not a lot of documentation, but enough to get started).

I was running raspbian (a debian derivative), so it was as simple as:
apt-get install libreadline-dev libncurses5-dev libpcre3-dev libssl-dev perl make libpq-dev
(notice libpq-dev is not in the docs, but it is needed for the postgresql module, which is not installed by default with openresty)

then download with wget (this is the latest version at the time)
wget http://openresty.org/download/ngx_openresty-1.5.12.1.tar.gz

Uncompress, configure, make and install
tar -xzf ngx_openresty-1.5.12.1.tar.gz
cd openresty-1.5.12.1
./configure --with-luajit --with-http_postgress_module && make && sudo make install

nginx gets installed into /usr/local/openresty/nginx , so we can edit the configuration file with:
sudo vim /usr/local/openresty/nginx/nginx.conf

(I was using vim; use your favorite text editor otherwise :)

And we get to the fun part ; in nginx, we use the server stanza to configure a server; inside the http section, we use upstream to specify how to connect to the database (db is just a name, which we'll use in our other directives)
upstream db {
    postgres_server 127.0.0.1 dbname=test user=test password=abc123;
}

And then inside the server section, we can define specific queries for certain locations; for example:
location /stores {
    rds_json          on;
    postgres_pass   db;
    postgres_query  "SELECT * FROM Stores";
}
Specifies that when the user visits /stores, we should return (as json) the result of executing SELECT * from Stores.

No comments:

Post a Comment