Introducing you in PostgreSQL

I realize that I always say that talk about technology with tons of information in internet tends to be useless.

Talking about postgres it is imposible to not read more and more about it, and avoid my imagination to turn on. I think, that is reason to search about technical concepts, this way our creativity going to adopt a more dynamic workflow. Particularly, this technology engage me, and when I find something interesting to read, it is difficult to keep me focused in writing.

Arquitecture

PostgreSQL basically follow a general syatem of Cluster > Database > tables. It is obvious there is other entities like Indexes, Functions or Triggers, but they are part of the database’s Schema.

A Role or a User?

In this section, I am going to differenciate two basic concepts, roles and user. They both have obvious distinctions based on their definitions. It’s mandatory to know the reasons, which ones all its behaviors are based on.

Previlieges

A User is a more sophisticated role that play with privilieges since its begining, when a Cluster is created. He even can create varied roles, with his same characteristics but the unique sever’s administrator going to be its creator.

Later the same owner can create specific roles for particular functions inside a database. It is important to be organized for a work-team, and even for an individual developer in his own project.

Database: setup of the cluster

Since the bin directory, where postgres was installed, we have diverse commands. What matters is to use those correctly in our basic development. Firstly, it is posible to create an area or space for all our data, what is called cluster.

Two of those important commands mentioned before are, for example initdb and pg_ctl. With its corresponding flags and arguments, are very simple for our specific goals, that is to turn on our database in its work place.

Initdb has the purpose to create a folder that contains all the data of the database, is an environment. Being more asertive, it is actually a database cluster, which is a collection of databases managed by a single server. Automatically, it generates template1 and postgres databases and each following database created will inherit from the first one. We can act as follows…

initdb -D ../mydata    — username=newuser — auth=trust

A folder mydata going to be generated in the same level as bin folder is, that is the reason I wrote ../. Then our information will store inside of that folder.

Also, pg_ctl initiates the server, we have to use the username specified in first folder, mydata in this case. It will be working in a second plane. This way…

pg_ctl start -D ../mydata

As you can see, it’s not needed to be outside if bin directory. However, we are not coding SQL statements. Nextly, opening other command-line window, we write…

psql -U newuser

Psql command going to search directly the port for default, the 5432 one, looking for something running there. There is when it find our server, and we are able to write SQL statements.

Creation and organization

The database clusters have certain considerations that you should be aware of. It utilize shared catalog tables, that are tables enabled along the entire cluster, not exclusive for an specific database. There’s also varied catalog systems whose field to work is the entire cluster.

How I mentioned before, autonatically are created two databases, template1 and postgres. They both can be used to create others databases, writing…

CREATE DATABASE databaseforproves;

And there to work in a particular topic, more focused.

Upgrading PostgreSQL

Postgres manage a version names system that allows us how upgrading works. There is a Mayor (first number) and a Minor (the last number) versions. PostgreSQL data store is compatible always that Mayor version is the same, the minor one does not affect at the upgrades.

If we figure out PostgreSQL 10.1, it is compatible with PostgreSQL 10.2 or 10.3. In upgrades all is about to change the executable, because does not going to suffer a change.

Some versions were named as 9.5.3 or 9.5.1. In those cases the mayor version is 9.5 and the rest is the minor one.

Security

In PostgreSQL exists varied way to mantain secured our data, even if it is the dummy one. Since hash just a single password to include an entire column.

There is always some norms and technologic burocracy, as I called those certificates that cross all the internet for a more secure connections between two computers. Here, SSL and TLS certificates comes in, and SSH backup them. Touching the TCP/IP comunications layer, let’s explain shortly about it is face it.

PostgreSQL has inside its file system certain files bearing with the great responsability to contain the server configuration.

postgresql.conf with its ssl variable turning to on allow the server to directly enable thhe SSL certification. Meanwhile Postgres choose to read the openssl.cnf file native of OpenSSL, which one has to be installed in both sever’s computer and client’s computer. It just lack to establish the files that contains server certificates and the keys.

The SSH server is capable to encrypt the network connections. Being posible to implement a secure tunnel to the remote server, even with non-SSL clients.

Backups

The database server provides us differents methods to mantain our data always emdorsed.

Through the SQL language, more specificaly utilizing pg_dump to dump databases and pg_dumpall to make the same with clusters. What those commands does is, to create files that will contain our data. Later, we will be able to restore a cluster or a database with that file we have created.

It is posible to do a File System backup, being intuitive that is based on copying those responsible files to store data. The documsntation names disavantages that makes this option impractical. Due to pg_xact contains all the transactions, it is imposible to restore a particular entity, like a table, it would render the entire database. Almost, it tends to be improductive because the database should be shut down to make a backup.

The PostgreSQL plays with write ahead log (WAL) that “records every change made to the database’s data files”. Combining file system backup and WAL, it is posible to “bring the system to a current state”. Official documentation explains completely of all the benefits that third alternative offers.

All that explanation was actually very short. I am reading documentation, and it is difficult to me to not repeat the same words writers utilize there. Maybe, that is why I am still learning to erite in English. Anyways, this gathered of information about postgres works for hnderstanding shortly what is for me the most engaging of this software piece.