AHMED ZBYR

AHMED ZBYR

Biker, EV Enthusiast

In this blog post we will be setting up a postgres database on docker.

Setting up Docker

Before we start we need docker to be installed on te system, we can use https://docs.docker.com/get-started/ to install and setup the environment.

Once we have installed we can test the setup using the commands below.

┌─[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ docker --version
Docker version 20.10.0, build 7287ab3

┌─[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ docker-compose --version
docker-compose version 1.27.4, build 40524192

We now have the docker setup ready.

Setting up the database.

We can get the latest version of postgres from the docker hub. Postgres Offical Image

We have different versions there but we will be using the version 9.6.21

1. Pull Image from docker.

To pull the latest postgres version we can do.

docker pull postgres

┌─[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ docker pull postgres
Using default tag: latest
latest: Pulling from library/postgres
6f28985ad184: Pull complete
163a60947b3a: Pull complete
1791984387e5: Pull complete
ccf9c39579c4: Pull complete
1d8dd50a5ee9: Pull complete
3991abc55a94: Pull complete
4cf2cdef0857: Pull complete
ed1bec410498: Pull complete
0930368b9a14: Pull complete
a9302936fdb5: Pull complete
bb3d505cd0cb: Pull complete
4f1bb2dd6f16: Pull complete
8d3f6ff7b2da: Pull complete
687caf1b1f9b: Pull complete
Digest: sha256:c83014a2b46834ef6d17f64c8e4a70089901a8c0dee158f1ca5ccae032ea32e5
Status: Downloaded newer image for postgres:latest
docker.io/library/postgres:latest

To get a specific version we have to give the tag. Image Tags

docker pull postgres:9.6.21

┌─[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ docker pull postgres:9.6.21
9.6.21: Pulling from library/postgres
a4feded82f54: Pull complete
d19e7242f4bd: Pull complete
6cb253df2129: Pull complete
ea721dc98310: Pull complete
68f7cd84a8b8: Pull complete
8393cbb84c4e: Pull complete
287e8e4b251d: Pull complete
2122c0a410c8: Pull complete
ce2fcfb3d94a: Pull complete
5124a549865d: Pull complete
57ea93a66d30: Pull complete
c9462e801511: Pull complete
b1177777b89f: Pull complete
bb48c796065e: Pull complete
Digest: sha256:a435b8edc3bdb4d766818dc6ce22ca3a5e6a922d19ca7001afd1359d060500eb
Status: Downloaded newer image for postgres:9.6.21
docker.io/library/postgres:9.6.21

2. Setting up the postgres database.

Based on the description on the docker image Environment Variables

We can use POSTGRES_PASSWORD to set the password for the database.

docker run -d -p 5432:5432 --name ahmedpostgres -e POSTGRES_PASSWORD=ahmedpostgres postgres:9.6.21

We are using

  • -p option to listen to postgres on 5432 port.
  • -d Detach the container. (like adding & in linux terminal)
  • -e Set environment variables on the docker container.
  • postgres:9.6.21 is the name of the image version we downloaded and will use.

We are using few options from the run command. We can get all information when we run docker run --help

┌─[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ docker run  --help

Usage:  docker run [OPTIONS] IMAGE [COMMAND] [ARG...]

Run a command in a new container

Options:

  -d, --detach                         Run container in background and
  -p, --publish list                   Publish a container's port(s) to
                                       the host
  -e, --env list                       Set environment variables                                       


┌─[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ docker run -d -p 5432:5432 --name ahmedpostgres -e POSTGRES_PASSWORD=ahmedpostgres postgres:9.6.21
7cd09103892af3f4a778ac9fbe89b4f6d4c6fc9f96eb8557220cc96fce69c957

We can then check if postgres is running using the docker ps command.

┌─[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ docker ps
CONTAINER ID   IMAGE             COMMAND                  CREATED          STATUS          PORTS
       NAMES
7cd09103892a   postgres:9.6.21   "docker-entrypoint.s…"   13 seconds ago   Up 12 seconds   0.0.0.0:5432->5432/tcp   ahmedpostgres

3. Login to the container.

Execute below command to login to the docker container.

docker exec -it ahmedpostgres bash
  • -i, --interactive Keep STDIN open even if not attached.
  • -t, --tty Allocate a pseudo-TTY.

More information about the exec subcommand.

┌─[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ docker exec --help

Usage:  docker exec [OPTIONS] CONTAINER COMMAND [ARG...]

Run a command in a running container

Options:
  -d, --detach               Detached mode: run command in the background
      --detach-keys string   Override the key sequence for detaching a
                             container
  -e, --env list             Set environment variables
      --env-file list        Read in a file of environment variables
  -i, --interactive          Keep STDIN open even if not attached
      --privileged           Give extended privileges to the command
  -t, --tty                  Allocate a pseudo-TTY
  -u, --user string          Username or UID (format:
                             <name|uid>[:<group|gid>])
  -w, --workdir string       Working directory inside the container

Login to the container and then execute psql -U postgres to login to postgres.

┌─[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ docker exec -it ahmedpostgres bash
root@7cd09103892a:/# psql -U postgres
psql (9.6.21)
Type "help" for help.

postgres=#
postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

4. Creating a Database and Table.

Creating a database.

postgres=# create database pythontest_db;
CREATE DATABASE
postgres=#

Creating table and data. Stackoverflow

CREATE TABLE DOCUMENT_TEMPLATE(
   ID INTEGER NOT NULL,
   NAME TEXT,
   SHORT_DESCRIPTION TEXT,
   AUTHOR TEXT,
   DESCRIPTION TEXT,
   CONTENT TEXT,
   LAST_UPDATED DATE,
   CREATED DATE
);

Insert Data into table.

INSERT INTO DOCUMENT_TEMPLATE(id,name, short_description, author,
                              description,content, last_updated,created)
SELECT id, 'name', md5(random()::text), 'name2'
      ,md5(random()::text),md5(random()::text)
      ,NOW() - '1 day'::INTERVAL * (RANDOM()::int * 100)
      ,NOW() - '1 day'::INTERVAL * (RANDOM()::int * 100 + 100)
FROM generate_series(1,100) id;

postgres=# \c pythontest_db;
You are now connected to database "pythontest_db" as user "postgres".
pythontest_db=# \l
                                   List of databases
     Name      |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
---------------+----------+----------+------------+------------+-----------------------
 postgres      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 pythontest_db | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0     | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
               |          |          |            |            | postgres=CTc/postgres
 template1     | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
               |          |          |            |            | postgres=CTc/postgres
(4 rows)

pythontest_db=# \dt
No relations found.
pythontest_db=# CREATE TABLE DOCUMENT_TEMPLATE(
pythontest_db(#    ID INTEGER NOT NULL,
pythontest_db(#    NAME TEXT,
pythontest_db(#    SHORT_DESCRIPTION TEXT,
pythontest_db(#    AUTHOR TEXT,
pythontest_db(#    DESCRIPTION TEXT,
pythontest_db(#    CONTENT TEXT,
pythontest_db(#    LAST_UPDATED DATE,
pythontest_db(#    CREATED DATE
pythontest_db(# );
CREATE TABLE
pythontest_db=# INSERT INTO DOCUMENT_TEMPLATE(id,name, short_description, author,
pythontest_db(#                               description,content, last_updated,created)
pythontest_db-# SELECT id, 'name', md5(random()::text), 'name2'
pythontest_db-#       ,md5(random()::text),md5(random()::text)
pythontest_db-#       ,NOW() - '1 day'::INTERVAL * (RANDOM()::int * 100)
pythontest_db-#       ,NOW() - '1 day'::INTERVAL * (RANDOM()::int * 100 + 100)
pythontest_db-# FROM generate_series(1,100) id;
INSERT 0 100
pythontest_db=# select * from DOCUMENT_TEMPLATE limit 3;
 id | name |        short_description         | author |           description            |             content              | last_updated |  created
----+------+----------------------------------+--------+----------------------------------+----------------------------------+--------------+------------
  1 | name | 7368ee015e12ee64e00c45806998cebe | name2  | 1bfb0f6b509adc0ceae2f3ac3065c798 | 6e0547a18cdc0483511afb4363a3874e | 2020-12-11   | 2020-12-11
  2 | name | 4318a086a664523289b3f80ab738d793 | name2  | 6b1615782c058275f3297e95e6dcee91 | 4372f17c46d4690039bfee814b2b0eb7 | 2021-03-21   | 2020-09-02
  3 | name | b1d3bdff70efeb292d7a9f93c67057f7 | name2  | bd8c99f801984160b8400ff9f53d4aaf | ed47daa6448f4f4599f902dfe2e15e76 | 2020-12-11   | 2020-12-11
(3 rows)

Now our database is running on port 5432.

4. Test connection using python.

Let do a test, using python.

Create a file called connect_to_postgres.py with below contents.

import psycopg2
conn = psycopg2.connect(host="localhost", database="pythontest_db",
                        user="postgres", password="ahmedpostgres")

if conn is not None:
    print('Connection established to PostgreSQL.')

    # Creating a cursor
    cur = conn.cursor()

    # Getting a query ready.
    cur.execute('SELECT * from DOCUMENT_TEMPLATE limit 3;')

    # we are fetching all the data from the query above.
    get_all_data = cur.fetchall()

    # Print all data
    print(get_all_data)

    # Close connection
    conn.close()
else:
    print('Connection not established to PostgreSQL.')

Setup the environment using below commands.

python -m venv .venv
source .venv/Scripts/activate
pip install psycopg2
python connect_to_postgres.py

Executing the python script, assuming we have the psycopg2, if not check the command output below for more details.

┌─(.venv)[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ python connect_to_postgres.py 
Connection established to PostgreSQL.
[
  (1, 'name', '7368ee015e12ee64e00c45806998cebe', 'name2', '1bfb0f6b509adc0ceae2f3ac3065c798', '6e0547a18cdc0483511afb4363a3874e', datetime.date(2020, 12, 11), datetime.date(2020, 12, 11)), 
  (2, 'name', '4318a086a664523289b3f80ab738d793', 'name2', '6b1615782c058275f3297e95e6dcee91', '4372f17c46d4690039bfee814b2b0eb7', datetime.date(2021, 3, 21), datetime.date(2020, 9, 2)), 
  (3, 'name', 'b1d3bdff70efeb292d7a9f93c67057f7', 'name2', 'bd8c99f801984160b8400ff9f53d4aaf', 'ed47daa6448f4f4599f902dfe2e15e76', datetime.date(2020, 12, 11), datetime.date(2020, 12, 11))
]

Create a virtual environment.

┌─[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ python -m venv .venv
┌─[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ source .venv/Scripts/activate
┌─(.venv)[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ pip list
Package    Version
---------- -------
pip        20.2.3
setuptools 49.2.1
WARNING: You are using pip version 20.2.3; however, version 21.0.1 is available.
You should consider upgrading via the 'd:\git_repos\docker-playground\.venv\scripts\python.exe -m pip install --upgrade pip' command.

Install the psycopg2 package.

┌─(.venv)[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ pip install psycopg2
Collecting psycopg2
  Downloading psycopg2-2.8.6-cp39-cp39-win_amd64.whl (1.2 MB)
     |████████████████████████████████| 1.2 MB 3.2 MB/s
Installing collected packages: psycopg2
Successfully installed psycopg2-2.8.6
WARNING: You are using pip version 20.2.3; however, version 21.0.1 is available.
You should consider upgrading via the 'd:\git_repos\docker-playground\.venv\scripts\python.exe -m pip install --upgrade pip' command.

Run the python script.

┌─(.venv)[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ python connect_to_postgres.py 
Connection established to PostgreSQL.
[
  (1, 'name', '7368ee015e12ee64e00c45806998cebe', 'name2', '1bfb0f6b509adc0ceae2f3ac3065c798', '6e0547a18cdc0483511afb4363a3874e', datetime.date(2020, 12, 11), datetime.date(2020, 12, 11)), 
  (2, 'name', '4318a086a664523289b3f80ab738d793', 'name2', '6b1615782c058275f3297e95e6dcee91', '4372f17c46d4690039bfee814b2b0eb7', datetime.date(2021, 3, 21), datetime.date(2020, 9, 2)), 
  (3, 'name', 'b1d3bdff70efeb292d7a9f93c67057f7', 'name2', 'bd8c99f801984160b8400ff9f53d4aaf', 'ed47daa6448f4f4599f902dfe2e15e76', datetime.date(2020, 12, 11), datetime.date(2020, 12, 11))
]

You May Also Enjoy