PostgreSQL 18 升级新体验：pg_upgrade --swap 极简教程

PostgreSQL 18 升级新体验：pg_upgrade --swap 极简教程

1、About pg_upgrade --swap

https://www.postgresql.org/about/news/postgresql-18-beta-1-released-3070/

Major version upgrade experience

Before PostgreSQL 18, an important step after performing a major version upgrade was to run the ANALYZE to generate statistics, which is a critical component of helping PostgreSQL to select the most efficient query plan. Based on the size and overall activity of a PostgreSQL cluster, this could be a time consuming process, and potentially impact query performance until the process completed. PostgreSQL 18 introduces the ability to keep planner statistics through a major version upgrade, which helps an upgraded cluster to get to its expected performance state sooner once it’s available.

Additionally, pg_upgrade, the utility used to facilitate a major version upgrade, added several performance enhancements to help accelerate upgrades with many objects, such as tables and sequences. This release also allows pg_upgrade to process its checks in parallel based on the settings of the --jobs flag, and also adds the --swap flag, which swaps upgrade directories instead of copying, cloning, or linking files.

https://www.postgresql.org/docs/18/pgupgrade.html

--swap

Move the data directories from the old cluster to the new cluster. Then, replace the catalog files with those generated for the new cluster. This mode can outperform --link, --clone, --copy, and --copy-file-range, especially on clusters with many relations.However, this mode creates many garbage files in the old cluster, which can prolong the file synchronization step if --sync-method=syncfs is used. Therefore, it is recommended to use --sync-method=fsync with --swap.Additionally, once the file transfer step begins, the old cluster will be destructively modified and therefore will no longer be safe to start. See Step 17 for details.

2、Practical process

[root@pgdb01 ~]# su - postgres
[postgres@pgdb01:/home/postgres]$pg_ctl start
waiting for server to start....2025-05-22 10:09:02.517 CST [3694] LOG:  redirecting log output to logging collector process
2025-05-22 10:09:02.517 CST [3694] HINT:  Future log output will appear in directory "/postgresql/log".
 done
server started
[postgres@pgdb01:/home/postgres]$psql
psql (17.4)
Type "help" for help.

postgres=# \l+
                                                                                  List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-----------------+---------+-------+--------+-----------+-----------------------+---------+------------+--------------------------------------------
 demo      | postgres | UTF8     | libc            | C       | C     |        |           |                       | 281 MB  | pg_default |
 postgres  | postgres | UTF8     | libc            | C       | C     |        |           |                       | 7699 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | libc            | C       | C     |        |           | =c/postgres          +| 7545 kB | pg_default | unmodifiable empty database
           |          |          |                 |         |       |        |           | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | libc            | C       | C     |        |           | =c/postgres          +| 7617 kB | pg_default | default template for new databases
           |          |          |                 |         |       |        |           | postgres=CTc/postgres |         |            |
(4 rows)

postgres=# \q
[postgres@pgdb01:/home/postgres]$psql --version
psql (PostgreSQL) 17.4

[postgres@pgdb01:/home/postgres]$psql
psql (17.4)
Type "help" for help.

postgres=# show server_version;
 server_version
----------------
 17.4
(1 row)

postgres=# \q

[postgres@pgdb01:/home/postgres]$exit
logout
[root@pgdb01 ~]# chown -R postgres:postgres /postgresql/backup/*
[root@pgdb01 ~]# su - postgres
[postgres@pgdb01:/home/postgres]$cd /postgresql/backup/
[postgres@pgdb01:/postgresql/backup]$ll
total 178904
-rwxrwxrwx  1 postgres postgres 103865532 Feb 21  2018 demo-small-en-20170815.sql
-rw-r--r--  1 postgres postgres  22187739 Mar 22 20:03 demo-small-en.zip
drwxr-xr-x  6 postgres postgres      4096 Feb 28 11:53 postgresql-17.4
-rw-r--r--  1 postgres postgres  28056622 Feb 28 11:50 postgresql-17.4.tar.gz
-rw-r--r--  1 postgres postgres  29048976 May 22 10:11 postgresql-18beta1.tar.gz
-rwxrwxr-x. 1 postgres postgres     24777 Jan 22  2024 uuid-devel-1.6.2-55.el9.x86_64.rpm
[postgres@pgdb01:/postgresql/backup]$tar -zxvf postgresql-18beta1.tar.gz

