Backup and Restore

This document describes backup and restore processes for JChem PostgreSQL Cartridge.

Application side

See here the recommended methods for archiving the application side (the service jchem-psql) of JChem PostgreSQL Cartridge.

Database side

Here we describe three methods - SQL dump, File system backup and Online backup - as possible backup and restore processes for the database side of JChem PostgreSQL Cartridge.

SQL dump method

A user has to be created with superuser privilege to perform the processes on the database and to use it with the database instances.

CREATE USER user_name WITH PASSWORD 'some_password' SUPERUSER

In the following scripts these parameters have to be adjusted accordingly:

user_name → the database user to use
some_password → password for the created user
input_db → the name of the source database
some_database → the name of the target database
schema_name → the schema name of the original database where the objects are stored
table_name → the name of the table to be imported
/path/to/dump_file.dmp → path to the directory and the file name to store the dump
molecule_column_name→ the name of the molecule type column where the index has to be created

Database dump

Backup creation

Compressed archive dump This dump is compressed by default.

pg_dump input_db -Fc > /path/to/dump_file.dmp

createdb some_db -O user_name

pg_restore -v -d some_db -U dumpload -W < /path/to/dump_file.dmp

ALTER USER user_name SET search_path='schema_name';

Schema dump

pg_dump input_db -Fc -n schema_name> /path/to/schema_dump_file.dmp

If -Fc is not given, it will result a plain text file.

Table dump

pg_dump input_db -Fc -t schema_name.table_name> /path/to/table_dump_file.dmp

If -Fc is not given, it will result a plain text file.

Restore

Overriding existing database

This scenario has to be followed if the restore process is to overwrite an existing database. If there are some missing objects in the existing database, the -e switch has to be removed and just the -vc switch has to be applied instead of -vce, otherwise the command will fail.

pg_restore -vce -d some_db -U user_name -W < /path/to/dump_file.dmp

Schema restore

Prerequisites:

schema has to be created or exists
extensions need to be created

CREATE SCHEMA schema_name AUTHORIZATION user_name;
CREATE EXTENSION chemaxon_type;
CREATE EXTENSION hstore;
CREATE EXTENSION chemaxon_framework;

pg_restore -v -d some_db -n schema_name -U user_name -W < /path/to/dump_file.dmp

Table restore

Prerequisites:

schema has to be created or exists
extensions need to be created

CREATE SCHEMA schema_name AUTHORIZATION user_name;
CREATE EXTENSION chemaxon_type;
CREATE EXTENSION hstore;
CREATE EXTENSION chemaxon_framework;

pg_restore -v -d some_db -n schema_name -t table_name -U user_name -W < /path/to/dump_file.dmp

Post-processing

After overriding existing database and after schema restore

Indexes are automatically recreated. The restored cartridge is working without requiring any post-processing.

After table restore

Manual index creation is needed.
```
CREATE INDEX index_name ON table_name USING chemindex(molecule_column_name);
```
or
```
CREATE INDEX index_name ON table_name USING sortedchemindex(molecule_column_name);
```

File system backup method

Backup creation

Backup the PostgreSQL data from the data directory of the database and backup also the cartridge data using file system backup. Example for PostgreSQL 17 on Ubuntu:
```
tar -cf /tmp/pgdata.tar /var/lib/postgresql/17/main
tar -cf /tmp/jchem-psql.tar /var/lib/jchem-psql
```

Restore Delete the current PostgreSQL and cartridge data directories and extract the backed up tar files to restore the state:

rm -rf /var/lib/postgresql/17/main
tar -xf /jpc-backup/pgdata.tar -C / 

rm -rf /var/lib/jchem-psql/
tar -xf /jpc-backup/jchem-psql.tar -C /

Post-processing The restored cartridge is working without requiring any post-processing.

Online backup method

Backup creation You need to set up postgres.conf. This will tell the database to archive WAL logs into the given folder.
```
archive_mode = on
archive_command = 'test ! -f /path/to/write_ahead_log/archive/folder/%f'
```
Also you need to set up pg_hba.conf to enable replication privilege. If it is not happening from local, set up accordingly.
```
local   replication     all                                     trust
```
To create an online backup you need a database user with replication or superuser privilege.

First you need to open a psqlwindow with the above mentioned user and run the command below. This will tell the database that you are about to create a backup and stay in a consistent state from now. Until the finish command all changes go into WAL files.
```
SELECT pg_backup_start(label => 'some_label', fast => false);
```
Then with postgres linux user create a base backup.
```
pg_basebackup -U user_name-W -l label_for_backup -D /path/to/base_backup/folder/ -Fp -Xs -P -v
```
After backup is done, go back to psqland close the backup session:
```
SELECT * FROM pg_backup_stop(wait_for_archive => true);
```
Restore To restore from backup, you need to copy the content of the base backup folder to the database’s data folder. In the same folder, create a recovery.signal empty file.

In postgres.conf file insert
```
restore_command = 'cp /path/to/write_ahead_log/archive/folder/%f %p'
```
If you have the WAL archive and want to roll database to an exact point in time also add:
```
recovery_target_time = '2025-06-27 01:19:30'
recovery_target_inclusive = 'on'
```
Restart the database. After the recovery is done remove recovery.signaland the recovery related lines from postgresql.conf and restart the database.
Post-processing If you are recovering into a new database be sure that the service jchem-psql is initialized by running the service initcommand and then starting the service by the startcommand. Modification of the host and port data of the PostgreSQL server and of the application server may also be necessary before initializing the service jchem-psql. See configuration instructions here. After the recovery, the database will not be in sync with the cartridge index data, so you need to reindex all the tables containing chemindexes or sortedchemindexes.