Caliach MuM - Installation Stage 2 (PostgreSQL and Monitoring)

Version Relevance: V5

Issue: How do I set up a client's server for Caliach's MuM Monitoring Service?

Background: Introduced in the late summer of 2018, Caliach's MuM Monitoring Service provides regular reports by Email and/or internal Message that your Caliach Vision data is in good shape, or otherwise reports issues with the data that may need your attention. It uses secure encrypted communications using SSL to routinely access your client's server and interrogate the data. To learn more about SSL see this Knowledge Base Article. This article explains how it is set up and is primarily directed at Caliach staff initialising the server for the service.

Aug 4th, 2018

This article provides details of the MuM Monitoring part of the MuM Service and is designed for Caliach Staff to assist in the Site server set-up alogside judging their time so that the final cost of Engineer time can be set on the Quote/SO on the Caliach Central Server. You should note that Steps are completed in this article and in order.

Another article Caliach MuM - Installation Stage 1 (File Distribution) covers the File Synchronisationg part of the MuM Service.

Feedback: Follow these steps.

Please note that throughout this article the word server means the computer on which PostgreSQL's server is running for the Caliach Vision licensee and the word client means the user's computer or device that is accessing the database on the server, usually over the Local Area Network (LAN).

Amendment note 5th Oct 2018: IT Support Service companies: Experience has demonstrated amply that it is not possible for Caliach to work directly with IT Support Service companies you may use to achieve the above. We can only work with Caliach Licensee staff who will have to deal with 3rd parties if they are involved - Caliach has no direct authority to effectively assist them.

