Debian,PostgreSQL,Software 09/24/2013 at 12:34

Compile PostgreSQL 9.2.4 with pldebugger support in Debian 6

Logo PostgreSQLRecently at work, I had to develop a trigger for PostgreSQL that was a bit complex, so the possibility to debug it step by step was very desirable. When trying to access the debugger from pgAdminIII, results that most versions of PostgreSQL for linux does not have this functionality integrated.

So, the only solution was to compile the pldebugger plugin from sources and add it to PostgreSQL.

To compile this plugin you need the PostgreSQL source code, so .. once stuck in the task … why not take the opportunity and install PostgreSQL from sources as well?

The versions of the software that will be used in the article are:

  • Debian 6.1
  • PostgreSQL 9.2.4
  • pldebugger

To achieve our goal, and starting from a clean install of Debian 6 NetInstall follow the following steps:

1.- Install build-esentials packages

The first thing to do is to install the essential packages to compile in our distribution. For Debian 6, we will run the following command:

# apt-get install build-essential

We must also get some extra libraries needed to successfully compile PostgreSQL:

# apt-get install libreadline5 libreadline5-dev zlib1g zlib1g-dev openssl libssl-dev

Installed libraries and their purpose are:

We also need the Git client to download pldebugger sources directly repository:

# apt-get install git


2.- Get PostgreSQL source code

The next step is to obtain the source code of PostgreSQL, to do so we will use the wget command:

#  wget http://ftp.postgresql.org/pub/source/v9.2.4/postgresql-9.2.4.tar.gz

Then extract the downloaded tar.gz file using the command:

#  tar xvzf ./postgresql-9.2.4.tar.gz

3.- Get pldebugger source code

We will get the pldebugger source code from the project repositories using the git client:

#  git clone git://git.postgresql.org/git/pldebugger.git

Following the plugin installation instructions, the nest step is to copy the downloaded directory to the directory /comtrib of PostgresSQL source code directory tree.

#  cp ./pldebugger/ ./postgresql-9.2.4/contrib/ -R

4.- Stablish PostgresSQL´s compilations options

Now we will stablsh the compilation options. To do so, we must enter inside the directory of PostgreSQL´s source code:

#  cd ./postgresql-9.2.4

and type this command:

# ./configure -with-openssl -enable-thread-safety

These are the options established:

  • -with-openssl: Enables encrypted connections using SSL
  • -enable-thread-safety: It allows safety use of concurrency (threads)

5.- Compile PostgreSQL´s source code

Once stablished the compilation options without errors, we will proceed to compile the source code. The tool to do so is make command:

# make


6.- Install binary files

If compilation has been completed successfully without errors, we proceed to install the binary files:

# make install

If every thing goes fine, we have PostgreSQL installed in our machine.

7.- Compile pldebugger

At this point we must compile the pldebugger plugin. To do so, we must get enter the directory inside the PostgreSQL source code tree where you copy the plugin source code. If you followed literally the instructions in this article, it should be the directory ./postgresql-9.1.3, so we will continue executing the following commands:

#  cd ./contrib/pldebugger/

and compile the plugin:

# make

8.- Install pldebugger

Install the plugin is as easy as execute this command:

# make install

9.- Configure PostgreSQL

9.1 Add binary files to system path

The next step os to edit the bash configuration file /etc/profile to add PostgresSQL´s binary files to the system´s PATH variable. To do so, add to the file the line:

PATH="/usr/local/pgsql/bin":$PATH

so, the configuration file looks like this:

# /etc/profile: system-wide .profile file for the Bourne shell (sh(1))
# and Bourne compatible shells (bash(1), ksh(1), ash(1), ...).

if [ "`id -u`" -eq 0 ]; then
  PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin"
else
  PATH="/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games"
fi
PATH="/usr/local/pgsql/bin":$PATH
export PATH

if [ "$PS1" ]; then
  if [ "$BASH" ]; then
    # The file bash.bashrc already sets the default PS1.
    # PS1='\h:\w\$ '
    if [ -f /etc/bash.bashrc ]; then
      . /etc/bash.bashrc
    fi
  else
    if [ "`id -u`" -eq 0 ]; then
      PS1='# '
    else
      PS1='$ '
    fi
  fi
fi

# The default umask is now handled by pam_umask.
# See pam_umask(8) and /etc/login.defs.

