My company has a legacy, internal server running CentOS 6.4 with postgreSQL 8.4.13. The goal is to upgrade it as far as possible without doing OS updates; it's a live internal server being used for services, so it's not practical to upgrade the OS or have a long downtime. It's old and doesn't follow best practices at all, but unfortunately, this is what I have to work with.
Using the CentOS vault, I can use the base repo to upgrade to 8.4.20.
I can also add and access the pgdg archive repos (for 8.4.22 and up), but they're not included in the CentOS postgresql group and install separately from the default CentOS installation.
I've tried doing that anyway with postgreSQL 8.4.22 as an initial small stop (since "minor" versions < 10 were actually major releases) and pg_upgrade fails with:
The old cluster lacks some required control information:
latest checkpoint oldestXID
If I check pg_controldata, I get
pg_control version number: 843
Catalog version number: 200904091
Database system identifier: 5893982526456722425
Database cluster state: in production
pg_control last modified: Sat 05 Mar 2022 04:35:52 PM JST
Latest checkpoint location: 278A/6517F558
Prior checkpoint location: 278A/6517F510
Latest checkpoint's REDO location: 278A/6517F558
Latest checkpoint's TimeLineID: 1
Latest checkpoint's NextXID: 7/1001247883
Latest checkpoint's NextOID: 260730376
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Time of latest checkpoint: Sat 05 Mar 2022 04:35:40 PM JST
Minimum recovery ending location: 0/0
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
There's clearly no reference to latest checkpoint oldestXID in it.
I looked at the changelogs for 8.4.21 and 8.4.22, but there are no references to "oldestXID". I've also tried, using a backup server, pg_resetxlog -f /var/lib/pgsql/data, which yields the same pg_control file without latest checkpoint oldestXID.
I realize that these are all incredibly old versions, but that just means I'm doubly lost here. I hope someone has some ideas, because I'm all out.
So, my friend asked me if I'd gone through the old source code looking for references, and I realized I hadn't because I hadn't been able to find the source. I looked around a bit more and found the source at https://www.postgresql.org/ftp/source/ -- in hindsight, very obvious.
I went through the source code from 8.4.13 up to 9.0.0 looking for "XID" in pg_controldata.c. It turns out that 9.0.0 added this field and later versions don't support migrating without it.
[pgdg90]
name=PostgreSQL 9.0 RPMs for RHEL/CentOS 6
baseurl=https://yum-archive.postgresql.org/9.0/redhat/rhel-6-x86_64
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
I added this repo to /etc/yum/repos.d and then checked the available versions with yum --showduplicates list postgresql90 -- the earliest available version was 9.0.6.
I shut down postgresql and installed 9.0.6 with yum install postgresql90-server-9.0.6-1PGDG.rhel6 postgresql90-devel-9.0.6-1PGDG.rhel6 postgresql90-libs-9.0.6-1PGDG.rhel6.
Strangely enough, pg_upgrade didn't actually exist in /usr/pgsql-9.0/bin... so a quick yum whatprovides '*pg_upgrade' | grep 9.0.6 showed me that I actually had to install postgresql90-contrib-9.0.6-1PGDG.rhel6.x86_64 as well.
I added /usr/pgsql-9.0/bin to my $PATH, ran initdb, ran pg_upgrade with --check, and all seemed well. I grabbed the settings I needed from the old postgresql.conf and pg_hba.conf and copied them to the 9.0 conf files, ran pg_upgrade, and everything went smoothly.
After checking basic operations, database integrity, and functions with the web apps we have, I went ahead and did yum update postgresql90 to update to the latest minor minor version in pgdg (9.0.23, in this case) and that went swimmingly as well.
So, if anyone else happens to find this niche problem, that's how you can try dealing with it. Best of luck!