How to work with PostgreSQL on Render

Introduction

Render offers PostgreSQL for free accounts, but the database is deleted after one month.
If the data matters to you, the solution is to back it up and recover it.

How to backup

Follow these steps;

1. Run this command in the terminal to backup the PostgreSQL DB:

    pg_dump -h dpg-cn9if6fsc6pc74fh6pd0-a.oregon-postgres.render.com -U mysite mysite_d75u -n public --no-owner > database_dump.sql
   

2. What each of the values in 1 above means can be found in 6 below

3. You will be prompted to input a password. The password can be obtained by following the steps described in 7 below.

4. In case your system pg_dump doesn’t match the server version, you might get the error below;

    pg_dump: error: server version: 16.4 (Debian 16.4-1.pgdg120+2); pg_dump version: 14.13 (Homebrew)
    pg_dump: error: aborting because of server version mismatch
   

5. To resolve this error, use the pg_dump that matches the server’s. Something like so;

    /opt/homebrew/opt/postgresql@16/bin/pg_dump -h dpg-cn9if6fsc6pc73fd6pc0-a.oregon-postgres.render.com -U mysitte mysite_d75u -n public --no-owner > database_dump.sql
   

6. Replace the relevant values with the ones that match yours as described in the render dashboard;

   1. dpg-cn9if6fsc6pc74fh6pd0-a : your Hostname

   2. oregon-postgres.render.com: your database region

   3. mysite : your database_username

   4. mysite_d75u : your database_name

7. The values are obtained by the following steps;

   1. Log into render

   2. Go to projects

   3. Under services, click PostgreSQL service name

   4. The details will be under the connections above Access Control

How to drop the DB

Follow these steps;

1. Run this command to drop the PostgreSQL DB:

    /opt/homebrew/opt/postgresql@16/bin/dropdb -h dpg-cn9if6fsc6pc73fd6pc0-a.oregon-postgres.render.com -U mysite mysite_d75u
   

2. You will be prompted to input the password.

How to create and restore the DB

Follow these steps;

1. Run this command to create a new PostgreSQL DB:

    /opt/homebrew/opt/postgresql@16/bin/createdb dpg-cn9if6fsc6pc73fd6pc0-a.oregon-postgres.render.com -U mysite mysite_d75u
   

2. Run this to restore your PostgreSQL DB from the backup

    psql -h dpg-cn9if6fsc6pc73fd6pc0-a.oregon-postgres.render.com -U mysite mysite_d75u < database_dump.sql
   

3. You will be prompted to input the password

Conclusion

Shout out to Silumesii who greatly assisted with this.
With this guide, you can back up, delete and restore your PostgreSQL DB on render.
I plan to explore a way of automating this process.