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

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.


7 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
  3. Varaskkar

    Hi Alex, it worked the first time, I owe you my life!

    On Friday I updated Manjaro and postresql stopped working when I upgraded from version 14 to 15, I thought I was in trouble because the DB had important data. The big lesson of all this is to make periodical backups or install packages with fixed versions ("pacman -S postgresql-12" for example).

     
    Reply
    1. Andrew

      *

      initdb: error while loading shared libraries: libcrypto.so.3: cannot open shared object file: No such file or directory

      **

       
      Reply
  4. George

    Great article, and I am alsost there.
    when I run: 
    sudo -u postgres pg_upgrade -b /opt/pgsql-12/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data

    I get:

    Performing Consistency Checks
    -----------------------------
    Checking cluster versions                                     ok
    Checking database user is the install user                    ok
    Checking database connection settings                         ok
    Checking for prepared transactions                            ok
    Checking for system-defined composite types in user tables    ok
    Checking for reg* data types in user tables                   ok
    Checking for contrib/isn with bigint-passing mismatch         ok
    Checking for incompatible "aclitem" data type in user tables  ok
    Creating dump of global objects                               ok
    Creating dump of database schemas                              
     dw_seismological_data                                        
    *failure*

    Consult the last few lines of "/var/lib/postgres/data/pg_upgrade_output.d/20231214T205528.455/log/pg_upgrade_dump_1642
    4.log" for
    the probable cause of the failure.
    Failure, exiting

    Where i get to see the log and it shows among others:
    pg_dump: error: query failed: ERROR:  could not access file "$libdir/postgis-3": No such file or directory

    Can I skip this database somehow and move to the next, or resolve the postgis-3 somehow?

    Thank you

     
    Reply

Leave Your Observation

Your email address will not be published. Required fields are marked *