How to install PostgreSQL on Ubuntu 18.04 LTS Bionic Beaver and derivatives?

PostgreSQL is an object-oriented relational database management system, powerful, advanced and high-performance, PostgreSQL is free and open source released under the PostgreSQL license, similar to BSD or MIT.

Uses and improves the SQL language, along with a large number of features for secure data storage and management. It is efficient, reliable, and scalable for handling high volumes and creating enterprise-grade and fault-tolerant environments, while ensuring high data integrity.

PostgreSQL it is also highly extensible with features such as indexes, they come with APIs so you can develop your own solutions to solve your data storage problems.

Like many other open source projects, PostgreSQL development is not managed by one company or person, but is run by a community of developers who work in a selfless, altruistic, free or supported by commercial organizations.

This community is called the PGDG (PostgreSQL Global Development Group).

PostgreSQL installation on Ubuntu 18.04 LTS and derivatives

In order to install this tool on our systems, we must create a file in /etc/apt/sources.list.d/pgdg.list which stores the repository configuration.

We are going to open a terminal with Ctrl + Alt + T and we are going to execute in it:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

sudo apt install wget ca-certificates

We import the public key

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add –

Y we proceed to install the application in our system with:

sudo apt update

sudo apt install postgresql-10 pgadmin4

And ready with it, we will already have PostgreSQL installed on our system.

The application service should be executed automatically after having installed it, we can verify this simply by executing the following command:

sudo systemctl status postgresql.service

How to use PostgreSQL on Ubuntu 18.04 LTS and derivatives?

In PostgreSQL, client authentication is controlled by the configuration file /etc/postgresql/10/main/pg_hba.conf.

The default authentication method is "peer" for the database administrator, which means that it receives the user name of the client operating system from the system and checks if it matches the requested database user name to allow access, for local connections.

This configuration file can be edited according to your needs.

Once everything is configured, the system account can be accessed with the following command:

sudo -i -u postgres

psql

postgres=#

It can also be accessed with this other command directly, without having to first access the postgres account, for this we only have to execute:

sudo -i -u postgres psql

To exit we only execute:

postgres=# \q

En PostgreSQL, a role and permission system is usedWhere roles are global objects that can access all cluster databases (with the appropriate privileges).

The roles are completely separate from the users at the operating system level, although it is convenient to maintain a correspondence between them.

In order to initialize a database system, each fresh installation always contains a predefined role.

How to create a user in PostgreSQL?

For the creation of a new role in the database we only have to execute the following command, in which we only have to replace "user" with the name we want to assign:

postgres=# CREATE ROLE usuario;

Now if we want to add the login attribute to the user's role, we only have to add the following:

postgres=#CREATE ROLE usuario LOGIN;

Or it can also be created as follows

postgres=#CREATE USER usuario;           

By creating this, we must assign a password which we can ensure an authentication method thereby providing an encrypted password when connecting to the database.

We can do this by typing the following command:

postgres=#CREATE ROLE usuario PASSWORD 'contraseña'

Finally you can find various tutorials and help in many forums and websites where they share content on the use and administration of PostgreSQL.