{"id":2366,"date":"2024-09-02T07:00:00","date_gmt":"2024-09-02T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=2366"},"modified":"2024-09-01T13:47:22","modified_gmt":"2024-09-01T10:47:22","slug":"ti-einai-i-postgresql-kai-pos-kanoyme-mia-pliris","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/ti-einai-i-postgresql-kai-pos-kanoyme-mia-pliris\/","title":{"rendered":"What is PostgreSQL and how do we do a full installation"},"content":{"rendered":"

In this article we will look at another Relational Database Management System (RDBMS) the PostgreSQL<\/strong>PostgreSQL is the most advanced open source<\/strong> relational database. As open-source it does not require the purchase of a license and is free<\/strong>. <\/p>\n\n\n\n

THE PostgreSQL <\/strong>supports the features ACID <\/a>like the rest RDBMS <\/strong>with the data being correlated in tables but also NoSQL <\/strong>(non-relational) with the data being in files with a structure like JSON<\/a>.<\/p>\n\n\n\n

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. <\/p>\n\n\n\n

Installing PostgreSQL<\/h2>\n\n\n\n

The installation steps in Red Hat Linux<\/strong> It's pretty simple. We'll need to download the repository and run the install.<\/p>\n\n\n\n

Also, running initdb will create Database Cluster<\/a> so that we can manage a collection of databases on a server instance.<\/p>\n\n\n\n

sudo yum install -y https:\/\/download.postgresql.org\/pub\/repos\/yum\/reporpms\/EL-7-x86_64\/pgdg-redhat-repo-latest.noarch.rpm\n\nsudo yum install -y postgresql11-server\n\nsudo \/usr\/pgsql-11\/bin\/postgresql-11-setup initdb<\/pre>\n\n\n\n
We enable the service so that it starts automatically and start it.<\/p>\n\n\n\n
sudo systemctl enable postgresql-11\n\nsudo systemctl start postgresql-11\n<\/pre>\n\n\n\n
The basic installation is now complete. To connect to the database for the first time, we will use the postgres user that is created in the database and in the operating system.<\/p>\n\n\n\n
sudo -u postgres psql <\/pre>\n\n\n\n
With  \\l<\/strong> we can see the bases that exist at the moment.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We can easily create a new user with a password and set it superuser <\/strong>so that he has full rights.<\/p>\n\n\n\n
create user stratos with password 'kwdikos';\n\ngrant all privileges on database postgres to stratos;\n\nalter user stratos with superuser;<\/pre>\n\n\n\n
With \\du<\/strong> We will see the available users and the rights they have.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Although we created the user, if we try to log in, it won't let us.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
This is because PostgreSQL's default behavior for local connections allows the connection peer <\/strong>which means that for a user to connect, there must also be a user of the operating system connecting to them.<\/p>\n\n\n\n
To change this we need to find the pg_hba.conf<\/strong>Unfortunately, for security reasons, the only user who has access to find this file in the operating system is the postgres user. With the following command, we find where the file is located so that we can edit it.<\/p>\n\n\n\n
sudo -u postgres psql -c 'SHOW hba_file'\n\nsudo vi \/var\/lib\/pgsql\/11\/data\/pg_hba.conf\n<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Changing it method <\/strong>from peer to md5 <\/strong>now we will be able to connect using a password. Also if on the host change <\/strong>her ip<\/strong> from 127.0.0.1\/32 to 0.0.0.0\/0 we will be able to connect from another computer wherever it is located.<\/p>\n\n\n\n
To do this, however, we will need to look for another file, postgresql.conf<\/strong>.<\/p>\n\n\n\n
sudo -u postgres psql -c 'SHOW config_file'\n\n\nsudo vi  \/var\/lib\/pgsql\/11\/data\/postgresql.conf<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
In this file we remove the comment (#) and set listen_addresses = '0.0.0.0' and port = 5432.<\/p>\n\n\n\n
A restart is required for these settings to work.<\/p>\n\n\n\n
sudo systemctl restart postgresql-11<\/pre>\n\n\n\n
By doing netstat we should see the ip 0.0.0.0:5432.<\/p>\n\n\n\n
netstat -nlp |grep 5432<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We do not forget that for remote access we need to open the door and firewall<\/strong>.<\/p>\n\n\n\n
sudo firewall-cmd --zone=public --add-port=5432\/tcp --permanent\nsudo firewall-cmd --reload<\/pre>\n\n\n\n
Installing the pgAdmin management tool<\/h2>\n\n\n\n
After completing the installation in the previous steps, we can install the PostgreSQL administration tool. pgAdmin<\/strong>.<\/p>\n\n\n\n
The steps are very simple and we follow them to install the web version that uses apache.<\/p>\n\n\n\n
During execution, it will ask us for an email and a password, which will be what we will use to log in to the platform.<\/p>\n\n\n\n
sudo rpm -i https:\/\/ftp.postgresql.org\/pub\/pgadmin\/pgadmin4\/yum\/pgadmin4-redhat-repo-1-1.noarch.rpm\n\nsudo yum install pgadmin4-web\n\nsudo \/usr\/pgadmin4\/bin\/setup-web.sh<\/pre>\n\n\n\n
We don't forget to open the firewall on port 80.<\/p>\n\n\n\n
sudo firewall-cmd --zone=public --add-port=80\/tcp --permanent\nsudo firewall-cmd --reload<\/pre>\n\n\n\n
Now all we have to do is go to the localhost\/pgadmin4 page internally or to the domain we have externally.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
After logging in to add servers, we use the user we created before with a password. For it to work, we must have set pg_hba.conf <\/strong> as a method md5<\/strong> so we can connect with a code.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Sources:<\/h2>\n\n\n\n