# 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:
    
    ```sql
    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;
    
    ```bash
    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;
    
    ```sql
    /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`](http://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:
    
    ```sql
    /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:
    
    ```sql
    /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
    
    ```sql
    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](https://x.com/silumesii?s=21) 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.