Skip to content

Docker container for a Postgres DB

Jump to bottom
authorjapps edited this page Sep 4, 2019 · 10 revisions 
 
After following the instructions from this page you should be able to successfully bring up a dockerized 
Postgres DB server and run some tests.

Table Of Content

How to configure/create a Docker compose file?

See here the docker-compose file

How to bring down the container using the compose file ?

  • $ docker-compose -f pg_compose.yml down

If the containers are up, the above command will bring them down and if they are not up, then it does no harm.

How to bring up the container using the compose file ?

  • Clone this repo

  • Go to the zerocode-docker-factory/compose dir in a terminal window

  • Then run

    • $ docker-compose -f pg_compose.yml up -d

    or

    • $ docker-compose -f pg_compose.yml up

This brings up both Postgres DB server and Postgres Admin server

How to check the status of PG DB and the Admin?

$ docker ps
$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                     NAMES
8796ed628983        adminer             "entrypoint.sh docke…"   25 seconds ago      Up 23 seconds       0.0.0.0:8080->8080/tcp    compose_adminer_1
09db1b3c982f        postgres:9.3        "docker-entrypoint.s…"   25 seconds ago      Up 23 seconds       0.0.0.0:35432->5432/tcp   compose_db_1
$ 

Note-
See under the "NAMES" column(right hand ride) above, for `compose_db_1` as container name
and the "PORTS" column to see which port the container is listening to for the external connection.
Here the port is `35432` for external connections.

How to verify the port has been exposed outside docker container ?

  • To make sure Postgres is running and it has exposed the port to external connections, user command

    • nc -vz localhost 

$ nc -vz localhost 35432   
found 0 associations
found 1 connections:
     1: flags=82<CONNECTED,PREFERRED>
        outif lo0
        src ::1 port 52494
        dst ::1 port 35432
        rank info not available
        TCP aux info available

Connection to localhost port 35432 [tcp/*] succeeded!

Note- If a port is not exposed, you get the following message.
--------------------------------------------------------------
$ nc -vz localhost 35433  
nc: connectx to localhost port 35433 (tcp) failed: Connection refused
nc: connectx to localhost port 35433 (tcp) failed: Connection refused
$

Now, how to connect to the server using terminal command/console?

(Or go to Do via UI Click-Click section to create object via the admin UI)

root@94970ab3bcdc:/# su - postgres
postgres@94970ab3bcdc:~$ whoami
postgres
postgres@94970ab3bcdc:~$ psql
psql (9.3.25)
Type "help" for help.

postgres=# 

postgres=# \list
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

postgres=#

Then, how to connect to a database and create a table e.g. employess?

postgres=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# 

postgres=# \dt+
No relations found. <----- No table/objects exits at this points in the db

postgres=# CREATE TABLE employees ( id bigint NOT NULL, name character varying(10) NOT NULL);
CREATE TABLE
postgres=# \dt+
                       List of relations
 Schema |   Name    | Type  |  Owner   |  Size   | Description 
--------+-----------+-------+----------+---------+-------------
 public | employees | table | postgres | 0 bytes | 
(1 row)

postgres=#

How to insert some rows into the employee table?

postgres=# insert into employees(id, name) values(1, 'Jack');
INSERT 0 1

postgres=# insert into employees(id, name) values(2, 'Pulsar');
INSERT 0 1
postgres=# select * from employees;
 id |  name  
----+--------
  1 | Jack
  2 | Pulsar
(2 rows)

How exit the container from Postgres shell?

postgres-# \q    <------ Comes out from PSQL
postgres@94970ab3bcdc:~$ exit  <------ Comes out from 'postgres' user
logout
root@94970ab3bcdc:/# exit  <------- Comes out from docker container to local OS prompt
exit
$

Or

how to connect to the server using the Admin UI?

Note : This is only a optional step i.e. another way of creating DB objects (i.e. without using the command line options explained above)

  • Go to http://localhost:8080 in a browser
  • Choose Postgres SQL from the dropdown
  • Enter user/password as postgres/example -> Click Login button
postgres_admin_ui
  • After logged in, Choose postgres from the list of DBs listed in the table
logged_in
  • Then create the employees table and insert some rows(copy the SQL queries from the command section above)
    • You can use various options in the admin panel including executing queries in an SQL window here.

Now, how to run the Zerocode tests?

In your test/resources/application_host.properties upddate the host/port/db to below

## ---------------------------
##  DB Host configs - Postgres
## ---------------------------
db_host_url=jdbc:postgresql://localhost:35432/postgres
db_username=postgres
db_password=example

and run the DB tests from here.

HelloWorld tests are here

How to bring down the containers once you are done with your testing?

Already explained in the above section, see here

Clone this wiki locally