PostgreSQL Start & cluster with docker
https://tharangarajapaksha.medium.com/postgresql-start-cluster-with-docker-59f3fdd661e2
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.
$ docker pull postgres$ docker run -d --name node1 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=postgres -p 6551:5432 postgresCheck Whether it is docker container has created as node1$ docker psLogin 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 databasespostgres=# \l// command to change the password for the postgres user:postgres=# ALTER USER postgres PASSWORD 'myPassword';// to exitpostgres=# \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.
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:latesthttps://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-phppgadminBut 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
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
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 inspect172.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
Tidak ada komentar:
Posting Komentar