if [ -d /etc/profile.d ]; then
  for i in /etc/profile.d/*.sh; do
    if [ -r $i ]; then
      . $i
    fi
  done
  unset i
fi

If every thing is all right, when closing session and login again with you user, the command:

# echo $PATH

should return something like this:

/usr/local/pgsql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

where you can see at first place PostgresSQL binary path.

9.2 Create the user postgres

Next, we will create the user postgres which is the user under the database will execute:

# adduser postgres

When system ask for password, we can type whatever we want, but we must remember this passwod becasue it will be used in the future.

9.3 Create and initialize data directory

Next we will create and initialize the directory where PostgreSQL will store the information, this is, the data directory:

# mkdir /usr/local/pgsql/data

The owner of this directory must be the user postgres:

# chown postgres /usr/local/pgsql/data

Next we will use the postgres user to initialize the data directory:

# su - postgres

and start the initialize process:

$ initdb -D /usr/local/pgsql/data

At this point, we shoud see in our command console something like this:

Success. You can now start the database server using:

    postgres -D /usr/local/pgsql/data
or
    pg_ctl -D /usr/local/pgsql/data -l logfile start

In this moment we can start the database in foreground with the command:

#  postgres -D /usr/local/pgsql/data

and stop it by pressing Ctr-C

If we want to start the database in the background, we use the second command:

#   pg_ctl -D /usr/local/pgsql/data -l logfile start

to stop it, use the next command:

$  pg_ctl -D /var/pgsql/data stop -m fast


9.4 Configure database access password for user postgres

To configure database access password for user postgres, first we must start the server:

#   pg_ctl -D /usr/local/pgsql/data -l logfile start

Once started and as postgres user, run the PostgreSQL´s commmand line tool:

$psql

Once in the database command line interface type:

postgres=# alter user postgres with password ‘passwd’;

where passwd is the new password for the user postgres.Database must respond with:

ALTER ROLE
postgres=#

Next, exit the database command line interface:

postgres=#\q 

and logout of postgres user account:

$exit 

9.5 Create autostart script

To start PostgreSQL at boot time, copy the file named linux from directory /contrib/start-scripts of postgreSQL source code tree to directory /etc/init.d. In this point of the installation you should be in the directory ./postgresql-9.2.4/contrib/pldebugger, so the next command will do the job:

#  cp ../start-scripts/linux /etc/init.d/postgresql

Once copied the script, we must edit it to add the LSB labels. To do so, we will add at the begining of the file /etc/init.d/posgresql, just bellow #! /bin/sh, this lines:

### BEGIN INIT INFO
# Provides:          PostgreSQL
# Required-Start:    $remote_fs $syslog
# Required-Stop:     $remote_fs $syslog
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: Start Postgresql at boot time
# Description:       Enable service provided by daemon.
### END INIT INFO

We also have to give running permissions to the script:

#  chmod 755 /etc/init.d/postgresql

With this, we can start and stop PostgreSQL with the typical command:

#  /etc/init.d/postgresql action

where action is one of this options:

  • start Starts the database server.
  • stop Stop the database server.
  • restart Stop the database server and starts it again.
  • reload Reloads the database server configuration.
  • status Reports if database server is running or not.

Finally, we must add this script to system startup scripts, so we will execute the command:

#  update-rc.d postgresql defaults

If for some reason you need to remove the script form boot secuence, run this command:

#  update-rc.d -f postgresql remove

9.6 Edit postgresql.conf file

The next step is edit the file /usr/local/pgsql/data/postgresql.conf . We will allow the database server listen all network interfaces and enable the pldebugger plugin.

To allow the server listen all network interfaces, locate the line:

#listen-addresses = 'localhost' 

(usually line 59), and replace it with the line :

listen-addresses = '*' 

if you want PostgreSQL listen only for certain IPs, you only have to enumerate it separated by commas, and surround the result string with single quotes, like in this example:

listen-addresses = '192.168.0.2,192.168.0.3'

Note: for security reasons it is not recommended to allow access from any machine.

To load pldebugger plugin when PostgreSQL starts, locate the line:

#shared_preload_libraries = ''          # (change requires restart)

(usually line 127), and replace it with the line :

shared_preload_libraries = '$libdir/plugin_debugger' # (change requires restart)


9.7 Configure access permissions

9.7.1 Configure local access permissions:

When compiling from source, PostgreSQL is not enough restrictive when managing permissions, as by default, it allows all users access the system without a password. For this reason we will constrain some permissions for local access.

To do so, we will edit the file /usr/local/pgsql/data/pg_hba.conf
and locate the line:

local all all trust

and replace it with:

local all all md5

With this we allow access to any user account from the local machine but we require a MD5 encrypted password. This way, when you restart the PostgreSQL server you will be able to access the command line tool from any user account using the postres database user account as shown below:

$psql -U postgres -W 
9.7.2 Configure remote access permissions:

Finally, we must configure remote access permissions. The idea is respond this questions:

  • What users have permissions to connect?
  • From what IPs can they connect?
  • What databases can they connect?
  • and… What authentication method can they use?

In this post, we will grant access to all users, to connect all databases, from any IP, using an MD5 encrypted password.

To do so, we must edit again the file /usr/local/pgsql/data/pg_hba.conf and locate the line:

host all all 127.0.0.1/32 trust

modify it to looks like:

host all all 0.0.0.0 0.0.0.0 md5

A simple explanation from this rule is:

  • host: Specifies that the rule is applicable to remote connections.
  • all: Answers the question: What users have permissions to connect?. In this case, all of them. If we wish to specify a given user we would replace the keyword “all” by the name of the desired user. If we want to apply the same rule to several users, we have to copy and paste the rule for each user, and change the user name in the new rule.
  • all: Answers the question: What databases can they connect?.In this case, all of them .if we want to specify a given database, we would replace the keyword “all” by the mane of the desired database. If we want to apply the same rule to several databases, we have to copy and paste the rule for each database, and change the database name in the new rule.
  • 0.0.0.0 0.0.0.0: Answers the question: From what IPs can the users connect?. in this case, any IP. The fisrt 4 digit group specifies the IP, the second one is the network mask. The values 0.0.0.0 are wildcard values,which means “any IP” and “any network mask” respectively.
  • md5: Answers the question: What authentication method is allowed?. In this case an MD5 encrypted password. Other possible values are : “password” for encrypted password or “trust” to allow access without password.

For more information about how the file /etc/postgresql/9.2/main/pg_hba.conf works, you can take a look to the official documentation : http://www.postgresql.org/docs/9.2/interactive/auth-pg-hba-conf.html

finally we will disable local connections through IP6 protocol.To do so, locate the line:

host all all ::1/128 trust

and comment it:

#host all all ::1/128 trust


10.- Restart the server

To test that everything is all right, that the autostart script is correctly installed, and that the defined database configurations are right we must restart the entire machine. So, we will run this command:

# reboot

11.- Test the debugger

To test the debugger, first of all is create a test database,with a test table, and with a test stored procedure that we can debug.

11.1.- Access PostgreSQL´s command line tool

To access the PostgresSQL´s command line tool, run this command:

$ psql -U postgres -W

11.2.- Create test database

Once inside the PostgresSQL´s command line tool we will create the test database with this SQL sentence:

postgres=# CREATE DATABASE test_debugger WITH ENCODING='UTF8';

11.3.- Create test table

The next step is connect to the database just created:

postgres=# \c test_debugger 

and system responds with:

You are now connected to database "test_debugger ".

Next create the test table:

test_debugger=# CREATE TABLE test_table (
	               Id BIGSERIAL PRIMARY KEY ,
	               Val VARCHAR (255) default ''
                  );

and system responds with:

NOTICE:  CREATE TABLE will create implicit sequence "test_table_id_seq" for serial column "test_table.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_table_pkey" for table "test_table"
CREATE TABLE


11.4.- Create a test stored procedure

Lets continue creating objects in the test database. Next one will be a stored procedure:

test_debugger=#
CREATE OR REPLACE FUNCTION test_table_trigger() RETURNS TRIGGER AS $test_table_trigger$
  DECLARE
	sValue VARCHAR (255):= NEW.Val;
	iId INTEGER:= NEW.Id;
	iCalc INTEGER:= 0;
  BEGIN
   RAISE NOTICE '---------- START test_debugger.test_table__trigger() ----------';
   RAISE NOTICE '---------- => New record has an Id: %',iId;
   RAISE NOTICE '---------- => New record has a value: %',sValue;
   iCalculo=iId +5;
   RAISE NOTICE '---------- => The Id of the new record +5 is: %',iCalc;
   RAISE NOTICE '---------- END test_debugger.test_table_trigger() -------------';
   NEW.Id= iCalc;
   RETURN NEW;
  END;

Note: to create the stored procedure with the command line tool copy it to the prompt and press the Enter key.

Next specify the language of the stored procedure:

test_debugger=# $test_table_trigger$ LANGUAGE plpgsql;

After type the above command and press the Enter key, system responds with:

 CREATE FUNCTION

11.5.- Create trigger to execute the stored procedure

We will create a trigger to run the stored procedure after each insert:

prueba_debugger=# CREATE TRIGGER update_test_table AFTER INSERT
                  ON test_table FOR EACH ROW EXECUTE PROCEDURE  test_table_trigger();

and system responds with:

 CREATE TRIGGER

11.6.- Run debugging preparation script

To be able to debug stored procedures we need to execute a preparation script on the database to debug. To do so, run this command:

test_debugger=# CREATE EXTENSION pldbgapi;

and system responds with:

CREATE EXTENSION

Once we have run the preparation script, we can connect to database from the GUI tool pgAdminIII and use the debugging feature.

Note: You must run the prepare script on each database you want to debug.


11.7.- Test the debugger

Finally, we get the reward for our efforts: open pgAdminIII,open the test database on public scheme, open the test table test_table, deploy the section Trigger-Functions, right click over test_table_trigger function, and click on “Set breakpoint”

In this screenshot we can see the debugger in action:


plDebugger in action

plDebugger in action




This post is also available in Español.

Tags:

Leave a reply

required

required

optional


%d bloggers like this: