Saving PMU data into a postgresql database

GPA Products openPDC
1

Greetings everyone, hope you are doing great.

I’m writing to see if someone can help me save PMU data into a postgresql database.

Currently I’m able to save the PMU historic data using openHistorian, but the problem is that this data is saved on a folder inside the openHistorian Program Files as shown in the image below:

image
image921×468 182 KB

The real problem comes because we want to be able to access this information remotely without having to access the workstation where the data is being saved, so I wanted to ask if there is a way to save the data of the PMU into a postgresql database using openPDC or openHistorian.

I’ve read some posts where they mention this is possible using an AdoOutputAdapter instance but to be honest I don’t have the knowledge to configure the instance.

2

Hello Henry,

It should indeed be possible to use the AdoOutputAdapter to save data in PostgreSQL. I can certainly try to help get you pointed in the right direction if that’s what you need. However, based on what you’ve said, it sounds like your primary concern is accessing the data from external systems. The openHistorian actually does have various options for different types of access that would enable remote access to the data, provided there is no security requirement that is preventing you from opening TCP connections directly to the openHistorian server.

  • Web interface
    • Real-time visualization
    • Interactive trend chart
    • CSV exports
    • Dashboard interfaces via Grafana
  • C# API for querying data over a socket
  • Synchrophasor protocols for streaming playback of archived data
  • SqlClr interface for SQL Server database queries

If any of these use cases suits your needs, then you might be better off trying to figure out how to work with these as it can be difficult for relational database systems to keep up with the volume of data from PMU systems.

Thanks,
Stephen

1 Like
3

Hello Stephen thanks for the answer and the recommendations, i will absolutely discuss the alternatives you mentioned with the team to see if any of those could help us achieve what we want.

With this in mind i want to give a little bit more context of our problem, currently we are able to access and record the PMU data into a workstation that is placed in the same space as our PMU, we mainly access this information via remote access to the workstation where we use the web interface to extract historic data in .csv and COMTRADE format. As for right now, we haven’t tried to access the web interface outside the workstation so this could be a solution for the accesibility problem, but i just recently tried it and works amazingly, so i will present this alternative to the team to see if this could be the solution.

But also, i would like to ask if you could explain how to use AdoOutputAdapter to save data into PostgreSQL, this could help us make a comparison of the solutions and find the one that fits better to our needs.

Also thanks again for the response,
Henry

4

Okay, keep in mind that this process is untested. I pieced this together using information from our PostgreSQL database scripts, the openPDC source code, and also openHistorian Historian data to be saved into RDBMS (Oracle/Sql Server) using AdoAdapters - #2 by StephenCWills.

Create a table in your PostgreSQL database with the following definition.

CREATE TABLE TimeSeriesMeasurement
(
    SignalID NCHAR(36) NOT NULL,
    Timestamp VARCHAR(24) NOT  NULL,
    Value DOUBLE NOT NULL
)

In the openPDC Manager, under Outputs > Manage Custom Outputs…

Name: ADO
Type: ADO: Archives measurements to an ADO data source
Connection String: BulkInsertLimit=500; DataProviderString={AssemblyName={Npgsql, Version=4.0.11.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7}; ConnectionType=Npgsql.NpgsqlConnection; AdapterType=Npgsql.NpgsqlDataAdapter}; DbConnectionString={Server=hostname; Port=5432; Database=openPDC; User Id=postgres; Password=password}; TableName=TimeSeriesMeasurement; IDFieldName=SignalID; TimestampFieldName=Timestamp; ValueFieldName=Value; SourceIDs=PPA
Enabled: Checked

I highlighted the DbConnectionString because you’ll need to update that value based on the hostname of your PostgreSQL server, the name of the database, and the credentials you use to access it.

1 Like
5

Hello Stephen,

Thanks for the response, we followed the steps mentioned above and everything is working perfectly, currenlty we are saving PMU data into a PostgreSQL database using the AdoOutputAdapter without problem.

Thanks again for the quick response,
Henry