Let’s check if somehow we have PostgreSQL before. If not install it as,
Now, query should be successful.
Creating a db requires ‘createdb’ command.
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;
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;
Help menu may be accessed by ‘/h’. Requesting help for creating a table may be
Which leads corresponding help documentation
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.
If we want to see the tables in our db, we have several options including;
Contents of (our empty) ‘CallTable’ may be listed as follows;
Let’s create another user ‘svr_capturer’ with password which will be used in remote client c++ application to populate ‘CallTable’ in ‘svrdb’
And user ‘sifa’ should grant privileges to ‘svr_capturer’ in order to use table ‘CallTable’ and sequence ‘CallTable_CallId_Seq’
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’
it is possible that we may not installed the client binding package headers. Check it and install if necessary,
In this case, install the package libpq-dev, which should lead proper (but useless) 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.
Running the program should modify our empty ‘CallTable’ which may be checked from ‘psql’ interface.
By default, because of security reasons, PostgreSQL permits connections from local. This may be seen from ‘netstat’.
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;
As indicated, this requires a restart ‘/etc/init.d/postgresql restart’. Now, we should see that, we are listening all interfaces
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.
Next, will be integrating this database connection to a more realistic c++ application.