Settng up the Server

  • Step 10.10 The PostgreSQL server, migrated database and Vision on the server must be operational before this setup can start. You must have something to monitor!
  • Step 10.11 On Caliach license site maintenance monitoring tab, click on the Create OpenSSL Script button which creates a <OfficeVisionServerFolder>\ssl_crt\<SiteShortName>.bat file. This assumes you have OpenSSL installed at C:\OpenSSL to run.
  • Step 10.12 Run the above <SiteShortName>.bat (You may have to Run as Adminstrator to avoid file privileges issues.). This will create 2 folders <SiteShortName>-cli and <SiteShortName>-svr in the C:\Pg_SslCerts folder.
  • Step 10.13 Copy the C:\Pg_SslCerts\<SiteShortName>-svr contents to the target server's C:\Program Files\PostgreSQL\10\data folder. Then in the same folder edit the pg_hba.conf with a line such as:

    hostssl all all all cert clientcert=1

    and the postgresql.conf file to have:

    ssl = on
    ssl_cert_file = 'server.crt'
    ssl_key_file = 'server.key'
    ssl_ca_file = 'root.crt'
    log_connections = on
    log_disconnections = on
    log_hostname = on
  • Step 10.14 Restart the PostgreSQL service in Task Manager (if it doesn't start you have missed a step!).
  • Step 10.15 Set the server's (and LAN) firewalls to accept PostgreSQL traffic on the appropriate port(s) from anywhere.
    Server Firewall Settings

Step 10.16 Finally you need to set up the Task Scheduler to backup the database, nightly or however frequently done. To do this Create Basic Task and enter relevent name and how frequently and at what time of the day you'd like to Backup. Browse for the directory :/CaliachScripts/<SiteShortName>_backup.bat and choose option of running whether user is logged in or not. This will create and systematically backup on a routinely basis.

Testing the Setup

  • Step 10.17 You can now test the Monitoring. Use the Test Monitor Single Selected Site button and you will get an Alert Message window open with the following, if there is a connection or other fatal error (assuming you only have 1 database name, in this case trainingdemo510) you will get the following:

    Test $RunMonitor        : Caliach Vision MuM Monitoring of Site 510001 Company Name Caliach-GB with shortname "caliachgb".
    Test 00_PingSite   254mS: ping site "caliachgb" success to caliach.co.uk.
    Test $RunMonitor        : This is your first monitoring occasion.
    Test $RunMonitor        : monitoring on database "trainingdemo510" started trainingdemo5103local monitor time.
    Test 01_Logon     15125mS: logon failed to caliach.co.uk:5434 on database "trainingdemo510" using user postgres with error: Test 01_Logon     15125mS: : Failed to logon to caliach.co.uk with error: Session Error: kDAMInternalError An internal error occurred. Contact Technical Support
    Test 98_Completed       : completed monitoring for caliach.co.uk:5434 with 4 tests of which 1 failed after an overall monitor duration of 15,141mS.

    Step 10.18 This is most likely caused by a blocking site LAN firewall which you will need to open up to TCP traffic from anywhere to the appropriate server port(s). There are ways of trouble shooting this:
    • Step 10.19 After an attempt to connect, look at the PostgreSQL server log. The latest will be the last in C:\Program Files\PostgreSQL\10\data\log\ (you can read it with Notepad). If you are getting no log record at all, the firewall is blocking the attempt to connect; check the IP and port, the server Firewall and the LAN firewall.
    • Step 10.20 If you get an entry but it shows something like this:

      LOG:  could not accept SSL connection: tlsv1 alert unknown ca
      then you need to re-run the Open SSL Script in 1 above and re-copy the .crt and .key files, both for client and server, in case you got that step wrong.
    • Step 10.21 In some cases we have found that some firewalls, anti-malware software or even ISP's deliberatly corrupt TCP packets containing point-to-point encryption keys. You will then get a log entry like this:

      FATAL:  client certificates can only be checked if a root certificate store is available
      Step 10.22 We had to solve that by using a VPN to tunnel through the offending corruption device.
      Further reseach showed that in these circumstances it was the Firewall (again!) imposing packet sniffing techniques to prevent SSL connections that did not use "Trusted CA certificates". As we use self-signed OpenSSL generated certificates, it was mangling the TCP packets and causing the server to reject the connection. So make sure any Firewall SSL rules are switched off for TCP packets directed at the relevant server ports.
    • Step 10.23 For a successful connection the PostgreSQL log will show something like this (assuming you have set the postgresql.conf file correctly in 3 above):

      2018-08-09 13:34:44.299 BST [8020] LOG:  connection received: host=10.10.10.222 port=65042
      2018-08-09 13:34:45.506 BST [8020] LOG:  connection authorized: user=postgres database=trainingdemo510 SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)
      2018-08-09 13:34:55.554 BST [8020] LOG:  disconnection: session time: 0:00:11.260 user=postgres database=trainingdemo510 host=10.10.10.222 port=65042
  • Step 10.24 When you fix these problems, you should get something like this in your Caliach Vision Alert Message:

    Test $RunMonitor        : Caliach Vision MuM Monitoring of Site 510001 Company Name Caliach with shortname "caliachhk".
    Test 00_PingSite    67mS: ping site "caliachhk" success to caliach.com.
    Test $RunMonitor        : This is your first site monitoring occasion.
    Test $RunMonitor        : monitoring on database "trainingdemo510" started 7 Aug 2018 05:44:47.66 local monitor time.
    Test 01_Logon      766mS: logon success to caliach.com:5434 using user postgres with server timestamp 7 Aug 2018 04:44:51.15.
    Test 02_SvrState    78mS: database check found 6 databases (the following tests are on database "trainingdemo510").
    Test 03_Tables      78mS: database "trainingdemo510" 120 table structure is standard with MCD, UST and USL present.
    Test 04_Version    312mS: your current version is 5.1002 while the monitor is running 5.1002.
    Test 05_Tokens      78mS: user tokens at 7 Aug 2018 04:44:51.68 were 0 of which 0 were expired.
    Test 06_Sessions    63mS: user sessions on file over 14 days from 12 Aug 2016, to 3 Aug 2018 = 'period' below.
    Test 06_Sessions        : user sessions total 30, closed 30, sent 24 MB, received 224 MB.
    Test 06_Sessions        : user sessions ave/day 2.143, closed 2.143, sent 1752 kB, received 16 MB.
    Test 06_Sessions        : user transactions 37, refreshed 0, failed 0, with latest logon at 3 Aug 2018 04:14:40.08.
    Test 06_Sessions        : user transactions ave/day 2.643, refreshed 0.000, failed 0.000.
    Test 06_Sessions        : user with most active time/day SYS, active 0h22m26s, idle 1h08m24s.
    Test 06_Sessions        : user with most idle time/day SYS, active 0h22m26s, idle 1h08m24s.
    Test 07_StockMove  141mS: stock movement count of 612 from 2 Apr 2018 00:00:00.00 to 1 Aug 2018 07:12:23.72.
    Test 08_LedgTrans   78mS: ledger transaction count of 218 from 9 Nov 2017 00:00:00.00 to 26 Jul 2018 00:00:00.00.
    Test 09_GlBalance   63mS: GL current trading UK£ -32572.01, bs 32572.01 future trading -47399.69 ,bs 47399.69.
    Test 10_ArBalance   78mS: AR Y01M02 control imbalance UK£ 0.00 debit 0.00 credit.
    Test 11_ApBalance   78mS: AR Y01M02 control imbalance UK£ 0.00 debit 0.00 credit.
    Test 12_Quotes      63mS: quotation line item count of 6 with net value UK£ 7,588.86.
    Test 13_SalesOut    62mS: sales order line item count of 20 with net value UK£ 192,483.41.
    Test 14_Delivery   109mS: dispatched line item count in period of 27 with net value UK£ 44,602.96.
    Test 15_JobsOut     79mS: job line item count of 13 with net value UK£ 14,080.76.
    Test 16_JobsComp    78mS: completed job line item count in period of 0 with net value UK£ 0.00.
    Test 17_PurchOrd    78mS: open po line item count of 24 with cost value UK£ 7,909.75.
    Test 18_PurchRec    62mS: po item reciepts period count of 43 with net cost value UK£ 39,363.16.
    Test 19_WorkOrd     63mS: work orders open count of 21 with current cost value UK£ 22,289.64.
    Test 20_WorkComp   125mS: wo completed in period  item count of 0 with current cost value UK£ 0.00.
    Test 21_Invoice     63mS: invoices in period count of 19 with net value before tax UK£ 43,763.29.
    Test 22_Orphans     63mS: orhaned sales history 0, so items 0, po items 0, job items 0.
    Test 23_Locked      79mS: locked dcuments so 0, po 0, wo 0, jobs 0.
    Test 24_Peek+Poke   62mS: WARNING! peek and poke activity "PandP: VISION.PERFILE: 24xU 0xD 0xA." took place on 31 Jul 2018 12:38:05.00.
    Test 25_OddDates    78mS: WARNING! odd dates: early before 20 Jun 1988, late after 20 Aug 2021.
    Test 25_OddDates        : odd dates in ledgers early 0, late 0. inventory history early 1, late 0.
    Test 26_Feedback   172mS: The server timestamp is 14 Aug 2018 11:21:32.09 or redacted 14 Aug 2018 11:21:32.09.
    Test 26_Feedback    63mS: Top sales entry user for the last month is SYS with UK£ 19,048.77 dispatches out of UK£ 19,048.77 or 100.0%.
    Test 26_Feedback    16mS: parameter ResetFrequency update success with value set to 14.
    Test 26_Feedback        : parameter TestParam is an unknown parameter name, so can not be acted upon.
    Test 50_Backup     156mS: last backup trainingdemo510_20180806_190001.backup was done at 6 Aug 2018 19:00:01.00.
    Test 96_MsgRes      94mS: monitoring results message for CR Chris Ross was not possible as user CR does not exist or is set to No Logon.
    Test 97_Logoff      78mS: logoff from database "trainingdemo510" caliach.com:5434 at 7 Aug 2018 04:44:53.38 server timestamp.
    Test 98_Completed       : completed monitoring for caliach.com:5434 with 28 tests of which 5 did not pass, severity 8. Monitor duration was 3,016mS.
  • Step 10.25 If you are getting non-zero severity at the Test 98-Completed stage, you can test with test-severity- values prefixed to each line by holding the Shift key down when operating the Test... button, so you can see what is going on. Giving the following results:

    0-0-Test $RunMonitor        : Caliach Vision MuM Monitoring of Site 510001 Company Name Caliach with shortname "caliachhk".
    1-0-Test 00_PingSite    70mS: ping site "caliachhk" success to caliach.com.
    0-0-Test $RunMonitor        : This is your first site monitoring occasion.
    0-0-Test $RunMonitor        : monitoring on database "trainingdemo510" started 7 Aug 2018 06:00:24.00 local monitor time.
    1-0-Test 01_Logon      969mS: logon success to caliach.com:5434 using user postgres with server timestamp 7 Aug 2018 05:00:27.68.
    1-0-Test 02_SvrState    78mS: database check found 6 databases (the following tests are on database "trainingdemo510").
    1-0-Test 03_Tables      78mS: database "trainingdemo510" 120 table structure is standard with MCD, UST and USL present.
    1-0-Test 04_Version    312mS: your current version is 5.1002 while the monitor is running 5.1002.
    1-0-Test 05_Tokens      78mS: user tokens at 7 Aug 2018 05:00:28.24 were 0 of which 0 were expired.
    1-0-Test 06_Sessions    79mS: user sessions on file over 14 days from 12 Aug 2016, to 3 Aug 2018 = 'period' below.
    0-0-Test 06_Sessions        : user sessions total 30, closed 30, sent 24 MB, received 224 MB.
    0-0-Test 06_Sessions        : user sessions ave/day 2.143, closed 2.143, sent 1752 kB, received 16 MB.
    0-0-Test 06_Sessions        : user transactions 37, refreshed 0, failed 0, with latest logon at 3 Aug 2018 04:14:40.08.
    0-0-Test 06_Sessions        : user transactions ave/day 2.643, refreshed 0.000, failed 0.000.
    0-0-Test 06_Sessions        : user with most active time/day SYS, active 0h22m26s, idle 1h08m24s.
    0-0-Test 06_Sessions        : user with most idle time/day SYS, active 0h22m26s, idle 1h08m24s.
    1-0-Test 07_StockMove   62mS: stock movement count of 612 from 2 Apr 2018 00:00:00.00 to 1 Aug 2018 07:12:23.72.
    1-0-Test 08_LedgTrans   78mS: ledger transaction count of 218 from 9 Nov 2017 00:00:00.00 to 26 Jul 2018 00:00:00.00.
    1-0-Test 09_GlBalance   78mS: GL current trading UK£ -32572.01, bs 32572.01 future trading -47399.69 ,bs 47399.69.
    1-0-Test 10_ArBalance   62mS: AR Y01M02 control imbalance UK£ 0.00 debit 0.00 credit.
    1-0-Test 11_ApBalance   94mS: AR Y01M02 control imbalance UK£ 0.00 debit 0.00 credit.
    1-0-Test 12_Quotes      78mS: quotation line item count of 6 with net value UK£ 7,588.86.
    1-0-Test 13_SalesOut    63mS: sales order line item count of 20 with net value UK£ 192,483.41.
    1-0-Test 14_Delivery    94mS: dispatched line item count in period of 27 with net value UK£ 44,602.96.
    1-0-Test 15_JobsOut     62mS: job line item count of 13 with net value UK£ 14,080.76.
    1-0-Test 16_JobsComp    78mS: completed job line item count in period of 0 with net value UK£ 0.00.
    1-0-Test 17_PurchOrd    63mS: open po line item count of 24 with cost value UK£ 7,909.75.
    1-0-Test 18_PurchRec    93mS: po item reciepts period count of 43 with net cost value UK£ 39,363.16.
    1-0-Test 19_WorkOrd     63mS: work orders open count of 21 with current cost value UK£ 22,289.64.
    1-0-Test 20_WorkComp    78mS: wo completed in period  item count of 0 with current cost value UK£ 0.00.
    1-0-Test 21_Invoice     78mS: invoices in period count of 19 with net value before tax UK£ 43,763.29.
    0-0-Test 22_Orphans     62mS: orhaned sales history 0, so items 0, po items 0, job items 0.
    0-0-Test 23_Locked      78mS: locked dcuments so 0, po 0, wo 0, jobs 0.
    1-2-Test 24_Peek+Poke   62mS: WARNING! peek and poke activity "PandP: VISION.PERFILE: 24xU 0xD 0xA." took place on 31 Jul 2018 12:38:05.00.
    0-0-Test 25_OddDates    63mS: WARNING! odd dates: early before 20 Jun 1988, late after 20 Aug 2021.
    1-1-Test 25_OddDates        : odd dates in ledgers early 0, late 0. inventory history early 1, late 0.
    1-0-Test 26_Feedback   172mS: The server timestamp is 14 Aug 2018 11:21:32.09 or redacted 14 Aug 2018 11:21:32.09.
    1-0-Test 26_Feedback    63mS: Top sales entry user for the last month is SYS with UK£ 19,048.77 dispatches out of UK£ 19,048.77 or 100.0%.
    1-0-Test 26_Feedback    16mS: parameter ResetFrequency update success with value set to 14.
    1-3-Test 26_Feedback        : parameter TestParam is an unknown parameter name, so can not be acted upon.
    1-1-Test 96_MsgRes      78mS: monitoring results message for CR Chris Ross was not possible as user CR does not exist or is set to No Logon.
    1-0-Test 97_Logoff      63mS: logoff from database "trainingdemo510" caliach.com:5434 at 7 Aug 2018 05:00:29.73 server timestamp.
    0-0-Test 98_Completed       : completed monitoring for caliach.com:5434 with 28 tests of which 5 did not pass, severity 8. Monitor duration was 3,016mS.

