PostgreSQL Time Travel: Upgrading from Version 9.6 to 14
iDempiere

PostgreSQL Time Travel: Upgrading from Version 9.6 to 14

2023-09-20 （最後更新：2026-02-20） · 50 分鐘 · Ray Lee (System Analyst)

Introduction

PostgreSQL is a powerful, open-source relational database management system that continues to evolve with each new release. Upgrading to a newer version of PostgreSQL can provide improved performance, security enhancements, and access to new features. In this guide, we will walk you through the process of upgrading PostgreSQL on an Ubuntu-based system.

Step 1: Prepare Your System Before you can begin the PostgreSQL upgrade process, it’s essential to prepare your system. Ensure that you have administrative privileges and take a backup of your data to prevent any potential data loss.

sudo apt-get update -y && sudo apt-get upgrade -y

Step 2: Add PostgreSQL Repository You need to add the PostgreSQL repository to access the desired version. This allows you to install the desired PostgreSQL version (in this example, PostgreSQL 14).

 wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
 echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql-pgdg.list > /dev/null
 sudo apt-get update -y

Step 3: Install PostgreSQL Now, you can install the desired PostgreSQL version. In this example, we’ll install PostgreSQL 14.

sudo apt-get install postgresql-14

Step 4: Stop the PostgreSQL Service Before you proceed with the upgrade, you must stop the PostgreSQL service.

bash
sudo systemctl stop postgresql.service

Step 5: Upgrade PostgreSQL

Upgrade from PostgreSQL 9.6

If you’re upgrading from PostgreSQL 9.6, use the following commands:

Upgrade from PostgreSQL 9.6

/usr/lib/postgresql/14/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/9.6/main \
--new-datadir=/var/lib/postgresql/14/main \
--old-bindir=/usr/lib/postgresql/9.6/bin \
--new-bindir=/usr/lib/postgresql/14/bin \
--old-options '-c config_file=/etc/postgresql/9.6/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/14/main/postgresql.conf'

Upgrade from PostgreSQL 10

/usr/lib/postgresql/14/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/10/main \
--new-datadir=/var/lib/postgresql/14/main \
--old-bindir=/usr/lib/postgresql/10/bin \
--new-bindir=/usr/lib/postgresql/14/bin \
--old-options '-c config_file=/etc/postgresql/10/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/14/main/postgresql.conf'

Upgrade from PostgreSQL 9.6 to PostgreSQL 15

/usr/lib/postgresql/15/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/9.6/main \
--new-datadir=/var/lib/postgresql/15/main \
--old-bindir=/usr/lib/postgresql/9.6/bin \
--new-bindir=/usr/lib/postgresql/15/bin \
--old-options '-c config_file=/etc/postgresql/9.6/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/15/main/postgresql.conf'

Step 6: Start the New PostgreSQL Cluster After successfully upgrading PostgreSQL, start the new cluster.

/usr/lib/postgresql/14/bin/pg_ctl start  -D "/var/lib/postgresql/14/main" -o "-c config_file=/etc/postgresql/14/main/postgresql.conf"

Step 7: Perform Consistency Checks Perform consistency checks to ensure everything is in order.

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 user-defined encoding conversions              ok
Checking for user-defined postfix operators                 ok
Checking for incompatible polymorphic functions             ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Checking for invalid "unknown" user columns                 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_clog 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 hash indexes                                   ok
Checking for extension updates                              ok

Upgrade Complete
----------------

Step 8: Complete the Upgrade If the consistency checks pass, your upgrade is complete. Keep in mind that optimizer statistics are not transferred by pg_upgrade. Consider running the following command to optimize your database:

/usr/lib/postgresql/14/bin/vacuumdb --all --analyze-in-stages
Step 9: Clean Up You can delete the old cluster's data files by running the provided script:


./delete_old_cluster.sh

Conclusion

Upgrading PostgreSQL on Ubuntu can be a complex process, but by following this guide, you can safely migrate to a newer version and enjoy the benefits of enhanced performance and security. Always remember to backup your data and test the upgrade process in a non-production environment before applying it to your live system to minimize any potential risks.

English Version

Introduction

