Upgrade of PostgreSQL Large Version
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
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 127.0.0.1/32 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.
Performing Consistency Checks
Checking cluster versions
New cluster data and binary directories are from different major versions.
This prompt occurs here because there is no pg_upgrade specified to run the new library version, written as an absolute path.
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.
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: ./analyze_new_cluster.sh Running this script will delete the old cluster's data files: ./delete_old_cluster.sh
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  LOG: listening on IPv6 address "::1", port 1921 2019-01-30 00:47:33.037 CST  LOG: listening on IPv4 address "127.0.0.1", port 1921 2019-01-30 00:47:33.038 CST  LOG: listening on Unix socket "/tmp/.s.PGSQL.1921" 2019-01-30 00:47:33.046 CST  LOG: database system was shut down at 2019-01-30 00:46:54 CST 2019-01-30 00:47:33.047 CST  LOG: database system is ready to accept connections done 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
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).