How to install the PL/pgSQL debugger

Pgadmin4 has a very nice PL/pgSQL debugger available on github.

To install it, we follow a procedure in 3 steps :

1. To download the code from git, to compile it and to install it

2. To modify postgresql.conf and to restart the PostgreSQL server

3. To install the extension in pgadmin4

The Docker embeds PostgreSQL in a minimal Debian distribution. Before installing any software, we create an apt repository

$ apt-get update

and we install the vim editor

$ apt-get install vim

and we install git

$ apt-get install git-core

Step 1 : compilation and installation of the debugger

We install some libraries needed by the debugger

$ apt-get install build-essential
$ apt-get install postgresql-server-dev-11
$ apt-get install openssl
$ apt-get install libkrb5-dev

We compile and we install he debugger

$ cd /usr/lib/postgresql/11/lib
$ mkdir -p contrib/src
$ cd contrib/src
$ git clone git://git.postgresql.org/git/pldebugger.git
$ export USE_PGXS=1
$ make 
$ make install

Step 2 : Configuration of PostgreSQL

To restart the server, we will use pg_ctl. We have to log as postgres to use pg_ctl

$ su - postgres

But before to be able to use pg_ctl, we need to set the environment variables we will find with psql

postgres=# show data_directory;
      data_directory      
--------------------------
 /var/lib/postgresql/data
(1 row)


postgres=# show config_file;
               config_file                
------------------------------------------
 /var/lib/postgresql/data/postgresql.conf
(1 row)

By the way, we can find this information with

select name, setting from pg_settings where category = 'File Locations';

We edit the .bash_profile that will be executed by the next call of su – postgres

$ vim .bash_profile

and we add the following lines

export PGDATA=/var/lib/postgresql/data
export PATH=$PATH:/usr/lib/postgresql/11/bin

We do ctrl-d to exit postgres account and we come back

$ su - postgres
$ pg_ctl status

and pg_ctl answers

$ pg_ctl: server is running (PID: 1)

We edit the configuration file of PostgreSQL

vim /var/lib/postgresql/data/postgresql.conf

and we replace

shared_preload_libraries = ''

by

shared_preload_libraries = 'plugin_debugger'

and we restart the PostgreSQL server

pg_ctl restart

Step 3 Configuration of pgadmin4

We go in pgadmin4. The debugger is an extension and PostgreSQL extensions are linked to a database, not to the cluster. Then we choose the database where we want to install the extension. We click Extensions and we find in the list : pldbgapi ! We click on it and that’s it.

debugger

How to install pgadmin4 with docker

We want PostgreSQL in a container and pgadmin4 in another container. And we want to connect the pgadmin4 container to the PostgreSQL container.

We do

$ sudo docker run -p 80:80 \
  --link postgres11:postgres11 \
  --name pgadmin_4pgdocker \
  -e "PGADMIN_DEFAULT_EMAIL=sql.design.company@gmail.com" \
  -e "PGADMIN_DEFAULT_PASSWORD=secret" \
  -d dpage/pgadmin4

80:80 means that the port 80 of the container is tunneled to the port 80 of the host.

–link postgres11:postgres11 means that we link the pgadmin4 container to the postgres11 container. Remember that we wrote in the definition of the postgres11 container the name of the host :

...
--name postgres11
...

and then we open our browser on 127.0.0.1:80, we give the username/password as defined above and when create a server, we give as host name postgres11

pgadmin4

For the host name, we give postgres11 instead of 127.0.0.1 !

and then…

pgadmin4-running

We can set about tackling the work !

And the next time we reboot, we have a look on our containers

sudo docker ps -a

that gives

CONTAINER ID   IMAGE           COMMAND                CREATED       STATUS                        
PORTS                        NAMES
c8f82e1fd7f6   dpage/pgadmin4  "/entrypoint.sh"       27 hours ago  Exited (255) 10 minutes ago   
0.0.0.0:80->80/tcp, 443/tcp  jolly_mendel
c9159dae8aae   postgres        "docker-entrypoint.s…" 27 hours ago  Up About a minute             
0.0.0.0:5432->5432/tcp       postgres11

They are of course there and their CONTAINER ID are the same. By the way, the creators of containers are used to give them poetical names like jolly_mendel.

And we restart them

$ sudo docker start c9159dae8aae
$ sudo docker start c8f82e1fd7f6

So nice !

And if we want that pgadmin4 reaches the cluster located on the host, we do