Important Note on the Site Backup Test

Step 10.26 Finally, the testing of whether the site backup of the database is being performed is indirect (as there are limits set by PostgreSQL as to what files it can see on the server, quite understandably). The test produces the following result:

Test 50_Backup     109mS: last backup trainingdemo510_20180806_190001.backup was done at 6 Aug 2018 19:00:01.00.

This is only possibe if the Caliach designed Backup Scripts are used, using the Sample Script button on the Caliach Licensee Site Maintenance window (see above). The script puts a folder named <Date>_<Time> in a folder named <DatabaseName> in a folder named caliach_backups in the PostgreSQL data folder giving a resulting path of C:\Program Files\PostgreSQL\10\data\caliach_backups\<DatabaseName>\<Date>_<Time>, after removing older ones, and it is this from which we can reasonably deduce that a backup was created. Note that Administrator privileges must be used to run the batch file in Scheduled Tasks to run the backup.

pgAdmin4 icon pgAdmin 4 issues

Experience has shown problems in launching pgAdmin 4 which is the administration utility installed with recent PostgreSQL server software. This is a completely re-developed stand-alone browser-based application that replaced the well established pgAdmin 3. When launched it builds an "application server" and this takes some time, so be patient.

pgAdmin4 launching

It is not uncommon in our experience for this to abruptly end with the following error message:

