The purpose of,

The current PostgreSQL data version used on the device is 9.2.24. The latest version of PostgreSQL is 12.2. Three major releases have been iterated, and each major release has several iterations in between. During each iteration, there were numerous hidden bug fixes and performance improvements, including CPU, memory usage, disk reads and writes, network I/O, indexing logic, and so on. To improve the performance of the PostgreSQL database on devices and reduce disk space usage, you need to replace the PostgreSQL9.2.24 database with the official PostgreSQL11.5 database.

Risk assessment

The internal storage relationship between large versions of PostgreSQL databases changes greatly. Therefore, the main risks are as follows: (1) Whether the data can be backed up completely before the database is upgraded; And whether the database is lost after the database is successfully upgraded. (2) Whether the old backup data can function in the new version of the database satisfactorily.

Iii. Implementation steps

There are three upgrade modes for a PostgreSQL database: pg_dumpall PG_UPDATE trigger replication The above three modes have their advantages and disadvantages. The dump + restore mode is the most reliable and usually takes a little time. Pg_update is a good choice for requiring short downtime, usually just a few minutes, even for a large database; Trigger replication mode, the shortest time, but the process is complex and prone to problems. Each of the three approaches has its merits, and which one to use depends on your needs. If you crave downtime and want to be guaranteed, pg_UPDATE is your best bet. On the other hand, if you need more data reliability than downtime, trust me, dump + restore is your best bet. Therefore, the first upgrade method, dump + ReSTOR, is adopted in this solution.

Step 1: Back up the old data in the PostgreSQL9.2.24 database. Of course, if the database cluster directory of PG11.5 and PG9.2.24 are not in the same directory, you can back up the database after installation. Otherwise, back up data first to avoid data loss. Pg_dump -h host IP address -p port number -u postgres Database name > file name

Step 2: Install the PostgreSQL version (PostgreSQL11.5) to the specified directory. The specified directory is /home/postgresQLClient. After the installation is complete, there are four directory files in this directory: bin, include, lib, and share. The bin/ directory stores terminal PostgreSQL client tools, such as logging in to the PSQL database, initializing the initDB database cluster, creating the createdB database, and deleting the DropDB database. [root@Thor bin]#./ PSQL –version PSQL (PostgreSQL) 11.5

Third, initialize the database cluster to a specified directory location. The specified installation directory is /home/postgresQLServer. This directory is used to store system libraries/tables, user databases/tables, index files, and log files of the PostgreSQL service. Example Change the permission on the PostgreSQLSever/ directory to Postgres. [root@Thor MetadataServer]# chown -r postgres: Postgres cluster directory [root@Thor MetadataServer]# chmod -r database cluster directory Now use the initdb command to initialize the database cluster as follows:

su postgres -c '/home/PostgreSQLClient/bin/initdb -D /home/PostgreSQLServer'1
Copy the code

After a period of time (the specific time depends on the system hardware configuration), the terminal displays a message indicating that the creation is successful. Waiting for server to start… Done server started Step 4 log in to the database using PSQL -p database port -u postgres. And check whether the current database version is the same as expected.

metadata_test=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)
Time: 0.160 ms
Copy the code

You can confirm that the current server version is PostgreSQL11.5 as expected.

Step 5: Import data from the backup file, such as database tables, into the database in PostgreSQL11.5. Note that you must create an empty database name to import on PostgreSQL11.5. PSQL -p Port number -u postgres -h Host IP address -f Backup file database name.

Step 6: Repeat step 4 to log in to the database. Check the number of databases, the number of tables, and the total number of records (tuples) in each table are the same as in PostgreSQL9.2.24. In addition, whether the data record (tuple) attribute (field) information is different.

Four, the results

In 11.5 Database, the database table records in PostgreSQL9.2.24 are the same as those in PostgreSQL9.2.24, including the total number of data records in each table and the index method of the table. In general, data backup and import work.

Fifth, pay attention to

The following three methods can be used to back up database table data: PostgreSQL9.2.24 Client tool PG_dump backup data, and the PSQL tool of this version is imported to the PostgreSQL11.5 database. PostgreSQL9.2.24 pg_dump backup data. PostgreSQL11.5 PSQL imports backup data to the PostgreSQL11.5 database. PostgreSQL11.5 Pg_dump backup data, and PSQL is imported to the database of this version.