How to self-host Postgres Database on Linux

The Smartbug — Thu, 07 Nov 2024 21:48:14 +0000

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

How to self-host MongoDB on a Raspberry Pi 4

The Smartbug — Wed, 06 Nov 2024 02:33:16 +0000

MongoDB Server

MongoDB is an open-source, NoSQL database management system designed to handle large volumes of data and provide high performance, scalability, and flexibility. Introduced in 2009 by Dwight Merriman, Eliot Horowitz, and Kevin Ryan, the co-founders of the company 10gen (later renamed MongoDB, Inc.), MongoDB emerged as a solution to the limitations and challenges posed by traditional relational databases.

Motivation behind

The primary motivation behind MongoDB's creation was the need to manage and store the vast amounts of unstructured and semi-structured data generated by modern applications. Traditional relational databases, which use fixed schemas and tables, struggled to efficiently handle the diverse and evolving data structures typical of contemporary web applications, big data, and real-time analytics.

MongoDB uses a document-oriented model, storing data in flexible, JSON-like documents. This schema-less design allows developers to easily adapt to changing data requirements without costly schema migrations. Additionally, MongoDB's architecture supports horizontal scaling through sharding, enabling it to manage large-scale, distributed data across multiple servers seamlessly.

By providing a more dynamic and scalable approach to data management, MongoDB has become a popular choice for organizations looking to build applications that require real-time processing, complex querying, and rapid iteration.

Self-host MongoDB setup

To setup instance of the MongoDB server on a Raspi4, we can use the following docker compose file

version: '2'

services:
  mongodb:

    # For Development / Testing
    image: mongodb/mongodb-community-server:4.4.3-ubuntu2004

    # For Production Deployment
    # image: mongodb/mongodb-enterprise-server:4.4.3-ubuntu2004

    environment:
      MONGODB_INITDB_ROOT_USERNAME: <DB_USERNAME>
      MONGODB_INITDB_ROOT_PASSWORD: <DB_PASSWORD>
    restart: unless-stopped
    ports:
      - 27017:27017
    volumes:
      - mongodb_data:/data/db

volumes:
  mongodb_data:
    driver: local

Note

It is extremely important to note that the latest versions of the docker images will NOT work on the Raspberry Pi 4. It is because the architecture of Raspi is arm64/v8 and there are many new additions in the architecute arm64/v8.2 that are absent on the Raspberry pi.
More details here and here.

Save the above docker-compose config in some file (say mongo-docker-compose.yaml), and run the following cmd

docker-compose -d mongo-docker-compose.yaml up

The -d flag instructs the command to run in a detached mode, in background.
After testing many different Docker images, and configurations, finally the above docker-compose file has been prepared. This has been proven to work on raspberry pi 4.

Conclusion

You can use your own self-hosted instance of MongoDB 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

Original Article published at The SmartBug

Forem: The Smartbug

How to self-host Postgres Database on Linux

PostgresDB

Motivation behind

Self-host Postgres Setup

Add SSL support for PostgresDB

Method : 1

Method : 2

Method : 3

Conclusion

How to self-host MongoDB on a Raspberry Pi 4

MongoDB Server

Motivation behind

Self-host MongoDB setup

Conclusion