postgreSQL 8.4.20：如何解决 pg_control 中缺少oldestXID 的问题，从而阻止在旧版 CentOS 6.4 服务器上使用 pg_upgrade？

发布于 2025-01-12 00:23:42 字数 2257 浏览 1 评论 0原文

我的公司有一台旧的内部服务器，运行 CentOS 6.4 和 postgreSQL 8.4.13。目标是尽可能在不进行操作系统更新的情况下进行升级；它是用于服务的实时内部服务器，因此升级操作系统或长时间停机是不切实际的。它很旧，根本不遵循最佳实践，但不幸的是，这就是我必须使用的。

使用 CentOS 保管库，我可以使用基本存储库升级到 8.4.20。

我还可以添加和访问 pgdg 存档存储库（适用于 8.4.22 及更高版本），但它们不包含在 CentOS postgresql 组中，并且与默认 CentOS 安装分开安装。

无论如何，我已经尝试使用 postgreSQL 8.4.22 作为初始小停止（因为“次要”版本 < 10 实际上是主要版本），并且 pg_upgrade 失败并显示：

The old cluster lacks some required control information:
  latest checkpoint oldestXID

如果我检查 pg_controldata，我得到

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 显然没有对 最新检查点oldestXID在其中。

我查看了 8.4.21 的变更日志8.4.22，但是有没有提及“oldestXID”。我还尝试过使用备份服务器 pg_resetxlog -f /var/lib/pgsql/data ，它会生成相同的 pg_control 文件，而没有最新检查点oldestXID 。

我意识到这些都是令人难以置信的旧版本，但这只是意味着我在这里双重迷失了。我希望有人有一些想法，因为我已经全力以赴了。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

凝望流年 2025-01-19 00:23:42

因此，我的朋友问我是否已经浏览过旧的源代码来寻找参考，我意识到我没有，因为我无法找到源代码。我又环顾四周，发现源代码位于 https://www.postgresql.org/ftp/ source/——事后看来，非常明显。

我浏览了从 8.4.13 到 9.0.0 的源代码，在 pg_controldata.c 中寻找“XID”。结果9.0.0添加了这个字段，以后的版本不支持没有它的迁移。

[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

我将此存储库添加到 /etc/yum/repos.d 中，然后使用 yum --showduplicates list postgresql90 检查可用版本 - 最早的可用版本是 9.0.6 。

我关闭了 postgresql 并安装了 9.0.6 yum install postgresql90-server-9.0.6-1PGDG.rhel6 postgresql90-devel-9.0.6-1PGDG.rhel6 postgresql90-libs-9.0.6-1PGDG.rhel6.

奇怪的是，pg_upgrade 实际上并不存在于 /usr/pgsql-9.0/bin 中...所以快速 yum whatprovides '*pg_upgrade' | grep 9.0.6 告诉我，我实际上还必须安装 postgresql90-contrib-9.0.6-1PGDG.rhel6.x86_64 。

我将 /usr/pgsql-9.0/bin 添加到我的 $PATH，运行 initdb，使用 --check 运行 pg_upgrade，一切看起来都很好。我从旧的 postgresql.conf 和 pg_hba.conf 中获取了所需的设置，并将它们复制到 9.0 conf 文件中，运行 pg_upgrade，一切都很顺利。

在检查了基本操作、数据库完整性以及我们拥有的 Web 应用程序的功能后，我继续执行 yum update postgresql90 来更新到 pgdg 中最新的次要版本（在本例中为 9.0.23））而且进展顺利。

因此，如果其他人碰巧发现了这个小众问题，那么您就可以尝试解决它。祝你好运！

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!

回复收藏 0 原文

~没有更多了~