Self-Host

How to self-host Postgres Database on Linux

Simar Mann Singh

Jul 13, 2024 — 2 min read

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

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
    # or set shared memory limit when deploy via swarm stack
    #volumes:
    #  - type: tmpfs
    #    target: /dev/shm
    #    tmpfs:
    #      size: 134217728 # 128*2^20 bytes = 128Mb
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: <VERY_SECURE_PASSWORD>

Add SSL support for PostgresDB

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

Method : 1

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 : 2

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
# ...