Adding a local PostgreSQL database

Back To Blog

In this tutorial we will add a PostgreSQL database in the db/ folder working in an opeNode instance. It is worth noting that it will consume at least 20 MB extra memory, so if you use for instance the smallest instance plan, it is recommended to increase your plan. Also, we use use an alpine-based docker image. You can enable PostgreSQL by adding the following instructions in your Dockerfile:

RUN apk add postgresql
RUN echo 'mkdir -p db' >> /usr/bin/start.sh

RUN echo 'mkdir -p /run/postgresql' >> /usr/bin/start.sh
RUN echo 'chmod -R 777 /run' >> /usr/bin/start.sh
RUN echo 'chmod -R 700 /opt/app/db' >> /usr/bin/start.sh
RUN echo 'chown -R postgres /opt/app/db' >> /usr/bin/start.sh
RUN echo 'su -c "initdb /opt/app/db/ || true" postgres' >> /usr/bin/start.sh
RUN echo 'su -c "postgres -D /opt/app/db/ &" postgres' >> /usr/bin/start.sh
RUN echo 'sleep 5' >> /usr/bin/start.sh

This by default will set up a user postgres having full access without password. Further, make sure to define a storage area so that it does not get deleted on updates (persistent):

openode add-storage-area db/

And also make sure to ignore that folder by creating an .openodeignore file with:

db/

The Complete Dockerfile we used:

FROM node:10-alpine

WORKDIR /opt/app

ENV PORT=80

RUN apk add postgresql
RUN echo 'mkdir -p db' >> /usr/bin/start.sh

RUN echo 'mkdir -p /run/postgresql' >> /usr/bin/start.sh
RUN echo 'chmod -R 777 /run' >> /usr/bin/start.sh
RUN echo 'chmod -R 700 /opt/app/db' >> /usr/bin/start.sh
RUN echo 'chown -R postgres /opt/app/db' >> /usr/bin/start.sh
RUN echo 'su -c "initdb /opt/app/db/ || true" postgres' >> /usr/bin/start.sh
RUN echo 'su -c "postgres -D /opt/app/db/ &" postgres' >> /usr/bin/start.sh
RUN echo 'sleep 5' >> /usr/bin/start.sh

RUN echo 'npm install --production' >> /usr/bin/start.sh

# npm start, make sure to have a start attribute in "scripts" in package.json
RUN echo 'npm start' >> /usr/bin/start.sh

We tested it with a simple Node.js application, please see this repository, the PostgreSQL code is located in server.js (Notice that it just creates a database and basic table).


Added on Tue Dec 11 2018 02:34:22 GMT+0000 (Coordinated Universal Time)