Skip to main content

Migrating from PostgreSQL 8.x to 9.x Using pg_dumpall

Between PostgreSQL versions 8.x and 9.x, several changes occurred that prevent a smooth transition.

Written by Petr Pech

For more information about migration methods, see the article Moving FlexiBee to a New Server.

The pg_dumpall approach appears to be the simpler option, especially if you have multiple companies and users. However, there are certain pitfalls to be aware of. First, you create a complete backup using pg_dumpall, transfer it to the new server, install the FlexiBee server, and then restore the backup using psql. After that, you connect to each company database and make a few adjustments:

  1. Version 9.x began enforcing access rights on "large objects". Use the following function to set the correct permissions:

  2. You also need to add the unaccent extension to the database: CREATE EXTENSION IF NOT EXISTS unaccent;

  3. Next, you need to configure the same string behavior as in 8.x (disabling the SQL standard behavior)

  4. Finally, verify the correct settings for the company database and template0: Encoding - UTF8, Collate - cs_CZ.UTF-8, Ctype - cs_CZ.UTF-8

Replace %firmaDbName% and %firmaRole% with the name of the database and the name of the role (the role is typically in the format "companynamerole", for example, a company named "Test" would have the role "testrole"). These adjustments must be made for each company database. It is important to install FlexiBee before restoring the databases so that the correct locale is configured.

Did this answer your question?