Step-by-step PostgreSQL 12.3 to 12.5 Upgrade
We at MinervaDB PostgreSQL Support strongly recommend PostgreSQL minor upgrades in a rolling fashion, i.e., in a PostgreSQL Master – Slave Replication, perform upgrade on one server after another (usually we begin with upgrade on slave), but definitely not all-at-once. The following are the sequence of steps you should follow to upgrade to upgrade from PostgreSQL 12.3 to PostgreSQL 12.5:
- Shutdown the PostgreSQL database server in slave
- Install the updated binaries
- Restart your PostgreSQL database server in slave
P.S. – In this example we have used Ubuntu 18.04 (bionic)
Step 1 – Import the repository key from https://www.postgresql.org/media/keys/ACCC4CF8.asc
|
1 |
root@MinervaDB-Infra1:~# sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' # Update the package lists: sudo apt-get update |
Step 2 – Install PostgreSQL 12.5:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
root@MinervaDB-Infra1:~# sudo apt-get -y install postgresql-12 performing post-bootstrap initialization ... ok syncing data to disk ... ok Success. You can now start the database server using: pg_ctlcluster 12 main start Ver Cluster Port Status Owner Data directory Log file 12 main 5432 down postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log update-alternatives: using /usr/share/postgresql/12/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode Processing triggers for systemd (237-3ubuntu10.42) ... Processing triggers for man-db (2.8.3-2ubuntu0.1) ... Processing triggers for ureadahead (0.100.0-21) ... root@MinervaDB-Infra1:~# pg_ctlcluster 12 main start |
Step 3 – Confirm PostgreSQL 12.5 is installed successfully:
|
1 2 3 4 5 6 |
postgres@MinervaDB-Infra1:/root$ psql psql (12.5 (Ubuntu 12.5-1.pgdg18.04+1)) Type "help" for help. postgres=# select version(); postgres=# |
Performance improvements in PostgreSQL 12
- Optimal space utilization and read/write performance for B-tree indexes
- Partitioning performance enhancements, including improved query performance on tables with thousands of partitions, improved insertion performance with INSERT and COPY, and the ability to execute ALTER TABLE ATTACH PARTITION without blocking queries
- Automatic (but overridable) inlining of common table expressions (CTEs)
- Reduction of WAL overhead for creation of GiST, GIN, and SP-GiST indexes
- Support for covering GiST indexes, via the INCLUDE clause
- Multi-column most-common-value (MCV) statistics can be defined via CREATE STATISTICS, to support better plans for queries that test several non-uniformly-distributed columns
- REINDEX CONCURRENTLY can rebuild an index without blocking writes to its table
- pg_checksums can enable/disable page checksums (used for detecting data corruption) in an offline cluster
- Progress reporting statistics for CREATE INDEX, REINDEX, CLUSTER, VACUUM FULL, and pg_checksums
- Support for the SQL/JSON path language
- Stored generated columns
- Nondeterministic ICU collations, enabling case-insensitive and accent-insensitive grouping and ordering
- New authentication features, including:
- Encryption of TCP/IP connections when using GSSAPI authentication
- Discovery of LDAP servers using DNS SRV records
- Multi-factor authentication, using the clientcert=verify-full option combined with an additional authentication method in pg_hba.conf
