Docker Postgres Install Extension

Posted : admin | On 17-04-2021

Introduction

This is the second of a two-part tutorial series explaining how to build and run a Python and PostgreSQL docker container. The container is being designed to uses the psycopg2 database adapter for PostgreSQL and Python programming language to interface with the database. Part two will also explain how to use Docker Compose and Dockerfile to customize a PostgreSQL-Python image and use the PythonPIP package manager to install the psycopg2 Postgres adapter.

Prerequisites for Docker

Docker must be properly installed on the machine or server running the programs. Elevate privileges must be enabled with access to a command prompt or UNIX terminal on the same machine or server.

Installing Extensions¶ Our official Docker image comes with Power Pack and Review Bot pre-installed. If you need to install additional extensions, you’ll need to build an image. Create a directory where your Dockerfile will live. If you’re installing custom extensions, create a packages/ directory inside it and place your extension.whl. When using the default (Debian-based) variants, installing additional extensions (such as PostGIS) should be as simple as installing the relevant packages (see github.com/postgis/docker-postgis for a concrete example). On some systems you may need to install an additional package (for example, postgresql-contrib) for certain extensions to become available. A typical migration failure scenario The following is an example of a situation when the extension hasn’t been installed before running migrations.

Docker Postgres Install Extension Tool

Open a terminal or command-prompt window on the host machine and execute the following command to confirm Docker is installed and running:

docker --version

Note that the Docker engine version number must be compatible with the Docker Compose version number to avoid errors.

PostgreSQL table records for testing

Part one of this series created a Docker container with docker run and then entered into the container using the docker exec -it command. This was done in order to create a PostgreSQL table and sample records in a bind-mounted volume for persistent data.

If the steps in part one of this series have not yet been completed, a table must first be inserted and records added into the Docker container’s bind-mounted volume.

IDE for Python and YAML

Confirm access to a text editor or IDE that has both YAML and Python syntax as well as indentation support. While the examples in this tutorial use the Sublime IDE, other editors like Atom IDE or Visual Studio Code that uses the code command will work as well.

Docker-Compose file for Postgres

A Docker Compose file will be used to set up the container and specify such things as the bind-mounted volumes and port mapping.

Create a Docker-Compose file for the Postgres container

Change into root of the PostgreSQL-Docker project directory and create a new Docker compose file.

Following is an example of how to create a new file in a UNIX-based terminal using the touch command:

The following command can be used to edit the new file with the Sublime IDE:

subl docker-compose.yml

Note that the .yaml file extension can also be used in above command.

Docker-Compose version number

The Docker Compose version must be specified at the top of the YAML file. As shown in the following command, version '3.2' should work for the most recent stable releases of Docker:

NOTE: Typically, releases of Docker Engine 17 or newer will use a version string of '3.x' or greater in the Docker Compose file.

Postgres container and environment variables

At the same indentation level as the version number, the services: keys must be declared and the container name must be specified at one indentation level below the version number. This is shown in the following code:

services:
# container name
postgres:
# build the image from Dockerfile
build:
context: ${PWD}
# bind mount volume for Postgres data
volumes:
- $PWD/pg-data/:/var/lib/postgresql/data
- $PWD/python-app:/var/www/html
# open port so host machine can access
ports:
- '5432:5432'
# configure Postgres environment variables
environment:
- POSTGRES_USER=objectrocket
- POSTGRES_DB=some_db
- POSTGRES_PASSWORD=1234

Docker compose postgres install extension

The above YAML markup will instruct Docker to map the host port to the default Postgres port of 27017 and set the PostgreSQL environment variables. Note that the relative location for the Dockerfile must be specified under the context: key.

Networking in Docker-Compose

The last part of the YAML mark sets up the network so the container will run on an IP address of 172.28.1.4:

networks:
node_net:
ipv4_address: 172.28.1.4

networks:
node_net:
ipam:
driver: default
config:
- subnet: 172.28.0.0/16

NOTE: Make certain to save the changes made here before moving on to the Dockerfile.

Dockerfile for Postgres

The Dockerfile for the container used to execute bash commands inside the container must be created.

Execute the following command to open an instance of Dockerfile in the Sublime text editor:

NOTE: The Dockerfile name cannot have a file extension after it.

Pull the Postgres image

The following commands will pull the latest image for postgres, if needed, from the Docker hub repository, including a “bare bones” distro of Debian Linux.

Execute the following code to install Python 3 and its PIP package manager:

FROM postgres:latest
# install Python 3
RUN apt-get update&&apt-get install-y python3 python3-pip
RUN apt-get-yinstall python3.7-dev
RUN apt-get install postgresql-server-dev-10gcc python3-dev musl-dev

Install the psycopg2 adapter

Now use pip3 to install the psycopg2 Python adapter for PostgreSQL. This will allow for the retrieval and modification PostgreSQL table data stored in the container’s volume.

