How to switch the default version of PostgreSQL

In certain situations, you may need to upgrade or switch the PostgreSQL version to take advantage of new features or security updates. This article will detail how to switch the default PostgreSQL version in the ServBay environment. Here are the specific steps:

TIP

The example will demonstrate a migration from PostgreSQL 10 to PostgreSQL 16.

1. First, back up the old version database

Before switching the PostgreSQL version, it is recommended to back up the existing database to prevent data loss. The pg_dumpall command can easily back up all databases.

pg_dumpall -U postgres -f postgresql_backup.sql

When executing the above command, the system will prompt you for the PostgreSQL postgres user password. After the backup is complete, you will have a file named postgresql_backup.sql in the current directory.

2. Install the new version, such as PostgreSQL 16

In the ServBay management panel, find the database management section, select PostgreSQL, and click to install the required new version (e.g., PostgreSQL 16).

3. Disable the old version, such as PostgreSQL 10

After installing the new version, you need to disable the old version to ensure that the system uses the new version of PostgreSQL. Find the old version (e.g., PostgreSQL 10) and disable it.

4. Enable the new version and confirm that the service is running normally

After disabling the old version, enable the new version of PostgreSQL and ensure its service is running normally.

5. In the settings panel, switch the default version from PostgreSQL 10 to PostgreSQL 16, and click Apply.

Next, you need to switch the default version in the settings panel to the newly installed version. Select PostgreSQL 16 and click Apply to apply the changes.

6. Return to the service panel to see the default version has been successfully set

In the service panel, you should be able to see that the default version has been successfully switched to PostgreSQL 16.

7. Go back to the command line, enter psql --version, and you should see the version has switched to 16.x

Enter the following command in the command line to confirm that the PostgreSQL version has been successfully switched:

psql --version

The output should show the current PostgreSQL version as 16.x.

8. Restore the database

After switching versions, you need to restore the previously backed-up database. Use the following command to restore the database:

psql -U postgres -f postgresql_backup.sql

The system will prompt you for the PostgreSQL postgres user password. After entering the password, the database will begin to restore.

9. Confirm the database is fully restored

To ensure the database is fully restored, log in to PostgreSQL and check if the data is complete:

psql -U postgres

Once logged in, you can use the following command to view the list of databases:

\l

Check each database and table to ensure the data is intact.

By following the above steps, you have successfully switched the default version of PostgreSQL to the new version and restored the database. I hope this article helps you smoothly complete the PostgreSQL version switch.