Create a postgres server using kubernetes and rancher

For a few month I’ve been using the Postgres server provided with my NAS, and it was fine, but since it reached its limit, let’s deploy a new database using Kubernetes on a Rancher cluster.

I won’t review how to create your K3S cluster and a Rancher server. For this part this was pretty well covered by NetworkChuck in this video:

The Goal

I’m starting to have quite a few project that uses databases one way or another. To keep everything tidy, I’ll need a reliable way to:

• Manage all the databases
• Ensure each project access the database with minimal privileges
• Backup data both locally and remotely

Ideally, you’ll want each project to have its own database instance, and have different instances for testing/beta/prod to ensure stability, but I don’t have enough compute space for this, and my overall throughput is low, so I’ll keep everything in the same instance.

Prerequisites

Before doing any work, what is already prepared:

• A working rancher instance
• A working cluster in the instance
• A NFS storage accessible by the instance

Preinstall

Prepare database secrets:

There is one thing you want: being able to set a secured password.

There is one thing you don’t want: storing this password in your source code

To avoid having to store the password in your deployment, let’s use a secrets manager, lucky for us rancher allow us to do exactly this:

1. Go in Cluster Explorer

2. And choose Secrets in the Storage Category

3. Create a new Secret
4. Select Opaque (Basically a key/value list)
   • Secret Name: postgres.config
   • POSTGRES_PASSWORD = your admin password
   • POSTGRES_USER = Your admin username (default is postgres)

Why using a secret and not write this in a file:

• Having the secret stored that way will allow you to publish the whole deployment setup without revealing any secrets
• It will make easier to update the password, meaning we will be able to rotate the passwords

Prepare the deployment

Before deploying, we need to prepare a few files.

In my case I have a NFS drive that handles the the storage and replication to a remote backup server (if you want some tips on this, just ask in the comment). But you can create the file in a specific directory in the server, or use AWS EBS or really anything supported by rancher and there is a lot.

Note that all the files and the YAML for the deployment will be available in my github.

postgres-master.conf

This file will contain all the postgres engine setup, I have a small cluster, and it will run have to share limited resources, so no big changes, I’ll keep the default values for everything and only add this to the default configuration:

# Replication
wal_level = replica
hot_standby = on
max_wal_senders = 10
max_replication_slots = 10
hot_standby_feedback = on

pg_hba.conf

This is the file that will allow users to connect to the database.

Ensure you have this line:

host all all all md5

Deployment

Everything is ready now, let’s create a new deployment:

1. Go in Workload-> Deployments
2. Click Create

3. Setup the general values:
   • Name: pgsql-master (I’ll come back to this on a later post and you’ll understand why)
   • Container Name: pgsql-master
   • Image: postgres:latest (will pull the latest postgres container from docker)
   • Ports (Add Port):
     • Service Type: Load Balancer
     • Name: postgres-master
     • Private container port: 5432 (postgres default port)
     • Protocol: TCP
     • Public Host Port: 15432 (it’s the port you will use to connect to the database)
   • Environment Variables (Add one):
     • Type: Secret
     • Secret: postgres.config (or whatever you entered for the secret name)
       This will create all the environment variables present in your secret
4. Setup the storage:
   • For this part you will have to choose the storage type best fitting for you, in my case: NFS
   • Mount Point:Sub path in volume
     • /var/lib/postgres/data:pgsql-writer/data
     • /etc/postgresql/postgresql.conf:pgsql-writer/postgresql-master.conf
     • /etc/postgresql/pg_hba.conf:pgsql-writer/pg_hba.conf
       

5. Create and voilà

What’s next?

You see that I created category for my projects, the next steps is now to use this database. So create a wiki deployment and make it accessible through internet.

Another part I want to explore: create read replicas for our master instance, that will automatically scale.

Create the services to handle the password rotation.

Resources:

• Rancher Documentation
• Docker Postgres Documentation