Forem Creators and Builders

loading...

Changing your Forem Postgres database for an RDS Postgres Server

Andrew Brown
・4 min read

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.

Alt Text

You will need to write a Database Identifier

Alt Text

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

Alt Text

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

Alt Text

You will need to expand Additional Configuration

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

Enable Deletion Protection

Alt Text

We can proceed to create the database

Alt Text

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
Enter fullscreen mode Exit fullscreen mode
DATABASE_URL=postgresql://forem_production:8qui6haeo6akoLffKah0cohgh3ka9thb11thahk8noo7teg6zecaing11jui1SahGhaeNmeoqueeciekee0eoj6ohSae7paileiQuaingaXiej8ieDiom0te9ua2doh3@localhost/forem_production
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

psql <DATABASE_URL> -f dump.sql
Enter fullscreen mode Exit fullscreen mode

So in my case:

psql postgresql://forem_production:8qui6haeo6akoLffKah0cohgh3ka9thb11thahk8noo7teg6zecaing11jui1SahGhaeNmeoqueeciekee0eoj6ohSae7paileiQuaingaXiej8ieDiom0te9ua2doh3@localhost/forem_production -f dump.sql
Enter fullscreen mode Exit fullscreen mode

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
Alt Text

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
Alt Text

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

Alt Text

Change out the security group

Alt Text

Apply the changes immediately

Alt Text

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

postgresql://{{username}}:{{password}}@{{endpoint}}/forem_production
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Once imported delete your dump.sql

rm dump.sql
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

sudo systemctl stop forem-postgresql.service
sudo systemctl disable forem-postgresql.service
Enter fullscreen mode Exit fullscreen mode

Restart Rails and Sidekiq for the new DATABASE_URL to take effect

sudo systemctl restart forem-rails.service
sudo systemctl restart forem-worker.service
Enter fullscreen mode Exit fullscreen mode

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.

Discussion (1)

Collapse
jdoss profile image
Joe Doss

No major headaches with this setup Andrew. We actually use RDS too with Forem Cloud.

One thing that might be easier than doing all of these manual edits to all of the required files and dumping/importing the DB to RDS, would be to set your RDS username, password and host in the inventory here github.com/forem/selfhost/blob/mai... before you launch the server with Ansible.

Just make sure your FCOS EC2 can access the RDS correctly and when it comes online, it should connect to PostgreSQL and bootstrap the database.

You can do this with Redis as well. Just point github.com/forem/selfhost/blob/mai... at your AWS Elasticache end points in your Ansible Inventory before you launch your Forem. It will configure everything to use that instead of the local Redis container. Just make sure to stop Redis via SSH.