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

How to install docker on Ubuntu

We follow the procedure advised by docker (Install using the repository) :

$ sudo apt-get update

$ sudo apt-get install \
    apt-transport-https \
    ca-certificates \
    curl \
    software-properties-common
    
$ curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
    
$ sudo apt-key fingerprint 0EBFCD88    

$ sudo add-apt-repository \
   "deb [arch=amd64] https://download.docker.com/linux/ubuntu \
   $(lsb_release -cs) \
   stable"
   
$ sudo apt-get install docker-ce

We do the classical test :

$ sudo docker run hello-world

and then we say to linux to start docker at boot :

$ sudo systemctl enable docker

That’s it !

Done on Ubuntu 16.04.

It is possible to use Docker as non root user.