Arch Linux

GoblinMendicant

Member

Registered: 2019-09-02

Posts: 15

pg_upgrade failing: database files are incompatible with server

I'm trying to upgrade postgresql from 12 to 13.

Background: Wherein I took some wrong steps

1. I had forgotten that I'd moved my data directory out of /var/ and into /home/. So when I first followed the wiki's upgrade directions, I was confused when the data folder wasn't found.
2. So then I downgraded (using "downgrade") postgresql and postgresql-libs.
3. Then I remembered, and so I updated those packages again and tried again.

As you can see below, where I run pg_upgrade using my data folders, I see two rather different errors. One is that the connection to the database failed "No such file or directory" (note that I can start the service using systemd). The second, in the log, is "The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 12.5."

My best take on this error is that "-B /usr/bin" is starting up a 12.5 server, when it should be starting a 13 server...?

Any tips would be appreciated - thank you!

[postgres@MasterControlUnit data]$ pg_upgrade -b /opt/pgsql-12/bin -B /usr/bin -d /home/postgres/olddata -D /home/postgres/data
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/home/postgres/data/.s.PGSQL.50432"?

could not connect to source postmaster started with the command:
"/opt/pgsql-12/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/home/postgres/olddata" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/home/postgres/data'" start
Failure, exiting

[postgres@MasterControlUnit tmp]$ cat pg_upgrade_server.log 

-----------------------------------------------------------------
  pg_upgrade run on Mon Dec 28 23:18:35 2020
-----------------------------------------------------------------

command: "/opt/pgsql-12/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/home/postgres/olddata" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/home/postgres/tmp'" start >> "pg_upgrade_server.log" 2>&1
waiting for server to start....2020-12-28 23:18:35.311 PST [31140] FATAL:  database files are incompatible with server
2020-12-28 23:18:35.311 PST [31140] DETAIL:  The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 12.5.
 stopped waiting
pg_ctl: could not start server
Examine the log output.

dtbaumann

Member

Registered: 2015-07-03

Posts: 28

Re: pg_upgrade failing: database files are incompatible with server

I guess you already checked, whether the directories

/home/postgres/data
/home/postgres/olddata

exist and what the corresponding PG_VERSION files contain. This information might be relevant to solving the case.

