Antwort How to upgrade postgres 10 to 14? Weitere Antworten – How do I upgrade to PostgreSQL 14

How to upgrade postgres 10 to 14?
High-Level pg_upgrade upgrade steps include:

  1. Install PostgreSQL 14 binaries.
  2. Initialize PostgreSQL cluster.
  3. Install extensions.
  4. Execute pg_upgrade with -c option (Consistency check)
  5. Execute pg_upgrade and review logs.
  6. Validation of data/objects.

Playbook

  1. Backup. First create a backup of all the databases for that (You can continue from B if you dont need a backup)
  2. Pull repos and install PostgreSQL 14. Download ca-certificates.
  3. Migrate the data (choose one option) (Option 1: Manual)
  4. Restart and check version.
  5. Cleanup.

there are 3 methods to upgrade PostgreSQL major version:

  1. Upgrade PostgreSQL database using pg_dump and pg_restore.
  2. Upgrade PostgreSQL database using pg_upgrade.
  3. Upgrade PostgreSQL database using logical replication.

Is Postgres version 14 obsolete : The PostgreSQL 14 version is deprecated, but the PostgreSQL package client or server are still installed. You must install the latest versions of the packages (postgresql-15 and postgresql-client-15) and update your clusters with the command pg_upgradecluster (see the man page).

How to upgrade postgres from 12 to 14

Perform the upgrade

  1. Update and install packages sudo apt-get update sudo apt-get install postgresql-14 postgresql-server-dev-14.
  2. Halt the old server sudo systemctl stop postgresql.service.

How to upgrade postgres from 13 to 14 : On Red Hat Enterprise Linux and derivatives using yum

  1. Install Percona Distribution for PostgreSQL 14 packages.
  2. Set up Percona Distribution for PostgreSQL 14 cluster.
  3. Log is as the postgres user.
  4. Set up locale settings.
  5. Initialize cluster with the new data directory.
  6. Stop the postgresql 13 service.
  7. Run the database upgrade.

Updating PostgreSQL cluster 10 to 15

  1. Database backup.
  2. Installation of PostgreSQL 15.
  3. Updating data in the database directory.
  4. Starting the Patroni cluster.
  5. Post-installation check.
  6. Cleaning up old data.


Updating a PostgreSQL cluster consists of six steps:

  1. Database backup.
  2. Installation of PostgreSQL 15.
  3. Updating data in the database directory.
  4. Starting the Patroni cluster.
  5. Post-installation check.
  6. Cleaning up old data.

How to upgrade Postgres 12 to 14

Perform the upgrade

  1. Update and install packages sudo apt-get update sudo apt-get install postgresql-14 postgresql-server-dev-14.
  2. Halt the old server sudo systemctl stop postgresql.service.

Already here we can see that Postgres 14 does much better in the initial data load. As you can see, the throughput of Postgres 14 at 5000 active connections is about 20% higher. At 10,000 active connections the improvement is 50% over Postgres 13, and at lower connection counts you can also see consistent improvements.On Red Hat Enterprise Linux and derivatives using yum

  1. Install Percona Distribution for PostgreSQL 14 packages.
  2. Set up Percona Distribution for PostgreSQL 14 cluster.
  3. Log is as the postgres user.
  4. Set up locale settings.
  5. Initialize cluster with the new data directory.
  6. Stop the postgresql 13 service.
  7. Run the database upgrade.


Upgrading Percona Distribution for PostgreSQL from 13 to 14

  1. Install Percona Distribution for PostgreSQL 14 packages.
  2. Stop the PostgreSQL service.
  3. Check the upgrade without modifying the data.
  4. Upgrade Percona Distribution for PostgreSQL.
  5. Start PostgreSQL service.

Should I use Postgres 13 or 14 : Already here we can see that Postgres 14 does much better in the initial data load. As you can see, the throughput of Postgres 14 at 5000 active connections is about 20% higher. At 10,000 active connections the improvement is 50% over Postgres 13, and at lower connection counts you can also see consistent improvements.

How to UPDATE PostgreSQL to 15 : Runbook to Migrate from PostgreSQL 11 to 15

  1. Install PostgreSQL 15: Install the new PostgreSQL version alongside the old one without removing PostgreSQL 11.
  2. Prepare the Environment: Stop the PostgreSQL 11 server.
  3. Run pg_upgrade :
  4. Example Command:
  5. Post-Upgrade Steps:
  6. Testing:

How to update PostgreSQL to 15

Runbook to Migrate from PostgreSQL 11 to 15

  1. Install PostgreSQL 15: Install the new PostgreSQL version alongside the old one without removing PostgreSQL 11.
  2. Prepare the Environment: Stop the PostgreSQL 11 server.
  3. Run pg_upgrade :
  4. Example Command:
  5. Post-Upgrade Steps:
  6. Testing:


Let's do some benchmarks to see how the merge algorithm change affects the performance of PostgreSQL 15. Figure 4 above shows us that PostgreSQL 15 performs much better than PostgreSQL 14 for large sorts with very small values of work_mem . As the work_mem setting is increased, the performance gap decreases.Perform the upgrade

  1. Update and install packages sudo apt-get update sudo apt-get install postgresql-14 postgresql-server-dev-14.
  2. Halt the old server sudo systemctl stop postgresql.service.

How to upgrade postgres 12 to 14 : Perform the upgrade

  1. Update and install packages sudo apt-get update sudo apt-get install postgresql-14 postgresql-server-dev-14.
  2. Halt the old server sudo systemctl stop postgresql.service.