7.2 Moving an Embedded PostgreSQL Database from One Primary Server to another Primary Server

Moving the data is a two step process:

IMPORTANT:While moving the database, we refer the source database server as PSDB1 and the destination database server as PSDB2.

7.2.1 Step 1: Backup the Database

Backup the Database on the Embedded Windows Primary Server (PSDB1)

Before executing the following steps, ensure that you have reviewed the information documented in the section Important Commands and Locations.

Ensure that you have noted the database username, password of the source Primary Server by running the zman dgcs command.
Stop the ZENworks Services on all the Primary Servers by running the following configure action:

novell-zenworks-configure -c Start

microfocus-zenworks-configure -c Start

After running the command, under Action, select Stop.
On the Primary Server on which the Embedded Database is installed (PSDB1), start the Novell ZENworks Embedded Datastore service using the following steps:

On Windows: Press the Win + R keys on the keyboard to open the Run window. Type services. msc and then hit Enter.

Start the NovellZENworks Embedded Datastore – PostgreSQL service.
Login to the Primary Server that has the database role.

To know the Primary Server that has the database role, log into ZCC and click Diagnostics.
On the Primary Server on which the Embedded Database is installed (PSDB1), initiate the backup by setting the following variables:
- 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-1 Example for Windows Primary Server

Open the command prompt and set the following variables based on the existing configuration.
- SET PGPORT=54327
- SET PGDATA=%ZENWORKS_HOME%\database\pgsql\data%ZENSERVER_HOME%\database\pgsql\data
- SET PGUSER=<super user retrieved from zman dgcs>
- SET PGDATABASE=postgres
- SET PGPASSWORD=<password retrieved from zman dgcs>;
Run the following commands to back up both the databases (ZENworks and Audit):
- - cd "%ZENWORKS_HOME%\share\postgres\bin\"
  - pg_dumpall.exe > "<path to sql file>"
    
    Example: pg_dumpall.exe > c:\postgres_dump.sql
On the Primary Server on which the Embedded Database is installed, Stop the Novell ZENworks Embedded Datastore service using the following steps:

On Windows: Press the Win + R keys on the keyboard to open the Run window. Type services. msc and then hit Enter.

Stop the Novell ZENworks Embedded Datastore – PostgreSQL service.

Stop the Micro Focus ZENworks Embedded Datastore – PostgreSQL service.
Copy the postgres_dump.sql file to the target Primary Server.
Copy the dmsuperaccount.properties file from source Primary Server to the target Primary Server.

The dmsuperaccount.properties file is available in the following location:
- On Linux: /etc/opt/novell/zenworks/datamodel/
- On Windows: %zenworks_home%\conf\datamodel\

Backup the Database on the Embedded Linux Primary Server (PSDB1)

Before executing the following steps, ensure that you have reviewed the information documented in the section Important Commands and Locations.

Ensure that you have noted the database username, password of the source Primary Server by running the zman dgcs command. Also, copy the target Primary Server’s GUID details, which is required while restoring the database.
Stop the ZENworks Services on all the Primary Servers by running the following configure action:

novell-zenworks-configure -c Start

microfocus-zenworks-configure -c Start

After running the command, under Action, select Stop.
Log in to the Primary Server that has the database role.
On the Primary Server on which the Embedded Database is installed (PSDB1), start the Micro Focus ZENworks Embedded Datastore service using the following command on SLES 12 and later:

systemctl start zenpostgresql.service
Run the following commands to back up the databases (ZENworks and Audit):

docker exec -e PGUSER=<super user retrieved from zman dgcs> -e PGDATABASE=postgres -e PGPASSWORD=<password retrieved from zman dgcs> zenpostgres pg_dumpall > /tmp/dump.sql

Example: docker exec -e PGUSER=zenpostgres -e PGDATABASE=postgres -e PGPASSWORD=Zw0#3dde8547adb388306de141850 zenpostgres pg_dumpall > /tmp/dump.sql

NOTE:You can ignore the no version information available error message.
Start the Micro Focus ZENworks Embedded Datastore service using the following command:
- On SLES 12 and later: systemctl start zenpostgresql.service
NOTE:Keep the /tmp/dump.sql in a safe place as it has the database backup.

7.2.2 Step 2: Restoring the Database

Depending on the target database platform, refer to the following section to restore the database:

Restore the Database on a Windows Primary Server (PSDB2)

If you are planning to restore the database on a Windows server, then perform the following steps:

NOTE:While restoring the database, if the following error is displayed, then install the vc_redist.x64.exe and try again.

