[TBD]PostgreSQL- An Object relational DBMS

Installation

Install as Docker container

PostgreSQL installation as docker container

root@master1:~/personal/triton# docker run --name some-postgres -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword -d postgres

e8c9fbf064fcf51a5672cd88a622e353a0665b35b4b0286c7e227cf9381dfa1a

root@master1:~/personal/triton# docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS

PORTS NAMES

e8c9fbf064fc postgres "docker-entrypoint.sh" 3 seconds ago Up 2 se

conds 0.0.0.0:5432->5432/tcp some-postgres

2b512abd7a97

root@master1:~/personal/triton# netstat -atpun | grep 5432

tcp6 0 0 :::5432 :::* LISTEN 20976/docker-proxy

root@master1:~/personal/triton# docker run -it --rm --link some-postgres:postgres postgres psql -h postgres -U postgres

Password for user postgres:mysecretpassword

psql: FATAL: password authentication failed for user "postgres"

root@master1:~/personal/triton# docker run -it --rm --link some-postgres:postgres postgres psql -h postgres -U postgres

Password for user postgres:

psql (10.1)

Type "help" for help.

postgres=# \d+

Did not find any relations.

Useful link: https://store.docker.com/images/postgres

Example code

Example code

root@6b774e439f98:/# cat test.py

#!/usr/bin/python2.4

#

# Small script to show PostgreSQL and Pyscopg together

#

import psycopg2

try:

conn = psycopg2.connect("dbname='postgres' user='mpsroot' host='172.17.0.10' password='password'")

except:

print "I am unable to connect to the database"

conn.set_isolation_level(0)

cur = conn.cursor()

try:

cur.execute("""SELECT datname from pg_database""")

#cur.execute("""DROP DATABASE foo_test""")

except:

print "I can't list our test database!"

rows = cur.fetchall()

print "\nShow me the databases:\n"

for row in rows:

print " ", row[0]

try:

cur.execute("""SELECT * FROM kubernetes_param""")

except:

print "I can't show our table!"

rows = cur.fetchall()

print "\nRows\n"

for row in rows:

print " ", row[1]

try:

cur.execute("""insert into kubernetes_param(kubeconfig) values ('abcde')""")

except:

print "I can't show our table!"

try:

cur.execute("""insert into ns_json(data) values ('{}')""")

except:

print "I can't insert into ns_json table!"

root@6b774e439f98:/#

Tips

Method to convert query response to json

Use array_to_json feature

Return query result as json object

postgres=# \d+ triton_config

Table "public.triton_config"

Column | Type | Collation | Nullable | Default | Storage | Stats target | Description

-----------------------+-------------------+-----------+----------+---------+----------+--------------+-------------

id | character varying | | | | extended | |

netmode | character varying | | | | extended | |

framework_healthcheck | character(1) | | | | extended | |

loopback_disabled | character(1) | | | | extended | |

active_framework | character varying | | | | extended | |

postgres=# SELECT array_to_json(array_agg(triton_config)) FROM triton_config;

array_to_json

--------------------------------------------------------------------------------------------------------------------------------------------------

[{"id":"3e0de7e5-c5da-48f9-a08d-1833fb17887c","netmode":"BRIDGE","framework_healthcheck":"t","loopback_disabled":"t","active_framework":"kube"}]

(1 row)

postgres=#

Useful link: https://stackoverflow.com/questions/24006291/postgresql-return-result-set-as-json-array/24006432

Method to auto-generate ID

Use serial field type

Method to auto-generate ID

mpsdb=> create table users (

mpsdb(> id serial primary key,

mpsdb(> name varchar(100) not null unique -- ?

mpsdb(> );

CREATE TABLE

mpsdb=> \d+ users

Table "Owner.users"

Column | Type | Collation | Nullable | Default | Storage | Stats target | Descriptio

n

--------+------------------------+-----------+----------+-----------------------------------+----------+--------------+-----------

--

id | integer | | not null | nextval('users_id_seq'::regclass) | plain | |

name | character varying(100) | | not null | | extended | |

Indexes:

"users_pkey" PRIMARY KEY, btree (id)

"users_name_key" UNIQUE CONSTRAINT, btree (name)

Useful link: https://stackoverflow.com/questions/35763115/generate-auto-id-in-postgresql

Reference

http://stackoverflow.com/questions/110927/would-you-recommend-postgresql-over-mysql

https://en.wikipedia.org/wiki/PostgreSQL

https://en.wikipedia.org/wiki/Object-relational_database

http://www.postgresql.org/docs/9.1/static/explicit-locking.html

https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems

https://www.unt.edu/benchmarks/archives/2001/june01/postg.htm

https://www.quora.com/What-are-pros-and-cons-of-PostgreSQL-and-MySQL

http://www.postgresql.org/docs/9.2/static/tutorial-inheritance.html

https://www.compose.io/articles/is-postgresql-your-next-json-database/

https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling

https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling

https://stackoverflow.com/questions/18068901/python-psycopg2-not-inserting-into-postgresql-table