Last modified by Sebastian Marsching on 2022/04/03 13:54

EJBCA is a software suite for managing a public key infrastructure (PKI) entirely written in Java and running in a Java Enterprise Edition (JEE) application server.

Migrating the EJBCA Database from H2 to PostgreSQL

When I had to migrate the EJBCA (6.x) database from H2 to PostgreSQL, the following steps worked for me:

  1. Make a backup of EJBCA, JBoss (or whichever application server you are using), and the EJBCA database files. You should stop the application server for doing this.
  2. Create a PostgreSQL user for EJBCA. You have to set a password for this user, because the JDBC driver does not support UNIX domain sockets. Example: CREATE USER ejbca WITH PASSWORD 'mypassword';
  3. Create a database for EJBCA. Example: CREATE DATABASE ejbca;
  4. Give the EJBCA user full access to the database. Example: GRANT ALL PRIVILEGES ON DATABASE ejbca TO ejbca;
  5. Install the PostgreSQL JDBC driver. I use JBoss AS 7.x and Java 7, therefore I used the JDBC 4.1 version. There are quite good instructions on how to install the driver on Stack Overflow.
  6. Stop the application server.
  7. Modify $EJBCA_HOME/conf/database.properties for your PostgreSQL database.
  8. Rebuild EJBCA, but do not deploy it. You can do this by running ant clean && ant build.
  9. Modify the data-source configuration in $JBOSS_HOME/standalone/configuration/standalone.xml to use the PostgreSQL driver and the new JDBC URI.
  10. Copy $EJBCA_HOME/dist/ejbca.ear to $JBOSS_HOME/standalone/deployments/ejbca.ear.
  11. Start the application server. After it has started completely, stop it again. The tables that are needed by EJBCA should now have been created in the PostgreSQL database.
  12. Be sure to delete all records from the tables in the PostgreSQL database. There are various ways to do this: I dumped the SQL schema with pg_dump and re-imported it, dropping all existing tables. Running DELETE statements on all tables or using SQuirrel SQL from the next step should also work. Whatever you do, make sure to delete all records before continuing with importing the existing data from the H2 database.
  13. Install and start SQuirreL SQL. You have to install and configure the H2 and PostgreSQL drivers. Create connections for both the old H2 and the new PostgreSQL database. If everything went right, you should now see the same tables for both connections. For each table in the H2 database choose copy from the context-menu and paste it in the PostgreSQL database. I proceeded alphabetically, however the table admingroupdata has to be copied before accessrulesdata and adminentitydata. A table with the name dbcopytest might be created automatically. After you have copied all tables, you can safely drop this table.
  14. Start the application server. Everything in the EJBCA administration interface should work as before. Be sure to test all functionality, before going back into normal operation.
Tags: Network

Need help?

If you need help with XWiki you can contact: