Kaizen-R

Going SQL: Postgres (2/3)

Last week we got a Container up and running with a Postgres SQL Server in it.

We’re going to need a Database client to check it.

At this point, in theory, we could use some R package (more on that later) to interact with our DBMS container, but instead we’ll go for a Visual client first. Once again, let’s not actually install anything on the server, and rather use another container…

Getting up our PGAdmin client

We’ll use the following as our base reference:

https://www.pgadmin.org/docs/pgadmin4/latest/container_deployment.html

docker pull dpage/pgadmin4

Then let’s run it:

sudo docker run --name pgadmin_test --network=r_analysis_br0 -p 9443:80 -e "PGADMIN_DEFAULT_EMAIL=user@kaizen-r.com" -e "PGADMIN_DEFAULT_PASSWORD=supersecret" -d dpage/pgadmin4

Note: we’ll need to allow incoming TCP packet on our Home-wifi interface onto port 9443, otherwise we won’t get far. And yes, we’re mapping port 80 for now (not great, as I’ll be connecting from my laptop to the server’s deployed container…), but we’ll switch to TLS later.

Good, let’s see if that works! (Note: It takes a couple of minutes for the container to actually start “working” on the port.)

So we did all that just to test whether the Database server was up and running… Let’s add a new server to pgadmin (the default user is postgres, and the pass, we informed that when launching our postgres container…).

Fair enough, it works!

Since we’re here already…

So before we continue, let’s create a new database user. One of the cool things of PgAdmin is that it makes it quite visual (for the less code-inclined, that is):

But another cool thing of PgAdmin, is that for certain stuff, it gives you the code you need to redo the whole thing later. It’s good when you’re a bit rusty on your SQL-KungFu (like me).

Remember when creating the new user, you probably need to set the option of login to yes:

CREATE ROLE default_user WITH
  LOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  INHERIT
  NOREPLICATION
  CONNECTION LIMIT -1
  PASSWORD 'xxxxxx';

We can then create a Database for use by the newly created user: 

Which is equivalent, in SQL, to this:

CREATE DATABASE default_db
    WITH 
    OWNER = default_user
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;

Conclusions

We’re almost ready to use R to connect to our database server. We have a new database, owned by a new user.

Next week, we’ll want to start putting our data in there, and get it back, of course. That way, we’ll reduce the need to keep some of the CSVs out there.

This will only be an example though. But we’ll get to it.

Nico

, , ,
, , , , ,