Upgrading Postgres

Occasionally, Baseplate may upgrade the Postgres version of its generated docker-compose file for backend apps. Suppose your Postgres instance was upgraded by a major version (e.g., from 14.0 to 15.0). In that case, you may need to manually upgrade your database because the schema of databases between major versions of Postgres may not be compatible, and Postgres cannot auto-migrate your database.

If you need to upgrade your production instance of Postgres, please refer to the documentation for your production instance host (e.g. Render). This only covers the local development instance of Postgres.

If you upgraded your local instance to an incompatible version of Postgres, you have two options:

Migrate your existing data: You can export a backup of your existing Postgres and re-import it to the upgraded Postgres.
Setup a new database from scratch: You can delete your existing database and set up a new instance with the upgraded Postgres version.

Migrate your existing data

To migrate your existing data, you’ll need to follow a series of steps: preparing Docker, exporting your database contents, deleting the database container and associated volumes, and finally, restoring your database contents into the new Postgres version.

Before migrating, ensure you are still on your old version of the docker-compose.yml file with the old version of Postgres.

Preparing Docker

To upgrade your Postgres version while maintaining your data, we first must stop any services that might access the database, e.g. running development servers. Once all services are stopped, you can go to the folder containing your docker-compose.yml file, ensure your Postgres container is running, and find the name of your docker container.

# cd into the folder with docker-compose.yml
cd <backend-app>/docker

# Ensure Docker instance is running
docker compose up -d

# List all Docker instances
docker compose ps --format "table {{.Name}}\t{{.Service}}"

You should get an output like:

NAME                         SERVICE
todoer-backend-dev-db-1      db
todoer-backend-dev-redis-1   redis

From this, pick the appropriate name of your database container, e.g., todoer-backend-dev-db-1, which we will use in the next step.

Exporting your database contents

pg_dumpall is a utility tool for creating a full backup of your Postgres database. This backup includes all your databases, roles, tables, and other global objects.

You can run the command below to export the data in your Postgres database to an SQL file:

docker exec -it <CONTAINER_NAME> pg_dumpall -U postgres > backup.sql

Replace <CONTAINER_NAME> with the container name you found in the previous step, e.g. todoer-backend-dev-db-1.

Delete existing database contents

Next, delete all the volumes associated with Postgres. Begin by identifying the volume associated with your Docker container:

docker inspect <CONTAINER_NAME> -f '{{ range .Mounts }}{{ .Name }}{{ end }}'
# This should return your volume name, for example, 'todoer-backend-dev_db-data'

Replace <CONTAINER_NAME> with your DB container name. For instance, todoer-backend-dev-db-1.

Next, stop the containers by running the command below:

docker compose down

Then, remove the volume by running the command below.

docker volume rm <VOLUME_NAME>

Replace <VOLUME_NAME> with the volume name returned from the docker inspect command.

Upgrade your docker-compose.yml file

Now, you need to upgrade your docker-compose.yml file. If you’re using git and the upgrade has already been performed in the repo, you can pull the latest changes. If you’re using Baseplate, you can run Sync with the latest version of Baseplate.

Restoring your data

Next, start your docker container by running the command below:

docker compose up -d

With your container running, run the command below to restore your backup on your new database:

cat backup.sql | docker exec -i <CONTAINER_NAME> psql -U postgres

Run your application and test the database queries to ensure your database works correctly. If everything is working correctly, you have successfully upgraded your Postgres version!

You can now safely delete your backup file by running the command below:

rm backup.sql

Setup a new database from scratch

If you choose to set up a new database from scratch, you’re starting with an empty one. This approach can be easier and require less time but it comes with the drawback of losing all your existing data.

Finding the name of the Docker volume

Get a list of your containers.

# cd into the folder with docker-compose.yml
cd <backend-app>/docker

# Start docker containers if needed
docker compose up -d

docker compose ps -a --format "table {{.Name}}\t{{.Service}}"

You should get an output like:

NAME                         SERVICE
todoer-backend-dev-db-1      db
todoer-backend-dev-redis-1   redis

From this, pick the appropriate name of your database container, e.g., todoer-backend-dev-db-1, which we will use in the next step.

Next, identify the volume associated with your Docker container:

docker inspect <CONTAINER_NAME> -f '{{ range .Mounts }}{{ .Name }}{{ end }}'
# This should return your volume name, for example, 'todoer-backend-dev_db-data'

Replace <CONTAINER_NAME> with your DB container name. For instance, todoer-backend-dev-db-1.

Upgrade your docker-compose.yml file

Remove volume from container

Tear down your docker-compose instance and remove the volume:

docker compose down

docker volume rm <VOLUME_NAME>

Replace <VOLUME_NAME> with the volume name from the previous step.

Then, restart your container by running the command below:

docker compose up -d

Now your container is up and running, run a database migration to sync your database with your schema by executing the command below:

# Return to parent backend directory
cd ..

# Run migrations
pnpm prisma migrate dev

# Seed the database
pnpm prisma db seed

Finally, ensure that your database is functioning correctly by running your application and testing the database queries. If everything is working correctly, you have now successfully set up a new database with the upgraded Postgres version.