D.6 Migrating between PostgreSQL versions

There are generally 2 ways to upgrade PostgreSQL that apply to normal LSMB installations. Both are described in the current PostgreSQL documentation at https://www.postgresql.org/docs/current/index.html.

1. 1.

   Dump and restore - Described in more detail below. Note that the roles must be restored before database creation and the data restore.

2. 2.

   Upgrade in place using pg_upgrade - When doing an upgrade in place, there are no specific LedgerSMB requirements. Beware, upgrade in place requires both versions of the PostgreSQL to be installed and working. It is therefore more complex.

LedgerSMB versions prior to version 1.9.16 did not support PostgreSQL 14 or higher due to a backward incompatible change in PostgreSQL 14. Best practice in this situation is to update to at least LSMB 1.10. prior to upgrading to any version of PostgreSQL 14 or higher.

The following upgrade procedure uses lsmb_dbadmin for the database administrative user.¹¹ 1 Default LedgerSMB installations do not automatically create an lsmb_dbadmin user. This user is created manually during the initial database installation. For an example see, https://ledgersmb.org/index.php/content/installing-ledgersmb-111. But if the original database installation did not follow the recommended installation practice, then the postgres superuser may have to be used. Using the postgres superuser is not considered best practice.

The following upgrade procedure should not be used when upgrading a cloud provided PostgreSQL installation. In this case, the cloud provider’s instructions have to be consulted to determine the proper upgrade procedure.

The upgrade procedure when using dump and restore with a local LSMB native or docker installation is:

1. 1.

   Stop users from accessing the current LSMB installation and make sure all users are logged off. The instructions for this step vary depending on how LSMB was installed. Note that access to setup.pl will be needed to create backups.

2. 2.

   Make sure the current LedgerSMB installation is using the latest version. If not, see the Section D.2 on page D.2 for instructions on upgrading LSMB.

3. 3.

   Make a dump of both the roles and data using setup.pl.

4. 4.

   Inspect the backup to make sure it is complete. Ideally, this inspection would include restoring the backup to temporary LedgerSMB installation with the same versions as the existing installation. Then make sure it is complete by logging into the temporary LedgerSMB installation and inspecting reports, accounts, etc. Do not proceed unless you are sure your backups can be restored completely and accurately.

5. 5.

   Create a new LedgerSMB installation with the new version PostgreSQL. The instructions for a new install are at https://ledgersmb.org/installation.

6. 6.

   The next 3 restore steps, using psql, came from https://ledgersmb.org/faq/how-do-i-restore-my-data, which these instructions supersede. Other restore tools might work, but they have not been tested.

7. 7.

   Restore the ”roles” backup. The psql syntax is:

   $ psql -h [database host] -U [database admin user] < [roles backup file]

   For example:

   $ psql -h localhost -U lsmb_dbadmin < lsmb-roles.sqlc

8. 8.

   Create a new database. The database name is usually very similar to the company name. Do not create the database using setup.pl as that creates the company structure inside the database, which will interfere with the restore. The psql syntax is:

   $ psql -h [database host] -U [database admin user] -c ’CREATE DATABASE [new company name]’

   For example:

   $ psql -h localhost -U lsmb_dbadmin -c ’CREATE DATABASE newcompany’

9. 9.

   Restore the ”data” backup. The psql syntax is:

   $ psql -h [database host] -U [database admin user] [new company name] < [company backup file]

   For example:

   $ psql -h localhost -U lsmb_dbadmin newcompany < lsmb-db.sqlc

10. 10.

    Read the beginning section of the PostgreSQL release notes for each PostgreSQL version from the old to the new and determine if there are any additional steps that must be performed on the database after the restore. If there are, do them now.

11. 11.

    Verify all data restored and that LedgerSMB is working correctly.

12. 12.

    Replace the old LSMB installation with the new installation. The exact steps to accomplish this varies based on how LSMB was originally installed.

13. 13.

    Allow users to use LSMB.