The vc_redist.x64.exe file is available in the following location:

<zenworks_mount_directory>: \Common\include

Install PostgreSQL by running the following command:

msiexec /i <postgresql_MSI_FILE> TARGETDIR="%ZENWORKS_HOME%\share" ALLUSERS=2

msiexec /i <postgresql_MSI_FILE> TARGETDIR="%ZENSERVER_HOME%\install\downloads\msi" ALLUSERS=2

For example:

msiexec /i "%ZENWORKS_HOME%\install\downloads\msi\novell-zenworks-postgres-11.4.x86_64.msi" TARGETDIR="%ZENWORKS_HOME%\share" ALLUSERS=2

msiexec /i "%ZENSERVER_HOME%\install\downloads\msi\microfocus-zenworks-postgres-.x86_64.msi" TARGETDIR="%ZENSERVER_HOME%\share" ALLUSERS=2
After installing the PostgreSQL, if the database folder is not available, create the folder as shown below folder structure, and ensure that you give write permission to the folder:

%ZENWORKS_HOME%\database\pgsql\data

To provide write permissions, perform the following steps:
1. In Windows Explorer, right-click a file or folder and select Properties.
2. In the Properties window, click the Security tab.
3. In Group or user names, select a user, and then click Edit.
4. In the Permissions window, select the logged in user.
5. Select Allow for the Write permission and then click OK or Apply.
Open the command prompt, and then set the following environment variables:
- PGDATA: <embedded_database_location>
- PGPORT: <existing source DB port configured> ZENworks default is 54327
- PGDATABASE: <existing database name> ZENworks default is postgres
Example:
- PGDATA=%ZENSERVER_HOME%\database\pgsql\data
- PGPORT=54327
- PGDATABASE=postgres
Go to %ZENWORKS_HOME%\share\postgres\bin%ZENSERVER_HOME%\share\postgres\bin, and then run the pg_ctl.exe initdb command.

In PSDB2, edit the pg_hba.conf and postgresql.conf files as shown below:

The pg_hba.conf and postgresql.conf files are available in the following location:

%ZENWORKS_HOME%\database\pgsql\data

%ZENSERVER_HOME%\database\pgsql\data

On Linux:

/etc/opt/novell/zenworks/datamodel/

/etc/opt/microfocus/zenworks/datamodel/

In the pg_hba.conf file, update the content as shown below:

# TYPE DATABASE  USER   ADDRESS     METHOD
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
host    all     all     0.0.0.0/0   trust
host    all     all     ::0/0   trust

NOTE:Ensure that you replace trust with to md5 value in the pg_hba.conf.

In the postgresql.conf file, uncomment the listen_addresses and Port entry keys and update the values as shown below:

listen_addresses = '*'

Port= 54327

max_connections = 500

Restart the server.
After restarting the server, open the command prompt, go to %ZENWORKS_HOME%\share\postgres\bin%ZENSERVER_HOME%\share\postgres\bin, and then set the following environment variables:

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

SET PGPORT=54327

SET PGDATABASE=postgres

NOTE:Since the device was restarted, you need to set these environment variables again.
Run the pg_ctl.exe start command.
To connect to the destination database server (PSDB2), run the psql.exe -w postgres command. By default, postgres is the database name.

In the same window, you will be logged into the PostgreSQL database.

The get the database details, see Important Commands and Locations.
Run the following command:

create user zenpostgres with password '<password that was obtained using zman dgcs>';

Example:

create user zenpostgres with password 'Zw0#dd5352592267b601daa85cb39';
Create the database for ZENworks and Audit databases, run the following command:

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';

If you have enabled Antimalware, then run the following:
NOTE:
- Names of the databases that are being created should exactly match (considering case) with the source database names.
- The source database names can be retrieved from the zdm.xml and zenaudit.xml files in the source database server. For more information on the file location, see Important Commands and Locations
Quit psql.

To quit psql, you use \q command and press enter to exit psql.
Restore the database by running the following command:

psql.exe -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.
- -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>.
For Example:
```
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
```
NOTE:The database was already created in Step 11 of the Restore process. Hence, ERROR about invalid locale should be ignored.
In the destination database server (PSDB2), change the server key in zdm.xml and zenaudit.xml, available in the following location with the local host address 127.0.0.1:

%ZENSERVER_HOME%\database\pgsql\data

Example: <entry key="Server">127.0.0.1</entry>
In all the other Primary Server (except destination database server (PSDB2)), update the value of the server key with the IP address of the new database server in zdm.xml and zenaudit.xml .

