Self-Hosted: Upgrading Postgres in a production instance

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.

First make a backup of the database

Manual Backup of Hudu Postgres

Log in to the server you want to back up.
Run:
```
cd ~/hudu2
```
Make sure the Hudu instance is up and running.

Create a database dump by running:

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. Make sure it is complete and has data!
Bring your instance down by running:
```
sudo docker compose down
```
Delete the database volume
List volumes and remove the Hudu Postgres volume:
```
sudo docker volume ls
sudo docker volume rm hudu2_postgres_data
```
Edit the docker-compose.yml file located in the ~/hudu2 directory and replace the Postgres image version with the version you wish to update to (latest tested version is 17.2). For example:
```
db:
  image: postgres:17.2
  restart: unless-stopped
  volumes:
    - postgres_data:/var/lib/postgresql/data
```
Bring the instance back up
From the project directory, run:
```
sudo docker compose up -d
```
Restore the database
1. Move the SQL database dump file into the ~/hudu2 directory if needed. Typically, the easiest way to move files is via SCP or SFTP.
2. Bring your instance down:
```
sudo docker compose down
```
3. Start only the database container:
```
sudo docker compose up -d db
```
4. Drop the existing database:
```
sudo docker compose exec db dropdb hudu_production -U postgres
```
5. Recreate the database:
```
sudo docker compose exec db createdb hudu_production -U postgres
```
6. Restore from the dump:
```
cat NAME-OF-DUMP.sql | sudo docker compose exec -T db psql -d hudu_production -U postgres
```
7. Bring the database container down:
```
sudo docker compose down
```
8. Bring the full instance back up:
```
sudo docker compose up -d
```
Test by confirming the site comes back up. This process can take a few minutes. Verify that the data in the database is present and working as expected.

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