[postgres@pgdb01:/postgresql/backup]$cd postgresql-18beta1/
[postgres@pgdb01:/postgresql/backup/postgresql-18beta1]$ll
total 832
-rw-r--r--  1 postgres postgres    365 May  6 04:25 aclocal.m4
drwxr-xr-x  2 postgres postgres   4096 May  6 04:25 config
-rwxr-xr-x  1 postgres postgres 588907 May  6 04:25 configure
-rw-r--r--  1 postgres postgres  88909 May  6 04:25 configure.ac
drwxr-xr-x 61 postgres postgres   4096 May  6 04:25 contrib
-rw-r--r--  1 postgres postgres   1192 May  6 04:25 COPYRIGHT
drwxr-xr-x  3 postgres postgres     87 May  6 04:25 doc
-rw-r--r--  1 postgres postgres   4176 May  6 04:25 GNUmakefile.in
-rw-r--r--  1 postgres postgres    277 May  6 04:25 HISTORY
-rw-r--r--  1 postgres postgres   1825 May  6 04:25 Makefile
-rw-r--r--  1 postgres postgres 121002 May  6 04:25 meson.build
-rw-r--r--  1 postgres postgres   6641 May  6 04:25 meson_options.txt
-rw-r--r--  1 postgres postgres    989 May  6 04:25 README.md
drwxr-xr-x 16 postgres postgres   4096 May  6 04:25 src

[postgres@pgdb01:/postgresql/backup/postgresql-18beta1]$export PREFIX=/postgresql/app/pg180

[postgres@pgdb01:/postgresql/backup/postgresql-18beta1]$./configure --prefix=${PREFIX} --with-blocksize=8 --with-segsize=1 --with-wal-blocksize=8 --enable-nls="zh_CN en_US" --with-llvm LL                    VM_CONFIG=/usr/bin/llvm-config CLANG=/usr/bin/clang --with-icu --with-tcl --with-perl --with-python --with-gssapi --with-pam --with-ldap --with-readline --with-libedit-preferred --with-li                    bxml --with-libxslt --with-lz4 --with-zstd --with-gnu-ld --with-openssl --with-uuid=LIB --with-ossp-uuid --with-zlib --with-systemd


[postgres@pgdb01:/postgresql/backup/postgresql-18beta1]$make world -j 4
[postgres@pgdb01:/postgresql/backup/postgresql-18beta1]$make install-world -j 4

[postgres@pgdb01:/postgresql/backup/postgresql-18beta1]$cd /postgresql/app/pg180/
[postgres@pgdb01:/postgresql/app/pg180]$cd /postgresql/app/pg180/bin/

[postgres@pgdb01:/postgresql/app/pg180/bin]$./initdb -D /postgresql/data18 -E UTF8 --locale=C -U postgres -W
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are enabled.

Enter new superuser password:
Enter it again:

creating directory /postgresql/data18 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /postgresql/data18 -l logfile start

[postgres@pgdb01:/postgresql/app/pg180/bin]$vi /postgresql/data18/postgresql.conf


[postgres@pgdb01:/postgresql/app/pg180/bin]$/postgresql/app/pg180/bin/pg_upgrade -b /postgresql/app/pg174/bin -B /postgresql/app/pg180/bin -d /postgresql/data/ -D /postgresql/data18 -                        p 5432 -P 5433 -c
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                     ok

old cluster does not use data checksums but the new one does
Failure, exiting
[postgres@pgdb01:/postgresql/app/pg180/bin]$rm -fr /postgresql/data18/*
[postgres@pgdb01:/postgresql/app/pg180/bin]$./initdb --help
  -k, --data-checksums      use data page checksums

      --no-data-checksums   do n