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

postgresql升级-逻辑备份与还原

说明

​ 传统的跨版本升级方法就是利用 pg_dump/pg_dumpall 逻辑备份导出数据库，然后在新版本中通过 pg_restore 进行还原。导出旧版本数据库时推荐使用新版本的 pg_dump/pg_dumpall 工具，可以利用最新的并行导出和还原功能，同时可以减少数据库膨胀问题。

​ 逻辑备份与还原非常简单但速度比较慢，停机时间取决于数据库的大小，因此适合中小型数据库的升级。

​ 本次文档主要记录的是版本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 ~]# 

升级数据库

备份数据库

执行逻辑备份之前停止应用程序，确保没有数据更新，因为备份开始后的更新不会被导出。如有必要，可以修改 /usr/local/pgsql/data/pg_hba.conf 文件禁止其他人访问数据库。

[postgres@lyp ~]$ /opt/pgsql14.1/bin/pg_dumpall > pgdumpallfull
[postgres@lyp ~]$ 

由于已经安装了新版本的 postgresql，可以使用新版本的 pg_dumpall 命令备份旧版本数据库。

停止数据库

[postgres@lyp ~]$ /opt/pgsql13.5/bin/pg_ctl stop -d /home/postgres/pgdata
waiting for server to shut down....2022-02-08 01:01:14.395 cst [25886] log:  received fast shutdown request
2022-02-08 01:01:14.396 cst [25886] log:  aborting any active transactions
2022-02-08 01:01:14.396 cst [25886] log:  background worker "logical replication launcher" (pid 25893) exited with exit code 1
2022-02-08 01:01:14.396 cst [25908] fatal:  terminating connection due to administrator command
2022-02-08 01:01:14.397 cst [25888] log:  shutting down
2022-02-08 01:01:14.442 cst [25886] log:  database system is shut down
 done
server stopped
[postgres@lyp ~]$ 

如果安装目录没有包含特定版本标识（如本文中的13.5/14.1），可以使用mv将目录改名，必要时可以再修改回来。

修改环境变量

[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/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 ~]$

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

启动数据库

[postgres@lyp ~]$ /opt/pgsql14.1/bin/pg_ctl -d /home/postgres/pgdata-14/ -l logfile start
waiting for server to start.... done
server started
[postgres@lyp ~]$ 

还原数据

[postgres@lyp ~]$ psql -d postgres -f pgdumpallfull 
set
set
set
create role
alter role
psql:pgdumpallfull:16: error:  role "postgres" already exists
alter role
you are now connected to database "template1" as user "postgres".
set
set
set
set
set
 set_config 
------------
 
(1 row)
set
set
set
set
you are now connected to database "postgres" as user "postgres".
set
set
set
set
set
 set_config 
------------
 
(1 row)
set
set
set
set
set
set
create table
alter table
create table
alter table
copy 1100000
copy 0
[postgres@lyp ~]$ 

​ 为了减少停机时间，可以将新版本的 postgresql 安装到另一个目录（例如 /usr/local/pgsql-13），同时使用不同的端口启动服务。然后同时执行数据库的导出和导入：

pg_dumpall -p 5433 | psql -d postgres -p 5434

执行以上操作时，新旧版本的后台服务同时运行，新版本使用 5434 端口，旧版本使用 5433 端口。

验证升级

数据库版本查询

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