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.