Recently 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:
- readline: Library for editing text lines. More Information
- zlib: Data compression library. More Information
- crypto: Cryptographic functions library of SSL traffic encryption. More Information
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
This post is also available in Español.
by 
English
Español
