PostgreSQL Start & cluster with docker

image from :https://cmobiler.com/article/how-to-import-postgreSQL-sql-file-in-docker-container

PostgreSQL is becoming very popular among developers due to its advance features respect to the MySQL which is the most popular opensource .

• This supports the locking mechanism.
• It has high availability.
• It is free and open source software.
• This is ACID compliant.
• It has the capacity for fault tolerance.
• It also supports image, video, audio storage and also supports graphical data.
• It requires very low maintenance.
• It supports Multi-version concurrency control (MVCC).
• Recovery is high.
• It has user defined data-types.
• Table inheritance.
• It runs on all operating systems.

SQL:

• Windowing functions and analytics (OVER-clause)
• Ordered sets (WITHIN GROUP)
• Hypothetical aggregates (WITHIN GROUP)
• Recursive SQL (WITH RECURSIVE)
• Partial aggregates (FILTER-clause)
• Table sampling

Performance:

• Parallel queries
• Advanced locking mechanisms
• Many different types of indexes (btree, gist, gin, hash. sp-gist, brin, bloom, etc.)
• Advanced cost-based query optimization
• Tablespaces
• Partitioned tables
• Synchronous and asynchronous COMMIT

Replication:

• Synchronous and asynchronous replication
• Quorum commits
• Logical and physical replication
• Partial replication (“CREATE PUBLICATION”)
• Point-In-Time-Recovery
• High-Availability and auto-failover (with “Patroni”)

Security:

• Users, roles, etc.
• Full support for SSL
• Full database encryption (with “Cybertec PostgreSQL_fde”)
• Support for single-sign-on (ActiveDirectory, LDAP, RADIUS, etc.)

Stored procedures:

• Support for stored procedures in various languages
• Support for custom aggregates
• Professional triggers

Creating and login to PostgreSQL with docker.

postgres - Docker Hub

Setup PostgreSQL on Windows with Docker

$ docker pull postgres
$ docker run -d --name node1 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=postgres -p 6551:5432 postgres
Check Whether it is docker container has created as node1
$ docker ps
Login to the container and run queries
$ docker exec -ti node1 bash
$ su postgres
$ psql
psql (11.2 (Debian 11.2–1.pgdg90+1))
Type “help” for help.
postgres=#
// to list all databases
postgres=# \l
// command to change the password for the postgres user:
postgres=# ALTER USER postgres PASSWORD 'myPassword';
// to exit
postgres=# \q

Check whether your PostgreSQL instance has created.

$ docker ps

Connect with the PostgreSQL from Client program.

You can connect with this PostgreSQL server with DBeaver Database management system. Database connection details as follows according to above docker created command. Further it will be required JDBC driver file which you can download from it bellow url.

host : localhost

user : postgres

password : postgres

database : postgres

Download JDBC driver for PostgreSQL.

Download

Using DBeaver default drivers for PostgreSQL which can be download when the connection creation can be used for the successful connection creation with PostgreSQL server.

Phppgadmin Installation

Phppgadmin is another web based PostgreSQL client and it can be installed using docker too. Following URL describe the way to do it.

https://hub.docker.com/r/dockage/phppgadmin/

docker run --name=phppgadmin -d  --publish=88:80 -e PHP_PG_ADMIN_SERVER_HOST=127.0.0.1 -e PHP_PG_ADMIN_SERVER_PORT=6551 -e PHP_PG_ADMIN_MIN_PASSWORD_LENGTH=0  dockage/phppgadmin:latest

https://hub.docker.com/r/zhajor/docker-phppgadmin

docker run -d -p 12345:80 --link node1:node1 -e  "DB_HOST=node1" -e "DB_PORT=6551"  --name=postg  zhajor/docker-phppgadmin

But i couldn’t connect with locally deployed docker PostgreSQL servers. Anyway web phppgadmin web interface was accessible from both of above methods.

Creating a PostgreSQL cluster

How to Deploy PostgreSQL to a Docker Container Using ClusterControl

Above web site describe how to create a PostgreSQL . Please check bellow command exicuted to re create a cluster in my server.

// Creatinga a server
$ docker pull postgres
// Creating node 1
$docker run --name pg_node_1 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=postgres -p 6551:5432 -d postgres
// creating node 2
docker run --name pg_node_2 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=postgres -p 6552:5432 -d postgres
// Check your containers 
$ docker ps
// Access the container
Ex : $ docker exec -ti [db-container] bash
$ docker exec -ti pg_node_1 bash

login to container and exicute commands

Enable SSH in containers

// login
$ docker exec -ti pg_node_1 bash
// update the apt repository
$ apt-get update
// Install the openssh client
$ apt-get install -y openssh-server openssh-client
// Changing the permission for ssh
$ sed -i 's|^PermitRootLogin.*|PermitRootLogin yes|g' /etc/ssh/sshd_config
$ sed -i 's|^#PermitRootLogin.*|PermitRootLogin yes|g' /etc/ssh/sshd_config
//start the service
$ service ssh start
// change the password
$ passwd
// you can give new password  "postgres"
Above same steps should be followed to pg_node_2 also.

Start the ClusterControl container (if it’s not started) and forward port 80 on the container to port 5000 on the host:

$ docker run -d --name clustercontrol -p 5000:80 severalnines/clustercontrol
// If you have already started containers but down you may see them by
$ docker ps -a

Once done you have to login by web interface.

http://[Docker_Host]:5000/clustercontrol

Then you have to fill a some data and just submit the form.

The last step is setting up the passwordless SSH to all database containers. For this, we need to know the IP Address for each database node. To know it, we can run the following command for each node:

$ docker inspect [db-container] |grep IPAddress
"IPAddress": "172.17.0.3",
// for windows use following command and search for IPaddress
$ docker inspect
172.17.0.3

Then you have to Copy the SSH key to all database containers:

// loging to clustercontrol
$ docker exec -it clustercontrol bash
// copy the keys
$ ssh-copy-id 172.17.0.3
$ ssh-copy-id 172.17.0.4

You have to select PostgreSQL & TimescaleDB Tab and configure SSH data and database data as follows.

Once it is imported the node will be added to the cluster.