PostgreSQL error “An old version of the database format was found” (SOLVED)

Warning: The following instructions could cause data loss. Do not run the commands below blindly, without understanding what they do. Backup database first.

If, while starting the PostgreSQL service:

sudo systemctl start postgresql.service

it didn't work and the following message came up:

Job for postgresql.service failed because the control process exited with error code.
See "systemctl status postgresql.service" and "journalctl -xe" for details.

Then check the status of this service:

systemctl status postgresql.service

In my case, this is:

● postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
     Active: failed (Result: exit-code) since Mon 2020-04-20 12:23:08 MSK; 8s ago
    Process: 63126 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGROOT}/data (code=exited, status=1/FAILURE)

апр 20 12:23:08 HackWare systemd[1]: Starting PostgreSQL database server...
апр 20 12:23:08 HackWare postgres[63126]: An old version of the database format was found.
апр 20 12:23:08 HackWare postgres[63126]: See https://wiki.archlinux.org/index.php/PostgreSQL#Upgrading_PostgreSQL
апр 20 12:23:08 HackWare systemd[1]: postgresql.service: Control process exited, code=exited, status=1/FAILURE
апр 20 12:23:08 HackWare systemd[1]: postgresql.service: Failed with result 'exit-code'.
апр 20 12:23:08 HackWare systemd[1]: Failed to start PostgreSQL database server.

The key line in this output is:

An old version of the database format was found.

To solve the problem, it is suggested to follow the link: https://wiki.archlinux.org/index.php/PostgreSQL#Upgrading_PostgreSQL

Updating databases is not required for minor PostgreSQL updates, but may be required for major updates, since due to innovations their schema may change.

The update can be done with the preservation of the existing information, as well as without it – in fact, by deleting and re-initializing the database. The second option is suitable for those who have installed the PostgreSQL package for a long time, but the need to use it arose significantly after its installation. As a result, you find that the PostgreSQL service is down, even though it has never been used.

How to Upgrade PostgreSQL Databases Preserving Information

The pg_upgrade utility is used to upgrade the databases. This utility is included in the postgresql package. This utility can update databases starting from 8.4.X.

Note that the database cluster directory does not change from version to version, so before running pg_upgrade, you must rename the existing data directory and change to the new directory. The new database cluster needs to be initialized.

Stop the service before updating if it is still running:

sudo systemctl stop postgresql.service
systemctl status postgresql.service

To upgrade databases, you need the executable files of the previous version of PostgreSQL, they are located in the postgresql-old-upgrade package, install it and upgrade PostgreSQL:

sudo pacman -S postgresql postgresql-libs postgresql-old-upgrade

Move the old data and initialize the database:

sudo mv /var/lib/postgres/data /var/lib/postgres/olddata
sudo mkdir /var/lib/postgres/data /var/lib/postgres/tmp
sudo chown postgres:postgres /var/lib/postgres/data /var/lib/postgres/tmp
cd /var/lib/postgres/tmp
sudo -u postgres initdb -D /var/lib/postgres/data

The following command will transfer data from the old cluster to the new one, in this command you need to replace PG_VERSION with the version of the previous database:

sudo -u postgres pg_upgrade -b /opt/pgsql-PG_VERSION/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data

For example:

sudo -u postgres pg_upgrade -b /opt/pgsql-12/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data

After that, start the service again and check its status:

sudo systemctl start postgresql.service
systemctl status postgresql.service

How to upgrade PostgreSQL databases without saving information

So, to initialize the new PostgreSQL format database run the following commands:

sudo mv /var/lib/postgres/data /var/lib/postgres/olddata
sudo mkdir /var/lib/postgres/data /var/lib/postgres/tmp
sudo chown postgres:postgres /var/lib/postgres/data /var/lib/postgres/tmp
cd /var/lib/postgres/tmp
sudo -u postgres initdb -D /var/lib/postgres/data

After that, start the service again and check its status:

sudo systemctl start postgresql.service
systemctl status postgresql.service

More information can be found in the official Wiki: https://wiki.archlinux.org/index.php/PostgreSQL#Upgrading_PostgreSQL

Possible errors and solutions

1.

If while running the command

sudo mv /var/lib/postgres/data /var/lib/postgres/olddata

you encounter an error

mv: cannot move '/var/lib/postgres/data' to '/var/lib/postgres/olddata/data': Directory not empty

then it means that you have already upgraded the database, saving the previous database to the /var/lib/postgres/olddata/data directory. You can save the next database to another directory, or simply delete the existing one:

sudo rm -rf /var/lib/postgres/olddata/data

2.

If during command execution

sudo mkdir /var/lib/postgres/data /var/lib/postgres/tmp

you encountered an error

mkdir: cannot create directory ‘/var/lib/postgres/tmp’: File exists

then just skip the given command, since the /var/lib/postgres/tmp directory already exists.