<entry key="Server"><IP-of-PSDB2></entry>

Example: <entry key="Server">10.1.1.2</entry>

On the destination database server (PSDB2), create the database service by running the following command:

"%ZENWORKS_HOME%\share\postgres\bin\pg_ctl.exe" register -N "Novell ZENworks Embedded Datastore - PostgreSQL" -U LocalSystem -D "%ZENWORKS_HOME%\database\pgsql\data" -S auto

Set the description of the service by running the following command:

sc description "Novell ZENworks Embedded Datastore - PostgreSQL" "Embedded datastore used for storing ZENworks objects and resources."
Go to the "%ZENWORKS_HOME%\share\postgres\bin\" location and run the pg_ctl.exe stop command.

You can also Stop the PostgreSQL Embedded Datastore service.

In PSDB2, update the pg_hba.conf file, and the file should be similar to as shown below:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
host    all     all     0.0.0.0/0   md5
host    all     all     ::0/0   md5

Now, you will be able to log into PostgreSQL using the psql.exe -W postgres, with the password that was obtained by running the zman dgcs command.

Start the Novell ZENworks Embedded Datastore service by performing the following steps:
- Press Win + R shortcut keys on the keyboard. In the Run dialogue, type services.msc, and then click OK.
- Select the Novell ZENworks Embedded Datastore - PostgreSQL service and then click Start.
Start the ZENworks services by running the novell-zenworks-configure –c Start command.

Ensure that Embedded PostgreSQL service is listed before Starting the services.
Run microfocus-zenworks-configure -c GenerateContentDatasourceConfigureAction on all the ZENworks servers.
Start the ZENworks services on the PSDB1 server by running the novell-zenworks-configure –c Start command.
NOTE:Ensure that the Novell ZENworks Embedded Datastore service is not running anymore on the source database server (PSDB1) device. You can either rename the service file, or unregister or remove the PSDB1 server.

To disable the service, perform the following steps:
1. Press Windows + R keys
2. Type services.msc.
3. Search for the Novell ZENworks Embedded Datastore - PostgreSQL service.
4. Right-click the option and then select Properties.
5. In the Startup type drop-down, select Disabled, and then click OK.
After disabling, the service will not start after the system restart.
Assign the database role to PSDB2 by running the query command in the pgadmin or any other client that can connect to PostgreSQL database server:
```
update zZenServerRoles set id='\x<PSDB2_GUID>', position=(select max(position)+1 from zZENServerRoles where id='\x<PSDB2_GUID>') where Roles='Database'
commit;
```
Example:

update zZenServerRoles set id='\xB66A9EA0D88A53B0801C07D5B44A5FE9', position=(select max(position)+1 from zZENServerRoles where id='\xB66A9EA0D88A53B0801C07D5B44A5FE9') where Roles='Database';
Assign the database role to PSDB2 by running the query command in the pgadmin or any other client that can connect to PostgreSQL database server:
```
microfocus-zenworks-configure -c MigrateServerRoleConfigureAction -DsourceGuid=<First PS GUID> -DdestGuid=<second PS GUID> -Drole= Database
```
Example: microfocus-zenworks-configure -c MigrateServerRoleConfigureAction - DsourceGuid=d1632252c35422d79d715b1e24b6de03 - DdestGuid=8d1878f8062c3ab74baf713bc8b0ccd8 -Drole=Database
The ZENworks Server now points to new database.

Ensure that you can successfully log into ZCC. After logging into ZCC, go to the Diagnostics page and in the ZENworks Databases panel, verify the Host IP addresses and also verify that the Audit database is connected.

Restore the Database on a Linux Primary Server or Appliance Server (PSDB2)

On a Linux Primary Server or an Appliance Server, perform the following steps:

Install PostgreSQL by performing the following steps:

On Appliance, perform the steps as a root user.
- Open the terminal in the following path:
  
  /opt/novell/zenworks/install/downloads/rpm
- Run rpm -Uvh <Postgre_RPM_File>
  
  For example,
  
  rpm -Uvh novell-zenworks-postgresql-11.4-1.x86_64.rpm
  
  NOTE:For setting the environment variables and running the database commands, ensure that you switch to the zenpostgres user (su zenpostgres).
