##
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 not use data page checksums
[postgres@pgdb01:/postgresql/app/pg180/bin]$./initdb -D /postgresql/data18 -E UTF8 --locale=C -U postgres -W --no-data-checksums
[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
Checking database connection settings could not translate host name "." to address: Name or service not known
Failure, exiting
[postgres@pgdb01:/postgresql/app/pg180/bin]$vi /postgresql/data/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
Checking database connection settings could not translate host name "." to address: Name or service not known
Failure, exiting
[postgres@pgdb01:/postgresql/app/pg180/bin]$pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2025-05-22 10:22:11.241 CST [18376] LOG: redirecting log output to logging collector process
2025-05-22 10:22:11.241 CST [18376] HINT: Future log output will appear in directory "/postgresql/log".
done
server started
[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
Checking database connection settings ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for valid logical replication slots ok
Checking for subscription state ok
Checking data type usage ok
Checking for objects affected by Unicode update 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
*Clusters are compatible*
[postgres@pgdb01:/postgresql/app/pg180/bin]$ls -lsa /postgresql/data18
total 64
4 drwx------ 20 postgres postgres 4096 May 22 10:22 .
0 drwxrwxr-x 9 postgres postgres 94 May 22 10:17 ..
0 drwx------ 5 postgres postgres 33 May 22 10:20 base
4 drwx------ 2 postgres postgres 4096 May 22 10:22 global
0 drwx------ 2 postgres postgres 6 May 22 10:20 pg_commit_ts
0 drwx------ 2 postgres postgres 6 May 22 10:20 pg_dynshmem
8 -rw------- 1 postgres postgres 5721 May 22 10:20 pg_hba.conf
4 -rw------- 1 postgres postgres 2640 May 22 10:20 pg_ident.conf
0 drwx------ 4 postgres postgres 68 May 22 10:22 pg_logical
0 drwx------ 4 postgres postgres 36 May 22 10:20 pg_multixact
0 drwx------ 2 postgres postgres 6 May 22 10:20 pg_notify
0 drwx------ 2 postgres postgres 6 May 22 10:20 pg_replslot
0 drwx------ 2 postgres postgres 6 May 22 10:20 pg_serial
0 drwx------ 2 postgres postgres 6 May 22 10:20 pg_snapshots
0 drwx------ 2 postgres postgres 25 May 22 10:22 pg_stat
0 drwx------ 2 postgres postgres 6 May 22 10:20 pg_stat_tmp
0 drwx------ 2 postgres postgres 18 May 22 10:20 pg_subtrans
0 drwx------ 2 postgres postgres 6 May 22 10:20 pg_tblspc
0 drwx------ 2 postgres postgres 6 May 22 10:20 pg_twophase
0 drwx------ 4 postgres postgres 60 May 22 10:22 pg_upgrade_output.d
4 -rw------- 1 postgres postgres 3 May 22 10:20 PG_VERSION
0 drwx------ 4 postgres postgres 77 May 22 10:20 pg_wal
0 drwx------ 2 postgres postgres 18 May 22 10:20 pg_xact
4 -rw------- 1 postgres postgres 88 May 22 10:20 postgresql.auto.conf
32 -rw------- 1 postgres postgres 32381 May 22 10:21 postgresql.conf
4 -rw------- 1 postgres postgres 343 May 22 10:22 postmaster.opts
[postgres@pgdb01:/postgresql/app/pg180/bin]$ls -lsa /postgresql/data
total 264
4 drwx------ 19 postgres postgres 4096 May 22 10:22 .
0 drwxrwxr-x 9 postgres postgres 94 May 22 10:17 ..
4 -rw------- 1 postgres postgres 216 Mar 22 21:30 backup_label.old
184 -rw------- 1 postgres postgres 187965 Mar 22 21:30 backup_manifest
0 drwx------ 6 postgres postgres 46 Mar 22 21:30 base
4 -rw------- 1 postgres postgres 36 May 22 10:22 current_logfiles
4 drwx------ 2 postgres postgres 4096 May 22 10:22 global
0 drwx------ 2 postgres postgres 6 Mar 22 21:30 pg_commit_ts
0 drwx------ 2 postgres postgres 6 Mar 22 21:30 pg_dynshmem
8 -rw------- 1 postgres postgres 5804 Mar 22 21:30 pg_hba.conf
4 -rw------- 1 postgres postgres 2640 Mar 22 21:30 pg_ident.conf
0 drwx------ 4 postgres postgres 68 May 22 10:22 pg_logical
0 drwx------ 4 postgres postgres 36 Mar 22 21:30 pg_multixact
0 drwx------ 2 postgres postgres 6 Mar 22 21:30 pg_notify
0 drwx------ 3 postgres postgres 25 Mar 22 21:37 pg_replslot
0 drwx------ 2 postgres postgres 6 Mar 22 21:30 pg_serial
0 drwx------ 2 postgres postgres 6 Mar 22 21:30 pg_snapshots
0 drwx------ 2 postgres postgres 6 May 22 10:22 pg_stat
0 drwx------ 2 postgres postgres 35 May 22 10:22 pg_stat_tmp
0 drwx------ 2 postgres postgres 18 Mar 22 21:35 pg_subtrans
0 drwx------ 2 postgres postgres 6 Mar 22 21:30 pg_tblspc
0 drwx------ 2 postgres postgres 6 Mar 22 21:30 pg_twophase
4 -rw------- 1 postgres postgres 3 Mar 22 21:30 PG_VERSION
4 drwx------ 4 postgres postgres 4096 Mar 22 21:45 pg_wal
0 drwx------ 2 postgres postgres 18 Mar 22 21:30 pg_xact
4 -rw------- 1 postgres postgres 88 Mar 22 21:38 postgresql.auto.conf
32 -rw------- 1 postgres postgres 31540 May 22 10:22 postgresql.conf
4 -rw------- 1 postgres postgres 35 May 22 10:22 postmaster.opts
4 -rw------- 1 postgres postgres 75 May 22 10:22 postmaster.pid
[postgres@pgdb01:/postgresql/app/pg180/bin]$
[postgres@pgdb01:/postgresql/app/pg180/bin]$du -sh /postgresql/data18
40M /postgresql/data18
[postgres@pgdb01:/postgresql/app/pg180/bin]$du -sh /postgresql/data
401M /postgresql/data
[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 -j 4 --swap
There seems to be a postmaster servicing the old cluster.
Please shutdown that postmaster and try again.
Failure, exiting
[postgres@pgdb01:/postgresql/app/pg180/bin]$pg_ctl stop
waiting for server to shut down.... done
server stopped
[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 -j 4 --swap
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database connection settings ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for valid logical replication slots ok
Checking for subscription state ok
Checking data type usage ok
Checking for objects affected by Unicode update 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
------------------
Setting locale and encoding for new cluster ok
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
*failure*
Consult the last few lines of "/postgresql/data18/pg_upgrade_output.d/20250522T102512.091/log/pg_upgrade_utility.log" for
the probable cause of the failure.
Failure, exiting
[postgres@pgdb01:/postgresql/app/pg180/bin]$cat /postgresql/data18/pg_upgrade_output.d/20250522T102512.091/log/pg_upgrade_utility.log
-----------------------------------------------------------------
pg_upgrade run on Thu May 22 10:25:12 2025
-----------------------------------------------------------------
command: "/postgresql/app/pg180/bin/pg_dumpall" --host /postgresql/app/pg180/bin --port 5432 --username postgres --globals-only --quote-all-identifiers --binary-upgrade --no-sync -f "/postgresql/data18/pg_upgrade_output.d/20250522T102512.091/dump/pg_upgrade_dump_globals.sql" >> "/postgresql/data18/pg_upgrade_output.d/20250522T102512.091/log/pg_upgrade_utility.log " 2>&1
command: "/postgresql/app/pg180/bin/vacuumdb" --host /postgresql/app/pg180/bin --port 5433 --username postgres --all --analyze >> "/postgresql/data18/pg_upgrade_output.d/20250522T102 512.091/log/pg_upgrade_utility.log" 2>&1
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"
command: "/postgresql/app/pg180/bin/vacuumdb" --host /postgresql/app/pg180/bin --port 5433 --username postgres --all --freeze >> "/postgresql/data18/pg_upgrade_output.d/20250522T1025 12.091/log/pg_upgrade_utility.log" 2>&1
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"
command: cp -Rf "/postgresql/data/pg_xact" "/postgresql/data18/pg_xact" >> "/postgresql/data18/pg_upgrade_output.d/20250522T102512.091/log/pg_upgrade_utility.log" 2>&1
command: "/postgresql/app/pg180/bin/pg_resetwal" -f -u 731 "/postgresql/data18" >> "/postgresql/data18/pg_upgrade_output.d/20250522T102512.091/log/pg_upgrade_utility.log" 2>&1
Write-ahead log reset
command: "/postgresql/app/pg180/bin/pg_resetwal" -f -x 902 "/postgresql/data18" >> "/postgresql/data18/pg_upgrade_output.d/20250522T102512.091/log/pg_upgrade_utility.log" 2>&1
Write-ahead log reset
command: "/postgresql/app/pg180/bin/pg_resetwal" -f -e 0 "/postgresql/data18" >> "/postgresql/data18/pg_upgrade_output.d/20250522T102512.091/log/pg_upgrade_utility.log" 2>&1
Write-ahead log reset
command: "/postgresql/app/pg180/bin/pg_resetwal" -f -c 902,902 "/postgresql/data18" >> "/postgresql/data18/pg_upgrade_output.d/20250522T102512.091/log/pg_upgrade_utility.log" 2>&1
Write-ahead log reset
command: cp -Rf "/postgresql/data/pg_multixact/offsets" "/postgresql/data18/pg_multixact/offsets" >> "/postgresql/data18/pg_upgrade_output.d/20250522T102512.091/log/pg_upgrade_utility .log" 2>&1
command: cp -Rf "/postgresql/data/pg_multixact/members" "/postgresql/data18/pg_multixact/members" >> "/postgresql/data18/pg_upgrade_output.d/20250522T102512.091/log/pg_upgrade_utility .log" 2>&1
command: "/postgresql/app/pg180/bin/pg_resetwal" -O 0 -m 1,1 "/postgresql/data18" >> "/postgresql/data18/pg_upgrade_output.d/20250522T102512.091/log/pg_upgrade_utility.log" 2>&1
Write-ahead log reset
command: "/postgresql/app/pg180/bin/pg_resetwal" -l 000000010000000000000029 "/postgresql/data18" >> "/postgresql/data18/pg_upgrade_output.d/20250522T102512.091/log/pg_upgrade_utility .log" 2>&1
Write-ahead log reset
command: "/postgresql/app/pg180/bin/psql" --echo-queries --set ON_ERROR_STOP=on --no-psqlrc --dbname=template1 --host /postgresql/app/pg180/bin --port 5433 --username postgres -f "/po stgresql/data18/pg_upgrade_output.d/20250522T102512.091/dump/pg_upgrade_dump_globals.sql" >> "/postgresql/data18/pg_upgrade_output.d/20250522T102512.091/log/pg_upgrade_utility.log" 2> &1
/postgresql/app/pg180/bin/psql: symbol lookup error: /postgresql/app/pg180/bin/psql: undefined symbol: PQservice
[postgres@pgdb01:/postgresql/app/pg180/bin]$cat ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
umask 022
export LANG=en_US.UTF8
export PS1="[`whoami`@`hostname`:"'$PWD]$'
#export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=5432
export PGDATA=/postgresql/data
export PGHOME=/postgresql/app/pgdb
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH:.
export DATE=`date +"%Y%m%d%H%M"`
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
alias log="tail -100f `cat /postgresql/data/current_logfiles |awk '{print$2}'`"
[postgres@pgdb01:/postgresql/app/pg180/bin]$export LD_LIBRARY_PATH=/postgresql/app/pg180/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
[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 -j 4 --swap
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database connection settings ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for valid logical replication slots ok
Checking for subscription state ok
Checking data type usage ok
Checking for objects affected by Unicode update 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
------------------
Setting locale and encoding for new cluster ok
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 connection to server on socket "/postgresql/app/pg180/bin/.s.PGSQL.5433" failed: FATAL: could not open relation with OID 2610
Failure, exiting
[postgres@pgdb01:/postgresql/app/pg180/bin]$rm -fr /postgresql/data18/*
[postgres@pgdb01:/postgresql/app/pg180/bin]$./initdb -D /postgresql/data18 -E UTF8 --locale=C -U postgres -W --no-data-checksums
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 disabled.
Enter new superuser password:
Enter it again:
fixing permissions on existing 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]$
[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 -j 4 --swap
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database connection settings ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for valid logical replication slots ok
Checking for subscription state ok
Checking data type usage ok
Checking for objects affected by Unicode update 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
------------------
Setting locale and encoding for new cluster ok
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
Adding ".old" suffix to old "global/pg_control" ok
Because "swap" mode was used, the old cluster can no longer be
safely started.
Swapping data directories
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
----------------
Some statistics are not transferred by pg_upgrade.
Once you start the new server, consider running these two commands:
/postgresql/app/pg180/bin/vacuumdb --all --analyze-in-stages --missing-stats-only
/postgresql/app/pg180/bin/vacuumdb --all --analyze-only
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
[postgres@pgdb01:/postgresql/app/pg180/bin]$ls -lsa /postgresql/app
total 0
0 drwxrwxr-x. 4 postgres postgres 44 May 22 10:16 .
0 drwxrwxr-x 9 postgres postgres 94 May 22 10:17 ..
0 drwxr-xr-x 6 postgres postgres 56 Feb 28 11:59 pg174
0 drwxr-xr-x 6 postgres postgres 56 May 22 10:16 pg180
0 lrwxrwxrwx 1 postgres postgres 5 Feb 28 12:01 pgdb -> pg174
[postgres@pgdb01:/postgresql/app/pg180/bin]$rm -fr /postgresql/app/pgdb
[postgres@pgdb01:/postgresql/app/pg180/bin]$cd /postgresql/app
[postgres@pgdb01:/postgresql/app]$ln -s pg180 pgdb
[postgres@pgdb01:/postgresql/app]$cat ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
umask 022
export LANG=en_US.UTF8
export PS1="[`whoami`@`hostname`:"'$PWD]$'
#export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=5432
export PGDATA=/postgresql/data
export PGHOME=/postgresql/app/pgdb
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH:.
export DATE=`date +"%Y%m%d%H%M"`
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
alias log="tail -100f `cat /postgresql/data/current_logfiles |awk '{print$2}'`"
[postgres@pgdb01:/postgresql/app]$du -sh /postgresql/data
160M /postgresql/data
[postgres@pgdb01:/postgresql/app]$du -sh /postgresql/data18
322M /postgresql/data18
[postgres@pgdb01:/postgresql/app]$mv /postgresql/data /postgresql/data17
[postgres@pgdb01:/postgresql/app]$mv /postgresql/data18 /postgresql/data
[postgres@pgdb01:/postgresql/app]$source ~/.bash_profile
cat: /postgresql/data/current_logfiles: No such file or directory
[postgres@pgdb01:/postgresql/app]$pg_ctl --version
pg_ctl (PostgreSQL) 18beta1
[postgres@pgdb01:/postgresql/app]$pg_ctl start
waiting for server to start....2025-05-22 10:38:45.338 CST [20328] LOG: starting PostgreSQL 18beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit
2025-05-22 10:38:45.338 CST [20328] LOG: listening on IPv4 address "0.0.0.0", port 5433
2025-05-22 10:38:45.338 CST [20328] LOG: listening on IPv6 address "::", port 5433
2025-05-22 10:38:45.340 CST [20328] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433"
2025-05-22 10:38:45.344 CST [20334] LOG: database system was shut down at 2025-05-22 10:35:54 CST
2025-05-22 10:38:45.346 CST [20328] LOG: database system is ready to accept connections
done
server started
[postgres@pgdb01:/postgresql/app]$psql
psql: error: connection to server on socket "/postgresql/data/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?
[postgres@pgdb01:/postgresql/app]$log
^C
[postgres@pgdb01:/postgresql/app]$tail -100f /postgresql/log/pgdb_
pgdb_Fri.log pgdb_Sat.log pgdb_Thu.log
[postgres@pgdb01:/postgresql/app]$vi /postgresql/data
data/ data17/
[postgres@pgdb01:/postgresql/app]$vi /postgresql/data/postgresql.conf
[postgres@pgdb01:/postgresql/app]$vi /postgresql/data17/postgresql.conf
[postgres@pgdb01:/postgresql/app]$
[postgres@pgdb01:/postgresql/app]$
[postgres@pgdb01:/postgresql/app]$
[postgres@pgdb01:/postgresql/app]$pg_ctl start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2025-05-22 10:40:47.213 CST [20450] FATAL: lock file "postmaster.pid" already exists
2025-05-22 10:40:47.213 CST [20450] HINT: Is another postmaster (PID 20328) running in data directory "/postgresql/data"?
stopped waiting
pg_ctl: could not start server
Examine the log output.
[postgres@pgdb01:/postgresql/app]$psql
psql: error: connection to server on socket "/postgresql/data/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?
[postgres@pgdb01:/postgresql/app]$pg_ctl stop
2025-05-22 10:40:56.879 CST [20328] LOG: received fast shutdown request
waiting for server to shut down...2025-05-22 10:40:56.880 CST [20328] LOG: aborting any active transactions
.2025-05-22 10:40:56.882 CST [20328] LOG: background worker "logical replication launcher" (PID 20337) exited with exit code 1
2025-05-22 10:40:56.882 CST [20332] LOG: shutting down
2025-05-22 10:40:56.883 CST [20332] LOG: checkpoint starting: shutdown immediate
2025-05-22 10:40:56.889 CST [20332] LOG: checkpoint complete: wrote 82 buffers (0.5%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.005 s, sync=0.001 s, total=0.007 s; sync files=24, longest=0.001 s, average=0.001 s; distance=365 kB, estimate=365 kB; lsn=0/2B05B780, redo lsn=0/2B05B780
2025-05-22 10:40:56.895 CST [20328] LOG: database system is shut down
done
server stopped
[postgres@pgdb01:/postgresql/app]$pg_ctl start
waiting for server to start....2025-05-22 10:40:59.102 CST [20458] LOG: starting PostgreSQL 18beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit
2025-05-22 10:40:59.103 CST [20458] LOG: listening on IPv4 address "0.0.0.0", port 5432
2025-05-22 10:40:59.103 CST [20458] LOG: listening on IPv6 address "::", port 5432
2025-05-22 10:40:59.104 CST [20458] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-05-22 10:40:59.108 CST [20464] LOG: database system was shut down at 2025-05-22 10:40:56 CST
2025-05-22 10:40:59.110 CST [20458] LOG: database system is ready to accept connections
done
server started
[postgres@pgdb01:/postgresql/app]$psql
psql: error: connection to server on socket "/postgresql/data/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?
[postgres@pgdb01:/postgresql/app]$cat /postgresql/data17/postgresql.conf
[postgres@pgdb01:/postgresql/app]$vi /postgresql/data/postgresql.conf
[postgres@pgdb01:/postgresql/app]$pg_ctl stop
waiting for server to shut down....2025-05-22 10:41:56.630 CST [20458] LOG: received fast shutdown request
2025-05-22 10:41:56.631 CST [20458] LOG: aborting any active transactions
2025-05-22 10:41:56.634 CST [20458] LOG: background worker "logical replication launcher" (PID 20467) exited with exit code 1
2025-05-22 10:41:56.635 CST [20462] LOG: shutting down
2025-05-22 10:41:56.635 CST [20462] LOG: checkpoint starting: shutdown immediate
2025-05-22 10:41:56.638 CST [20462] LOG: checkpoint complete: wrote 0 buffers (0.0%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.004 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB; lsn=0/2B05B830, redo lsn=0/2B05B830
2025-05-22 10:41:56.645 CST [20458] LOG: database system is shut down
done
server stopped
[postgres@pgdb01:/postgresql/app]$pg_ctl start
waiting for server to start....2025-05-22 10:41:58.526 CST [20554] LOG: redirecting log output to logging collector process
2025-05-22 10:41:58.526 CST [20554] HINT: Future log output will appear in directory "/postgresql/log".
done
server started
[postgres@pgdb01:/postgresql/app]$
[postgres@pgdb01:/postgresql/app]$
[postgres@pgdb01:/postgresql/app]$psql
psql: error: connection to server on socket "/postgresql/data/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?
[postgres@pgdb01:/postgresql/app]$psql -h localhost
psql (18beta1)
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 | | | | 7894 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | libc | C | C | | | =c/postgres +| 7910 kB | pg_default | unmodifiable empty database
| | | | | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | libc | C | C | | | postgres=CTc/postgres+| 7737 kB | pg_default | default template for new databases
| | | | | | | | =c/postgres | | |
(4 rows)
postgres=# \q
[postgres@pgdb01:/postgresql/app]$vi /postgresql/data/postgresql.conf
[postgres@pgdb01:/postgresql/app]$pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2025-05-22 10:42:46.151 CST [20635] LOG: redirecting log output to logging collector process
2025-05-22 10:42:46.151 CST [20635] HINT: Future log output will appear in directory "/postgresql/log".
done
server started
[postgres@pgdb01:/postgresql/app]$psql
psql (18beta1)
Type "help" for help.
postgres=# \q
[postgres@pgdb01:/postgresql/app]$/postgresql/app/pg180/bin/vacuumdb --all --analyze-in-stages --missing-stats-only
vacuumdb: processing database "demo": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "demo": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "demo": Generating default (full) optimizer statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
[postgres@pgdb01:/postgresql/app]$/postgresql/app/pg180/bin/vacuumdb --all --analyze-only
vacuumdb: vacuuming database "demo"
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"
[postgres@pgdb01:/postgresql/app]$ll
total 0
drwxr-xr-x 6 postgres postgres 56 Feb 28 11:59 pg174
drwxr-xr-x 6 postgres postgres 56 May 22 10:16 pg180
lrwxrwxrwx 1 postgres postgres 5 May 22 10:37 pgdb -> pg180
[postgres@pgdb01:/postgresql/app]$
[postgres@pgdb01:/postgresql/app]$cd /postgresql/app/pg180/bin
[postgres@pgdb01:/postgresql/app/pg180/bin]$ll
total 17040
-rwxr-xr-x 1 postgres postgres 109128 May 22 10:16 clusterdb
-rwxr-xr-x 1 postgres postgres 112936 May 22 10:16 createdb
-rwxr-xr-x 1 postgres postgres 113848 May 22 10:16 createuser
-rwx------ 1 postgres postgres 37 May 22 10:35 delete_old_cluster.sh
-rwxr-xr-x 1 postgres postgres 108304 May 22 10:16 dropdb
-rwxr-xr-x 1 postgres postgres 108240 May 22 10:16 dropuser
-rwxr-xr-x 1 postgres postgres 908080 May 22 10:16 ecpg
-rwxr-xr-x 1 postgres postgres 200416 May 22 10:16 initdb
-rwxr-xr-x 1 postgres postgres 62320 May 22 10:16 oid2name
-rwxr-xr-x 1 postgres postgres 144520 May 22 10:16 pg_amcheck
-rwxr-xr-x 1 postgres postgres 58592 May 22 10:16 pg_archivecleanup
-rwxr-xr-x 1 postgres postgres 213104 May 22 10:16 pg_basebackup
-rwxr-xr-x 1 postgres postgres 240192 May 22 10:16 pgbench
-rwxr-xr-x 1 postgres postgres 86096 May 22 10:16 pg_checksums
-rwxr-xr-x 1 postgres postgres 162072 May 22 10:16 pg_combinebackup
-rwxr-xr-x 1 postgres postgres 52696 May 22 10:16 pg_config
-rwxr-xr-x 1 postgres postgres 63320 May 22 10:16 pg_controldata
-rwxr-xr-x 1 postgres postgres 135424 May 22 10:16 pg_createsubscriber
-rwxr-xr-x 1 postgres postgres 78608 May 22 10:16 pg_ctl
-rwxr-xr-x 1 postgres postgres 506240 May 22 10:16 pg_dump
-rwxr-xr-x 1 postgres postgres 285080 May 22 10:16 pg_dumpall
-rwxr-xr-x 1 postgres postgres 107744 May 22 10:16 pg_isready
-rwxr-xr-x 1 postgres postgres 126288 May 22 10:16 pg_receivewal
-rwxr-xr-x 1 postgres postgres 125864 May 22 10:16 pg_recvlogical
-rwxr-xr-x 1 postgres postgres 78312 May 22 10:16 pg_resetwal
-rwxr-xr-x 1 postgres postgres 278344 May 22 10:16 pg_restore
-rwxr-xr-x 1 postgres postgres 194976 May 22 10:16 pg_rewind
-rwxr-xr-x 1 postgres postgres 64168 May 22 10:16 pg_test_fsync
-rwxr-xr-x 1 postgres postgres 53152 May 22 10:16 pg_test_timing
-rwxr-xr-x 1 postgres postgres 232424 May 22 10:16 pg_upgrade
-rwxr-xr-x 1 postgres postgres 147232 May 22 10:16 pg_verifybackup
-rwxr-xr-x 1 postgres postgres 124408 May 22 10:16 pg_waldump
-rwxr-xr-x 1 postgres postgres 73144 May 22 10:16 pg_walsummary
-rwxr-xr-x 1 postgres postgres 10838424 May 22 10:16 postgres
-rwxr-xr-x 1 postgres postgres 870912 May 22 10:16 psql
-rwxr-xr-x 1 postgres postgres 122432 May 22 10:16 reindexdb
-rwxr-xr-x 1 postgres postgres 127064 May 22 10:16 vacuumdb
-rwxr-xr-x 1 postgres postgres 58488 May 22 10:16 vacuumlo
[postgres@pgdb01:/postgresql/app/pg180/bin]$cat delete_old_cluster.sh
#!/bin/sh
rm -rf '/postgresql/data'
[postgres@pgdb01:/postgresql/app/pg180/bin]$rm -fr delete_old_cluster.sh
[postgres@pgdb01:/postgresql/app/pg180/bin]$
[postgres@pgdb01:/postgresql/app/pg180/bin]$
[postgres@pgdb01:/postgresql/app/pg180/bin]$cd
[postgres@pgdb01:/home/postgres]$du -sh /postgresql/data
data/ data17/
[postgres@pgdb01:/home/postgres]$du -sh /postgresql/data17
160M /postgresql/data17
[postgres@pgdb01:/home/postgres]$
3、Summary
1.PostgreSQL 18 enables data-checksums by default when using initdb. If it needs to be prohibited, you need to use the parameter**–no-data-checksums**.
2.If the value of parameter unix_socket_directories contains**.**, you will encounter an error when upgrading.
Checking database connection settings could not translate host name "." to address: Name or service not known
3.When upgrading, you need to specify a new version of LD_LIBRARY_PATH environment variable, otherwise you will encounter an error.
/postgresql/app/pg180/bin/psql: symbol lookup error: /postgresql/app/pg180/bin/psql: undefined symbol: PQservice
扫一扫
1475
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
