Upgrade of PostgreSQL Large Version

0. Explanation
The experiment was upgraded from pg10 to pg11

1. Source code installation, compiling new version

itm_pg@oracle-> cd postgresql-11.1
Use the new data directory
itm_pg@oracle-> ./configure --prefix=/home/itm_pg/pgsql11.1 --with-perl --with-python
itm_pg@oracle->make world -j 8
itm_pg@oracle->make install-world

2. Initialization of a new database cluster
itm_pg@oracle-> /home/itm_pg/pgsql11.1/bin/initdb -D /home/itm_pg/pgsql11.1/pgdata -E UTF8 --locale=C -U postgres
Here initdb must be written as an absolute path! Otherwise, the original version of initdb will be used to view the initial data.

3. Install custom shared object files
Install all custom shared object files (or DLLs) used by the old cluster into the new cluster, such as pgcrypto.so, whether they come from contrib or some other source code. Do not install schema definitions (such as CREATE EXTENSION pgcrypto) because they will be upgraded from the old cluster. Furthermore, any custom full-text search files (dictionaries, synonyms, dictionaries, stop words) must also be copied into the new cluster. If there is no plug-in installed in the original database, it can be ignored.

4. Stop the old version of the database and modify the new library authentication file
itm_pg@oracle-> pg_ctl stop
If the new library is not started, do not stop
itm_pg@oracle-> vi pg_hba.conf
host all all trust
Because the upgrade requires multiple connections to the old and new cluster database instances, it is modified to use local trust authentication.
5. Inspection update

itm_pg@oracle-> cd /home/itm_pg/pgsql11.1/bin
itm_pg@oracle-> /home/itm_pg/pgsql11.1/bin/pg_upgrade -c -b /home/itm_pg/pgsql10.3/bin/ -B /home/itm_pg/pgsql11.1/bin/ -d /home/itm_pg/pgdata/ -D /home/itm_pg/pgsql11.1/pgdata/ -U postgres
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 presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

*Clusters are compatible*

This prompt indicates that updates can be made, and here are some of the problems that may arise here.
Question 1:
Performing Consistency Checks

Checking cluster versions
New cluster data and binary directories are from different major versions.
Failure, exiting
This prompt occurs here because there is no pg_upgrade specified to run the new library version, written as an absolute path.
Question 2:
This utility can only upgrade to PostgreSQL version 11.1
This is due to the fact that the new version of initdb is not specified when the database cluster is initialized, and it needs to be written as an absolute path.
6. Update

itm_pg@oracle-> /home/itm_pg/pgsql11.1/bin/pg_upgrade  -b /home/itm_pg/pgsql10.3/bin/ -B /home/itm_pg/pgsql11.1/bin/ -d /home/itm_pg/pgdata/ -D /home/itm_pg/pgsql11.1/pgdata/ -
U postgres

Upgrade Complete
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:

Running this script will delete the old cluster's data files:

The above prompt indicates that the update is successful. The upgrade process is to copy the data of the original directory into the directory specified by the new version. Finally, the prompt generates two scripts, one is analysis_new_cluster.sh, which needs to be executed in the new version to collect statistics, and the other is delete_old_cluster.sh, which is used to delete the old version set. Cluster data, of course, can be deleted after the system has been running for a few days for security reasons.

7. Start a new library
Modify the new library configuration file, and the previously modified authentication file can also be modified back as needed.
Update the environment variables (update PGDATA and PGOME), and then start the new library:

itm_pg@oracle-> pg_ctl start
waiting for server to start....2019-01-30 00:47:33.037 CST [8736] LOG:  listening on IPv6 address "::1", port 1921
2019-01-30 00:47:33.037 CST [8736] LOG:  listening on IPv4 address "", port 1921
2019-01-30 00:47:33.038 CST [8736] LOG:  listening on Unix socket "/tmp/.s.PGSQL.1921"
2019-01-30 00:47:33.046 CST [8737] LOG:  database system was shut down at 2019-01-30 00:46:54 CST
2019-01-30 00:47:33.047 CST [8736] LOG:  database system is ready to accept connections
server started
itm_pg@oracle-> ./delete_old_cluster.sh
itm_pg@oracle-> psql
psql (11.1)
Type "help" for help.

postgres=# /d
        List of relations
 Schema | Name | Type  |  Owner   
 public | t    | table | postgres
(1 row)

8. Follow-up Processing
Update statistics:
Since pg_upgrade does not transmit optimizer statistics, at the end of the upgrade you will be instructed to run a command for the next life.
Make this information. You may need to set connection parameters to match your new cluster.
Delete the old database cluster:
Once you are satisfied with the upgrade, you can delete the old cluster's data directory by running the script mentioned when pg_upgrade is completed (if there is a user-defined table space in the old data directory, it is impossible to delete automatically). You can also delete old installation directories (such as bin, share).

Tags: Oracle Database PostgreSQL Python

Posted on Fri, 06 Sep 2019 02:34:44 -0700 by preston_stone