Database Management

Manually backing-up and restoring internal PostgreSQL database

Backing-up internal PostgreSQL database

To take a backup of the internal PostgreSQL database, perform the following steps:

  1. Stop Servicedesk service, by running the following command:

    service servicedesk stop

  2. witch to postgres user, by running the following command:

    sudo -su postgres

  3. Go to the root level (/) or to the /tmp to avoid permission issue warning

    Example: cd /tmp

  4. Take database backup and keep it in /tmp location with file name "servicedesk_db.backup"

    pg_dump --dbname=servicedesk --file=/tmp/servicedesk_db.backup --encoding=UTF8 --blobs --format tar

  5. Start the service desk service, by running the following command:

    service servicedesk start

Restoring the internal PostgreSQL database

To restore the internal PostgreSQL database, perform the following steps:

  1. Copy the database backup to the /tmp location

  2. stop Servicedesk service, by running the following command:

    service servicedesk stop

  3. Switch to postgres user, by running the following command:

    sudo -su postgres

  4. Go to the root level (/) or to the /tmp to avoid permission issue warning

    Example: cd /tmp

  5. Drop existing internal database.

    If the drop fails, then the database is being used. Example: servicedesk any other database viewer tool. Ensure that you stop those services)

    dropdb servicedesk

  6. Create empty internal database, by running the following command:

    createdb servicedesk --template=template0 --encoding=UTF8 --owner=sdadmin

  7. Restore database from the backup file, , by running the following command:

    pg_restore /tmp/servicedesk_db.backup --dbname=servicedesk -n public

  8. run vacuum command to optimize, by running the following command:

    vacuumdb -f -z servicedesk

  9. Start the service desk service, by running the following command:

    service servicedesk start

Using script to back-up and VACCUM the internal PostgreSQL database

Maintaining the health of an embedded database is crucial for ensuring consistent performance, reliability, and data safety. Two key tasks in database maintenance are performing regular backups and optimizing the database file through the VACUUM operation. This script offers the option to check the database size, create a backup, or perform a VACUUM on the embedded database.

Before taking a backup of the database, ensure that you check the database size and confirm that there is enough free disk space to save the backup. If sufficient disk space is not available, the backup script will fail.

Perform the following steps to run the script:

  1. Connect to the terminal.

  2. Stop the Service Desk service.

    To stop the Service Desk service from the console, run the following command:

    systemctl stop servicedesk

  3. Navigate to the script directory.

    Change to the /opt/novell/servicedesk-config/script/ directory by running:

    cd /opt/novell/servicedesk-config/script/

  4. Run the script, and follow the on-screen instructions:

    sh embedded-db-maintenance.sh

    • To check the size of the database, run:

      sh embedded-db-maintenance.sh -s

    • To take a database backup, run:

      sh embedded-db-maintenance.sh -b

    • To perform a VACUUM on the database, run:

      sh embedded-db-maintenance.sh -v

    The options above can be combined. For example, to take a database backup and perform a VACUUM, run:

    sh embedded-db-maintenance.sh -b -v

  5. The backup will be stored in the /tmp directory. Ensure you copy the backup to a reliable location for safekeeping.