pg9.5升级 到 pg11 的步骤
- 2019 年 10 月 4 日
- 筆記
pg9.5升级 到 pg11 的步骤：
## 这里pg9.5 和 pg11 都使用rpm包安装。
pg9.5 已经在运行，参数如下：
[root@node77 data]# egrep "^w+" postgresql.conf listen_addresses = '*' # what IP address(es) to listen on; port = 5432 # (change requires restart) max_connections = 100 # (change requires restart) shared_buffers = 128MB # min 128kB dynamic_shared_memory_type = posix # the default is the first option wal_level = hot_standby # minimal, archive, hot_standby, or logical fsync = on # turns forced synchronization on or off wal_sync_method = fsync # the default is the first option full_page_writes = on # recover from partial page writes max_wal_size = 1GB min_wal_size = 80MB archive_mode = on # enables archiving; off, on, or always archive_command = 'cd ./' # command to use to archive a logfile segment max_wal_senders = 10 # max number of walsender processes wal_keep_segments = 100 # in logfile segments, 16MB each; 0 disables wal_sender_timeout = 60s # in milliseconds; 0 disables hot_standby = on # "on" allows queries during recovery log_destination = 'stderr' # Valid values are combinations of logging_collector = on # Enable capturing of stderr and csvlog log_directory = 'pg_log' # directory where log files are written, log_filename = 'postgresql-%a.log' # log file name pattern, log_truncate_on_rotation = on # If on, an existing log file with the log_rotation_age = 1d # Automatic rotation of logfiles will log_rotation_size = 0 # Automatic rotation of logfiles will log_checkpoints = on log_connections = on log_duration = on log_line_prefix = '< %m >' # special values: log_statement = 'ddl' # none, ddl, mod, all log_timezone = 'PRC' autovacuum = on # Enable autovacuum subprocess? 'on' datestyle = 'iso, mdy' timezone = 'PRC' lc_messages = 'en_US.UTF-8' # locale for system error message lc_monetary = 'en_US.UTF-8' # locale for monetary formatting lc_numeric = 'en_US.UTF-8' # locale for number formatting lc_time = 'en_US.UTF-8' # locale for time formatting default_text_search_config = 'pg_catalog.english'
pg9.5 的编译参数如下：
[root@node77 data]# /usr/pgsql-9.5/bin/pg_config BINDIR = /usr/pgsql-9.5/bin DOCDIR = /usr/pgsql-9.5/doc HTMLDIR = /usr/pgsql-9.5/doc/html INCLUDEDIR = /usr/pgsql-9.5/include PKGINCLUDEDIR = /usr/pgsql-9.5/include INCLUDEDIR-SERVER = /usr/pgsql-9.5/include/server LIBDIR = /usr/pgsql-9.5/lib PKGLIBDIR = /usr/pgsql-9.5/lib LOCALEDIR = /usr/pgsql-9.5/share/locale MANDIR = /usr/pgsql-9.5/share/man SHAREDIR = /usr/pgsql-9.5/share SYSCONFDIR = /etc/sysconfig/pgsql PGXS = /usr/pgsql-9.5/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--enable-rpath' '--prefix=/usr/pgsql-9.5' '--includedir=/usr/pgsql-9.5/include' '--mandir=/usr/pgsql-9.5/share/man' '--datadir=/usr/pgsql-9.5/share' '--with-perl' '--with-python' '--with-tcl' '--with-tclconfig=/usr/lib64' '--with-openssl' '--with-pam' '--with-gssapi' '--with-includes=/usr/include' '--with-libraries=/usr/lib64' '--enable-nls' '--enable-dtrace' '--with-uuid=e2fs' '--with-libxml' '--with-libxslt' '--with-ldap' '--with-selinux' '--with-system-tzdata=/usr/share/zoneinfo' '--sysconfdir=/etc/sysconfig/pgsql' '--docdir=/usr/pgsql-9.5/doc' '--htmldir=/usr/pgsql-9.5/doc/html' 'CFLAGS=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic' 'LDFLAGS=-Wl,--as-needed' CC = gcc CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic CFLAGS_SL = -fPIC LDFLAGS = -Wl,--as-needed -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-9.5/lib',--enable-new-dtags LDFLAGS_EX = LDFLAGS_SL = LIBS = -lpgcommon -lpgport -lselinux -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lrt -lcrypt -ldl -lm VERSION = PostgreSQL 9.5.15 ### 然后 随便在pg9.5上造点测试数据，过程忽略 ###
在同一台机器上，安装下 pg11的 rpm包：
[root@node77 pg11_el7]# l total 9.4M -rw-r--r-- 1 root root 616K 2019-03-08 17:16 postgresql11-contrib-11.2-2PGDG.rhel7.x86_64.rpm -rw-r--r-- 1 root root 1.7M 2019-03-08 17:16 postgresql11-11.2-2PGDG.rhel7.x86_64.rpm -rw-r--r-- 1 root root 360K 2019-03-08 17:16 postgresql11-libs-11.2-2PGDG.rhel7.x86_64.rpm -rw-r--r-- 1 root root 2.1M 2019-03-08 17:16 postgresql11-devel-11.2-2PGDG.rhel7.x86_64.rpm -rw-r--r-- 1 root root 4.8M 2019-03-08 17:16 postgresql11-server-11.2-2PGDG.rhel7.x86_64.rpm ## 安装并初始化下 pg11 的文件目录 [root@node77 pg11_el7]# yum localinstall postgresql11-* ### 修改配置文件，确保端口和老的实例不冲突 su - postgresql -bash-4.2$ cd /var/lib/pgsql/11/data/
-bash-4.2$ egrep "^w+" postgresql.conf 参数如下：
listen_addresses = '*' # what IP address(es) to listen on; port = 5433 # (change requires restart) max_connections = 100 # (change requires restart) shared_buffers = 128MB # min 128kB dynamic_shared_memory_type = posix # the default is the first option wal_level = replica # minimal, replica, or logical fsync = on # flush data to disk for crash safety wal_sync_method = fsync # the default is the first option full_page_writes = on # recover from partial page writes max_wal_size = 1GB min_wal_size = 80MB archive_mode = on # enables archiving; off, on, or always archive_command = 'cd ./' # command to use to archive a logfile segment max_wal_senders = 10 # max number of walsender processes wal_keep_segments = 100 # in logfile segments; 0 disables wal_sender_timeout = 60s # in milliseconds; 0 disables hot_standby = on # "off" disallows queries during recovery log_destination = 'stderr' # Valid values are combinations of logging_collector = on # Enable capturing of stderr and csvlog log_directory = 'pg_log' # directory where log files are written, log_filename = 'postgresql-%a.log' # log file name pattern, log_truncate_on_rotation = on # If on, an existing log file with the log_rotation_age = 1d # Automatic rotation of logfiles will log_rotation_size = 0 # Automatic rotation of logfiles will log_checkpoints = on log_connections = on log_duration = on log_line_prefix = '%m [%p] ' # special values: log_timezone = 'PRC' autovacuum = on # Enable autovacuum subprocess? 'on' datestyle = 'iso, mdy' timezone = 'PRC' lc_messages = 'en_US.UTF-8' # locale for system error message lc_monetary = 'en_US.UTF-8' # locale for monetary formatting lc_numeric = 'en_US.UTF-8' # locale for number formatting lc_time = 'en_US.UTF-8' # locale for time formatting default_text_search_config = 'pg_catalog.english'
# 先初始化一个pg11的数据库实例出来
su - postgresql /usr/pgsql-11/bin/pg_ctl initdb -D /var/lib/pgsql/11/data/
下面准备开始做升级任务
# 1 关闭 pg9.5
# 2 关闭 pg11 （默认我们上面只是初始化了下pg11，并没有启动）
# 3 检测pg11下 是否可升级
su – postgres
/usr/pgsql-11/bin/pg_upgrade -b /usr/pgsql-9.5/bin -B /usr/pgsql-11/bin/ -d /var/lib/pgsql/9.5/data/ -D /var/lib/pgsql/11/data/ -p 5432 -P 5433 -U postgres -j 8 –link –check
结果类似下面这样：
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 reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for invalid "unknown" user columns ok
Checking for hash indexes ok
Checking for roles starting with "pg_" ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
*Clusters are compatible*
上面都是OK就是没问题的，我们可以继续第四步。
# 4 正式执行 upgrade操作
# pg_upgrade有两种升级方式，一个是缺省的通过拷贝数据文件到新的data目录下，一个是创建硬链接。拷贝的方式升级较慢，但是原库还可用；硬链接的方式升级较快，但是原库不可用。
# 缺省拷贝方式升级的命令，(硬链接方式升级的命令只需要添加 -k 或者 –link)
su – postgres
/usr/pgsql-11/bin/pg_upgrade -b /usr/pgsql-9.5/bin -B /usr/pgsql-11/bin/ -d /var/lib/pgsql/9.5/data/ -D /var/lib/pgsql/11/data/ -p 5432 -P 5433 -U postgres -j 8 –link –retain –verbose ## 这里使用硬链接方式升级
结果类似下面这样：
"/usr/pgsql-11/bin/pg_ctl" -w -D "/var/lib/pgsql/11/data/" -o "" -m smart stop >> "pg_upgrade_server.log" 2>&1
Upgrade Complete
—————-
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
./analyze_new_cluster.sh
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
# 5 修改配置文件，并启动pg11 数据库 【注意暂时不要让业务连接进来】
su – postgres
vim /var/lib/pgsql/11/data/postgresql.conf 修改 port = 5432 还有其他一些文件的路径配置即可
/usr/pgsql-11/bin/pg_ctl start -D /var/lib/pgsql/11/data/
/usr/pgsql-11/bin/psql
# 6 重建下统计信息
实际上执行的是这个命令：/usr/pgsql-11/bin/vacuumdb -U postgres –all –analyze-only
注意： 全库的vacuumdb 操作，比较重量级，因此最好自己手工对重要的大表执行下。
有时候，pgsql大版本升级，psql连接问题会报这个错误：undefined symbol: PQsetErrorContextVisibility
解决方法：
su – postgres
vim .bash_profile 加一行
export LD_LIBRARY_PATH=/usr/pgsql-10/lib
没有ZFS的情况下的，pg的升级建议：
1、新加一台pg流复制从库X
2、在pg流复制的从库X，使用pg_upgrade进行升级（硬链接比较快）
3、低峰期，切换主从复制关系
关于 pg_upgrade 的文章， 可以看德哥的这篇：
https://github.com/digoal/blog/blob/master/201412/20141219_01.md?spm=a2c4e.11153940.blogcont640709.22.1ffc508cWN9t9E&file=20141219_01.md
