Page Comparison

Database Container Launch

The container is started using the docker command with the appropriate settings:

docker run 
  --name postgresql-9.6.9
  --volume /opt/postgresql9.6.9/dbdata:/var/lib/postgresql/data 
  -e POSTGRES_PASSWORD='{STRONG_PASSWORD}' 
  -p 5442:5432 
  -d 
postgres:9.6.9

Let's consider each of these parameters:

--name postgresql-9.6.9 - container name. Consists of the database type postgresql and version 9.6.9.

--volume /opt/postgresql9.6.9/dbdata:/var/lib/postgresql/data - container volume. You can read more about this on the corresponding docker manual page. But, at the moment, it is enough for us to know that the volume is used to save our data if the container is restarted or rebuilt. /opt/postgresql9.6.9/dbdata - the folder in the external system where our data will be stored. /var/lib/postgresql/data - the folder inside the container, which we want to save.

-e POSTGRES_PASSWORD='{STRONG_PASSWORD}' - required container variable which set the superuser password for PostgreSQL.

-p 5442:5432 - using this parameter, we set the port that will provide access to the container. 5442 - external port on which this container will be available.

5432 - the port on which the database is accessible inside the container. Thus, we forward requests from an external port to an open port inside the container.

-d - mean “detach”, we run container in background

postgres:9.6.9 - indicate the PostgreSQL image that exists on the docker hub with the corresponding version.

As a result of execution, we will see the following console picture:

Image Modified

Database Console Launch

Logging into the bash console of a docker container is done using the command:

docker exec -it postgresql-9.6.9 bash

The database console is launched using two commands:

su postgres
psql

As a result, we will observe the following picture in the console:

Image Modified

Setting Up The Database and Access Mode

The database is configured using sequential execution of SQL queries.

Let's create a role with which SQL Connector will have access to the database:

CREATE 
  ROLE exportuser 
  PASSWORD '{STRONG_USER_PASSWORD}' 
  NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

{STRONG_USER_PASSWORD} - the password that we will later use to export data by SQL Connector. As we can see, this role will not be a superuser and will not be able to create databases or roles. This is a necessity for security reasons.

Let's create a database that will be used to export data:

CREATE DATABASE exportdb 
  WITH 
    ENCODING 'UNICODE' 
    LC_COLLATE 'C' 
    LC_CTYPE 'C' 
    TEMPLATE template0;

Let's connect to the database we created and create a data schema that SQL Connector will use:

\connect exportdb
CREATE SCHEMA exportschema;

We will give the role that we created earlier as development access to this scheme.

GRANT CREATE, USAGE ON SCHEMA exportschema TO exportuser;

As a result, we will see the following picture in the console.

Image Modified

You can exit the database by writing with the command \q and exit from console by exit.

Let's check if we have configured our database correctly and try to connect to it. Go to SQL Connector add-on and on the step of adding a database, click “Validate connection”.

Image Modified