$ sudo docker run \
  -d \
  --rm \
  --net=host \
  -e "PGADMIN_DEFAULT_EMAIL=sql.design.company@gmail.com" \
  -e "PGADMIN_DEFAULT_PASSWORD=secret" \
  --name pgadmin_4_local dpage/pgadmin4                                      

We have to stop pgadmin_4pgdocker before starting the previous command.

At this very moment, I don’t know how to manage two pgadmin4 containers simultaneously.

Or to have two clusters in pgadmin4 : one for the PostgreSQL cluster in the container and one for the PostgreSQL on the host.

A bit patience…

How to add a persistant volume

We create a volume with

$ sudo docker volume create pgdata

and we call docker run

$ sudo docker run -p 5432:5432 -d \
    -e POSTGRES_PASSWORD=secret \
    -e POSTGRES_USER=postgres \
    -e POSTGRES_DB=postgres \
    --name postgres11 \
    -v pgdata:/var/lib/postgresql/data \
    postgres

5432 is the usual port number for PostgreSQL and we tunnel the port of the host into the same port within the container.

The parameter -v maps this volume with the standard location of data in PostgreSQL cluster

/var/lib/postgresql/data is the usual location of PostgreSQL data files. Those files are located in the container. To know the location of the files on the host, we inspect the volume

$ sudo docker volume inspect pgdata pgdata

that gives

...
"Mountpoint": "/var/lib/docker/volumes/pgdata/_data"
... 

We do a ls /var/lib/docker/volumes/pgdata/_data on the host and we see the same files !

We have a look on the running container

$ sudo docker ps

that gives

CONTAINER ID    IMAGE       COMMAND                  CREATED             STATUS              PORTS                           NAMES
c9159dae8aae    postgres    "docker-entrypoint.s…"   14 minutes ago      Up 14 minutes       0.0.0.0:5432->5432/tcp          postgres11

and we open a psql session on it

$ sudo docker exec -it c9159dae8aae psql -U postgres postgres

and we create a database, a table and a record in it

create database window-functions;

create table test (a text);

insert into test(a) values('hello');

select * from a;

and psql answers

  a   
-------
 hello
(1 row)

and we quit psql

\q

We stop the container (or we do a shutdown our computer)

$ sudo docker stop c9159dae8aae

We start it again (to list the containers, do a sudo docker ps -a after a shutdown because sudo docker ps gives running containers only)

$ sudo docker start c9159dae8aae

We come back in psql

$ sudo docker exec -it c9159dae8aae psql -U postgres postgres

and we connect to our database window-functions

postgres=# \c window-functions

psql answers

You are now connected to database "window-functions" as user "postgres".

and we type

window-functions=# select * from test;

and we obtain

   a   
-------
 hello
(1 row)

The deal is in the bag !

The CONTAINER IDs are persistent. Then we can encapsulate the docker commands in a script.
And this containerized version of PostgreSQL coexists with the local one as we can see in the list of running processes.

$ ps -ef | grep postgres

gives

UID        PID  PPID  C STIME TTY          TIME CMD

...
postgres  1661     1  0 16:41 ?        00:00:00 /usr/lib/postgresql/11/bin/postgres 
       -D /var/lib/postgresql/11/main -c config_file=/etc/postgresql/11/main/postgresql.conf
postgres  1736  1661  0 16:41 ?        00:00:00 postgres: 11/main: checkpointer   
postgres  1737  1661  0 16:41 ?        00:00:00 postgres: 11/main: background writer   
postgres  1738  1661  0 16:41 ?        00:00:00 postgres: 11/main: walwriter   
postgres  1740  1661  0 16:41 ?        00:00:00 postgres: 11/main: autovacuum launcher   
postgres  1742  1661  0 16:41 ?        00:00:00 postgres: 11/main: stats collector   
postgres  1743  1661  0 16:41 ?        00:00:00 postgres: 11/main: logical replication launcher   
999       8637  8615  0 17:47 ?        00:00:00 postgres
999       8719  8637  0 17:47 ?        00:00:00 postgres: checkpointer  
999       8720  8637  0 17:47 ?        00:00:00 postgres: background writer  
999       8721  8637  0 17:47 ?        00:00:00 postgres: walwriter  
999       8722  8637  0 17:47 ?        00:00:00 postgres: autovacuum launcher  
999       8723  8637  0 17:47 ?        00:00:02 postgres: stats collector  
999       8724  8637  0 17:47 ?        00:00:00 postgres: logical replication launcher  
999       8973  8637  0 17:49 ?        00:00:28 postgres: mchl postgres 172.17.0.3(45606) idle
...

