Thursday, August 29, 2024

PostgreSQL - Upgrade Methods

There are several upgrade database methods, such as:
  • Upgrade with downtime
    • Using pg_dump and pg_restore
    • Using pg_upgrade
      • pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/restore typically required for major version upgrades, e.g., from 12.14 to 13.10 or from 14.9 to 15.5. It is not required for minor version upgrades, e.g., from 12.7 to 12.8 or from 14.1 to 14.5
      • pg_upgrade supports upgrades from 9.2.X and later to the current major release of PostgreSQL, including snapshot and beta releases
    • Using pg_dumpall
  • Upgrading with no/near zero downtime using logical/trigger based replication
    • PostgreSQL logical replication (v10 and above)
      • Known as transactional replication, the subscriber initially receives a copy of the replicated database object from the publisher and pulls any subsequent changes on the same object as they occur in real-time.
      • The typical use-cases for logical replication are:
        • Sending incremental changes in a single database or a subset of a database to subscribers as they occur.
        • Firing triggers for individual changes as they arrive on the subscriber.
        • Consolidating multiple databases into a single one (for example for analytical purposes).
        • Replicating between different major versions of PostgreSQL.
        • Replicating between PostgreSQL instances on different platforms (for example Linux to Windows)
        • Giving access to replicated data to different groups of users.
        • Sharing a subset of the database between multiple databases.
    • pglogical2 (9.4 and above)
      • Logical Replication extension for PostgreSQL 15, 14, 13, 12, 11, 10, 9.6, 9.5, 9.4 (Postgres), providing much faster replication than Slony, Bucardo or Londiste, as well as cross-version upgrades
      • Use cases supported are:
        • Upgrades between major versions (given the above restrictions)
        • Full database replication
        • Selective replication of sets of tables using replication sets
        • Selective replication of table rows at either publisher or subscriber side (row_filter)
        • Selective replication of table columns at publisher side
        • Data gather/merge from multiple upstream servers
    • Bucardo (Last updated: 2020-02-28)
    • EDB Replication Server
      • EDB Postgres Replication Server (EPRS) replicates data between Postgres databases, or from non-Postgres (Oracle, SQL Server) databases to Postgres databases, for ongoing data synchronization as part of a migration.
      • Replication Server is an asynchronous replication system available for PostgreSQL and for EDB Postgres Advanced Server.
      • Certified and supported product versions of PostgreSQL & EDB:
        • PostgreSQL versions 12, 13, 14, 15, and 16
        • EDB Postgres Advanced Server versions 12, 13, 14, 15, and 16
    • EDB Postgres Distributed (PGD)
      • Provides multi-master replication and data distribution with advanced conflict management, data-loss protection, and throughput up to 5X faster than native logical replication. It enables distributed Postgres clusters with high availability up to five 9s.
      • PGD is compatible with version 12-16 of PostgreSQL, EDB Postgres Extended Server, and EDB Postgres Advanced Server and is deployed as a standard Postgres extension named BDR.
Ref:
at
Labels: