7.3 Restoring the Database on another Server

  1. Stop the ZENworks Services on the Primary Servers where the database is to be restored, by running the following configure action:

    microfocus-zenworks-configure -c Start

    After running the command, under Daemons, deselect Micro Focus ZENworks Embedded PostgreSQL, under Action, select Stop.

  2. On the Windows Primary Server:

    1. Open the command prompt and set the following variables based on the existing configuration:

      • SET PGPORT=54327

      • SET PGUSER=<PostgreSQL super user>

      • SET PGDATABASE=postgres

      • SET PGPASSWORD=<Postgres super user password>

    2. cd "%ZENSERVER_HOME%\share\postgres\bin\"

  3. On the Linux server:

    1. Switch the user to zenpostgres by running the command su zenpostgres

    2. Open the terminal and set the following variables based on the existing configuration.

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

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

      • export PGPORT=54327;

      • export PGUSER=<Postgres super user retrieved from zman dgcs of the target database device>;

      • export PGDATABASE=postgres;

      • export PGPASSWORD=<password retrieved from zman dgcs of the target database device>;

  4. Start PSQL connection by running ./psql.

  5. Close the database dangling connections by running the following command:

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

  6. Drop the following databases and users:

    • DROP DATABASE ZENWORKS;

    • DROP DATABASE ZENWORKSAUDIT;

    • DROP USER ZENAUDITADMIN;

    • DROP USER ZENADMIN;

  7. Create the database using the following query for the databases that should be restored:

    create database <DB name> with template=template0 encoding='UTF8';

    For example:

    • ZENworks Database: create database zenworks with template=template0 encoding='UTF8';

    • Audit Database: create database zenworksaudit with template=template0 encoding='UTF8';

  8. Run following command to change the password for user 'zenpostgres':

    ALTER ROLE zenpostgres WITH PASSWORD '<password retrieved from zman dgcs of the source db machine>';

  9. Exit PSQL.

  10. Set the following variables again:

    • PGPORT: <existing source DB port configured>

      By default, ZENworks uses 54327

    • PGDATA: <embedded_database_location>

    • PGUSER: <existing super user for ZENworks postgres DB> The default user for ZENworks is zenpostgres.

    • PGDATABASE: <existing database name> ZENworks default is postgres

    • PGPASSWORD: <password for the above super user PGUSER>

    Example 7-3 Example for Windows Primary Server

    Open the command prompt and set the following variables based on the existing configuration.

    • SET PGPORT=54327

    • SET PGDATA= %ZENSERVER_HOME%\database\pgsql\data

    • SET PGUSER=<super user retrieved from zman dgcs of the source database device>

    • SET PGDATABASE=postgres

    • SET PGPASSWORD=<password retrieved from zman dgcs of the source database device>;

    Example 7-4 Example for Linux Primary Server:

    Open the terminal and set the following variables based on the existing configuration.

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

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

    • export PGPORT=54327;

    • export PGDATA=/var/opt/microfocus/pgsql/data;

    • export PGUSER=<super user retrieved from zman dgcs of the source database device>;

    • export PGDATABASE=postgres;

    • export PGPASSWORD=<password retrieved from zman dgcs of the source database device>;

  11. Restore the database by running the following commands:

    • On Windows: In the Command prompt, run the following commands:

      • psql.exe -v ON_ERROR_STOP=0 -f c:\postgres_dump.sql -o c:\postgres_dump_log.txt -L c:\postgres_dump_log.log > c:\postgres_dump_log.err 2>&1

        • -f <filename>: Uses the filename as the source of SQL commands

        • -l <filename>: Writes all query output into the file, in addition to the normal output destination

        • -o <filename>: Logs all query output into the file

        All the errors will be redirected to <error_log_location>.

        psql.exe -v ON_ERROR_STOP=0 -f c:\tmp\postgres_dump.sql -o c:\tmp\postgres_log.txt -L c:\tmp\postgres_log.log >c:\postgres\postgres_log.err 2>&1

    • On Linux:

      In the Terminal, run the following commands as PostgreSQL user:

      • ./psql -v ON_ERROR_STOP=0 -f <dump_location> -o <log_location> -L <log_location> > <error_log_location> 2>&1

        • -f <filename>: Uses the file filename as the source of SQL commands. Ensure that the PostgreSQL user has access to the dump being imported.

        • -L <filename>: Writes all query output into the file, in addition to the normal output destination

        • -o <filename>: Logs all query output into the file

        All the errors will be redirected to <error_log_location>.

        ./psql -v ON_ERROR_STOP=0 -f /tmp/postgres_dump.sql -o /tmp/postgres_log.txt -L /tmp/postgres_log.log > /tmp/postgres_log.err 2>&1