postgresqlvscode-devcontainer

How to delete PostgreSQL database previously created by devcontainer?

I recently had to delete all Docker containers on my system, and when VSCode rebuilt my Java devcontainer with PostgreSQL, it wouldn't start. I get the following error in the logs:

PostgreSQL Database directory appears to contain a database; Skipping initialization

2023-11-07 20:27:40.074 UTC [1] FATAL:  database files are incompatible with server
2023-11-07 20:27:40.074 UTC [1] DETAIL:  The data directory was initialized by PostgreSQL version 15, which is not compatible with this version 16.0 (Debian 16.0-1.pgdg120+1).

I assume this is because the new image is using the database from the old image. Where exactly is this database stored, and how do I get rid of it if it's still there after docker system prune --all --force --volumes?

Solution

  • It appears that you have a volume that contains a PostgreSQL instance that is compatible with v. 15, but you are trying to load it up with a postgres:16 image. This is not possible, as data directories are not immediately compatible with newer versions of Postgres. Even without Docker, moving from an older database instance/cluster to a newer database binary requires either 1) dump/reload or 2) use of pg_upgrade. Some options below:

    Option 0: Find and delete the data cluster

    You mentioned that you have 3 .yml files -- you'll need to look under the volumes: section of the service corresponding to postgres:##. Without knowing the actual directory mapping, I cannot 100% help you. However, since we know that you did docker system prune --all --force --volumes and the data is still there, then we know that the data you're trying to overwrite is not in a Docker Volume, but probably some directory mapping to your host machine. Once you identify which directory that is, you can either 1) delete the mapping from the .yml file or 2) delete the contents of the mapped folder

    Option 1: stick with Postgres 15

    The work around the issue quickly, just change from using a postgres:16 image to a postgres:15 image in your docker-compose.yml file

    Option 2: Use Postgres 15 to dump, then reload to Postgres 16

    Using a postgres:15 image to call pg_dump against the database cluster will result in a logical/plaintext dump of your database. With this dump, you can start up a postgres:16 image with a new Docker Volume, and then use psql to copy the contents of the dump into the new database

    Option 3: Use pg_upgrade to perform the upgrade against the cluster

    Note: This is a lot more complex, and requires a bit of advanced knowledge of how the containers work.

    To use pg_upgrade, you'll need to use a postgres:15 image (preferably running on a Debian/Ubuntu underlying OS). You'll need to use apt to install Postgres 16, and then use pg_upgrade to upgrade the data directory. After that, you can tear down the container, and use a postgres:16 container to access and serve up the now-updated data cluster.