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.11 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.

D.6.1 Notes on migration from PostgreSQL 8.3 (or earlier) to 8.4 - deprecated

The content of this sub-section is deprecated, unedited, and only remains for historical reference purposes.

Performance benefit due to built in support for recursive queries only available after the next setup.pl run Also, after next setup.pl run, one should uninstall the tablefunc extension

In 9.1+ may need to DROP EXTENSION tablefunc;.

In 8.4, to 9.0 may need to uninstall_tablefunc.sql from the PostgreSQL contrib directory.

LedgerSMB 1.3 uses some extension modules for versions PostgreSQL 8.3 and before for functionality that has been built into PostgreSQL 8.4 and later. To make use of the (faster) built in version of that functionality, the following restore procedure should be used.

  1. 1.

    Migrate the database to the new function as described in section

  2. 2.

    If you’re using 9.1 and up, issue the command “CREATE EXTENSION tablefunc FROM UNPACKAGED” from a psql prompt when connected to the company database

  3. 3.

    Run ’setup.pl’ from your browser to upgrade the database’s routines; this command will install routines optimized for your version of PostgreSQL

  4. 4.

    To clean up functions and procedures in the database which are no longer used you may need to run one of the commands below:

    8.4 or 9.0

    psql ... -f uninstall_tablefunc.sql in a shell

    9.1 to 9.5

    DROP EXTENSION tablefunc; from a psql session connected to the company database