Migrating the Database (till version 4.2.x)

Note: if you are migrating Azure Database for PostgreSQL from Single Server to Flexible Server with the migration service, please follow official Microsoft documentation.

The following steps describe how to migrate the data from the JetPatch database from one server to another. This assumes the same major version of JetPatch from one server to another.

Prerequisites

• Original DB access details. These can be obtained from the file /usr/share/tomcat/default/conf/intigua.properties:

  _{* Please note that after changing the values in the intigua.properties file, a sever restart is required. You may contact us to learn more about these changes.}

  • The username is the value of the property db.user
  • The password is the value of the property db.password
  • The host, port and database name can be taken from the db.url property, which has the format jdbc:postgresql://<host>:<port>/<database>.

    In case the port has been omitted in this property, this means that the default port 5432 is used.

• Target database access details. This includes the hostname, port, database name, username, and password.
• The target database must be ready to work with the JetPatch server: it must allow access from the JetPatch server on the selected port, and must allow remote login by the new user using the new password
• The target database server must already include an empty database for JetPatch to use. The database user must be the owner of this database.

As a security best practice, it is highly recommended not to use a database superuser as the JetPatch user. Instead, log in as an a superuser, and create a new user and empty database:

CREATE USER <username> WITH CREATEDB PASSWORD '<password>';

CREATE DATABASE <dbname> WITH OWNER <username>;

If specifying the password as part of the user creation command, it is suggested to immediately change the password for the created user with a prompt-based utility. From the psql superuser prompt, this can be done with:

\password <username>

Database Migration Steps

1. On the JetPatch server, stop the tomcat service to prevent it from writing to the database during the migration:

   service tomcat stop

2. Dump the database into a file. You can run this on any server that has the pg_dump utility:

   pg_dump --host=<orighost> --port=<origport> --username=<origuser> --dbname=<origdbname> --format=p --no-owner --file=<db_dump_filename>

3. Restore the database contents into the new database:

   psql --host=<newhost> --port=<newport> --username=<newuser> --dbname=<newdbname> < <db_dump_filename>

4. You can now test the restore by connecting to the new database and running a query. For example, you could use psql and check the users table:

   echo 'SELECT username FROM appuser;' | psql --host=<newhost> --port=<newport> --username=<newuser>

5. To make JetPatch use the new database, go back to the server configuration file at /usr/share/tomcat/default/conf/intigua.properties, and change the db.url property to reflect the new database settings:

   db.url=jdbc:postresql://<hostname>:<port>/<dbname>

   db.username=<username>

   db.password=<password>

6. Restart the tomcat service with "service tomcat restart". Then wait a few minutes, and access the JetPatch user interface to verify it's working correctly.
7. Once the migration has finished, you can proceed to delete the database dump file, and optionally also the old database.