postgresql升级-m6米乐安卓版下载

postgresql升级-pg_upgrade升级

说明

​ pg_upgrade 工具可以支持 postgresql 跨版本的就地升级，不需要执行导出和导入操作。pg_upgrade 可以支持 postgresql 8.4.x 到最新版本的升级，包括快照版本和测试版本。

​ pg_upgrade 提供了升级前的兼容性检查（-c 或者 --check 选项）功能， 可以发现插件、数据类型不兼容等问题。如果指定了 --link 选项，新版本服务可以直接使用原有的数据库文件而不需要执行复制，通常可以在几分钟内完成升级操作。

​ 本次文档主要记录的是版本13.5到14.1的升级演示

old环境检查

环境变量检查

[postgres@lyp ~]$ cat .bash_profile 
# .bash_profile
# get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# user specific environment and startup programs
path=$path:$home/.local/bin:$home/bin
export path
export pghome=/opt/pgsql13.5
export pgdata=/home/postgres/pgdata
export pguser=postgres
export pgport=5433
export path=$home/bin:$pghome/bin:$path
export ld_library_path=$pghome/lib:$ld_library_path
[postgres@lyp ~]$ 

数据库版本检查

[postgres@lyp ~]$ psql
psql (13.5)
type "help" for help.
postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 postgresql 13.5 on x86_64-pc-linux-gnu, compiled by gcc (gcc) 4.8.5 20150623 (red hat 4.8.5-36), 64-bit
(1 row)
postgres=# 

模拟数据

postgres=# \d
               list of relations
 schema |       name        | type  |  owner   
-------- ------------------- ------- ----------
 public | emp               | table | postgres
(1 rows)
postgres=# select count(*) from emp;
  count  
---------
 1100000
(1 row)
postgres=# 

new环境安装

解压安装包

[root@lyp ~]# tar -zxvf postgresql-14.1.tar.gz 
[root@lyp ~]# ll postgresql-14.1
total 756
-rw-r--r--.  1 1107 1107    490 nov  9 05:58 aclocal.m4
drwxrwxrwx.  2 1107 1107   4096 nov  9 06:10 config
-rwxr-xr-x.  1 1107 1107 580807 nov  9 05:58 configure
-rw-r--r--.  1 1107 1107  83288 nov  9 05:58 configure.ac
drwxrwxrwx. 58 1107 1107   4096 nov  9 06:10 contrib
-rw-r--r--.  1 1107 1107   1192 nov  9 05:58 米乐app官网下载 copyright
drwxrwxrwx.  3 1107 1107     87 nov  9 06:10 doc
-rw-r--r--.  1 1107 1107   4259 nov  9 05:58 gnumakefile.in
-rw-r--r--.  1 1107 1107    277 nov  9 05:58 history
-rw-r--r--.  1 1107 1107  63953 nov  9 06:11 install
-rw-r--r--.  1 1107 1107   1665 nov  9 05:58 makefile
-rw-r--r--.  1 1107 1107   1213 nov  9 05:58 readme
drwxrwxrwx. 16 1107 1107   4096 nov  9 06:11 src
[root@lyp ~]# 
[root@lyp ~]# mv postgresql-14.1 /opt/pgsql14.1
[root@lyp ~]# 

编译安装

[root@lyp ~]# cd /opt/pgsql14.1/
[root@lyp pgsql14.1]# ./configure --prefix=/opt/pgsql14.1
checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
......
config.status: linking src/include/port/linux.h to src/include/pg_config_os.h
config.status: linking src/makefiles/makefile.linux to src/makefile.port
[root@lyp pgsql14.1]# 
[root@lyp pgsql14.1]# gmake world
gmake -c ./src/backend generated-headers
gmake[1]: entering directory `/opt/pgsql14.1/src/backend'
......
gmake[2]: leaving directory `/opt/pgsql14.1/contrib/vacuumlo'
gmake[1]: leaving directory `/opt/pgsql14.1/contrib'
[root@lyp pgsql14.1]# 
[root@lyp pgsql14.1]# gmake install-world
gmake -c ./src/backend generated-headers
gmake[1]: entering directory `/opt/pgsql14.1/src/backend'
......
gmake[2]: leaving directory `/opt/pgsql14.1/contrib/vacuumlo'
gmake[1]: leaving directory `/opt/pgsql14.1/contrib'
[root@lyp pgsql14.1]# 

初始化数据库

[postgres@lyp ~]$ mkdir pgdata-14
[postgres@lyp ~]$ /opt/pgsql14.1/bin/initdb -d /home/postgres/pgdata-14/
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 "en_us.utf-8".
the default database encoding has accordingly been set to "utf8".
the default text search configuration will be set to "english".
data page checksums are disabled.
fixing permissions on existing directory /home/postgres/pgdata-14 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128mb
selecting default time zone ... prc
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
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:
    /opt/pgsql14.1/bin/pg_ctl -d /home/postgres/pgdata-14/ -l logfile start
