Loading...
X

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


4 observations on “PostgreSQL error “An old version of the database format was found” (SOLVED)
  1. icancto

    [$] ~postgres/tmp$ sudo -u postgres pg_upgrade -b /opt/pgsql-13/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data

    for some reason when running this I get the following: 

    could not open version file "/var/lib/postgres/olddata/PG_VERSION": Permission denied

                                                                                                                    
     

     
    Reply
    1. Alex

      Hello! It looks like a file permissions issue. You can check the owner and file permissions with the command:

      sudo ls -l /var/lib/postgres/olddata/PG_VERSION

      Output example:

      -rw------- 1 postgres postgres 3 Nov 30 2018 /var/lib/postgres/olddata/PG_VERSION

      So, the owner of the file is the postgres user. If in your case the owner of the file is another user, for example root, then try to change ownership of the file:

      sudo chown postgres /var/lib/postgres/olddata/PG_VERSION
      
       
      Reply
  2. Dillah

    when I run this

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

    I get following

    could not open log file "pg_upgrade_internal.log": Permission denied
    Failure, exiting

    is there any solution?

     
    Reply
    1. Alex

      Hello! Like previous comments, this looks like a file permissions issue. First try to find the file:

      locate pg_upgrade_internal.log

      Output on my OS:

      /var/lib/postgres/tmp/pg_upgrade_internal.log

      Check the owner and permissions of the file with the command:

      sudo ls -l /var/lib/postgres/tmp/pg_upgrade_internal.log

      Output example:

      -rw------- 1 postgres postgres 501 Jun 8 2021 /var/lib/postgres/tmp/pg_upgrade_internal.log

      So, the owner of the file is the postgres user. If in your case the owner of the file is another user, for example root, then try to change ownership of the file:

      sudo chown postgres /var/lib/postgres/tmp/pg_upgrade_internal.log

      If the pg_upgrade_internal.log file is not found, check the permissions of the directory where it should be created:

      sudo ls -dl /var/lib/postgres/tmp/
      drwxr-xr-x 2 postgres postgres 4096 Jun 8 2021 /var/lib/postgres/tmp/
      
       
      Reply

Leave Your Observation

Your email address will not be published.