The user 999 is the owner of the postgres processes launched by docker and the user postgres is the user of the local PostgreSQL cluster.

The PPID (Parent Process ID of the postgres process owned by 999 is 8615 : the docker process.

The PPID of the postgres process owned by postgres is 1 : /sbin/init, the father of all processes.

To run a shell inside the container, we do

$ sudo docker exec -it c9159dae8aae /bin/bash

and to run psql inside the container, we do

$ sudo docker exec -it c9159dae8aae psql -U postgres -d postgres

How to install PostgreSQL with docker

We pull the latest version of PostgreSQL :

$ sudo docker pull postgres

This image comes from the hub of docker for PostgreSQL. Without additional parameter, pull takes the last version.

We run PostgreSQL :

$ sudo docker run --name pg-docker -e POSTGRES_PASSWORD=secret -d postgres

And we list the containers

$ sudo docker ps   

gives

CONTAINER ID   IMAGE     COMMAND                  CREATED             STATUS             PORTS     NAMES
00ab12a6f24d   postgres  "docker-entrypoint.s…"   About an hour ago   Up About an hour   5432/tcp  pg-docker

and we list the images

$ sudo docker images

gives

REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
postgres            latest              f9b577fb1ed6        3 weeks ago         311MB
hello-world         latest              4ab4c602aa5e        3 months ago        1.84kB

and now we connect to PostgreSQL with psql :

$ sudo docker run -it --rm --link pg-docker:postgres postgres psql -h postgres -U postgres

and we obtain :

Password for user postgres: 
psql (11.1 (Debian 11.1-1.pgdg90+1))
Type "help" for help.

postgres=# 

It works !

It’s nice but… If we insert data in the database, they will be lost when the container will be stopped. To implement persistence, we will use volumes. Have a look on the next post.

We can now remove the image of hello-world

 $ sudo docker rmi hello-world -f

and then

$ sudo docker images

gives

REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
postgres            latest              f9b577fb1ed6        3 weeks ago         311MB

The sandbox framework : 7. Usage

Let’s play a bit with the framework !

We are connected to the sandbox of a user.

We reset the table log :

delete from log;

We connect to the kernel :

select public.connect_kernel('192.167.1.156','micheld', 'password')

We connect a database :

select public.connect_database('contacts','micheld', 'password')

We verify that the foreign servers are initialized :

select public.list_fdw_servers();

We must obtain something like that (mgr is the owner of the databases) :

(fdw_contacts,mgr,postgres_fdw,"{host=192.167.1.156,dbname=contacts,port=5432}")
(fdw_kernel,mgr,postgres_fdw,"{host=192.167.1.156,dbname=kernel,port=5432}")

We map the tables of the database database in the sandbox.

select public.attach_database('contacts')

and we go with pgAdmin on the tab Foreign tables to verify that everything works fine.

We disconnect now from the corporate database and all foreign tables will be removed from the sandbox :

select public.disconnect_database('contacts')

We verify in the log table that everything is OK :

select * from log order by 1 desc

That’s simple ! Those functions must be called from the front end applications with the appropriate syntax.

In fact, the function attach_database could be called directly by the function connect_database.

The sandbox framework : 6. The attach functions

The following function is central : it maps a foreign table in the sandbox.

create or replace function public.attach_table(
	this_database_name text,
	this_from_table_name text,
	this_to_table_name text default null,
	this_from_schema_name text default 'public',
	this_to_schema_name text default 'public')
    returns boolean
    language 'plpgsql'
    cost 100
    volatile 
as $BODY$

declare
    this_fdw_server_name text;
    this_fdw_table_definition text;
    sql_stmt text;
    this_fdw_table_name text;
    fdw_server_exists boolean;
begin

    sql_stmt =     'select public.is_database_connected('
                || ''''
                || this_database_name
                || ''''
                || ');';

    execute sql_stmt into fdw_server_exists;
    
    if fdw_server_exists is false then
        perform public.write2log(
		      'ERROR : attach_table : '  
        	   || quote_ident(this_database_name)
        	   || ' is not connected'
   	 	);
	return false;
    end if;
    
    if this_to_table_name is null then
    	this_to_table_name = this_from_table_name;
    end if;
    
    this_fdw_server_name = 'fdw_' || this_database_name;
        
    this_fdw_table_name = this_database_name || '_fdw_table_definitions';

    sql_stmt =     'select fdw_table_definition from '
               || quote_ident(this_fdw_table_name)
               || ' where table_name = '
               || ''''
               || this_to_table_name
               || ''''
               || ' and schema_name = '
               || ''''
               || this_from_schema_name
               || ''''
               || ';';

    execute sql_stmt into this_fdw_table_definition;
    
    if this_fdw_table_definition is null then
    	 perform public.write2log(
		       'ERROR : attach_table : no fdw definition for table '  
        	    || quote_ident(this_from_table_name)
   		 );
	 return false;
    end if;
 
    -- prefix the name of the table to be mapped with the database name
    this_to_table_name = this_database_name || '_' || this_to_table_name;
    
    select replace(this_fdw_table_definition, 
                   'THIS_FDW_SERVER_NAME', 
                   quote_ident(this_fdw_server_name)
                  ) 
    	into this_fdw_table_definition;
    
    select replace(this_fdw_table_definition, 
                   'THIS_TO_TABLE_NAME', 
                   quote_ident(this_to_table_name)
                  ) 
    	into this_fdw_table_definition;
    
    select replace(this_fdw_table_definition, 
                   'QUOTE', 
                   ''''
                  ) 
     	into this_fdw_table_definition;
        
    select replace(this_fdw_table_definition, 
                   'THIS_FROM_SCHEMA_NAME', 
                   quote_ident(this_from_schema_name)
                  ) 
    	into this_fdw_table_definition;
     
    select replace(this_fdw_table_definition, 
                   'THIS_TO_SCHEMA_NAME', 
                   quote_ident(this_to_schema_name)) 
    	into this_fdw_table_definition;
    
    execute this_fdw_table_definition;

    exception when others then 
  	perform public.write2log(
		      'ERROR : attach_table for table : '  
       	 	   || quote_ident(this_from_table_name)
        	   || sqlerrm
    	         );
	return false;
end 
$BODY$;

This one maps all the relevant tables of a database (the tables for this database type) in the sandbox. And for each table, this function calls attach_table.

create or replace function public.attach_database(this_database_name text)
    returns boolean
    language 'plpgsql'
    cost 100
    volatile 
as $BODY$
declare
    r record;
begin
    for r in     
        select     
            distinct pg_table_name, pg_schema_name     
        from         
            permissions_matrix p         
        inner join         
            sdbx_tables_2_attach s        
        on           
            p.database_type = s.database_type          
        where             
            p.pg_database_name = this_database_name
    loop
        
    	perform public.attach_table(
       		this_database_name,
               	r.pg_table_name,
                r.pg_table_name,
        	r.pg_schema_name
       		);
        
    end loop;
	
    return true;
    
    exception when others then 
        perform public.write2log(
               'ERROR : attach_database for database : '  
            || quote_ident(this_database_name)
            || ', '
            || sqlerrm
        );
        return false;
end 
$BODY$;

The sandbox framework : 5. Utililities

The following function gives the IP address of the server where this database resides and the port number. The usual value is 5432. If we have a server with multiple instances of PostgreSQL servers, this value can be different.

create or replace function public.get_database_parameters(
	    this_database_name text,
	    this_username text,
	out pg_server_address text,
	out pg_port_number text)
    returns record
    language 'sql'
AS $BODY$

    select 
    	pg_server_address,
        pg_port_number
    from
    	permissions_matrix
    where
    	pg_database_name = this_database_name
    and
    	pg_user_name = this_username;
        
$BODY$;

The two following functions dont need commentaries :

create or replace function public.is_database_connected(this_database_name text)
    returns boolean
    language 'plpgsql'
as $BODY$
declare
	r 	text;
begin
    select 
	srvname
    into
    	r
    from 
    	pg_foreign_server
    join 
    	pg_foreign_data_wrapper w 
    on 
    	w.oid = srvfdw
    where
    	srvname = 'fdw_' || this_database_name;
        
    if r is not null then 
        return true; 
    else 
        return false; 
    end if;
  
end $BODY$;
create or replace function public.list_fdw_servers()
    returns setof record 
    language 'sql'
as $BODY$

    select 
        srvname as name, 
        srvowner::regrole as owner, 
        fdwname as wrapper, 
        srvoptions as options
    from 
        pg_foreign_server
    join 
        pg_foreign_data_wrapper w 
    on 
        w.oid = srvfdw;

$BODY$;

This one is the most important of the framework :

create or replace function public.write2log(msg text)
    returns void
    language 'plpgsql'
as $BODY$
begin
        insert into public.log 
		(info, timestamp)
	values   
		(msg, now());
end $BODY$

A simple

select * from log order by 1 desc;

gives the last error messages.

An amusing detail : when we give access rights to the table log, we must also give access rights to the sequence. It is not automatically done by PostgreSQL.