This content originally appeared on DEV Community and was authored by The Smartbug
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
This content originally appeared on DEV Community and was authored by The Smartbug
The Smartbug | Sciencx (2024-11-07T21:48:14+00:00) How to self-host Postgres Database on Linux. Retrieved from https://www.scien.cx/2024/11/07/how-to-self-host-postgres-database-on-linux/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.