Execute the following command to install the psycopg2 Python adapter:

# install psycopg2 library with PIP
RUN pip3 install psycopg2

Setup the PostgreSQL environment

In the final part of the Dockerfile, the following command is executed to set up the Postgres volumes, add the postgres admin role and expose the port for the Postgres service:

# add the 'postgres' admin role
USER postgres
# expose Postgres port
EXPOSE 5432
# bind mount Postgres volumes for persistent data
VOLUME ['/etc/postgresql', '/var/log/postgresql', '/var/lib/postgresql']

Now save all of the changes made to the file and then open a terminal window at the project’s root directory where the docker-compose.yml file is located.

Build the Postgres container

Execute the following command to spin up and build the Docker container using the specifications laid out in the Docker files:

NOTE: If a bind mount error is received because the specified port for Postgres is not accessible, use the sudo lsof -i -P -n | grep 5432 command to search for the cause of the error and then pass the PID (Process ID) to the sudo kill -9 command to fix the error.

Create a Python script

While waiting for the container to build from Docker Hub’s postgres image, begin working on the Python script for the RESTful API calls to the container’s bind-mounted Postgres database.

Python script in the app folder

Execute the following code to create a new Python script in the python-app directory on the host machine that is bind-mounted to the container’s volume:

subl python-app/test-postgres.py

Now paste the following Python code into the .py script, making certain to save it in the directory specified under volumes: in the Docker Compose file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

#!/usr/bin/python3
# -*- coding: utf-8 -*-
# import the connect library from psycopg2
from psycopg2 import connect
table_name ='some_table'
# declare connection instance
conn = connect(
dbname ='some_db',
user='objectrocket',
host ='172.28.1.4',
password ='1234'
)
# declare a cursor object from the connection
cursor = conn.cursor()
# execute an SQL statement using the psycopg2 cursor object
cursor.execute(f'SELECT * FROM {table_name};')
# enumerate() over the PostgreSQL records
for i, record inenumerate(cursor):
print('n',type(record))
print( record )
# close the cursor object to avoid memory leaks
cursor.close()
# close the connection as well
conn.close()

The results should resemble the following:

Enter the Postgres Container

The container should run as a foreground process in the terminal window after it has finished building and installing Python. Open a new terminal window, or a new tab in the terminal, and use the below docker exec command to connect to the container interactively as explained in part one of this tutorial series. However, the container ID must first be obtained by using docker ps. Once the ID is obtained, use the first three digits of the alpha-numeric ID to execute the following interactive TTY session with the running container:

Once inside of the container, input python3 -V and psql -V to verify that both Python 3 and Postgres are working properly, as shown here:

Execute a pycopg2 Python script in the container

Navigate to the /var/www/html directory inside the container where the Python script should be visible. Now use the python3 command to execute the script from the terminal.

The Python code should return the PostgreSQL records stored in the container’s volume. Assuming Python and the psycopg2 adapter are working properly on the host machine, those records should be accessible outside of the container by changing the script’s host to localhostas shown in the following screenshot:

Docker Compose Postgres Install Extension

Now type exit into the terminal to leave the container and then input docker-compose down from the root directory of the project to shut down the container.

Conclusion

Docker Postgres Install Extensions

This was part two of a tutorial series explaining how to build and run a Python and PostgreSQL docker container. This second part of the series covered how to use Docker Compose and Dockerfile to customize a PostgreSQL-Python image and use the PythonPIP package manager to install the psycopg2 Postgres adapter to make API calls to a Postgres database in Python. It is critical to remember that the Dockerfile name cannot have a file extension after it when executing the command to open an instance of Dockerfile in the Sublime text editor.

These instructions are for PostgreSQL 9.1 and higher, PostGIS 2.2 and higher that is compiled with raster support.Note: if you have postgis, without raster support, you can not use CREATE EXTENSION. Refer to PostGIS install.

Enabling PostGIS

PostGIS is an optional extension that must be enabled in each database you want to use it in before you can use it. Installing the software is just the first step.DO NOT INSTALL it in the database called postgres.

Connect to your database with psql or PgAdmin. Run the following SQL.You need only install the features you want:

Upgrading PostGIS

Docker Postgres Install Extension Tutorial

To upgrade PostGIS, you first have to install the latest binaries and then upgrade each database you have PostGIS installed in

Docker Postgres Install Extension Chrome

For example connect to database you want to upgrade and if you just installed binaries for 2.1.3You can upgrade from 2.0 to 2.1, 2.2 et.c using this approach. To go from 1.* to 2.* you need to do a hard upgrade.Refer to PostGIS install for more extensive instructions.Note: that as of PostGIS 2.1.3 and PostGIS 2.0.6, you need to set environment variables to get full features.

or to a specific version

Spatial SQL

See the documentation for more guidance.