PostgreSQL is a powerful, open-source relational database management system that continues to evolve with each new release. Upgrading to a newer version of PostgreSQL can provide improved performance, security enhancements, and access to new features. In this guide, we will walk you through the process of upgrading PostgreSQL on an Ubuntu-based system.

Step 1: Prepare Your System Before you can begin the PostgreSQL upgrade process, it’s essential to prepare your system. Ensure that you have administrative privileges and take a backup of your data to prevent any potential data loss.

sudo apt-get update -y && sudo apt-get upgrade -y

Step 2: Add PostgreSQL Repository You need to add the PostgreSQL repository to access the desired version. This allows you to install the desired PostgreSQL version (in this example, PostgreSQL 14).

 wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
 echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql-pgdg.list > /dev/null
 sudo apt-get update -y

Step 3: Install PostgreSQL Now, you can install the desired PostgreSQL version. In this example, we’ll install PostgreSQL 14.

sudo apt-get install postgresql-14

Step 4: Stop the PostgreSQL Service Before you proceed with the upgrade, you must stop the PostgreSQL service.

bash
sudo systemctl stop postgresql.service

Step 5: Upgrade PostgreSQL

Upgrade from PostgreSQL 9.6

If you’re upgrading from PostgreSQL 9.6, use the following commands:

Upgrade from PostgreSQL 9.6

/usr/lib/postgresql/14/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/9.6/main \
--new-datadir=/var/lib/postgresql/14/main \
--old-bindir=/usr/lib/postgresql/9.6/bin \
--new-bindir=/usr/lib/postgresql/14/bin \
--old-options '-c config_file=/etc/postgresql/9.6/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/14/main/postgresql.conf'

Upgrade from PostgreSQL 10

/usr/lib/postgresql/14/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/10/main \
--new-datadir=/var/lib/postgresql/14/main \
--old-bindir=/usr/lib/postgresql/10/bin \
--new-bindir=/usr/lib/postgresql/14/bin \
--old-options '-c config_file=/etc/postgresql/10/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/14/main/postgresql.conf'

Upgrade from PostgreSQL 9.6 to PostgreSQL 15

/usr/lib/postgresql/15/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/9.6/main \
--new-datadir=/var/lib/postgresql/15/main \
--old-bindir=/usr/lib/postgresql/9.6/bin \
--new-bindir=/usr/lib/postgresql/15/bin \
--old-options '-c config_file=/etc/postgresql/9.6/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/15/main/postgresql.conf'

Step 6: Start the New PostgreSQL Cluster After successfully upgrading PostgreSQL, start the new cluster.

/usr/lib/postgresql/14/bin/pg_ctl start  -D "/var/lib/postgresql/14/main" -o "-c config_file=/etc/postgresql/14/main/postgresql.conf"

Step 7: Perform Consistency Checks Perform consistency checks to ensure everything is in order.

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 user-defined encoding conversions              ok
Checking for user-defined postfix operators                 ok
Checking for incompatible polymorphic functions             ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Checking for invalid "unknown" user columns                 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_clog 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 hash indexes                                   ok
Checking for extension updates                              ok

Upgrade Complete
----------------

Step 8: Complete the Upgrade If the consistency checks pass, your upgrade is complete. Keep in mind that optimizer statistics are not transferred by pg_upgrade. Consider running the following command to optimize your database:

/usr/lib/postgresql/14/bin/vacuumdb --all --analyze-in-stages
Step 9: Clean Up You can delete the old cluster's data files by running the provided script:


./delete_old_cluster.sh

Conclusion

Upgrading PostgreSQL on Ubuntu can be a complex process, but by following this guide, you can safely migrate to a newer version and enjoy the benefits of enhanced performance and security. Always remember to backup your data and test the upgrade process in a non-production environment before applying it to your live system to minimize any potential risks.

日本語版

はじめに

PostgreSQLは、リリースごとに進化を続ける強力なオープンソースのリレーショナルデータベース管理システムです。PostgreSQLの新しいバージョンにアップグレードすることで、パフォーマンスの向上、セキュリティの強化、新機能へのアクセスが可能になります。このガイドでは、Ubuntuベースのシステム上でPostgreSQLをアップグレードする手順を説明します。

