Photo by Kevin Ku on Unsplash

The purpose of this article is to highlight certain configurations that you might need in case you want to run your own postgresql. There is a slight difference between running it locally and deploying postgres in production. Therefore, I decided to write down the configurations that one needs to change in order to get it up and running.

I will attach some code towards the end

Before we get started, here is addressing some questions you may have:

  1. I want to run postgres on Docker (or compose), will this article be helpful? Yes.
  2. If I want to run Multi-master main-main setup (Stolon or something similar), will this article still help me? Yes! Terms and Conditions apply 😛

Why would I choose to run my own Postgres?

You can skip this part and move to code part of the blog if this does not interest you.

There are plenty of managed RDBMS on the cloud — a sensible choice for 99% of use cases except when you want to:

  1. Save cost
  2. Have more control over DB configs
  3. Change the configuration of the VM on which DB is running

I was working on a project that had a simpler requirement — to deal with very low but constant load of requests. So, I decided to run postgres on a simple EC2 instance. You are right to be alarmed at this point. Because there are very nice things that RDS gives me that I am doing away with, such as:

  1. Connection configurations — doesn’t RDS give you sensible defaults? Now you have to configure all of this yourself 😠
  2. Wouldn’t I lose data if the EC2 goes down?
  3. Backups
  4. Would you be able to run main-main or read-replicas etc?

So let us address each of these problems one by one.

Code:

  1. What about connection configurations — doesn’t RDS give you sensible defaults? Now you have to configure all of this yourself 😠

Yes. Now you have to set this up yourself 😤

  1. Make postgres listen to all IPs (and not just local — in case you need to connect from a remote machine, which is the most probable case).

For this, you need to make two changes. On postgres.conf— which is usually at /etc/postgresql/16/main/postgresql.conf

  1. Make listen_address = ‘*’ (which is commented out — so you can append to this file).
  2. Change pg_hba.conf like so
host    all             all             127.0.0.1/32            md5 (or sha-256)

# to

host    all             all             0.0.0.0/0            md5 (or sha-256)

You can do both of these through the following linux commands:

sudo sed -i  "s/127.0.0.1\/32/0.0.0.0\/0/" /etc/postgresql/16/main/pg_hba.conf
sudo echo "listen_addresses = '*'" >> /etc/postgresql/16/main/postgresql.conf

Moving on to the next issue…

2. Wouldn’t I lose data if the EC2 goes down? The obvious solution is that you move the data from your EC2 node into an external volume.

If you think I will use a Network File System such as EFS like I did, good job. Unfortunately, we are both wrong. EFS is very slow (Faster than a S3 write, slower than disk write). Therefore, the obvious choice is EBS.

Now, typically, EBS is allowed to be attached to one node only. Therefore, you need EBS in multi-attach mode (This is a new feature of EBS 😄). You need this because if your VM goes down, even after re-attaching the EBS, data cannot be read by the new postgres server!

Attaching an EBS to EC2 can be done through the mount command. This is how I do it:

export instanceId=$(curl http://169.254.169.254/latest/meta-data/instance-id)
aws ec2 attach-volume --volume-id ${aws_ebs_volume.postgres-ebs-volume.id} --instance-id $instanceId --device /dev/sdf --region ${var.current_region}
sudo mkdir /dev/sdf
sudo mount -t xfs -o nouuid /dev/nvme1n1 /dev/sdf
# Note - it might be /dev/nvme1n2 or nvme1n0 for you (Good idea to use *
# and test it out here).

I also put the EC2 behind an auto-scaling group (in case the EC2 decides to die randomly).

Caution: If the size of your Auto-scaling group is > 1 (you have decided to scale horizontally) — you cannot use this!

Since multiple postgres writing to same disk without knowing other postgres instances would cause data race problems, you need to move to a fork of postgres that supports main-main or main-replica mode.

Now you also have to make postgres write to the data directory you mounted. For this, you can use this script:

sudo sed -i "s#/var/lib/postgresql/16/main#/dev/sdf/#" /etc/postgresql/16/main/postgresql.conf
sudo chown -R postgres:postgres /dev/sdf

if [ -z "$(ls -A /dev/sdf)" ]; then
 echo 'Initializing Postgres to /dev/sdf'
 echo "sudo -u postgres /usr/lib/postgresql/16/bin/initdb -D /dev/sdf"
 sudo -u postgres /usr/lib/postgresql/16/bin/initdb -D /dev/sdf
fi

This checks if /dev/sdfis empty, and if it is, initializes it using postgres initdb module.

3. Backups:

There are two ways to do this:

  1. Use pg_dump and send the content to an object storage (S3 or Cloud Bucket). This can be set up through a cron job.
  2. Use cloud-specific backups. EBS provides EBS snapshots. Additionally, AWS has a aws backup service that does this automatically for you.

I prefer the second option because restoring to an EBS volume is a one-click operation. Postgres can continue working without a change.

pg_dump is something I need to manually upload back into postgres.

4. Would you be able to run main-main or read-replicas etc?

No.

Wait, given the current setup — the answer is no. However, you can move to any fork of postgres and you will need to do all of the above changes. There might be more changes you need to do — but the configs I have mentioned are a good starting point

That is it, once you make these changes, your postgres is ready to accept traffic from other servers. You can also sleep soundly knowing that if your DB node does go down, at least your data is safe and you can start where you left off.