엑셈

Translated from the Korean original. Some screenshots show the original Korean interface.

※ Contents ※ Upgrade PostgreSQL Minor Version on Linux Upgrade PostgreSQL Minor Version on Windows

Upgrade PostgreSQL Minor Version on Linux

PostgreSQL minor releases only fix frequently encountered bugs, security issues, and data corruption problems, without changing (altering) the architecture of the major version. Because the internal architecture of the major version does not change, compatibility is maintained across minor versions.

There are three ways to perform a minor version upgrade on Linux.

First, a minor upgrade through yum or apt. The server must have internet access and the PostgreSQL repository must be registered. During the upgrade, the service (systemd) is refreshed automatically, so the PostgreSQL server restarts automatically. (Strictly speaking, it runs in the order stop → upgrade → start.)

Second, a minor upgrade through rpm or dpkg, which can be used on servers without internet access. The procedure is the same as upgrading with yum or apt; the only difference is how the installation files are prepared, depending on whether the server can reach the internet.

Third, a method that uses the Source File.

ㅤ	yum	rpm	apt	dpkg	Source File
Installation files	Repository	Prepared separately	Repository	Prepared separately	Prepared separately
Installed version	Latest minor per major	Selectable	Latest minor per major	Selectable	Selectable
Command	yum install -y postgresql{VERSION}-server	rpm -Uvh {Package}	apt install -y --only-upgrade postgresql*	dpkg -i {Package}	ㅤ

This section covers minor version upgrades on the Linux platform in the following order: ① prepare installation files, ② check the PostgreSQL installation, ③ upgrade PostgreSQL, ④ verify the PostgreSQL upgrade. We use CentOS 7.9 and Ubuntu 22.04 as the Linux platforms and upgrade PostgreSQL 14.2 to 14.5.

To verify that the upgrade succeeded, we query the exem_tab table in the postgres database.

1. Prepare Installation Files

Prepare the installation files according to the upgrade method.

Upgrade method	Description
yum	Requires internet access and a registered PostgreSQL yum repository. See PostgreSQL Setup - Installation > Install PostgreSQL on Linux (CentOS - yum)
rpm	Download from yum.postgresql.org/packages See PostgreSQL Setup - Installation > Install PostgreSQL on Linux (CentOS - rpm)
apt	Requires internet access and a registered PostgreSQL apt repository. See PostgreSQL Setup - Installation > Install PostgreSQL on Linux (Ubuntu - apt)
dpkg	Download from apt.postgresql.org/pub/repos/apt/pool/main/p/ See PostgreSQL Setup - Installation > Install PostgreSQL on Linux (Ubuntu- dpkg)
Source File	Download from www.postgresql.org/ftp/source See PostgreSQL Setup - Installation > Install PostgreSQL on Linux (Source File)

2. Check the PostgreSQL Installation

CentOS (Red Hat family)

Output

Ubuntu (Debian family)

Output

Source File

Output

3. Upgrade PostgreSQL

CentOS (Red Hat family) - Using yum

Output

CentOS (Red Hat family) - Using rpm

Output

Ubuntu (Debian family) - Using apt

Output

Ubuntu (Debian family) - Using dpkg

Output

Using Source File

Install the upgrade version of PostgreSQL the same way as the initial installation.

When the installation is complete, stop the running PostgreSQL, rename the directories, and start it again.

Output

4. Verify the PostgreSQL Upgrade

CentOS (Red Hat family)

Output

Ubuntu (Debian family)

Output

Source File

Output

📢 Error related to the libpq.so.5 library when connecting with psql

** Problem

error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory

** Solution

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/postgres/PostgreSQL_Engine/lib

Upgrade PostgreSQL Minor Version on Windows

There are two ways to perform a minor version upgrade on Windows.

First, a minor upgrade using the PostgreSQL installer (exe file). The process is the same as installing PostgreSQL (see PostgreSQL Setup - Installation > Install PostgreSQL on Windows - Installer), and the installer detects the existing installation folder and performs the upgrade. The upgrade runs in the order stop service → remove service → upgrade → register service → start service. So downtime lasts as long as the upgrade (installation) takes.

Second, a minor upgrade using the binary file (zip archive). This method is also the same as installing PostgreSQL (see PostgreSQL Setup - Installation > Install PostgreSQL on Windows - Binary File); you can upgrade just by extracting the archive and renaming the folder. The upgrade runs in the order extract → stop service → rename folder → start service, which keeps downtime to a minimum.

This section covers minor version upgrades on the Windows platform in the following order: ① prepare installation files, ② check the PostgreSQL installation, ③ upgrade PostgreSQL, ④ verify the PostgreSQL upgrade. We upgrade PostgreSQL 14.2 to 14.5 on the Windows 10 platform. To verify that the upgrade succeeded, we query the exem_tab table in the postgres database.

1. Prepare Installation Files

Upgrade method	Description
exe File	Only the latest minor version can be downloaded from www.enterprisedb.com/downloads/postgres-postgresql-downloads.
Binary File	Only the latest minor version can be downloaded from https://www.enterprisedb.com/download-postgresql-binaries.

2. Check the PostgreSQL Installation

Output

3. Upgrade PostgreSQL

Installer

Step 1. Run the PostgreSQL installer

Run the PostgreSQL installer. (File name : postgresql-14.5-1-windows-x64.exe

Step 2. Select the components to install and click Next

Select (or deselect) the components to install and click Next

PostgreSQL Server : includes the PostgreSQL Database Server

pgAdmin4 : includes the PostgreSQL GUI management tool

Stack Builder : includes Management, integration, Migration, Replication, Geospatial, and more

Command Line Tools : includes command line interfaces such as psql, pg_dump, and pg_restore

Step 3. An existing PostgreSQL installation is detected; confirm whether to upgrade it. Click Next

An existing PostgreSQL installation is detected; confirm whether to upgrade it. Click Next

Step 4. Confirm whether to use the existing PostgreSQL data directory and port. Click Next

Step 5. Review the settings from Step 3 to Step 5 and click Next

Step 6. Click Next to start the installation

Step 7. A warning appears if the PostgreSQL Server is running. Click OK to continue the upgrade

📢 Minor upgrade sequence when the PostgreSQL Server is running

Stop PostgreSQL Server service → remove PostgreSQL Server service → warning appears → OK → upgrade starts → upgrade completes → register PostgreSQL Server service → start PostgreSQL Server service