What is PostgreSQL and how do we do a full installation
Certifications:
In this article we will look at another Relational Database Management System (RDBMS). PostgreSQL. PostgreSQL is the most advanced open source relational database. As open-source it does not require the purchase of a license and it is free.
THE PostgreSQL supports the features ACID like the rest RDBMS with the data being related in tables but also NoSQL (non-relational) with the data being in files with a structure like the JSON.
In the article we will see step by step how to complete the installation of PostgreSQL on Linux as well as the pgAdmin web tool to manage it.
Installing PostgreSQL
Installation steps in Red Hat Linux they are quite simple. We should download the repository and run the install.
Also by running initdb it will be created Database Cluster so we can manage a collection of bases on a server instance.
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm sudo yum install -y postgresql11-server sudo /usr/pgsql-11/bin/postgresql-11-setup initdb
We activate the service so that it starts by itself and we start it.
sudo systemctl enable postgresql-11 sudo systemctl start postgresql-11
Somewhere here the basic installation is completed. To connect to the database for the first time we will make use of the postgres user that is created on the database and operating system.
sudo -u postgres psql
With \l we can see the bases that exist at this moment.
We can easily create a new user with a password and define it superuser so that he has full rights.
create user stratos with password 'kwdikos'; grant all privileges on database postgres to stratos; alter user stratos with superuser;
With \du we will see the available users and the rights they have.
While we created the user, if we try to connect, it won't let us.
This happens because PostgreSQL's default behavior for local connections allows the connection peer which means that in order for a user to connect there must also be a user of the operating system connected to it.
To change this we need to find the pg_hba.conf. Unfortunately, for security reasons, the user who only has access to find this file in the operating system is the postgres user. With the following command we find where the file is so that we can edit it.
sudo -u postgres psql -c 'SHOW hba_file' sudo vi /var/lib/pgsql/11/data/pg_hba.conf
By changing it method from peer to md5 now we will be able to log in using a password. Also if on the host let's change her ip from 127.0.0.1/32 to 0.0.0.0/0 we will be able to connect from another computer wherever it is.
To do this, however, we will have to look for another file postgresql.conf.
sudo -u postgres psql -c 'SHOW config_file' sudo vi /var/lib/pgsql/11/data/postgresql.conf
In this file we remove the comment (#) and put listen_addresses = '0.0.0.0' and port = 5432.
A reboot is required for these settings to work.
sudo systemctl restart postgresql-11
Doing netstat we should see the ip 0.0.0.0:5432.
netstat -nlp |grep 5432
We don't forget that for remote access we need to open the door and at firewall.
sudo firewall-cmd --zone=public --add-port=5432/tcp --permanent sudo firewall-cmd --reload
The installation of the pgAdmin administration tool
After the previous steps have completed the installation, we can install the PostgreSQL management tool pgAdmin.
The steps are very simple and we follow them to install the web version that uses apache.
During execution it will ask us for an email and a code that will be the ones we will connect to the platform.
sudo rpm -i https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-1-1.noarch.rpm sudo yum install pgadmin4-web sudo /usr/pgadmin4/bin/setup-web.sh
Don't forget to open the firewall on port 80.
sudo firewall-cmd --zone=public --add-port=80/tcp --permanent sudo firewall-cmd --reload
Now all we have to do is go to the localhost/pgadmin4 page internally or to the domain we have externally.
After logging in to add servers, use the user we created before with a password. For it to work we must have put it on pg_hba.conf as a method md5 so we can log in with a password.
