How to self-host Postgres Database on Linux

Postgres is a performant SQL Database, and packs a ton of features. You can self-host Postgres on any linux machine including a Raspberry Pi. Read on to find out how.

PostgresDB

PostgreSQL, commonly referred to as Postgres, is a powerful, open-source relational database management system (RDBMS) known for its robustness, extensibility, and standards compliance. It was introduced in 1986 by Michael Stonebraker and his team at the University of California, Berkeley, as part of the POSTGRES project, which aimed to address some of the limitations of existing database systems at the time.

Motivation behind

The primary motivation behind the creation of PostgreSQL was to extend the ideas of the Ingres database project, also led by Stonebraker, and to support a wider variety of data types and complex queries. The name "POSTGRES" originally stood for "POST Ingres," reflecting its goal of building upon the foundational concepts of its predecessor.

PostgreSQL was designed to support advanced data types, indexing techniques, and a rich set of features like transactional integrity, concurrency control, and complex querying capabilities. One of the core ideas was to provide a database system that could be easily extended by users, allowing them to define new data types, operators, and functions to suit their specific needs.

Since its initial release, PostgreSQL has evolved into a feature-rich and highly reliable RDBMS used by organizations worldwide. Its adherence to SQL standards, combined with its extensibility and strong community support, has made PostgreSQL a preferred choice for a wide range of applications, from simple web services to complex, data-intensive applications.

Self-host Postgres Setup

At the time of writing this blog post, the latest postgres version is 14. So, to setup a PostgreSQL instance on your machine, (without SSL), use the following docker compose file

version: '3.9'

services:

  db:
    image: postgres:14-alpine3.18
    restart: always
    # set shared memory limit when using docker-compose
    shm_size: 128mb
    ports:
      - <CUSTOM_PORT>:5432

    # You can set additional values
    #volumes:
    #  - type: tmpfs
    #    target: /dev/shm
    #    tmpfs:
    #      size: 134217728 # Calculated like this: 128*2^20 bytes = 128Mb

    environment:
      POSTGRES_USER: <DATABASE_USERNAME>
      POSTGRES_PASSWORD: <DATABASE_PASSWORD>

Add SSL support for PostgresDB

To enable the SSL certificate, we can take multiple routes and all are equally safe.

I personally prefer the first approach, someone else may like other approach, its subjective.

Method : 1

One way to setup SSL for the PostgresDB is to create /acquire SSL certificates and simply add them using the Environment variables. There are many certificate authorities like - DigiCert, GeoTrust, or perhaps GlobalSign from where one can purchase a secure SSL certificates. If you prefer free SSL certificates, you can also generate an SSL certificate signed by Let's Encrypt. Otherwise, you can use OpenSSL to create a self-signed certificate as well, but these are not recommended for production use. But for development, it works fine.

Once you have the certificates, simply use the environment variables POSTGRES_SSL_CERT_FILE and POSTGRES_SSL_KEY_FILE to load them while deploying the postgres instance using docker-compose as shown below

version: '3.9'

services:

  db:
    image: postgres:14-alpine3.18
    restart: always
    shm_size: 128mb
    ports:
      - <CUSTOM_PORT>:5432
    environment:
      POSTGRES_USER: <DATABASE_USERNAME>
      POSTGRES_PASSWORD: <DATABASE_PASSWORD>
      POSTGRES_SSL_CERT_FILE: /path/to/ssl/certificate.crt
      POSTGRES_SSL_KEY_FILE: /path/to/ssl/key.key

Method : 2

One of the simplest option is to use the self-signed SSL certs which every debian distribution includes. Meaning, your docker-compose config could look like this

version: "3.8"
services:
  postgres:
    command: >
      -c ssl=on 
      -c ssl_cert_file=/etc/ssl/certs/ssl-cert-snakeoil.pem 
      -c ssl_key_file=/etc/ssl/private/ssl-cert-snakeoil.key
# ...
# rest of the config
# ...

Method : 3

Another method can be to use the SSL certificates obtained from a renowned SSL certificate provider like DigiCert, a Certificate Authority for more than two decades now.

version: "3.8"
services:
  postgres:
    command: >
      -c ssl=on 
      -c ssl_cert_file=/path/to/certificate.pem 
      -c ssl_key_file=/path/to/privkey.key
# ...
# rest of the config
# ...

Conclusion

You can use your own self-hosted instance of PostgresDB without any restriction and allocate as much data as you like. It is an easy to use, and easy to setup Database. It only takes 5 minutes of your time if you follow this guide.

Feel free to post in the comments below if something is unclear or if you've any suggestion. I would be very glad to read your comments.

Also, any feedback is most welcome.

Feel free to checkout my other Blog post on Some commonly used Postgres commands

Original Article published at The SmartBug