The applications focused on databases every day take more and more force due to all that the data implies in the development of organizations and users that are in this world, so it is sought that these applications are as complete as possible to that the development of the tasks is not affected by compatibility, performance or use of features..
In this aspect we have two key tools such as PostgreSQL and pgAdmin4, of which we will talk to you in detail in TechnoWikis.
What is PostgreSQL
PostgreSQL has been created as an open source object relational database system which offers us security, reliability and better performance.
PostgreSQL can be executed in all the main current operating systems and is compatible with ACID, in addition to this it offers us special add-ons such as the PostGIS geospatial database extender..
PostgreSQL offers us a wide set of features which allow developers to create applications, protect data integrity or implement fault-tolerant environments regardless of the size of the organization where it is implemented.
characteristics
Some of its most outstanding features are:
- Supports different types of data like integer, numeric, string, boolean, structured like date, time, array, range, of data like JSON, JSONB, XML, of geometry like point, line, circle, polygon and more
- Manage data integrity UNIQUE, NOT NULL, primary keys, foreign keys, or exclusion restrictions
- Features asynchronous, synchronous, and logical replication
- Use the B-tree, multicolumn, expressions or partial indexing methods
- At an advanced level it uses advanced indexing GiST, SP-Gist, KNN Gist, GIN, BRIN, coverage indexes and more.
- Multiple Version Concurrency Control (MVCC)
- Manage table partitioning
- At the security level, it implements the authentication methods GSSAPI, SSPI, LDAP, SCRAM-SHA-256, Certificate and more.
- It has a robust access control system.
- Multi-factor authentication with certificates and an additional method.
- Add column and row level security.
- Support for international characters.
- Manages PL / PGSQL, Perl, Python languages.
- Controls SQL / JSON path expressions.
- Stored procedures and functions
What is pgAdmin4
pgAdmin4 has been built as an open source development and management platform for PostgreSQL.
We can use pgAdmin4 on Linux , Unix, macOS and Windows allowing us to manage PostgreSQL and EDB Advanced Server 9.5 and higher..
characteristics
Among its characteristics we find:
- Support for schema level constraint
- Support for anonymous LDAP binding
- Supports external configuration files
- Compatible for Windows, Linux or macOS
- It can be used to manage PostgreSQL 9.5 and higher or EDB Postgres Advanced Server 9.5 and higher
- Integrate desktop and server mode
- Fast data network for data display / entry
- Add the Schema diff tool which allows us to manage the differences between schemas
- Supports PostgreSQL server-side encodings
- Integrates a procedural language debugger
- Allows you to perform backup, restore, vacuum and scan tasks on demand
- It has a SQL / shell / batch job scheduling agent
- It is possible to create, view or edit SQL objects such as databases, domains, columns, FTS dictionaries, FTS parsers, FTS templates and much more.
Without further ado, let's see how to install PostgreSQL and pgAmdin4 in Ubuntu 20.10.
1. How to install PostgreSQL on Ubuntu 20.10
Step 1
The first thing to do is update the current system packages, for this we execute:
sudo apt update
Step 2
Once the system is updated we will install the following command:
sudo apt install postgresql
Step 3
We enter the letter "S" to confirm the download and installation of PostgreSQL:
Step 4
When this process comes to an end we will see the following:
During the installation process, a new PostgreSQL cluster will be created, which is a collection of databases to be managed by a single server instance, this allows the database to be started. The default data directory is / var / lib / postgresql / 12 / main and the configuration files will be stored in the / etc / postgresql / 12 / main directory.
Step 5
After installation we will execute the following commands:
sudo systemctl is-active postgresql (activates PostgreSQL) sudo systemctl is-enabled postgresql (enables PostgreSQL) sudo systemctl status postgresql (we see the status of PostgreSQL)
Step 6
We are going to validate that the PostgreSQL server can accept client connections, for this we execute:
sudo pg_isready
2. How to create database in PostgreSQL
We are going to create a new database in PostgreSQL, for this we must access the PostgreSQL database shell (psql).
Step 1
First, we switch to the postgres system user account and execute the psql command like this:
sudo su - postgres psql
Step 2
Now we will create the database by executing the following:
CREATE USER "user" WITH PASSWORD 'password'; CREATE DATABASE "database"; GRANT ALL PRIVILEGES ON DATABASE "base" to "user"; \ q
3. How to configure user authentication in PostgreSQL
PostgreSQL makes use of client authentication in order to determine which user accounts can connect to which databases from which computers and this configuration is found in the configuration of the client authentication file, we will see it in the / etc path /postgresql/12/main/pg_hba.conf.
Step 1
We access this with a text editor:
sudo nano /etc/postgresql/12/main/pg_hba.conf
We have seen within the characteristics that PostgreSQL makes use of different types of client authentication methods. In this case, md5 is the most secure and recommended since this authentication method requires the client to enter a password with double MD5 hash for authentication and access.
Step 2
We check that the method is md5 in the configuration file described above:
If we have made any changes, we must apply the changes to PostgreSQL with the following command:
sudo systemctl restart postgresql
4. How to install pgADmin4 on Ubuntu 20.10
It is time to install pgAdmin4, pgAdmin4 is not available in the Ubuntu repositories, so it will be necessary to install it from the APT pgAdmin4 repository.
Step 1
For this we are going to add the public key for the repository and then create the repository configuration file:
curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
Step 2
Then we execute:
sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main"> /etc/apt/sources.list.d/pgadmin4.list && apt update '
Step 3
After this process it will be possible to install pgAmdin4, for this we execute:
sudo apt install pgadmin4
Step 4
We enter the letter "S" to confirm the download and installation, the necessary packages will be installed there, including the Apache2 web server. This allows access to the pgadmin4-web application from a web browser.
Step 5
When the process ends we will see the following:
Step 6
It is necessary to know the IP address of Ubuntu, for this we execute ip add:
Step 7
We go to a browser and enter the following:
http: // Address _IP / pgadmin4
Note
During the installation of pgAdmin4 the entry of the email will be requested for registration:
Step 8
We can leave the email by default and then we will assign a password to it:
Step 9
Now in a browser we enter the given syntax and we must register the email and password created previously:
Step 10
We can select the desired language and click on Login to access:
Step 11
Once we have access we will see the following:
Step 12
There we must connect the PostgreSQL server, for this we click on "Add a New Server" and in the popup window we define:
general
On the General tab:
- Group of servers where it will be hosted
Connection
We go to the tab “Connection and there we define:
- Host name, there we set "localhost"
- We leave the default port (5432) in the "Port" field
- We assign a username and password for the maintenance database, this password is the one created in the PostgreSQL database
Step 13
We click on "Save" and this will open the PostgreSQL management:
Step 14
On the left side we will have access to the database tools, we can navigate through the different available options:
Step 15
It will be possible to go to the group roles for their control and management:
Thanks to PostgreSQL together with pgAdmin4 we will have a centralized administration of all the variables and aspects of the database to be worked on in a special way in terms of security and functionality of these.