The commands themselves seem ok (uppercase new, lowercase old), but the old pg_ctl (that's the on in /opt/psql-12/bin) seems to find a postgres 13 structure in /home/postgres/olddata. I would guess that this might be a leftover from the previous unsuccessful attempt to upgrade?

GoblinMendicant

Member

Registered: 2019-09-02

Posts: 15

Re: pg_upgrade failing: database files are incompatible with server

Thanks. Yes, they exist and here are the PG_VERSIONs they contain:

[postgres@MasterControlUnit data]$ ls
base          pg_dynshmem    pg_logical    pg_replslot   pg_stat      pg_tblspc    pg_wal                postgresql.conf
global        pg_hba.conf    pg_multixact  pg_serial     pg_stat_tmp  pg_twophase  pg_xact               postmaster.opts
pg_commit_ts  pg_ident.conf  pg_notify     pg_snapshots  pg_subtrans  PG_VERSION   postgresql.auto.conf
[postgres@MasterControlUnit data]$ cat PG_VERSION 
13
[postgres@MasterControlUnit data]$ cd ../olddata/
[postgres@MasterControlUnit olddata]$ ls
base          pg_dynshmem    pg_logical    pg_replslot   pg_stat      pg_tblspc    pg_wal                postgresql.conf
global        pg_hba.conf    pg_multixact  pg_serial     pg_stat_tmp  pg_twophase  pg_xact               postmaster.opts
pg_commit_ts  pg_ident.conf  pg_notify     pg_snapshots  pg_subtrans  PG_VERSION   postgresql.auto.conf
[postgres@MasterControlUnit olddata]$ cat PG_VERSION 
12

Here is a gist showing my command with -v and -c: https://gist.github.com/RyanTG/156e9b8f … 55fd573848

Any idea where/how I could confirm if there is a postgres 13 structure in /home/postgres/olddata ?

dtbaumann

Member

Registered: 2015-07-03

Posts: 28

Re: pg_upgrade failing: database files are incompatible with server

you might want to dive into the subdirectories of base and check the PG_VERSION files there.

find . -name PG_VERSION -exec cat {} \;

do the file sizes look like olddata actually contains data

du base

does the number of subdirectories look like the number of databases you had?

GoblinMendicant

Member

Registered: 2019-09-02

Posts: 15

Re: pg_upgrade failing: database files are incompatible with server

Again - I appreciate your help in troubleshooting and getting me to understand what I'm looking for.

Yes, file size of olddata definitely looks like it has my data in it:

$ du -sh /home/postgres/olddata
72G     /home/postgres/olddata

$ du -sh /home/postgres/data     
40M     /home/postgres/data

Number of subdirectories looks right:

[postgres@MasterControlUnit olddata]$ cd base/
[postgres@MasterControlUnit base]$ ls
13425  16401  16457  1691383  2088715  2092120  2948223  pgsql_tmp
16400  16402  16500  1836912  2089843  2093255  808748

12 all the way down!

[postgres@MasterControlUnit base]$ find . -name PG_VERSION -exec cat {} \;
12
12
12
12
12
12
12
12
12
12
12
12
12
12

dtbaumann

Member

Registered: 2015-07-03

Posts: 28

Re: pg_upgrade failing: database files are incompatible with server

hmmh....

what about

0. check the ownership (postgres:postgres) and permissions (0700) for old and new data directories and files within

1. stop the postgresql.service
2. make a backup copy of your olddata (whole directory, just to be sure)
3. move the data directory out of the way
4. move olddata to data (ideally to /var/lib/postgres/data, because this is where the standard installation would expect it; if space is an issue you could bind mount it in fstab like

/home/postgres/data        /var/lib/postgres/data   none  bind  0 0

5. downgrade postgresql and libs to 12.5 (I know you did that already once, files should be in /var/cache/pacman/pkg)
6. run systemctl daemon-reload
7. start postgresql.service (hopefully successful, if not report back any errors)
8. manually dump database

pg_dumpall > db-12.5-before-upgrade.out

9. stop postgresql.service
10. upgrade postgres and libs
11. start from scratch according to wiki
12. recover old data

psql -U postgres -f db-12.5-before-upgrade.out postgres 

13. compare individual settings between old and new directories (i.e. pg_hba.conf and postgresql.conf)

Last edited by dtbaumann (2020-12-30 20:33:55)

GoblinMendicant

Member

Registered: 2019-09-02

Posts: 15

Re: pg_upgrade failing: database files are incompatible with server

7. starting postgresql service - was not successful. I experienced this before after downgrading yesterday, and it made me realize that I couldn't settle for sticking to 12.5. As far as I can tell, the error seems to be caused by icu updating and now being incompatible with 12.5. I don't know much about icu, but I'm assuming I should not downgrade that since more things depend on it.

● postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
    Drop-In: /etc/systemd/system/postgresql.service.d
             └─override.conf
     Active: failed (Result: exit-code) since Wed 2020-12-30 13:20:16 PST; 8s ago
    Process: 153689 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGROOT}/data (code=exited, status=0/SUCCESS)
    Process: 153691 ExecStart=/usr/bin/postgres -D ${PGROOT}/data (code=exited, status=127)
   Main PID: 153691 (code=exited, status=127)

Dec 30 13:20:15 MasterControlUnit systemd[1]: Starting PostgreSQL database server...
Dec 30 13:20:16 MasterControlUnit postgres[153691]: /usr/bin/postgres: error while loading shared libraries: libicui18n.so.67: cannot open shared object file: No such file or directory
Dec 30 13:20:16 MasterControlUnit systemd[1]: postgresql.service: Main process exited, code=exited, status=127/n/a
Dec 30 13:20:16 MasterControlUnit systemd[1]: postgresql.service: Failed with result 'exit-code'.
Dec 30 13:20:16 MasterControlUnit systemd[1]: Failed to start PostgreSQL database server.

(in addition to your steps, after binding /home/postgres/data in fstab I also edited the postgresql.service so it stops using /home/postgres/data. Also, I created an empty folder at /var/lib/postgres/data - I need to look into binding to see if it was correct to do that).

dtbaumann

Member

Registered: 2015-07-03

Posts: 28

Re: pg_upgrade failing: database files are incompatible with server

Sorry about that, I should have remembered the icu dependency.

postgresql-12 is not yet in the aur but I guess it's only a matter of time (or maybe you) of building a package against icu68. The old PKGBUILD is at

https://github.com/archlinux/svntogit-p … tra-x86_64

and the patch for icu68 could be taken from

https://github.com/archlinux/svntogit-p … 3eaf/trunk

It seems you will need to build postgresql and postgresql-libs (and postgis if you need that as well)

GoblinMendicant

Member

Registered: 2019-09-02

Posts: 15

Re: pg_upgrade failing: database files are incompatible with server

Ah nice. I’ll look more into patching packages, but I suspect this foe is beyond me. And it looks like the person originally packaging these is no longer active.

loqs

Member

Registered: 2014-03-06

Posts: 18,039

Re: pg_upgrade failing: database files are incompatible with server

You can use the ALA to downgrade the system to a date when postgresql or postgresql-old-upgrade are at the version you want and all the libraries will be in sync.

twelveeighty

Member

From: Alberta, Canada

Registered: 2011-09-04

Posts: 1,134

Re: pg_upgrade failing: database files are incompatible with server

Something isn't adding up here, I did the usual postgres-old-upgrade recently to go from 12 to 13 and it worked flawlessly. Did you actually run ALL the steps in the Upgrading PostgreSQL Wiki? Common mistakes are 1) forget the initdb step 2) not becoming the 'postgres' user and 3) running the pg_upgrade command while being in the wrong folder.

You can redo the process at any time. Be sure to back up your old data first, of course. Also be sure to first stop and disable the postgresql service.

GoblinMendicant

Member

Registered: 2019-09-02

Posts: 15

Re: pg_upgrade failing: database files are incompatible with server

I am really certain I did all the steps correctly. And I tried it many times. Plus I’ve been using arch since pg 9, and never had issues with upgrades previously. But this is the first time since I’ve been using a different data directory.

I definitely blame myself here. I suspect my problem stemmed from the first time I tried to upgrade and I forgot that my data was elsewhere... something got wack at that point, and it was continuously mis-recognizing my versions.

I ended up starting from scratch. My databases are pretty simple: converted OpenStreetMaps data, as well as dumps for rails dev work.