8.5 Restoring the PostgreSQL Database

Restoring an archived database is performed by using the pg_restore command. Using the pg_restore command, you can restore the backup taken by both pg_dump and AutomaticPostgresMaintenanceConfigureAction.

To restore a backup, use the following command:

pg_restore [connection-option...] [option...] [filename]

For more information on options, see pg_restore.

8.5.1 Restoring the Database Backup Taken Using the ZMAN Command

To restore a backup that was taken using the zman command (zman db), perform the following:

On Linux

  1. Stop the services using the following commands:

    • systemctl stop microfocus-zenserver.service

    • systemctl stop microfocus-zenloader.service

  2. (conditional) If Vertica is enabled in your zone, then stop the following services in ZENworks Appliance, using following command:

    • systemctl stop zenworks-connect.service

    • systemctl stop zenworks-kafka.service

  3. Close the database dangling connections by running the following commands:

    SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = pg_backend_pid();

  4. Change the user by running the su zenpostgres command.

  5. Set the super user password for pg_restore and dropdb using the following command:

    $ PGPASSWORD=<<set superuser password>>; export PGPASSWORD

  6. Set the library path and BIN path as shown below:

    • LD_LIBRARY_PATH=/opt/microfocus/zenworks/share/pgsql/lib; export LD_LIBRARY_PATH

    • PATH=$PATH:/opt/microfocus/zenworks/share/pgsql/bin; export PATH

  7. Drop the existing database, the database name can be zenworks or audit database.

    $ dropdb --host=localhost --dbname=<<database_name>> --port=54327 --username=<<superuser_name>> <<database_name>>

    $ dropdb --host=localhost --dbname=<<database_name>> --port=54327 --username=<<superuser_name>> <<database_name>>

  8. Restore the database by running the following command:

    $ pg_restore --host=localhost --port=54327 --username=<<superuser_name>> --format=directory --dbname=<<database_name>> -C <<full path to zenDB_pg_dump>>

    $ pg_restore --host=localhost --port=54327 --username=<<superuser_name>> --format=directory --dbname=<<database_name>> -C <<full path to zenDB_pg_dump>>

NOTE:Ensure that the dump directory is accessible from zenpostgres or postgres user.

On Windows

  1. Stop the following services using the Windows Services MSI:

    • microfocus-zenserver.service

    • microfocus-zenloader.service

  2. (conditional) If Vertica is enabled in your zone, then stop the following services in ZENworks Appliance:

    • zenworks-connect.service

    • zenworks-kafka.service

  3. Close the database dangling connections by running the following commands from PSQL:

    • SELECT pg_terminate_backend(pid)

    • FROM pg_stat_activity

    • WHERE pid = pg_backend_pid

  4. Set the super user password for the pg_restore and dropdb commands:

    set PGPASSWORD=<<set superuser password>>

  5. Change the command line to the PostgreSQL bin folder path as shown below:

    cd /opt/microfocus/zenworks/share/pgsql/bin/

  6. Drop the existing database by running the following command. The database name can be zenworks or audit database.

    dropdb --host=localhost --port=54327 --username=<<superuser_name>> <<database_name>>

  7. Restore the database by running the following command:

    pg_restore --host=localhost --port=54327 --username=<<superuser_name>> --format=directory --dbname=<<database_name>> -C <<full path to zenDB_pg_dump>>

NOTE:Ensure that the dump directory is accessible from zenpostgres or postgres user.