##
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
-
--swapMove 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=syncfsis used. Therefore, it is recommended to use--sync-method=fsyncwith--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
最低0.47元/天 解锁文章
扫一扫
792
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
