PostgreSQL Setup and Configuration on Docker

 

Install PostgreSQL Database on Docker

 

Run the command in terminal to install PostgreSQL in Docker(map a port).

 

docker run --name some-postgres -e POSTGRES_PASSWORD=secret -d -p 5432:5432 postgres

 

So now you have mapped the port 5432 of your container to port 5432 of your server. -p <host_port>:<container_port> .So now your postgres is accessible from your public-server-ip:5432

 

I have described the option explanation below :-

--name                   - The container name

-e                          - It refers to Postgres User name and password

-d                          - I will run in Detached mode (background) . if you run it without using it, the docker container will be stopped when you press the Ctrl + C combination or close the terminal screen. This is uncommon because the container should be running in the background.

-p                         - 5432 port is used by default . but it can be changed this according to preference. Local port and container port can be set separately. The first port is for local, and the second for container.

Postgres               - image name

To test: Run the postgres database (command below).

 

Now connect to container using container id :-

docker exec -it 5556f4c78c1f bash

 

 Go inside your container and create a database:

root@05b3a3471f6f:/# psql -U postgres

postgres-# CREATE DATABASE mytest;

postgres-# \q

  Now connect to container using container id :-

 

postgres=# \q

root@5556f4c78c1f:/# hostname -i

172.17.0.2

root@5556f4c78c1f:/#

 

Go to your localhost (where you have some tool or the psql client).

 

How to connect to PostgreSQL in docker container from outside #

First, check the container id:

 

root@worldpgdb:~# docker ps

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS               NAMES

5556f4c78c1f        postgres            "docker-entrypoint.s…"   3 days ago          Up 3 days           5432/tcp            some-postgre

root@worldpgdb:~#

Suppose, if we want to verify the Ubuntu version from the container image the below steps we need to follow:

first, connect to the container using container ID 

Then, check the release version from /etc/*release* as below commands 

 

 

Remove container image from docker by step by step:

The below commands to delete the container which is no longer used

 

Since it is (image id "b97bae343e06") referenced to multiple repositories, we have to use the force option to delete the image permanently which I verified was not working. 

Now the image is successfully deleted with the force option. 

Python Programming for Oracle Database

Connect to Oracle Database by using Python

 

Let us check the python version and the OS bits:-

 

C:\Users\welcome>python

Python 3.6.2 (v3.6.2:5fd33b5, Jul  8 2017, 04:57:36) [MSC v.1900 64 bit (AMD64)] on win32

Type "help", "copyright", "credits" or "license" for more information.

>>> import sys

>>> print(sys.version)

3.6.2 (v3.6.2:5fd33b5, Jul  8 2017, 04:57:36) [MSC v.1900 64 bit (AMD64)]

 

Next install cx_Oracle from the command line:-

C:\Users\welcome>pip install cx_Oracle

Collecting cx_Oracle

  Downloading https://files.pythonhosted.org/packages/8b/d6/8a3d8d257619ea3a30bf6d73c449fc2d37f3b40f4b8cfe896b486a5f46e0/cx_Oracle-8.0.1-cp36-cp36m-win_amd64.whl (201kB)

    100% |████████████████████████████████| 204kB 930kB/s

Installing collected packages: cx-Oracle

Successfully installed cx-Oracle-8.0.1

You are using pip version 9.0.1, however, version 20.2.3 is available.

You should consider upgrading via the 'python -m pip install --upgrade pip' command.

 

C:\Users\welcome>

 

 

Here we are upgrading pip version into 20.2.3

 

C:\Users\welcome>python -m pip install --upgrade pip

Cache entry deserialization failed, entry ignored

Collecting pip

  Cache entry deserialization failed, entry ignored

  Downloading https://files.pythonhosted.org/packages/4e/5f/528232275f6509b1fff703c9280e58951a81abe24640905de621c9f81839/pip-20.2.3-py2.py3-none-any.whl (1.5MB)

    100% |████████████████████████████████| 1.5MB 550kB/s

Installing collected packages: pip

  Found existing installation: pip 9.0.1

    Uninstalling pip-9.0.1:

      Successfully uninstalled pip-9.0.1

Successfully installed pip-20.2.3

 C:\Users\welcome>

 

Connect to Oracle Database:-

import cx_Oracle

con=cx_Oracle.connect('c##ashok369/*******@ASHOK-cloud/ORCL12C')

ver = con.version.split(".")

for v in ver:

    print (v)

    if v == "12":

        print ("Its12")

    else:

        print ("Not12")

con.close()

SELECT Query :-

import cx_Oracle

con=cx_Oracle.connect('c##ashok369/*******@ASHOK-cloud/ORCL12C')

cur=con.cursor()

cur.execute('select * from BIGTAB where rownum<10')

for result in cur:

    print (result)

cur.close()

con.close()

Fetching Data:- 

Fetchone() Function 

fetchone() method to return just a single row as a tuple . When called multiple times then consecutive rows are returned

import cx_Oracle

con=cx_Oracle.connect('c##ashok369/*******@ASHOK-cloud/ORCL12C')

cur=con.cursor()

cur.execute('select * from BIGTAB order by rownum')

row = cur.fetchone()

print (row)

row = cur.fetchone()

print (row)

row = cur.fetchone()

print (row)

row = cur.fetchone()

print (row)

row = cur.fetchone()

print (row)

row = cur.fetchone()

print (row)

row = cur.fetchone()

print (row)

row = cur.fetchone()

print (row)

row = cur.fetchone()

print (row)

row = cur.fetchone()

print (row)

cur.close()

con.close()

SELECT ROWS MANY:-

import cx_Oracle

con=cx_Oracle.connect('c##ashok369/********@ASHOK-cloud/ORCL12C') 

cur=con.cursor()

cur.execute('select * from BIGTAB order by 1')

res=cur.fetchmany(numRows=3)

print(res)

cur.close()

con.close()

C:\Users\welcome>python SELECTMANY.py

[(0, -353815256, datetime.datetime(2018, 12, 19, 12, 30, 31)), (0, -1689077988, datetime.datetime(2019, 11, 3, 0, 42, 33)), (0, 753485672, datetime.datetime(2020, 1, 13, 15, 16, 52))]

C:\Users\welcome>

The fetchmany() method returns a list of tuples . Here the numRows parameter specifcices that three rows should be returned 

SELECT ALL ROWS :-

import cx_Oracle

con=cx_Oracle.connect('c##ashok369/*******@ASHOK-cloud/ORCL12C') 

cur = con.cursor()

cur.execute('select * from BIGTAB order by 1')

res = cur.fetchall()

for r in res:

    print (r)

cur.close()

con.close()

Query performance improvement using Python method:

Here we will improve the query performance by increasing the number of rows returned in each batch from Oracle to the Python program.

Step1:-

import time

import cx_Oracle

 con=cx_Oracle.connect('c##ashok369/********@ASHOK-cloud/ORCL12C')

start = time.time()

cur = con.cursor()

cur.arraysize = 100      /* we set 100 for arraysize which catches the data of 100 records at time to be retuned from the database to a cache in Python. */

cur.execute('select * from bigtab')

res = cur.fetchall()

 

# print res #

elapsed = (time.time() - start)

print (elapsed, "seconds")

 cur.close()

con.close()

We set the arraysize 100 which causes batches of 100 records at a time to be returned from the database to a cache in python. 

this will reduces the number of roundtrips made to the database often reducing network and reducing the number of context switches on the database server.

Now let us change the arraysize to 1000 then we will see below