Skip to content

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. 

1
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.

    1
2
3
4
5
6
7
    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

    1
    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

    1
    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.

    1
    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
    1
2
3
4
5
6
    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
    1
2
3
4
5
6
    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. 

    1
    CREATE INDEX index_name ON table_name USING chemindex(molecule_column_name);

    or

    1
    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:

    1
2
    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:

    1
2
3
4
5
    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.

    1
2
    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.

    1
    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.

    1
    SELECT pg_backup_start(label => 'some_label', fast => false);

    Then with postgres linux user create a base backup.

    1
    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:

    1
     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 

    1
    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:

    1
2
    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.
    ​