ステップ1：システムの準備 PostgreSQLのアップグレードプロセスを開始する前に、システムを準備することが重要です。管理者権限があることを確認し、データのバックアップを取得してデータ損失を防止してください。

sudo apt-get update -y && sudo apt-get upgrade -y

ステップ2：PostgreSQLリポジトリの追加 目的のバージョンにアクセスするために、PostgreSQLリポジトリを追加する必要があります。これにより、目的のPostgreSQLバージョン（この例ではPostgreSQL 14）をインストールできます。

 wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
 echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql-pgdg.list > /dev/null
 sudo apt-get update -y

ステップ3：PostgreSQLのインストール 目的のPostgreSQLバージョンをインストールします。この例では、PostgreSQL 14をインストールします。

sudo apt-get install postgresql-14

ステップ4：PostgreSQLサービスの停止 アップグレードを進める前に、PostgreSQLサービスを停止する必要があります。

bash
sudo systemctl stop postgresql.service

ステップ5：PostgreSQLのアップグレード

PostgreSQL 9.6からのアップグレード

PostgreSQL 9.6からアップグレードする場合は、以下のコマンドを使用します：

PostgreSQL 9.6からのアップグレード

/usr/lib/postgresql/14/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/9.6/main \
--new-datadir=/var/lib/postgresql/14/main \
--old-bindir=/usr/lib/postgresql/9.6/bin \
--new-bindir=/usr/lib/postgresql/14/bin \
--old-options '-c config_file=/etc/postgresql/9.6/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/14/main/postgresql.conf'

PostgreSQL 10からのアップグレード

/usr/lib/postgresql/14/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/10/main \
--new-datadir=/var/lib/postgresql/14/main \
--old-bindir=/usr/lib/postgresql/10/bin \
--new-bindir=/usr/lib/postgresql/14/bin \
--old-options '-c config_file=/etc/postgresql/10/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/14/main/postgresql.conf'

PostgreSQL 9.6からPostgreSQL 15へのアップグレード

/usr/lib/postgresql/15/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/9.6/main \
--new-datadir=/var/lib/postgresql/15/main \
--old-bindir=/usr/lib/postgresql/9.6/bin \
--new-bindir=/usr/lib/postgresql/15/bin \
--old-options '-c config_file=/etc/postgresql/9.6/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/15/main/postgresql.conf'

ステップ6：新しいPostgreSQLクラスターの起動 PostgreSQLのアップグレードが成功したら、新しいクラスターを起動します。

/usr/lib/postgresql/14/bin/pg_ctl start  -D "/var/lib/postgresql/14/main" -o "-c config_file=/etc/postgresql/14/main/postgresql.conf"

ステップ7：整合性チェックの実行 すべてが正常であることを確認するために、整合性チェックを実行します。

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 user-defined encoding conversions              ok
Checking for user-defined postfix operators                 ok
Checking for incompatible polymorphic functions             ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Checking for invalid "unknown" user columns                 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_clog 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 hash indexes                                   ok
Checking for extension updates                              ok

Upgrade Complete
----------------

ステップ8：アップグレードの完了 整合性チェックが通れば、アップグレードは完了です。オプティマイザの統計情報はpg_upgradeでは転送されないことに注意してください。データベースを最適化するために、以下のコマンドの実行を検討してください：

/usr/lib/postgresql/14/bin/vacuumdb --all --analyze-in-stages
ステップ9：クリーンアップ 提供されたスクリプトを実行して、古いクラスターのデータファイルを削除できます：


./delete_old_cluster.sh

まとめ

UbuntuでのPostgreSQLのアップグレードは複雑なプロセスになり得ますが、このガイドに従うことで、新しいバージョンに安全に移行し、パフォーマンスとセキュリティの向上を享受できます。データのバックアップを必ず取得し、本番環境に適用する前に非本番環境でアップグレードプロセスをテストして、潜在的なリスクを最小限に抑えることを常に忘れないでください。

Ray Lee (System Analyst)
作者 Ray Lee (System Analyst)

iDempeire ERP Contributor, 經濟部中小企業處財務管理顧問 李寶瑞

相關文章