PostgreSQL internal db - Tips & Utilities

  • 7024768
  • 03-Aug-2020
  • 17-Sep-2020

Environment

Service Desk
ZENworks Configuration Management

Situation

Postgres Database can be an internal database in the ZENworks Systems.
Postgres syntax is related in this document.
You can use 3rd party tools to navigate through the database - see Additional Information below.

Example Environment

ZCM Appliance
ZENworks DB Name: zenworks
Audit DB Name: zenworksaudit
DB Connection Username: zenadmin
Password: (hint: Use command zman dgc)
Port: 54327

ZSD Appliance
Appliance Server name: NIM-ZSD
DBName: servicedesk
Port: 5432

Resolution

It is possible to run postgres command directly in the internal database.

Example: ZENworks Service Desk Server Name is NIM-ZSD

Log in to appliance as root/pw and run queries

     NIM-ZSD:~#  su  -  postgres
     postgres@NIM-ZSD:~>

Check postgres Version
    postgres@NIM-ZSD:~> psql  -V

Connect to the DB

postgres@NIM-ZSD:~> psql  \servicedesk

Run db queries (don’t forget the ending  ;  )
servicedesk=# :  select * from team;

Run a script
servicedesk=#  \i   <filename>.sql;

Restore a dump of the database (see Additional Information for details)
servicedesk=# psql  -U  postgres  -d  servicedesk  <  /tmp/zsdDb.backup

Quit Service Desk db command line

servicedesk=# \quit

Exit from Service Desk Database console
postgres@NIM-ZSD:~>exit

Log out of elevated user
NIM-ZSD:~#    logout
NIM-ZSD:~#

Backup the internal PostgreSQL Database  (will be prompted for the db password)
NIM-ZSD:~# pg_dump  --dbname=postgresql://sdadmin@localhost:5432/servicdesk  --file=/tmp/zsdDb.backup  --encoding=UTF8  --blobs  --format=tar
password:
(see Additional Information below for details on the db password)

Appliance command for the database
ZCM Check service:  systemctl [status | stop | start | restart] zenpostgresql.service  |  more
ZCM Check version: cat  /vastorage/var/opt/novell/pgsql/data/PG_VERSION
ZSD Check service:  systemctl [status | stop | start | restart] postgresql.service  |  more
ZSD Check version: cat  /vastorage/pgsql/data/PG_VERSION      (or see above logged in as postgres)

Additional Information

To change the ZSD use the Service Desk Configuration Tile in the Administrators Console (9443)
   pg_dump command

Utilities

PGAdmin4
By default PGAdmin 4 doesn't pick up the guid hex so encode is needed in the syntax.
     Example to display guid zuid with encode(zuid::bytea,'hex')  
     MSSQL: select zuid, platform, hostname, password from zdevice
     PGAdmin4: select encode(zuid::bytea, 'hex'),platform, hostname,password from zdevice

     Example to call out guid id with '\x<guid>'
     MSSQL: select * from recurringqueueaction where id = 0x 99d89b65dc2e0f43a8bbbfff4684710a
     PGAdmin4: select * from recurringqueueaction where id:='\x99d89b65dc2e0f43a8bbbfff4684710a'
     OR
     MSSQL: select * from zdevice where zuid = 0x99d89b65dc2e0f43a8bbbfff4684710a
     PGAdmin4: select * from zdevice where zuid = '\x99d89b65dc2e0f43a8bbbfff4684710a'

DBeaver
By default DBeaver doesn't pick up the guid hex so modifications to the default properties is needed.
DBeaver Properties (global) for Editors>Data Editor> Editors - Binary data formatter=hex

Feedback service temporarily unavailable. For content questions or problems, please contact Support.