Set the following environment variables:
- PGPORT:<target DB port>
- PGDATA:<database_path>
- PGDATABASE:<target initial connection database name> preferable is postgres
NOTE:It is recommended that you run the database commands in one terminal and other operations such as file edit (pg_hba.conf and postgresql.conf) in another terminal.
Example 7-2 Open the terminal prompt and set the following variables based on the existing configuration.
- export LD_LIBRARY_PATH=/opt/novell/zenworks/share/pgsql/lib;
- export PATH=$PATH:/opt/novell/zenworks/share/pgsql/bin;
- export PGPORT=54327;
- export PGDATA=/var/opt/novell/pgsql/data;
- export PGDATABASE=postgres;
After setting the variables, open terminal in the following location perform the next steps:

/opt/microfocus/zenworks/share/pgsql/bin
Open a terminal as Linux, and run the ./pg_ctl initdb command.

Edit the pg_hba.conf and postgresql.conf files as shown below:

The pg_hba.conf and postgresql.conf files are available in the following location:

/var/opt/microfocus/pgsql/data

In the pg_hba.conf file, update the content as shown below:

# TYPE DATABASE  USER   ADDRESS     METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
host    all     all     0.0.0.0/0   trust
host    all     all     ::0/0   trust

In the postgresql.conf file, uncomment the listen_addresses and Port entry keys and update the values as shown below:

listen_addresses = '*'

Port= 54327

max_connections = 500

To log into the database, run ./psql -w postgres. By default, postges is the database name.
Manually create the database using the following query:

create database <DB name> with template=template0 encoding='UTF8';
Ensure that the PostgreSQL user has access to the dump being imported.
Quit psql.

To quit psql, you use \q command and press enter to exit psql.
Go to the /opt/novell/zenworks/share/pgsql/bin location and run the following command to restore the database 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.
- -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>.
For example:

./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

NOTE:The database was already created in Step 8 of the Restore process. Hence, ERROR about invalid locale should be ignored.
In the destination database server (PSDB2), change the server key in zdm.xml and zenaudit.xml available in the following location with the localhost address 127.0.0.1:

/etc/opt/novell/zenworks/datamodel

Example: <entry key="Server">127.0.0.1</entry>
In all the other Primary Server (except destination database server (PSDB2)), update the value of the server key with the IP address of the destination database server in zdm.xml and zenaudit.xml.

<entry key="Server"><PSDB2_IP></entry>

Example: <entry key="Server">10.1.1.2</entry>
Restart postgres by running the systemctl restart zenpostgresql.service command for changes to take effect.
Run microfocus-zenworks-configure -c GenerateContentDatasourceConfigureAction on all the ZENworks servers.
Start ZENworks services on the destination database server (PSDB2) by running the novell-zenworks-configure –c Start command.
On all the Primary Servers, restart the ZENworks services by running the following command:

novell-zenworks-configure -c Start
Assign the database role to destination database server (PSDB2) by running the following configure action:
```
microfocus-zenworks-configure -c MigrateServerRoleConfigureAction -DsourceGuid=<First PS GUID> -DdestGuid=<second PS GUID> -Drole= Database
```
Example: microfocus-zenworks-configure -c MigrateServerRoleConfigureAction - DsourceGuid=d1632252c35422d79d715b1e24b6de03 - DdestGuid=8d1878f8062c3ab74baf713bc8b0ccd8 -Drole=Database
On the Primary Server on which the Embedded Database is installed, stop the Micro Focus ZENworks Embedded Datastore service using the following steps:

Run the following command to start the Micro Focus ZENworks Embedded Datastore – PostgreSQL service.

On SLES 12 and later: systemctl stop zenpostgresql.service

In the PSDB2, edit the pg_hba.conf file, and the file should be similar to as shown below:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
host    all     all     0.0.0.0/0   md5

Now, you will be able to log into PostgreSQL using the ./psql -W postgres, with the password that was obtained by running the zman dgcs command.

Assign the database role to destination database server (PSDB2) by running the query command in the pgadmin or any other client that can connect to PostgreSQL database server:
```
update zZenServerRoles set id='\x<PSDB2_GUID>', position=(select max(position)+1 from zZENServerRoles where id='\x<PSDB2_GUID>') where Roles='Database'
commit;
```
Example:

update zZenServerRoles set id='\xB66A9EA0D88A53B0801C07D5B44A5FE9', position=(select max(position)+1 from zZENServerRoles where id='\xB66A9EA0D88A53B0801C07D5B44A5FE9') where Roles='Database';
The ZENworks Server now points to new database.

Ensure that you can successfully log into ZCC. After logging into ZCC, go to the Diagnostics page and in the ZENworks Databases panel, verify the Host IP addresses and also verify that the Audit database is connected.