SystemLink PostgreSQL Database No Longer Starts

Issue Details

• I repaired or upgraded or uninstalled/reinstalled SystemLink Server,  thereafter its PostgreSQLDatabase service never starts, and the Test Insights section of SystemLink is also not able to start.
• When I open the NI SystemLink Configuration application, I see the PostgreSQLDatabase service bouncing between the Starting and Initializing states.

Solution

There are multiple different issues that can cause these symptoms.  This article addresses the issue that the PostgreSQL connection information is lost in the following SystemLink configuration file: C:\ProgramData\National Instruments\Skyline\Config\PostgreSQLDatabase.json.  The PostgreSQLDatabase service is a dependency for the TestMonitor service, so the TestMonitor service (and therefore the Test Insights part of SystemLink) will never start if the PostgreSQLDatabase service is unable to start.

The first thing to check is if the above PostgreSQLDatabase.json file is missing the database connection password entirely, which is commonly the result of a SystemLink Server upgrade, repair or uninstall/reinstall action.  The password belongs in 2 locations, the Postgres.Password field, and the Postgres.ConnectionString field.  While you're there, also verify that the Postgres.Database field and the Postgres.User field are both set to nisystemlink (always the case).


If your PostgreSQLDatabase.json file has a password in both fields, the next thing to check is if the database connection string from that file works with the PostgreSQL database that SystemLink is configured to talk to.  Open the NI SystemLink Server Configuration dialog and click the PostgreSQLDatabase item in the navigation list on the left.  Even if you're using the default local PostgreSQL database instance that SystemLink installs, temporarily select the Connect to an externally managed server using a connection string radio button.  Then paste in the value of the Postgres.ConnectionString field into the Npgsql connection string field of this dialog and click the Test connection button.  The result of the connection test displays below the Test connection button-- if you don't see Successfully connected to PostgreSQL database there, then you need to fix the connection string, which most likely involves finding the real password or resetting the PostgreSQL database password yourself.  If you're using the default local PostgreSQL database instance that SystemLink installs, remember to set the radio button back to The PostgreSQLDatabase server is launched and managed by SystemLink.


If your PostgreSQLDatabase.json file is missing the database password, or if the password there does not work and you don't know the correct password, you will need to follow the below steps to change the PostgreSQL database password.  You will shut down the SystemLink Server services and its PostgreSQL database, then use a series of DOS commands to start/stop the PostgreSQL database and change the PostgreSQL database password.  Once you again know the correct PostgreSQL password, you will manually enter it into the PostgreSQLDatabase.json file.

1. Open the Windows Task Manager and navigate to the processes that start with "P" to see if SystemLink's PostgreSQL database is currently running.  If it is, you will see the following PostgreSQL Server processes:

2. Open the NI SystemLink Server Configuration dialog to see which SystemLink services are currently started.  If the PostgreSQLDatabase service is successfully started, you will see a green checkmark beside it, otherwise you will see a red X beside it.

3. Open the Windows Services dialog and select the  NI SystemLink Service Manager  service, which controls whether the services shown in the NI SystemLink Server Configuration dialog can start.  Click Stop the service to stop all the SystemLink Server services, including the PostgreSQLDatabase service.

4. Once the  NI SystemLink Service Manager  service has completely stopped, you should see all the child services display in the Stopped state in the NI SystemLink Server Configuration dialog.


5. Check the Windows Task Manager and verify that all the PostgreSQL Server processes are gone now.  If they persist, then the underlying PostgreSQL database did not shut down, in which case you'll need to stop the PostgreSQL database with a DOS command in a step 13 below.

6. Locate the PostgreSQL data folder left behind by the previous install and open the pg_hba.conf file in that folder. By default, the path is:
    C:\ProgramData\National Instruments\Skyline\PostgreSQLDatabase\pg_hba.conf


7. Comment out the line that requires a password for database connections by inserting a "#" symbol as its first character:
    #host all all 127.0.0.1/32 scram-sha-256

8. Insert a new line that allows database connections without providing a password, using this syntax:
    host all all 127.0.0.1/32 trust


9. Save the pg_hba.conf file with whatever file editor you are using. You are now ready to change the PostgreSQL database password.

10. Locate the folder where SystemLink Server installed the PostgreSQL binaries. Launch a command prompt with administrator privileges and navigate to this folder. By default, this is located here:
    C:\Program Files\National Instruments\Shared\Skyline\PostgreSQLDatabase\pgsql\bin

11. Register the PostgreSQL data folder left behind from the previous install with the pg_ctl utility as a new Windows service named postgres.
    pg_ctl register -N postgres -D "C:\ProgramData\National Instruments\Skyline\PostgreSQLDatabase"


12. Notice that there is now a new entry in your Windows Services dialog that shows as postgres (you may need to manually refresh the Services dialog to see it).  This new postgres service will now control the underlying PostgreSQL database that SystemLink Server uses.  You can now start and stop that underlying PostgreSQL database with the postgres line in the Windows Services dialog.


13. If you still saw active "PostgreSQL" processes in Windows Task Manager in step 5, then click  Stop the service in the Windows Services dialog, or instead you can issue this command in your DOS shell.
    net stop postgres

14. Now start the postgres service fresh (with the new setting in the pg_hba.conf file) using this command syntax:
    net start postgres

15. Find the port number the SystemLink PostgreSQLDatabase service uses in the postgresql.conf file, which by default is here:
    C:\ProgramData\National Instruments\Skyline\PostgreSQLDatabase\postgresql.conf

16. Open a connection to the SystemLink Server's PostgreSQL database using the port you discovered (by default the port is 5433) and as the user nisystemlink.
    psql -h localhost -p 5433 -U nisystemlink

17. Once connected, set a strong new password for the nisystemlink user.
    ALTER USER nisystemlink with password 'mynewpassword';

18. Disconnect from the postgres service by typing quit or: exit or
    \q

19. Stop the postgres service, so you can launch it fresh with a new pg_hba.conf setting and new PostgreSQLDatabase.json settings.
    net stop postgres

20. If you don't want to keep the postgres service you registered earlier, you can unregister it so it disappears from the Services dialog.
    pg_ctl unregister -N postgres

21. Undo the changes you made to the pg_hba.conf file in step 2, then save the file again.


22. Modify the Postgres.Password and Postgres.ConnectionString parameters in the PostgreSQLDatabase.json file to contain your new strong password. By default the PostgreSQLDatabase.json file is here:
    C:\ProgramData\National Instruments\Skyline\Config\PostgreSQLDatabase.json


23. Launch the NI SystemLink Server Configuration dialog and restart all the SystemLink services:

24. If the database service fails to start after the above steps, make sure the PostgreSQLDatabase service is configured to run as a user that has appropriate permissions in the selected database path but is NOT registered as an administrative account (hard PostgreSQL limitation). By default it is set to run as a Network Service, this configuration is available when clicking the PostreSQLDatabase option in the NI SystemLink Server Configuration application and looking for the Run service as selection radio buttons.

Additional Information

NOTE: Do not put backup versions of the PostgreSQLDatabase.json file in the same folder as the active file.  SystemLink will read all those backup *.json files, regardless of file name, and it will load all those contents into objects in memory.  The last *.json file loaded with PostgreSQLDatabase content will clobber all previously loaded versions.  You should put backup files in a different folder.

SystemLink versions prior to SystemLink 2022 Q1 did not use PostgreSQL, so this problem will not occur prior to SystemLink 2022 Q1.