OpenBSD上的PostgreSQL:使用PG_UPGRADE升级14到15
#postgres #database #迁移 #openbsd

概括

OpenBSD为我们提供了一系列项目的良好文档。 (例如,the man pages受到照顾和维护。)它应用于主要服务套件和操作系统。也是PostgreSQL

好吧,根据他们在postgresql上的pkg-readme(/usr/local/share/doc/pkg-readmes/postgresql-server),他们建议我们有两种升级数据库的方法:

  1. koude1
  2. koude2

前者较慢,因此不适合大数据库,但不需要其他包装。后者反之亦然。

I wrote about the former,其中我将postgresql 11升级为12。这是后者的转。

环境

  • OS: OpenBSD 7.3 (<- 7.2)
  • DB: PostgreSQL 15 (<- 14)

教程

这是逐步指南。让我们开始。

备份(可选)

当您担心备份时,pg_dumpall可用:

$ pg_dumpall > pg.sqldump 

在这里,Password:将在数据库中询问您。

安装pg_upgrade

通过端口软件包系统获取它:

$ doas pkg_add postgresql-pg_upgrade

结果是:

quirks-6.42 signed on 2023-04-06T19:16:59Z
postgresql-pg_upgrade-14.5:postgresql-previous-13.5p0: ok
postgresql-pg_upgrade-14.5: ok

升级OpenBSD 7.2至7.3,然后PostgreSQL 14至15

这是关于升级OpenBSD,而不是PostgreSQL。

按以下顺序运行sysupgradesysmergepkg_add -u
细节是in this post

好吧,在上面的过程中,将询问您有关以下的postgresql:

postgresql-server-15.2p0: Updating across a major version - data migration needed, see the pkg-readme.
Do you want to update now ? [y/N/a] y

输入“ y”,将升级PostgreSQL的软件包(不是数据库本身)。它将像下面一样打印出来:

postgresql-client+postgresql-contrib+postgresql-pg_upgrade+postgresql-server-14.5->postgresql-client-15.2+postgresql-contrib-15.2+postgresql-pg_upgrade-15.2+postgresql-server-15.2p0: ok
(...)
New and changed readme(s):
    /usr/local/share/doc/pkg-readmes/postgresql-server

停止守护程序

通过停止服务器来准备好:

$ doas rcctl stop postgresql

结果是:

postgresql(ok)

现在我们已经准备好了。让我们升级数据库!

创建PostgreSQL 15群集

准备创建data目录:

$ doas mv /var/postgresql/data /var/postgresql/data-14

然后koude10 it和koude11 for Postgresql:

$ # doas su _postgresql -c "mkdir /var/postgresql/data && cd /var/postgresql && \
        initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF8 -W"
$ doas su _postgresql -c "mkdir /var/postgresql/data && cd /var/postgresql && \
      initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF8 --locale=ja_JP.UTF-8 -W"

上面的前者是由于pkg-readme造成的,后者是对我的情况的特定的,其中添加了--locale=(...)选项。

结果是:

The files belonging to this database system will be owned by user "_postgresql".
This user must also own the server process.

The database cluster will be initialized with locale "ja_JP.UTF-8".
initdb: could not find suitable text search configuration for locale "ja_JP.UTF-8"
The default text search configuration will be set to "simple".

Data page checksums are disabled.

Enter new superuser password: 
Enter it again: 

两次输入PostgreSQL Superuser的密码。然后将随后进行以下:

fixing permissions on existing directory /var/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 20
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Tokyo
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /var/postgresql/data -l logfile start

是的。该数据库是由它们生成的。

配置迁移

这里的步骤是数据迁移的暂时性。

编辑当前的pg_hba.conf(以及下一个):

$ doas nvim /var/postgresql/data-14/pg_hba.conf

为了允许超级用户的本地连接:

  # TYPE  DATABASE        USER            ADDRESS                 METHOD
+ local all postgres trust

请注意,您应该将线路放在顶部,以使其成为最优先的优先级。

然后,将下面的文件复制在内

$ doas cp -p /var/postgresql/data-14/pg_hba.conf /var/postgresql/data/

$ # in addition, in case that tls is used:
$ doas cp -p /var/postgresql/data-14/server.{crt,key} /var/postgresql/data/

运行pg_upgrade

好的。您的数据库现在正在等待升级!!!运行命令行(感谢软件和PKG-ReadMe):

$ doas su _postgresql -c "cd /var/postgresql && \
      pg_upgrade -b /usr/local/bin/postgresql-14/ -B /usr/local/bin \
      -U postgres -d /var/postgresql/data-14/ -D /var/postgresql/data"

在我的情况下,结果在下面:

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
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok
Checking for new cluster tablespace directories             ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_xact to new server                           ok
Setting oldest XID for new cluster                          ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
                                                            ok
Copying user relation files
                                                            ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to delete old cluster                       ok
Checking for extension updates                              ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /usr/local/bin/vacuumdb -U postgres --all --analyze-in-stages

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

成功:)

还原配置

通过删除临时行来恢复配置:

$ doas nvim /var/postgresql/data/pg_hba.conf

$ # in addition, if you care about restoring the previous one:
$ doas nvim /var/postgresql/data/pg_hba.conf

喜欢下面:

  # TYPE  DATABASE        USER            ADDRESS                 METHOD
- local all postgres trust

配置PostgreSQL 15(可选)

当您对postgresql.conf等具有特定配置等,请应用它们:

$ # create the backup (optional):
$ doas cp -p /var/postgresql/data/postgresql.conf /var/postgresql/data/postgresql.conf.org

$ doas nvim /var/postgresql/data/postgresql.conf

喜欢下面的(例如):

  (...)
+ listen_addresses = '*'
  (...)
+ ssl = on
  (...)

此外,对于postgresql.conf,原始版本在/usr/local/share/postgresql/postgresql.conf.sample中。

所有升级都已经完成。

再次开始守护程序

让我们启动数据库服务器:

$ doas rcctl start postgresql

我希望结果成功:)

删除包装(可选)

您现在可以自由告别,并感谢效果正常的包装:

$ doas pkg_delete postgresql-pg_upgrade

结果是:

postgresql-pg_upgrade-15.2: ok

结论

通过上面的步骤,我们可以将Postgresql 14升级到15。

此外,data-14目录可能会在某些未来变为“旧”。

祝您在最新的OpenBSD

上使用最新的PostgreSQL祝您时光愉快