PostgreSQL

Latest revision as of 09:19, 10 June 2014

The module pgsqldb is used to make the connection between Yate and one or more PostgreSQL databases.

When using this module for storing CDRs, also read [call.cdr] section in Register.

[edit] Requirements

Install devel packages for PostGreSQL in your platform. Then in Yate sources run:

./configure
make

Then start Yate:

./run -vvvvv -CDo

[edit] Check if module is loaded

Note: Check if module PostGreSQL is loaded. It is required to be loaded so that Yate can connect to database.

[edit] Verify module loading when starting Yate

When Yate starts you can see a list of modules that are loaded. If PostGreSQL does not appear, you must install the devel packages, then reload Yate.

./run -vvvvv -CDo
..........
Loaded module PostgreSQL
.......

[edit] Verify module loading from rmanager

Another way to check if module is loaded is from telnet, use command status check if pgsqldb is in the list:

 %%+status                                                             
 ...............
name=pgsqldb,type=database,format=Total|Failed|Errors|AvgExecTime;conns=0,failed=0
............
%%-status

Or simply type after command status the name of the module like this:

status pgsqldb
%%+status:pgsqldb
name=pgsqldb,type=database,format=Total|Failed|Errors|AvgExecTime;conns=0,failed=0
%%-status

If line name=pgsqldb,.... don't appear in the list that means that module is not loaded. Install devel packages. Then restart Yate for changes to take place.

[edit] Solutions

• A reason that the module is not loaded may be that you don't have the devel packages installed.

Install devel packages. Then run ./configure and make in Yate's sources.

• Another reason could be that the module is not listed in file yate.conf. If parameter modload is disabled, then you have to check in [modules] section that module is enabled.

yate.conf

[general]
modload=no

[modules]
pgsqldb.yate=yes

[edit] Configuration

The configuration file is pgsqldb.conf. It is found in directory conf.d where Yate sources are.
The file must be renamed from pgsqldb.conf.sample into pgsqldb.conf.

This file has the [general] section that holds the common settings to all connections.
The other sections in this file will describe each database connection.
The section name written between [], is the account name of the connection.
This name can be used in other configuration files to set the database account to be used.

There can be multiple connections to different databases.

pgsqldb.conf

[general]
; This section is special - holds settings common to all connections

; priority: int: Handler priority
;priority=100
 

; Each other section in this file describes a database connection

;[default]
; The section name is used as the database connection name

; autostart: bool: Automatically initiate the connection on startup
;autostart=yes

; timeout: int: Query timeout in milliseconds
;timeout=10000

; retry: int: How many times to retry the connection or query
;retry=5

; encoding: string: Character set encoding used to communicate with the server
;  If not set will match the encoding of server side database
;encoding=

; connection: string: Ready-built client connection string for PQconnectStart
;  If this is not set the string is built from the following pieces
;  Take care if you set this - it will override anything that follows
 ;connection=

; host: string: PostgreSQL server to connect to
;host=localhost

; port: int: Port on which the server is listening for connections
;port=5432

; database: string: Name of the database to use in queries
;database=yate

; user: string: Username to use to access the database
;user=postgres

; password: string: Password to authenticate the user to the database server
;password=

[edit] Example

Example of configuring an PostGreSQL database account in file pgsqldb.conf:

[yateadmin]
host=localhost
port=5432
database=yateadmin
user=postgres
password=yate_admin

This is where a new connection is made.
You can have multiple connections to different databases.
The name between [] will be used in other configuration files to set the used account.

If you need to store CDRs, you will have to use register.conf file, so that Yate will know which connection to make.

register.conf

[general]
account=yateadmin

[edit] Status of running module

Example of "status pgsqldb" command output:

name=pgsqldb,type=database,format=Total|Failed|Errors|AvgExecTime;conns=1,failed=0;postgres=1|0|0|2

Where:

• conns=1 - number of defined connections
• failed=0 - number of connections that failed to connect

• format=Total|Failed|Errors|AvgExecTime

postgres=1|0|0|2

Above postgres is the name of the connection you defined.

• Total: total number of queries that were executed(or tried to execute) on this connection
• Failed: number of failed queries on this connection
• Errors: number of errors when trying to execute queries(including retrials) on this connection
• AvgExecTime: average execution time per query (miliseconds)

See also

• MySQL
• Register
• Modules