Changing your Forem Postgres database for an RDS Postgres Server

While it is very cost effective to have your Postgres database residing on the same server as your application there are a few drawbacks.

If you were to horizontally scale by adding more of the same servers via a cloud load balancer, we will end having two databases and this will lead to a broken experience and data loss with data being written randomly between the two servers.

If we want to connect the database to Business Intelligence (BI) tool like PowerBi its going to painful to establish a connection since we'll have to do some work to expose the ports on the server and container.

We also have to devise our own database backup strategy. Technically the database is being backed up when we create AMIs but you only really makes new AMIs when you have made configuration changes you want to preserve not for the database.

You can do EBS snapshots, on a schedule, but we have to pay for those where managed databases will give us free backup storage. We will have the cost of the managed database server but it should reduce the cost of the server for the application, so it should reduce our overall cost.

Create an RDS Instance

AWS Relational Database Service (RDS) is for fully-managed open-source relational databases like Postgres.

Within AWS Console Navigate to RDS and create a new Database.

Choose Standard Create, Postgres and Free Tier.

You will need to write a Database Identifier

Generate a strong password. I recommend using a 128 length password.

I am going to make give it Public Access so I can easily connect to via TablePlus.

You will need to expand Additional Configuration

Name your database. I am using the same name as the original forem_production

Enable Deletion Protection

We can proceed to create the database

It can take quite a while to create the RDS instance. Lets proceed to dumping our current database.

Dump the current database

SSH into your Forem Server

The application configuration for your Forem Rails app resides at /opt/forem/envs/rails.env

We can grep to extract out the DATABASE_URL used to dump the data.

sudo cat /opt/forem/envs/rails.env | grep DATABASE_URL

DATABASE_URL=postgresql://forem_production:8qui6haeo6akoLffKah0cohgh3ka9thb11thahk8noo7teg6zecaing11jui1SahGhaeNmeoqueeciekee0eoj6ohSae7paileiQuaingaXiej8ieDiom0te9ua2doh3@localhost/forem_production

We will need to enter the forem-rails container to perform the dump because it will have a PSQL client installed and will have ports open to the Postgres database container and will have outbound access to the internet so we can import our dump into our RDS instance.

sudo podman exec -it forem-rails /bin/bash

Using our database url we will dump the entire database to a sql files called dump.sql

psql <DATABASE_URL> -f dump.sql

So in my case:

psql postgresql://forem_production:8qui6haeo6akoLffKah0cohgh3ka9thb11thahk8noo7teg6zecaing11jui1SahGhaeNmeoqueeciekee0eoj6ohSae7paileiQuaingaXiej8ieDiom0te9ua2doh3@localhost/forem_production -f dump.sql

Importing into RDS

With our database dumped and our RDS instance ready we need to ensure our EC2 instance can reach our RDS instance.

Click on the default security group to take is to EC2 Security Groups and create a new security group

We give it a name and description and add two inbound rules for Postgres (port 5432), one allowing the security group of our server and our own IP address if we want to connect via TablePlus

We will need to change the Security Group on the database, so modify the database

Change out the security group

Apply the changes immediately

We need to assemble our new Postgres database connection url which follows this format:

postgresql://{{username}}:{{password}}@{{endpoint}}/forem_production

You can test on your local machine if your string works by supplying it as the first parameter to psql.

To import you need to run this command from within the forem-rails container:

psql [your-postgres-connection-url] < dump.sql

Once imported delete your dump.sql

rm dump.sql

exit the forem-rails back to the Forem's OS

Replace the application configuration DATABASE_URL

In order for our new database to be in use we need update our application configuration.

replace the DATABASE_URL with our new one by editing the application configuration file.

sudo vi /opt/forem/envs/rails.env

Turn off the postgres service and ensure it doesn't restart.

sudo systemctl stop forem-postgresql.service
sudo systemctl disable forem-postgresql.service

Restart Rails and Sidekiq for the new DATABASE_URL to take effect

sudo systemctl restart forem-rails.service
sudo systemctl restart forem-worker.service

Check that your Forem server is working normally be visting your website.

Perserving Configuration

You need to create a new AMI and update your Launch template.

Conclusion

That's all there is to it. I don't know what would happen when we would perform an forem update. Maybe @jdoss can tell me what trouble I have created for my future self.