Tips for Migrating HSQL to PostgreSQL
In this article, I share a few tips that will be helpful for anyone working on a similar project.
Join the DZone community and get the full member experience.Join For Free
Recently, I was involved in a project to migrate DB from HSQL to PostgreSQL. While doing performance testing with a high volume dataset, we identified HSQL DB to be a bottleneck and wanted to see if switching to PostgreSQL will eliminate some of the drawbacks of HSQL. In this article, I share a few tips that will be helpful for anyone working on a similar project.
While creating a schema from the code, use the "AUTHORIZATION" clause. Your syntax should be "CREATE SCHEMA <schema-name> AUTHORIZATION <db-username>"
If you have used "blob" in your HSQL schema, then while migrating to PostgreSQL use "bytea" as the closest replacement for "blob".
For columns that are marked as "IDENTITY", an auto-increment clause is used e.g. "p_key integer not null generated always as identity (start with 1, increment by 1)" Then in PostgreSQL, remove the comma from between the increment clause.
In conjunction with using IDENTITY columns, if your code relies on reading the last incremented value of the column using "CALL IDENTITY()" statement then in PostgreSQL you need to use "SELECT LASTVAL()" for the same purpose.
Make PostgreSQL listen on all IP Addresses. This is mandatory if you want to connect or debug remotely. Search for "listen_addresses" and use '*' as its value. You can also change the port number (default 5432).
Maximum connections to DB, default is 100. Based on your application's requirement, you can set this number to a higher value. However, it is recommended to use a connections pool to safeguard from database overhead.
You can also control logging through this file. Search for "REPORTING AND LOGGING" section and follow the various configurations as per your needs. My recommendation is to enable Syslog and stream it to a log monitoring tool.
This file handles client authentication configuration and controls as per doc "which hosts are allowed to connect, how clients are authenticated, which PostgreSQL user names they can use, which
databases they can access". You need to edit this file to enable remote connections. The content is self-explanatory in this file.
In addition to the above, PostgreSQL has two important files where most of the developer-friendly configurations are defined: "postgresql.conf" and "pg_hba.conf". You can find these files under "C:\Program Files\PostgreSQL\11\data" or incase of Linux "/var/lib/pgsql/11/data/".
Let us see a few common edits for these files:
File : "pg_hba.conf"
Opinions expressed by DZone contributors are their own.