[postgres@lyp ~]$ 

修改目录权限

[root@lyp ~]# chown -r postgres:postgres /opt/pgsql14.1/
[root@lyp ~]# 

备份数据库

利用 pg_dump/pg_dumpall 对数据库进行备份，保证升级中遇到各种问题后以便回退。

[postgres@lyp ~]$ /opt/pgsql13.5/bin/pg_dumpall > pgdumpallfull
[postgres@lyp ~]$

升级数据库

升级检查

[postgres@lyp ~]$ /opt/pgsql14.1/bin/pg_upgrade \
>   --old-datadir /home/postgres/pgdata/ \
>   --new-datadir /home/postgres/pgdata-14/ \
>   --old-bindir /opt/pgsql13.5/bin/ \
>   --new-bindir /opt/pgsql14.1/bin/ \
>   --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 system-defined composite types in user tables  ok
checking for reg* data types in user tables                 ok
checking for contrib/isn with bigint-passing mismatch       ok
checking for user-defined encoding conversions              ok
checking for user-defined postfix operators                 ok
checking for presence of required libraries                 fatal
your installation references loadable libraries that are missing from the
new installation.  you can add these libraries to the new installation,
or remove the functions using them from the old installation.  a list of
problem libraries is in the file:
    loadable_libraries.txt
failure, exiting
[postgres@lyp ~]$ more loadable_libraries.txt
could not load library "$libdir/walminer": error:  could not access file "$libdir/walminer": no such file or directory
in database: postgres
[postgres@lyp ~]$ 

如果旧版本中安装了扩展模块，在新版本中也需要安装相应的共享对象文件或者 dll 文件。但是不要执行create extension命令，因为会从旧数据库中进行升级。另外，如果使用了任何自定义的全文搜索文件（字典、同义词、词库、停用词），也需要复制到新的数据库集群目录中。

但是这里的walminer插件majorversion当前只支持‘10’,‘11’,‘12’,‘13’，所以需要删除插件。

删除插件

[postgres@lyp ~]$ /opt/pgsql13.5/bin/psql 
psql (13.5)
type "help" for help.
postgres=# drop extension walminer ;
drop extension
postgres=# 

重新升级检查

–check 表示执行升级检查，而不会真的执行升级操作。

[postgres@lyp ~]$ /opt/pgsql14.1/bin/pg_upgrade \
>   --old-datadir /home/postgres/pgdata/ \
>   --new-datadir /home/postgres/pgdata-14/ \
>   --old-bindir /opt/pgsql13.5/bin/ \
>   --new-bindir /opt/pgsql14.1/bin/ \
>   --check
performing consistency checks on old live server
------------------------------------------------
checking cluster versions                                   ok
checking database user is the install user                  ok
checking database connection settings                       ok
checking for prepared transactions                          ok
checking for system-defined composite types in user tables  ok
checking for reg* data types in user tables                 ok
checking for contrib/isn with bigint-passing mismatch       ok
checking for user-defined encoding conversions              ok
checking for user-defined postfix operators                 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@lyp ~]$ 

停止数据库

确定没有客户端访问之后备份数据库，然后停止旧版本的后台服务。

[postgres@lyp ~]$ /opt/pgsql13.5/bin/pg_ctl stop -d /home/postgres/pgdata
waiting for server to shut down....2022-02-08 02:28:38.499 cst [47025] log:  received fast shutdown request
2022-02-08 02:28:38.499 cst [47025] log:  aborting any active transactions
2022-02-08 02:28:38.500 cst [47034] fatal:  terminating connection due to administrator command
2022-02-08 02:28:38.500 cst [47033] fatal:  terminating connection due to administrator command
2022-02-08 02:28:38.501 cst [47025] log:  background worker "logical replication launcher" (pid 47032) exited with exit code 1
2022-02-08 02:28:38.502 cst [47027] log:  shutting down
2022-02-08 02:28:38.509 cst [47025] log:  database system is shut down
 done
server stopped
[postgres@lyp ~]$ 

升级数据库

可以使用–link 表示将新版本的数据目录硬链接到旧版本的数据目录，而不会复制一份新的数据文件，可以快速进行升级，但回退较为麻烦。

这里我们不使用–link直接升级。

