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

Tags:

VirMach 便宜 VPS