Upgrading PostgreSQL to a newer Major version

Major PostgreSQL releases often add new features that change some of the internal workings of PostgreSQL. There are two main ways to upgrade your PSQL instance - by dumping your entire database and then re-inserting it in the new instance (pg_dumpall) or with the new pg_upgrade command. Both methods have their benefits and their drawbacks.

• Usually, using pg_dumpall to dump the whole database and then re-insert it into the new instance is the best way to ensure data integrity but for large databases it can be a very slow process.
• Using pg_upgrade is a lot faster than dumping the entire database, but it doesn't work with older versions of PSQL.

Warning: As PostgreSQL is a third-party product, Acronis cannot guarantee that these methods will work the same for everyone. Always consult PostgreSQL's documentation for your version of PostgreSQL before implementing anything in your production environment.

Note: Please consult the PostgreSQL documentation if pg_upgrade is usable with your version of PostgreSQL and the new version you're planning to use.

Before you begin

Files Advanced does not support versions of Tomcat, Java and PostgreSQL newer than the ones included with each release. To request information about a specific version, please contact Acronis Support.

Note: We strongly recommend that you run a test upgrade outside of your production environment.

Important things to take note of, of your current configuration:

• Are the Files Advanced Server and PostgreSQL server on the same machine?
• What port is PostgreSQL running on?
• What is the locale of your current PostgreSQL installation? You can check this by openning the PostgreSQL Administration tool and clicking on the acronisaccess_production database. On the right, under Properties, you will see the Encoding and Character type.

  Warning: Make sure that your new PostgreSQL installation has the same Encoding and Character type, otherwise you will not be able to upgrade successfully.

• What is the IP and/or DNS name of the machine running PostgreSQL?
• What is the PostgreSQL version number of your current server. The easiest way to find this is to look at the folder name inside the main PostgreSQL folder (by default:. C:\Program Files (x86)\Acronis\Files Advanced\Common\PostgreSQL), the inside folder's name is the PostgreSQL major version number (e.g. 9.2; 9.3; 9.4).
• Make sure that all necessary permissions in the file system(s) are configured.
• Make sure that access between the two instances is allowed via the pg_hba.conf. This is very important if your new PostgreSQL instance is not on the same machine.

Using pg_dumpall

Dumping the database from the old instance

Note: We strongly recommend that you run a test backup/restoration outside of your production environment.

1. Stop the Files Advanced Tomcat service
2. Make sure that the Old instance of PostgreSQL is running and that the New instance is stopped.
3. Open the Files Advanced PostgreSQL Administrator application and connect to the database server. You may be prompted to enter the password for your postgres user.
4. Expand Databases and right-click on the acronisaccess_production database.
5. Choose Maintenance -> Vacuum and press OK.
6. Expand the database, expand Schemas and expand Public. Take note of the number of the Tables section. This will help you verify that the database transfer is successful.
7. Close the PostgreSQL Administrator and open an elevated command prompt.
8. In the command prompt, navigate to the PostgreSQL bin directory.

   e.g. cd "C:\Program Files(x86)\Acronis\Files Advanced\Common\PostgreSQL\9.3\bin"

9. Enter the following command: pg_dumpall --host localhost --port 5432 --username postgres --file alldbs.sql
   • alldbs.sql will be the filename of the backup. It will be saved in the PostgreSQL bin directory. You can use a path in the above command if you wish to save it somewhere else - e.g. change the last part of the command above like so: --file D:\Backups\alldbs.sql
   • If you are using a non-default port, change 5432 to the correct port number.
   • If you are not using the default PSQL administrative account postgres, please change postgres to the name of your administrative account in the command above.
   • You will be prompted to enter the postgres user's password several times for this process. For each prompt, enter the password and hit Enter.

     Note: Typing the password will not result in any visual changes in the Command Prompt window.

10. Once you verify that the dump process is finished, stop the Old PostgreSQL instance and start the New one.

Inserting the database in the new instance

1. Make sure that the New instance of PostgreSQL is running and that the Old instance is stopped.
2. Open the Files Advanced PostgreSQL Administrator application, connect to the local database server, select Databases, and check if there is a database called acronisaccess_production. If there isn't one, you will have to create it.
3. Right-click on the database and select Refresh.
4. Expand it and expand Schemas, expand Public and verify that there are zero (0) Tables.
5. If there are any tables in the database, right click on the database and rename it to oldacronisaccess_production. Finally, go to Databases, right-click and create a new database called acronisaccess_production.
6. Close the PostgreSQL Administrator and open an elevated command prompt.
7. Copy the database backup file alldbs.sql (or whatever you have named it) into the bin directory of the new instance.
8. In the command prompt, navigate to the PostgreSQL bin directory.

   e.g. cd "C:\Program Files (x86)\Acronis\Files Advanced\Common\PostgreSQL\9.3\bin"

9. Enter the following command: psql -U postgres -f alldbs.sql
10. Enter your postgres password when prompted for it.

    Note: Depending on the size of your database, the restore can take some time.

11. After the restore is complete, close the command prompt window.

Verify that the new instance has the correct database

1. Open the Files Advanced PostgreSQL Administrator application and connect to the New database server. You may be prompted to enter the password for your postgres user.
2. Expand Databases and right-click on the acronisaccess_production database.
3. Expand the database, expand Schemas and expand Public.
4. Verify that the Tables section contains the same number of tables as the one you saw before.

Using pg_upgrade

The upgrading proccess

1. Stop the Files Advanced Tomcat service.
2. Make sure that both instances of PostgreSQL are running. The new instance will typically choose a different port if the Old one is running on the default port.
3. Open the Files Advanced PostgreSQL Administrator application and connect to the Old database server. You may be prompted to enter the password for your postgres user.
4. Expand Databases, expand the database, expand Schemas and expand Public. Take note of the number of the Tables section. This will help you verify that the database transfer is successful.
5. Close the PostgreSQL Administrator.
6. Make sure that both PostgreSQL instances can access each-other. This can be done by checking if the pg_hba.conf file has an entry for localhost (127.0.0.1/32) with Trust as the authentication method.

   Note: If the New instance is on another machine, you must configure access to that machine.

7. Open an elevated command prompt and navigate to the New PostgreSQL bin directory with the cd command.

   e.g. cd C:\Program Files(x86)\Acronis\Files Advanced\Common\PostgreSQL\9.5\bin

8. Use the pg_upgrade command with the following parameters:

   pg_upgrade -b <OLD_BIN_FOLDER> -B <NEW_BIN_FOLDER> -d <OLD_DATA_FOLDER> -D <NEW_DATA_FOLDER> -U postgres

   Note: OLD_BIN_FOLDER refers to the bin folder of the PostgreSQL installation that you wish to upgrade. It's the same for the Data folder.

   Note: NEW_BIN_FOLDER refers to the bin folder of the new PostgreSQL installation. It's the same for the Data folder.

Verify that the new instance has the correct database

1. Open the Files Advanced PostgreSQL Administrator application and connect to the New database server. You may be prompted to enter the password for your postgres user.
2. Expand Databases and right-click on the acronisaccess_production database.
3. Choose Maintenance -> Vacuum and press OK.
4. Right-click on the acronisaccess_production database again.
5. Choose Maintenance -> Reindex and press OK.
6. Expand the database, expand Schemas and expand Public.
7. Verify that the Tables section contains the same number of tables as the one you saw before.