<?xml version='1.0' encoding='UTF-8' ?>
<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>
<html class="client-nojs" dir="ltr" lang="en">
<head>
<meta charset="UTF-8" />
<title>Modules/PostgreSQL - OpenSCADAWiki</title>
<meta content="MediaWiki 1.26.4" name="generator" />
<link href="https://www.gnu.org/copyleft/fdl.html" rel="copyright" />
<link href="../files/doc.css" rel="stylesheet" /></head>
<body><div class="floatright"><a href="../index.html"><img alt="OpenSCADA" src="../../en/files/index.png" /></a></div><div id="mw_header">
<div class="mw-indicators">
</div>
<h1 id="firstHeading" lang="en">Modules/PostgreSQL</h1>
</div><div class="mw-content-ltr" dir="ltr" id="mw-content-text" lang="en"><div class="mw-pt-languages" dir="ltr" lang="en"><div class="mw-pt-languages-list autonym"><span class="mw-pt-languages-ui mw-pt-languages-selected mw-pt-progress mw-pt-progress--complete">English</span> • ‎<a class="mw-pt-progress mw-pt-progress--complete" href="../../ru/Modules/PostgreSQL.html" title="Модули/PostgreSQL (100% translated)">mRussian</a> • ‎<a class="mw-pt-progress mw-pt-progress--complete" href="../../uk/Modules/PostgreSQL.html" title="Модулі/PostgreSQL (100% translated)">УкраїнÑька</a></div></div>
<table class="wikitable">
<tr>
<th> Module </th>
<th> Name </th>
<th> Version </th>
<th> License </th>
<th> Source </th>
<th> Languages </th>
<th> Platforms </th>
<th> Type </th>
<th> Author </th>
<th> Description
</th></tr>
<tr>
<td> <a href="../Modules/PostgreSQL.html" title="Special:MyLanguage/Modules/PostgreSQL">PostgreSQL</a> </td>
<td> DB PostgreSQL
<ul><li> <b><a href="../Program_manual.html#DBFeat" title="Special:MyLanguage/Documents/Program manual">Features</a>:</b> SQL, LIST, STRUCT, GET, SEEK, PRELOAD, SET, DEL, FIX, TR, ERR</li></ul>
</td>
<td> 3.1 </td>
<td> GPL2 </td>
<td> bd_PostgreSQL.so </td>
<td> en,uk,ru,de </td>
<td> x86,x86_64,ARM
</td>
<td> DB </td>
<td> Roman Savochenko<br /> <font size="-2"><i>Maxim Lysenko (2010-2011)</i></font> </td>
<td> DB module. Provides support of the DBMS PostgreSQL.
</td></tr></table>
<p>The module provides OpenSCADA with support the DBMS PostgreSQL, which is a powerful relational and multiplatform DBMS, available under a free license and developed by <a class="external text" href="http://www.postgresql.org/" rel="nofollow noreferrer noopener" target="_blank">the PostgreSQL community</a>. The module is based on the C language API library of the DBMS PostgreSQL developers and allows you to perform operations over databases, tables and contents of tables.
</p>
<div class="toc" id="toc"><div id="toctitle"><h2>Contents</h2></div>
<ul>
<li class="toclevel-1 tocsection-1"><a href="#Operations_over_the_database"><span class="tocnumber">1</span> <span class="toctext">Operations over the database</span></a></li>
<li class="toclevel-1 tocsection-2"><a href="#Operations_over_the_table"><span class="tocnumber">2</span> <span class="toctext">Operations over the table</span></a></li>
<li class="toclevel-1 tocsection-3"><a href="#Operations_over_the_table_content"><span class="tocnumber">3</span> <span class="toctext">Operations over the table content</span></a></li>
<li class="toclevel-1 tocsection-4"><a href="#Access_rights"><span class="tocnumber">4</span> <span class="toctext">Access rights</span></a></li>
<li class="toclevel-1 tocsection-5"><a href="#Productivity_of_the_DBMS"><span class="tocnumber">5</span> <span class="toctext"><span>Productivity of the DBMS</span></span></a></li>
</ul>
</div>
<h2><span class="mw-headline" id="Operations_over_the_database"><span class="mw-headline-number">1</span> Operations over the database</span></h2>
<p>The operations of opening and closing of the database is supported, with the possibility of creating a new database when you open and delete existing at the close. In terms of the subsystem "DB" of OpenSCADA, opening of DB is its registration for further using of the program. It also supports the operation of requesting the list of tables in the database.
</p><p>DBMS PostgreSQL addresses by a string of the following type: <b>{host};{hostaddr};{user};{pass};{db};{port}[;{connect_timeout}]</b>. Where:
</p>
<ul><li> <i>host</i> — hostname on which the DBMS server PostgreSQL works. If this begins with a slash ('/'), it specifies Unix socket rather than TCP/IP communication; the value is the name of the directory in which the socket file is stored;</li>
<li> <i>hostaddr</i> — numeric IP address of the host for connecting, which should be in the standard IPv4 address format, e.g., 172.28.40.9; if IPv6 is supported, you can also use those addresses; TCP/IP communication is always used when a nonempty string is specified for this parameter;</li>
<li> <i>user</i> — user name of the DB;</li>
<li> <i>pass</i> — password of the user for accessing the DB;</li>
<li> <i>bd</i> — name of the DB; </li>
<li> <i>port</i> — port, which listening by the DBMS server (default is 5432); </li>
<li> <i>connect_timeout</i> — connection timeout, in seconds. Zero or not specified means wait indefinitely. It is not recommended to use a timeout of less than 2 seconds.</li></ul>
<p>In the case of local access to the DBMS in the same host, the address string should be as follows: <b>;;user;password;OpenSCADA;;10</b>
</p><p>In the case of remote access to the DBMS you must use the hostname and port of the DBMS server, for example: <b>server.nm.org;;user;password;OpenSCADA;;10</b>
</p>
<h2><span class="mw-headline" id="Operations_over_the_table"><span class="mw-headline-number">2</span> Operations over the table</span></h2>
<p>Supports opening, closing tables, with the possibility of creating a new table when opening and removing an existing one when closed, as well as querying the structure of the table.
</p>
<h2><span class="mw-headline" id="Operations_over_the_table_content"><span class="mw-headline-number">3</span> Operations over the table content</span></h2>
<ul><li> scanning of the table records;</li>
<li> requesting the values of specified records;</li>
<li> setting the values of specified records;</li>
<li> removing the records.</li></ul>
<p>API of the subsystem "DB" suppose the access to the contents of the table on the value of key(s) fields. Thus, the operation of requesting of the record implies the preset of key columns of the object TConfig, which will do the request. Creating a new record (row) is performed by setting the record values that are missing.
</p><p>The module allows you to dynamically change the structure of the database tables PostgreSQL. So, in the case of inconsistency between the structure of the table and the structure of the record being set, the structure of the table will be brought to the desired structure of the record. In the case of requesting the record values and discrepancies between record structures and tables, only the values of the general entries of the record and the table will be obtained. The module does not track the order of the elements in the record and in the structure of the table!
</p><p>The module implements the mechanism of supporting multilingual text variables. For fields with a multilingual text variable, columns of individual languages are created in the format " <b>{lang}#{FldID}</b>" (en#NAME). In this case, the base column contains values for the base language. Columns of individual languages are created when needed when saving to a DB and when execution OpenSCADA in the corresponding locale. In the absence of values for a particular language, the value for the base language will be used.
</p><p>Types of PostgreSQL database elements are corresponding with the types of OpenSCADA elements in the following way:
</p>
<table class="wikitable">
<tr>
<th> Types of OpenSCADA fields </th>
<th> Types of fields of DB PostgreSQL
</th></tr>
<tr>
<td> TFld::String </td>
<td> character(n), character varying(n), text
</td></tr>
<tr>
<td> TFld::Integer </td>
<td> integer, bigint, timestamp with time zone [for the fields with the flag TFld::DateTimeDec]
</td></tr>
<tr>
<td> TFld::Real </td>
<td> double precision
</td></tr>
<tr>
<td> TFld::Boolean </td>
<td> smallint
</td></tr></table>
<h2><span class="mw-headline" id="Access_rights"><span class="mw-headline-number">4</span> Access rights</span></h2>
<p>The PostgreSQL DBMS contains a mechanism for sharing access, which consists of the privileges specified by the user database. The table below lists the necessary privileges for full-time work.
</p>
<table class="wikitable">
<tr>
<th> Operation </th>
<th> SQL-commands
</th></tr>
<tr>
<td> Creation of the DB </td>
<td> CREATEDB
</td></tr>
<tr>
<td> Creation of the connection </td>
<td> LOGIN
</td></tr></table>
<p>To access the database, the right must be allowed directly for the specified database, and in the case of the creation of a database, it is also necessary to allow access to the system database "template1", which is used to connect at the stage of creating a new database.
</p><p>Briefly we will look at the initial configuration of the MySQL server to connect for it using by this module:
</p>
<ul><li> Installing the PostgreSQL DBMS server by the package or building.</li>
<li> Primary server initialization:</li></ul>
<pre style="white-space: pre-wrap; margin-left: 30pt">
# DB initialization
$ service postgresql initdb
# DB start
$ service postgresql start
</pre>
<ul><li> Enabling the trusted access from a local or a required subnet by editing the file <b>/var/lib/pgsql/data/pg_hba.conf</b> for the installation of "trust":</li></ul>
<pre style="white-space: pre-wrap; margin-left: 30pt">
local all all trust
host all all 127.0.0.1/32 trust
</pre>
<ul><li> Restarting the server after editing the access:</li></ul>
<pre style="white-space: pre-wrap; margin-left: 30pt">
$ service postgresql restart</pre>
<ul><li> Setting the password for the system user "postgres":</li></ul>
<pre style="white-space: pre-wrap; margin-left: 30pt">
$ psql -U postgres -d template1 -c "ALTER USER postgres PASSWORD '123456'"</pre>
<ul><li> Connecting the DBMS server using this module by entering the database address: <b>"localhost;;postgres;123456;test"</b></li></ul>
<h2><span class="mw-headline" id="Productivity_of_the_DBMS"><span class="mw-headline-number">5</span> <span id="Tests" title="#Tests">Productivity of the DBMS</span></span></h2>
<p>Measurement of the DBMS productivity were carried out by the test "DB" of the tests module "SystemTests", performing operations over the records of the structure: "<b>name char (20), descr char (50), val double (10.2), id int (7), stat bool, reqKey string, reqVal string</b>".
</p>
<table class="wikitable">
<tr>
<th> Operations for 1000 records, seconds </th>
<th> Nokia N800, PostgreSQL 8.3 (remote) </th>
<th> AMD Turion L625 1.6, 2G, PostgreSQL 9.1 [local; 100Base-TX] </th>
<th> Intel(R) Core(TM) i3 CPU 1.33GHz, 3G, HDD [100Base-TX]
</th></tr>
<tr>
<td> <i>Creating:</i> </td>
<td> 5 </td>
<td> 1.08; 1.6; 3.8 </td>
<td> 2.07
</td></tr>
<tr>
<td> <i>Updating:</i> </td>
<td> 4.8 </td>
<td> 1.12; 1.6; 3.9 </td>
<td> 2.35
</td></tr>
<tr>
<td> <i>Getting:</i> </td>
<td> 2.96 </td>
<td> 0.64; 1.2; 2.1 </td>
<td> 1.26
</td></tr>
<tr>
<td> <i>Seeking:</i> </td>
<td> - </td>
<td> - </td>
<td> <span style="color: red">7.9</span>
</td></tr>
<tr>
<td> <i>Seeking in preload:</i> </td>
<td> - </td>
<td> - </td>
<td> 0.038
</td></tr>
<tr>
<td> <i>Deleting:</i> </td>
<td> 1.73 </td>
<td> 0.39; 0.6; 1.6 </td>
<td> 0.94
</td></tr></table>
</div><table style="border-top: dotted 2px #999999; margin-top: 20pt; color: gray;" width="100%"><tr><td style="text-align: left;" width="40%"><a href="http://oscada.org/wiki/Modules/PostgreSQL/en">Modules/PostgreSQL/en</a> - <a href="http://oscada.org/en/main/about-the-project/licenses/">GFDL</a></td><td style="text-align: center;">April 2025</td><td style="text-align: right;" width="40%">OpenSCADA 1+r3018</td></tr></table></body>
</html>