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.
On the Windows Primary Server:
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>
cd "%ZENSERVER_HOME%\share\postgres\bin\"
On the Linux server:
Switch the user to zenpostgres by running the command su zenpostgres
Enter zenpostgres container prompt by running below command:
docker exec -it -e PGUSER=<super user retrieved from zman dgcs> -e PGDATABASE=postgres -e PGPASSWORD=<password retrieved from zman dgcs> zenpostgres bash
cd /opt/microfocus/zenworks/share/pgsql/bin
Start PSQL connection by running ./psql.
Close the database dangling connections by running the following command:
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = pg_backend_pid();
Drop the following databases and users:
DROP DATABASE ZENWORKS;
DROP DATABASE ZENWORKSAUDIT;
DROP USER ZENAUDITADMIN;
DROP USER ZENADMIN;
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';
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>';
Exit PSQL.
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-2 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>;
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:
Copy the postgres_dump.sql to /var/opt/microfocus/pgsql/data
Run the following command to restore the database:
./psql -w postgres -v ON_ERROR_STOP=0 -f /var/opt/microfocus/pgsql/data/<dump_file> -o /var/opt/microfocus/pgsql/data/<log_file> -L /var/opt/microfocus/pgsql/data/<log_file> > /var/opt/microfocus/pgsql/data/<error_log_location> 2>&1
-f <filename>: Uses the file 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>.
NOTE:Only /var/opt/microfocus/pgsql/data is accessible by zenpostgres. Hence, keep dump and logs in the same path.
For example: ./psql -w postgres -v ON_ERROR_STOP=0 -f /var/opt/microfocus/pgsql/data/dump.sql -o /var/opt/microfocus/pgsql/data/postgres_log.txt -L /var/opt/microfocus/pgsql/data/postgres_log.log > /var/opt/microfocus/pgsql/data/postgres_log.err 2>&1
NOTE:
The database schema already exists in the zenpostgres image. Hence, ERROR about existing schema and invalid locale should be ignored.
You can ignore the no version information available error message.
./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