Create backup Database for use with Vercel Deployments

[HS-90]

Currently, our Vercel preview Deployments for each PR share the same central database as end users of c0d3.com. This means any changes made to modules, lessons, challenges in a Vercel preview will affect the production database and effect the user experience. In the past, if an engineer wanted to test the functionality of a new feature manually, any GraphQL mutations that are triggered will alter the production database unintentionally.

Creating a dummy database for PR related Vercel preview Deployments, and keeping it separate from production, will prevent these issues going forward.

We can accomplish this by creating a new db in DigitalOcean and configuring it so that it is only used for previews.

---

Preview deployments database design doc

Whenever a new preview release is deployed, it has direct access to our production database.

It has the following disadvantages:

1. Erase data — Mistakes happen, and a prisma.user.deleteMany({}) might slide in the code.
2. Add unintended data — An engineer's PR introduced a new feature of immediate-user-creation. They missed some parts and now it creates them without the user interaction.
3. Update data — It's also possible to update things that aren't supposed to be updated in production. An engineer introduced a new feature where a lesson can be updated. They try to update the lesson and it's now updated in production.

Ensuring the separation of what services the production and preview deployments use will prevent any issues from happening to production.

Action items

1. Create a droplet and install Postgres
2. Create the user and the database
3. Allow connections to port 5432
4. Allow Postgres to listen to connections from all IPs

Create a server and install Postgres

Create a droplet in DigitalOcean then install Postgres by following this guide

In a nutshell:

sudo apt install postgresql postgresql-contrib

then

sudo systemctl start postgresql.service

Things you need to be aware of after spinning up the droplet:

1. NEVER remove the rule for port 22 by accidentally running sudo ufw reset
   • This rule allows other computers to communicate with the droplet SSH service to either allow or refuse connections
   • If you removed the rule for port 22, enter the droplet by the recovery console and run sudo ufw allow 22
2. Make sure to set a root password and share it with the team

Create the user and the database

Enter psql by running sudo -i -u postgres psql

Once you're in and it shows postgres=#, run CREATE USER c0d3_user WITH PASSWORD 'c0d3iscool'; to create a user and set its password.

To create a database, run CREATE DATABASE c0d3 OWNER c0d3_user;. This will create a database and set its owner as the user provided.

Allow connections to port 5432

To allow other computers to communicate with our Postgres database service on port 5432, we run:

• sudo ufw allow 5432. We use the ufw firewall to allow connections to port 5432.
• To ensure it's set, run sudo ufw status

Allow Postgres to listen to connections from all IPs

To allow Postgres to listen to connections from all IPs, we can update two files:

1. The Postgres policy configuration file pg_hba.conf under /etc/postgresql/14/main is used to grant or deny access to the database. Postgres only listens to local connections (localhost) with the default setup. To change this, we'll add a rule in the policy config file to grant access to any computers
   • host c0d3 c0d3_user 0.0.0.0/0 md5
     • host: specify the rule as a remote connection
     • c0d3: the database name
     • c0d3_user: the user making the connection (in this case, it's the owner of the database)
     • 0.0.0.0/0: grant access to this database for any IP
     • We could've done 123.123.1.23/32 to allow a single IP
     • md5: authentication method
2. The Postgres primary configuration file postgresql.conf under /etc/postgresql/14/main/ is the source for all Postgres settings. In this file, we'll configure the Postgres server to listen through all the interfaces (localhost, the droplet IP, ...etc)
   • listen_addresses = '*'
     • listen_address: a property that takes a list of comma-separated IPs or localhost
     • '*': listen through all the interfaces

In the end, we'll need to restart the Postgres service in order for the changes to take effect:

sudo systemctl restart postgresql

---

References

• DigitalOcean remote access via PgAdmin4
• Setup firewall with ufw on a droplet
• pg_hba.conf explained
• pg_hba.config explained (official)
• Postgres connections setting
• Enable remote access to Postgres

[evo777]

It is good to have a back up.