Upgrading Postgres in a production instance

Updated June 14, 2024 21:33

Make sure you make a backup and a snapshot of the instance prior to performing these or any other configuration changes! These actions can be destructive.

1. First make a backup of the database

Manual Backup of Hudu Postgres

Login into the server you want to back up.
Run cd ~/hudu2
Make sure the server is up and running.

Run:

sudo docker compose exec -T db pg_dump -U postgres hudu_production >> NAME-OF-DUMP.sql

This file can now be moved and stored in a secure place if needed for an additional database backup.
Bring your instance down. Run
```
sudo docker compose down
```

2. Delete the database volume

sudo docker volume ls
sudo docker volume rm hudu2_postgres_data

3. Edit the ~/hudu2/docker-compose.yml file and replace the version with the version you wish to update to (current version 3/27/24 is 16.2).

db:
   image: 'postgres:16.2'
   restart: unless-stopped
   volumes:
     - postgres_data:/var/lib/postgresql/data

4. Take the instance back up

cd ~/hudu2
sudo docker compose up -d

5. Restore the database

Move the .sql database dump file into the ~/hudu2 directory if needed. Typically, the easiest way to move files is via SCP or SFTP.
Run:
```
sudo docker compose down
```
to bring your instance down.
Run:
```
 sudo docker compose up -d db
```

Run the command:

sudo docker compose exec db dropdb hudu_production -U postgres

Run the command:

sudo docker compose exec db createdb hudu_production -U postgres

Run the command:

cat NAME-OF-DUMP.sql | sudo docker compose exec -T db psql -d hudu_production -U postgres

Run:
```
sudo docker compose down
```
Run:
```
sudo docker compose up -d
```
to get your instance back up and running!

6. TEST by confirming the site comes back up. This process can take a few minutes. Test that the data from the database is back and working.

Please visit our Self-Hosted Backup and Restores article to update your automatic backup script to be compatible with the new Postgres version.