ubuntu postgresql更新升级步骤

Preparations
Before starting the upgrade, complete these critical steps to avoid data loss:

• Backup Data: Use pg_dumpall to create a full backup of all databases. Run sudo -u postgres pg_dumpall > /path/to/backup.sql to save all roles and databases to a SQL file.
• Check Compatibility: For major version upgrades (e.g., 14.x → 15.x), review the PostgreSQL release notes to ensure your applications, extensions (e.g., PostGIS), and custom scripts are compatible with the new version. Minor version upgrades (e.g., 14.5 → 14.7) typically do not require additional checks.
• Add Official Repository (if needed): If your current Ubuntu repository does not include the desired PostgreSQL version, add the official PostgreSQL APT repository. Create a new file /etc/apt/sources.list.d/pgdg.list with the content deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main, then import the GPG key using wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - and update the package list with sudo apt update.

Minor Version Upgrade (e.g., 14.5 → 14.7)
Minor upgrades involve minimal changes and can be completed using the package manager:

1. Stop PostgreSQL Service: Halt the current service to prevent data corruption during the upgrade. Run sudo systemctl stop postgresql.
2. Update Packages: Refresh the package list and upgrade PostgreSQL to the target minor version. For example, for PostgreSQL 14: sudo apt update && sudo apt upgrade postgresql-14.
3. Start PostgreSQL Service: Restart the service to apply the upgrade. Run sudo systemctl start postgresql.

Major Version Upgrade (e.g., 14.x → 15.x)
Major upgrades require more steps to ensure data compatibility. Follow this workflow:

1. Install New Version: If you haven’t added the official repository, complete the steps in the “Preparations” section. Then, install the new PostgreSQL version (e.g., 15) using sudo apt install postgresql-15. This command installs the new version alongside the old one.
2. Stop Old Service: Stop the current PostgreSQL service to prepare for data migration. Run sudo systemctl stop postgresql.
3. Initialize New Cluster: Create a new data directory for the new version. For Ubuntu, use sudo /usr/lib/postgresql/15/bin/pg_ctl initdb -D /var/lib/postgresql/15/main/.
4. Migrate Data: Choose one of two methods based on your needs:
   • pg_upgrade (Fast, In-Place): This method migrates data without a full dump/restore. First, run a compatibility check: sudo -u postgres /usr/lib/postgresql/15/bin/pg_upgrade -b /usr/lib/postgresql/14/bin -B /usr/lib/postgresql/15/bin -d /var/lib/postgresql/14/main -D /var/lib/postgresql/15/main --check. If no errors appear, execute the upgrade: sudo -u postgres /usr/lib/postgresql/15/bin/pg_upgrade -b /usr/lib/postgresql/14/bin -B /usr/lib/postgresql/15/bin -d /var/lib/postgresql/14/main -D /var/lib/postgresql/15/main -o '-c config_file=/etc/postgresql/14/main/postgresql.conf' -O '-c config_file=/etc/postgresql/15/main/postgresql.conf'. Afterward, update statistics with sudo -u postgres /usr/lib/postgresql/15/bin/vacuumdb --all --analyze-in-stages.
   • Logical Dump/Restore (Safe, Cross-Version): Export all data to a SQL file, create a new cluster, and import the data. Export: sudo -u postgres pg_dumpall -f /path/to/backup.sql. Create a new cluster: sudo -u postgres /usr/lib/postgresql/15/bin/initdb -D /var/lib/postgresql/15/main/. Start the new service: sudo systemctl start postgresql@15-main. Import the data: sudo -u postgres psql -f /path/to/backup.sql postgres.
5. Configure New Version: Copy old configuration files (e.g., postgresql.conf, pg_hba.conf) to the new directory: sudo cp /etc/postgresql/14/main/*.conf /etc/postgresql/15/main/. Update settings like port (if using a different port for the new version) or listen_addresses (to enable remote access).
6. Start New Service: Start the new PostgreSQL service: sudo systemctl start postgresql@15-main.
7. Verify Upgrade: Check the version to confirm success: sudo -u postgres psql -p 5433 -c "SELECT version();" (replace 5433 with the new version’s port if changed).
8. Clean Up Old Version (Optional): Once you’ve confirmed the new version works correctly, remove the old version to free up disk space. For example, for PostgreSQL 14: sudo apt remove postgresql-14.

Post-Upgrade Tasks
After the upgrade, complete these steps to ensure stability:

• Check Logs: Review the new version’s log file (e.g., /var/log/postgresql/postgresql-15-main.log) for errors or warnings.
• Reinstall Extensions: If you use extensions (e.g., PostGIS), reinstall them in the new version. For example: sudo -u postgres psql -c "CREATE EXTENSION postgis;".
• Test Applications: Verify that your applications connect to the new version and function as expected.