Skip to main content

Dump and restore DBmarlin PostgreSQL repository DB

info

Requires DBmarlin 4.11.0 or above.

The DBmarlin repository database is PostgreSQL but simply using the native pg_dump and pg_restore will not leave you with a working database due to TimeScaleDB hypertables and materialized hypertables which need special treatment. We provide wrapper scripts in the scripts directory to take care of this.

On source machine​

Where you are exporting from:

  1. Stop Tomcat with ./stop.sh -t so no new data is being written to PostgreSQL
  2. Start PostgreSQL with ./start.sh -p (if it isn’t already running).
  3. Use the wrapper script scripts/dbadmin/pg_dump.sh It takes 1 param which is the dump directory like this.
cd scripts/dbadmin
./pg_dump.sh [dump_dir]

# E.g.
# ./pg_dump.sh /mnt/volume1/pgdump

On the destination machine​

Where you are importing to:

  1. scp or sftp the dump file from the source machine to this machine.

  2. Do a clean install DBmarlin using the exact same version as the one you exported from. Complete the full install including running configure.sh

  3. Start PostgreSQL but leave Tomcat stopped. start.sh -p

  4. Use the wrapper script scripts/dbadmin/pg_restore.sh. It takes 1 param which is the dump file like this.

    cd scripts/dbadmin
    ./pg_restore.sh [dump_file]

    # E.g.
    # ./pg_restore.sh /mnt/volume1/pgdump/dbmarlin-postgresql.dump

    The reason you need to use the wrapper script is to avoid the potential pitfalls with using pg_dump and pg_restore with TimeScaleDB (see below).

  5. Start up Tomcat and Nginx and check all 3 processes are running.

    cd /opt/dbmarlin
    ./start.sh -t && ./start.sh -n
    ./status.sh
    nginx		(Running)
    tomcat		(Running)
    postgres	(Running)

  6. Check logs under dbmarlin/tomcat/logs and dbmarlin/postgresql/data/log for any errors.

Files provided​

These are the wrapper scripts provided. Only #1 and #2 are called directly and you can find them under scripts/dbadmin.

  1. pg_dump.sh - wrapper for pg_dump with correct params and LD_LIBRARY_PATH set.
  2. pg_restore.sh - wrapper for pg_restore with correct params and LD_LIBRARY_PATH set and several important post restore scripts which are needed for TimeScaleDB to avoid problems.
  3. drop_ts_insert_blocker.sql - Called automatically by pg_restore.sh . It removes triggers on TimeScaleDB chunks which shouldn’t be there.
  4. timescaledb_pre_restore.sql - Called automatically by pg_restore.sh . Disables TimeScaleDB jobs during restore.
  5. timescaledb_post_restore.sql - Called automatically by pg_restore.sh . Re-enables TimeScaleDB jobs.

Problems using pg_dump and pg_restore with TimeScaleDB​

info

This is just for background information and provides and explanation of why the wrapper scripts are used instead of just calling pg_dump and pg_restore directly.

The pg_restore.sh wrapper script does serval things to avoid problems restoring TimeScaleDB table using pg_restore.

  1. It calls timescaledb_pre_restore.sql before pg_restore and timescaledb_post_restore.sql afterwards. This is to disable the TimeScale jobs from running while data is importing.

  2. It calls drop_ts_insert_blocker.sql after the pg_restore . This drops the trigger ts_insert_blocker from all chunks of the hypertables and continuous aggregates (it shouldn't be there - only on the parent tables). This is a TimeScaleDB issue. See https://github.com/timescale/timescaledb-docker/issues/86 and https://github.com/timescale/timescaledb/issues/1298#issuecomment-505865516 for more details. If you don't run this you will see the following errors in the logs.

    Hint: Make sure the TimescaleDB extension has been preloaded.
          org.postgresql.util.PSQLException: ERROR: invalid INSERT on the root table of hypertable "_hyper_4_9_chunk"

    It also drops the trigger ts_cagg_invalidation_trigger from all chunks of the hypertables and continuous aggregates (it will be there after pg_restore which prevents it being created on the parent which is where it should be).

  3. It then extracts all the failed CREATE and ALTER statements from the pg_restore log file and amends them so they are correct, saves as post_restore.sql and then runs it.

    1. Changes ALTER TABLE ONLY to ALTER TABLE since ONLY option won't work. This allows the Primary Key and Foreign Keys on TimeScaleDB tables to be created.
    2. Reruns the CREATE TRIGGER ts_cagg_invalidation_trigger on the TimeScaleDB tables which will succeed this time now it was removed from the underlying chunks.