[postgres@lyp ~]$ /opt/pgsql14.1/bin/pg_upgrade \
>   --old-datadir /home/postgres/pgdata/ \
>   --new-datadir /home/postgres/pgdata-14/ \
>   --old-bindir /opt/pgsql13.5/bin/ \
>   --new-bindir /opt/pgsql14.1/bin/ 
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 system-defined composite types in user tables  ok
checking for reg* data types in user tables                 ok
checking for contrib/isn with bigint-passing mismatch       ok
checking for user-defined encoding conversions              ok
checking for user-defined postfix operators                 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
------------------
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
copying user relation files
                                                            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
----------------
optimizer statistics are not transferred by pg_upgrade.
once you start the new server, consider running:
    /opt/pgsql14.1/bin/vacuumdb --all --analyze-in-stages
running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh
[postgres@lyp ~]$ 

更新hba及参数

将旧版本配置文件 pg_hba.conf 和 postgresql.conf 等中的改动在对应的新配置文件中再次进行修改。

修改环境变量

[postgres@lyp ~]$ vi .bash_profile 
# .bash_profile
# get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# user specific environment and startup programs
path=$path:$home/.local/bin:$home/bin
export path
export pghome=/opt/pgsql14.1
export pgdata=/home/postgres/pgdata-14
export pguser=postgres
export pgport=5433
export path=$home/bin:$pghome/bin:$path
export ld_library_path=$pghome/lib:$ld_library_path
[postgres@lyp ~]$ source .bash_profile 
[postgres@lyp ~]$ 

启动数据库

[postgres@lyp ~]$ pg_ctl start
waiting for server to start....2022-02-08 04:07:35.375 cst [95334] log:  starting postgresql 14.1 on x86_64-pc-linux-gnu, compiled by gcc (gcc) 4.8.5 20150623 (red hat 4.8.5-36), 64-bit
2022-02-08 04:07:35.376 cst [95334] log:  listening on ipv6 address "::1", port 5433
2022-02-08 04:07:35.376 cst [95334] log:  listening on ipv4 address "127.0.0.1", port 5433
2022-02-08 04:07:35.379 cst [95334] log:  listening on unix socket "/tmp/.s.pgsql.5433"
2022-02-08 04:07:35.382 cst [95335] log:  database system was shut down at 2022-02-08 04:04:46 cst
2022-02-08 04:07:35.384 cst [95334] log:  database system is ready to accept connections
 done
server started
[postgres@lyp ~]$

更新统计信息

pg_upgrade 不会生成新版本数据库的统计信息，按提示执行命令：

optimizer statistics are not transferred by pg_upgrade.
once you start the new server, consider running:
    /opt/pgsql14.1/bin/vacuumdb --all --analyze-in-stages

[postgres@lyp ~]$ /opt/pgsql14.1/bin/vacuumdb --all --analyze-in-stages
vacuumdb: processing database "postgres": generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": generating default (full) optimizer statistics
vacuumdb: processing database "template1": generating default (full) optimizer statistics
[postgres@lyp ~]$ 

删除原集群数据文件（按需）

确认升级成功后，可以选择删除或者保留旧的数据文件。pg_upgrade 提供了一个删除旧数据文件的脚本

running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh
[postgres@lyp ~]$

[postgres@lyp ~]$ more delete_old_cluster.sh
#!/bin/sh
rm -rf '/home/postgres/pgdata'
[postgres@lyp ~]$ 

验证升级

数据库版本检查

[postgres@lyp ~]$ psql
psql (14.1)
type "help" for help.
postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 postgresql 14.1 on x86_64-pc-linux-gnu, compiled by gcc (gcc) 4.8.5 20150623 (red hat 4.8.5-36), 64-bit
(1 row)
postgres=# 
postgres=# 

模拟数据检查

postgres=# select count(*) from emp;
  count  
---------
 1100000
(1 row)
postgres=# 

回退方式

若需回退到旧版本的数据库，可以分为以下三种情况：

1. 如果只运行了 --check 选项命令，表示没有真正执行升级，重新启动服务即可；

2. 如果升级时没有使用 --link 选项，旧版本的数据库集群没有任何修改，重新启动服务即可；

3. 如果升级时使用了 --link 选项，数据库文件可能已经被新版本的集群使用：

• 如果 pg_upgrade 在链接操作之前终止，旧版本的数据库集群没有任何修改，重新启动服务即可；

• 如果没有启动过新版本的后台服务，旧版本的数据库集群没有修改，但是链接过程已经将 $pgdata/global/pg_control 文件重命名为 $pgdata/global/pg_control.old；此时需要将该文件名中的 .old 后缀去掉，然后重新启动服务即可；

• 如果已经启动了新版本的数据库集群，已经修改了数据库文件，再启动旧版本的服务可能导致数据损坏；此时需要通过备份文件还原旧版本的数据库。(所以做任何操作之前，需要备份数据库，以便回退)

所以在升级中，尽量避免使用link参数进行升级。