PostBooks is an accounting, CRM and ERP suite from xTuple
It is notable for having a high-quality GUI and using a proper SQL database backend. It is considered to be superior to commercial solutions such as Quickbooks but easier to get started with than frameworks such as Adempiere/Compiere and OpenERP.
The aim of this wiki page is to cover system/technical issues for installation, system management and upgrade on Debian systems
Functional/business issues can be discussed through the xTuple web forum as they are not specific to Debian.
PostgreSQL version issues
Using PostgreSQL 9.2 or 9.3 from jessie/unstable results in an error:
ERROR: column "procpid" does not exist
This is due to changes to system tables in PostgreSQL. The GUI has been patched to check the PostgreSQL server version and display a more helpful error. This is discussed in bug #724305
Super user access
The PostBooks admin user is a superuser in the PostgreSQL server. All PostBooks users created by the administrator automatically become PostgreSQL users too.
Therefore, it is recommended that you do not host any unrelated PostgreSQL databases in the same PostgreSQL instance, to avoid any accidents or overlapping access rights between any PostBooks users.
Best practice is to run the PostgreSQL server for Postbooks in a virtual machine, for example, using Xen Cloud Platform on Debian. This will ensure that the machine is isolated from any unrelated upgrades and no other databases will be accessible to the PostBooks administrator/users.
Upgrades and versions
The Debian packages provide the GUI application used to access a PostBooks database.
Upgrading the package is trivial. However, a client package upgrade does not upgrade the database schema.
It is necessary for all clients on a network to use exactly the same version. The client version must match exactly with the schema version. The client will always check the schema version at login time and will immediately display an alert if a mismatch is detected. It is suggest that users should not bypass this alert and use the client with a mismatched schema version.
To ensure that versions remain synchronized across all machines on a network, it is recommended that you pin the version of the package with dpkg, for example:
echo "postbooks hold" | dpkg --set-selections
When you do want to upgrade
Upgrading a client/server product of this nature is not necessarily something that the Debian postinst script can automate. For maximum control and to ensure you are backing up the data, the Debian maintainers suggest you run the upgrade manually.
Here is a suggested procedure:
- make a backup of the PostgreSQL database (use pg_dump)
- set up a test client and test server for a trial run, try the whole procedure in the test environment first
- upgrade the Debian OS and the PostgreSQL server version if required by the upgrade (check upstream compatibility matrix)
- use the upgrade scripts from upstream to upgrade the database schema
- upgrade one client, verify that it can log in
- upgrade the remaining clients
If using virtualisation, and if a PostgreSQL version upgrade is required, it is suggested that you create a new virtual machine for the new PostgreSQL version, use pg_dump and pg_restore to copy the database from the old VM to the new one, and then upgrade the schema in the new VM. Should the upgrade not complete satisfactorily, you can always go back to using the original VM and try the whole process again later.
Whichever way you go about it, you must take precautions to ensure no users can login and access the database during the upgrade process. Any changes they make in the old database after you pg_dump will be lost when you cut users over to the new server.
Using the database upgrade utility
Upstream provides a GUI tool for updating the database. It is available in the package postbooks-updater
Using the tool is fairly simple:
- install the package:
$ sudo apt-get install postbooks-updater
- download the update file you want from Sourceforge
look in the database section for files with names like pb40xto410.gz
- you must find all the intermediate update files from the version you have now to the version you want
- we are evaluating a convenient way to package these files and keep them in /usr/share/postbooks
- run the GUI:
- log in as the admin user
- load each of the files and let it work
- it can take a few minutes to run each upgrade