An old version of the database format was found, You need to dump and reload before using PostgreSQL 16.

By | 7 months ago

databasepostgrespsqldockerpacmanarchlinuxpostgresqlrecoverupgrade

An old version of the database format was found, You need to dump and reload before using PostgreSQL 16. Encountered this error while doing system update in archlinux using pacman -syu command.As system upgrade updated my psql from 14 to 16

Followed a dicussion in reddit

first you need to pull the version of the docker that was previously present in your system like the following `docker pull postgres:14` since my old version was 14 (you can find out what your last psql version was by checking the logs file of your system and the searching for pslq inside it)

You'll need to run a Docker container with the old version of PostgreSQL. This involves mounting your existing data directory into the Docker container so that docker can access old PostgreSQL data. Replace <YOUR_OLD_VERSION> with the version number you identified earlier. your psql data might be located inside `</var/lib/postgresql/data>` or check `</var/lib/postgres/data>` after mounting and running docker image

Here is the Docker command:

docker run -it -v /var/lib/postgresql/data:/var/lib/postgresql/data postgres:<YOUR_OLD_VERSION>

This command does the following:

  • docker run: Executes a command in a new container.
  • -it: Runs the container in interactive mode with a TTY.
  • -v /var/lib/postgresql/data:/var/lib/postgresql/data: Mounts the host's data directory (/var/lib/postgresql/data) into the container at the same path. This assumes that /var/lib/postgresql/data is the location of your PostgreSQL data directory on the host.
  • postgres:<YOUR_OLD_VERSION>: Specifies the Docker image to use, with the appropriate version tag.

i ran the following command as my old psql username is `postgres` and my old psql password is ""


docker run -it -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD= -e POSTGRES_HOST_AUTH_METHOD=trust -v /var/lib/postgres/data:/var/lib/postgresql/data postgres:14

this command will run a docker container(keep that terminal running) and in new tab you can try out `docker ps` to see list of running docker images

identify its name and then run the following command to get into psql terminal inside docker image we just started `docker exec -it NAME_OF_DOCKER_JUST_STARTED bash` the above command will give us a terminal of docker container we started

first we can try to connect to our old database through docker by using the following command

su postgres

then we should enter psql terminal by useing psql\ where we can use `\l` command to see all databases, if you don't see the databases it means that you gave wrong path while mounting postgres, maybe you should stop that docker container and fix the path and start again.

if you find that `\l` is listing all the databases it means you can now dump the data to a file

with the following command `pg_dumpall -U postgres > /var/lib/postgresql/data/all_databases_2.sql` this will dump all data to /var/lib/postgresql/data/all_databases_2.sql

once the above command is run we can verify that file is generated by using the following command, just run this in same terminal ls -l /var/lib/postgresql/data/old_data_base_april_24.sql

this data is inside the docker container, we need to copy it from docker container to our local system by using below command, to do that in a new terminal run the below command, here i am copying the file `old_data_base_april_24.sql ` to `/home/rani` .

docker cp NAME_OF_DOCKER_JUST_STARTED:/var/lib/postgresql/data/old_data_base_april_24.sql /home/rani/

after running this, open the copied file and verify that data is present inside it. if data looks fine then you can fix the database error by doing whatever necessary.[before doing this you can even try to import this file to a new docker image of the desired version and see if all the data import was success.]

we are going to fix database error at last as i we dont dont want to loose the data by mistake. since in the blog we used docker images the local files remain in the same condition.

once database error is fixed we can connect to psql in local machine by using following command `sudo -i -u postgres` and run this inside as postgres user

psql -U postgres -h 127.0.0.1 -f /home/rani/old_data_base_april_24.sql

for me i got some error like permission denied `psql: error: /home/rani/old_data_base_april_24.sql: Permission denied` so i based on this link, moved the dumped output file to `/tmp` folder and retryed with new path and i got it imported successfully Permission denied