Tags

,

Let’s check if somehow we have PostgreSQL before.  If not install it as,

database - postgre query and installation

Now, query should be successful.

database - postgre query installation

Creating a db requires ‘createdb’ command.

database - createdb role doesnt exists error

Here, we are not able to create db because ‘sifa’ does not have PostgreSQL user account. In this case, administrator should create one.

Default administrator account ‘postgres’ is locked. It is better not to unlock it, and use root in order to jump postgres. Then, we may create a new role for ‘sifa’ which the upper figure request. Let’s give only database creation privilege for now. Then we should be able to create database as seen below;

database - creating postgre role sifa

As database is created, we may access it through command line utility ‘psql’, or any language bindings available. Let’s start with investigating command line utility;

database - connection through psql

Help menu may be accessed by ‘/h’. Requesting help for creating a table may be

database - help menu create table

Which leads corresponding help documentation

database - help menu create table result

Let’s create a table named ‘CallTable’. Remember from the upper version query that we are using version 9.1. We may see available data types in PostgreSQL documentation: http://www.postgresql.org/docs/9.1/static/runtime-config-query.html

Our table will hold call data that appears in a sample call center voice recording solution. Attributes will be a sequentially incrementing call id, a recording device indicator capturer id, IP, port and id of calling and called parties, and finally start time and duration of the call.

database - creating calltable

If we want to see the tables in our db, we have several options including;

database - listing tables in database

Contents of (our empty) ‘CallTable’ may be listed as follows;

database - listing content of tables in database

Let’s create another user ‘svr_capturer’ with password which will be used in remote client c++ application to populate ‘CallTable’ in ‘svrdb’

database - creating postgre role svr_capturer

And user ‘sifa’ should grant privileges to ‘svr_capturer’ in order to use table ‘CallTable’ and sequence ‘CallTable_CallId_Seq’

database - giving privileges to role svr_capturer

Now it is time for binding our application and using database programmatically. In order to use PostgreSQL in c/c++ programs, we may use ‘libpq’ library. Required header file is ‘libpq-fe.h’

database - including libpq-fe header

it is possible that we may not installed the client binding package headers. Check it and install if necessary,

database - package query for libpq-dev

In this case, install the package libpq-dev, which should lead proper (but useless) compilation.

database - including libpq-fe header compilation

Now let’s add some functionality. Let’s try to make a local connection using loopback address to ‘svrdb’ and try to modify the table.

database - a local application

database - compilation and execution of a local application

Running the program should modify our empty ‘CallTable’ which may be checked from ‘psql’ interface.

database - listing content of tables before and after remote application

By default, because of security reasons, PostgreSQL permits connections from local. This may be seen from ‘netstat’.

database - using netstat to see default usage

In order to allow remote connections, we should enable corresponding interfaces. In order to listen connections from interfaces we should edit ‘/etc/postgresql/9.1/main/postgresql.conf’ and change ‘listen_addresses’ item;

database - configuring postgresql conf for remote listening

As indicated, this requires a restart ‘/etc/init.d/postgresql restart’. Now, we should see that, we are listening all interfaces

database - using netstat to see extended usage

Another required step is to add an entry for user ‘svr_capturer’ to reach ‘svrdb’ by modifying ‘pg_hba.conf’ file. Modification will require a restart.

database - adding remote connection entry at pg_hba_conf

database - modification of pg_hba_conf

Next, will be integrating this database connection to a more realistic c++ application.

Advertisements