pgAdmin4 launching

We don't know exactly what causes this and a search on the web uncovers loads of blog pages that all suggest fixes that are frustratingly useless. Don't bother to do the same, you won't win! It seems that it has something to do with the way pgAdmin4 attempts to connect to the last PostgreSQL instance that it was connected to before - that is our guess anyway! There is an easy fix. Open the Task Manager by right-clicking on the Taskbar and operate Task Manager. Then click on the Services tab pane and scroll down the list until you find PostgreSQL. If you have installed multiple versions, they will each have their own instance running, as shown below where we have V9.3, 9.4, 9.5, 9.6 and V10 running.

Task Manager

Make sure you don't have Caliach Vision connected, so shut it down! Now choose the most likely PostgreSQL service, right click and Restart. You can quite comfortably Stop all instances as they will restart when a connection is attempted. Now try to launch pgAdmin4 again, and it should work. You will get something like this in your Browser:

pgAdmin4 Running

In the above example we have 5 server instances running and one external server connection set up - Caliach HK. The local servers all operate through different ports, which for convenience we have added to the Name (in brackets) - right click and operate Properties to do the same. We have connected to PostgreSQL 10 which has 6 databases, including the base database postgres. From here you can do pretty much what you want!

Step 10.19 Remember, if you want a full database restore, you need to drop the database first (right-click on it in the tree list), then Create a new database with the correct lower-case name, then do a Restore on it. The Restore will create the entire database structure which will include a Schema which is given the name from the backup. For Caliach Vision the Schema Name must be exactly the same as the Database Name. This can be a problem if you are getting a backup from a client but want to restore into a database name that, for your convenience you want under a different name. Once you have restored you will end up with a miss-match in database and schema names. To fix that, you need to select the database, operate Tools -- Query Tool and type in the following statement and execute it:

ALTER SCHEMA name RENAME TO new_name

Chris Ross - Senior Consultant

Links to other MuM KB Articles: