Postgres Installation

Postgres-Docker Installation:

docker pull postgres

mkdir -p $HOME/docker/volumes/postgres

docker run --name pgdocker -e POSTGRES_PASSWORD=postgres -d -p 5432:5432 -v $HOME/docker/volumes/postgres:/var/lib/postgresql/data  postgres

docker exec -it pgdocker bash

container-root# psql -h localhost -p 5432 -U postgres

port: (got from network utility)
10.4.223.6

# Inside DB
CREATE DATABASE raja;
CREATE USER raja WITH PASSWORD 'rajapass';
GRANT ALL PRIVILEGES ON DATABASE raja TO raja;

# Use TablePlus and Dump these SQLs
CREATE TABLE CITY(
 ID serial PRIMARY KEY,
 NAME VARCHAR (50) UNIQUE NOT NULL,
 STATE VARCHAR (50),
 COUNTRY VARCHAR (355)
);

INSERT INTO CITY (NAME, STATE, COUNTRY) VALUES ('Madurai', 'Tamilnadu', 'India');
INSERT INTO CITY (NAME, STATE, COUNTRY) VALUES ('Toronto', 'Ontario', 'Canada');

SELECT * FROM CITY;

Test in Java code:
package org.rj;

import java.sql.*; 

public class Postgres11DockerTest {

	private final static String DB_URL = "jdbc:postgresql://10.4.223.6:5432/raja";
	private final static String USER = "raja";
	private final static String PASS = "rajapass";

	public static void main(String[] args) {
		Connection conn = null;
		try {
			Class.forName("org.postgresql.Driver");
			System.out.println("Connecting to database...");
			conn = DriverManager.getConnection(DB_URL, USER, PASS);
			
			System.out.println("Connected");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// ignore
				}
			}
		}
	}
}

--- Python

import psycopg2 

def startpy():

    try:
        connection = psycopg2.connect(user = "raja",
                                    password = "rajapass",
                                    host = "192.168.86.109",
                                    port = "5433",
                                    database = "raja")

        cursor = connection.cursor()
        
        # Print PostgreSQL Connection properties
        print ( connection.get_dsn_parameters(),"\n")

        cur = connection.cursor()
        cur.execute("SELECT * FROM CITY")
    
        rows = cur.fetchall()
        
        print('rows : '+str(len(rows)))
        
        if(len(rows) <= 0):
            print('No Data available')
 
        for row in rows:
            print(row)     
        
        
    except (Exception, psycopg2.Error) as error :
        print ("Error while connecting to PostgreSQL", error)
    finally:
        pass

        '''
        #closing database connection.
            if(connection):
                cursor.close()
                connection.close()
                #print("PostgreSQL connection is closed")
        '''


if __name__ == '__main__':
    startpy()

ref:
https://hackernoon.com/dont-install-postgres-docker-pull-postgres-bee20e200198
http://www.postgresqltutorial.com/postgresql-create-table/
https://medium.com/coding-blocks/creating-user-database-and-adding-access-on-postgresql-8bfcd2f4a91e
https://medium.com/@lvthillo/connect-from-local-machine-to-postgresql-docker-container-f785f00461a7