+3 votes
2.6k views
Install PostgreSQL and pgAdmin4 on Ubuntu 20.10

in Linux / Unix by (552k points)
reopened | 2.6k views

1 Answer

+4 votes
Best answer

1. How to install PostgreSQL on Ubuntu 20.10
2. How to create database in PostgreSQL
3. How to configure user authentication in PostgreSQL
4. How to install pgADmin4 on Ubuntu 20.10

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
  • Write-Ahead Record (WAL)
  • 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.
  • Full text search.
  • 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
  • It has a control panel
  • 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.

 

image

 

 

 

 


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 
image

 

Step 2

Once the system is updated we will install the following command:
 sudo apt install postgresql 
image

 

Step 3

We enter the letter "S" to confirm the download and installation of PostgreSQL:

 

image

 

Step 4

When this process comes to an end we will see the following:

 

image

 

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) 
image

 

Step 6

We are going to validate that the PostgreSQL server can accept client connections, for this we execute:
 sudo pg_isready 
image

 


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 
image

 

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 
image

 


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 
image

 

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:

 

image

 

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 
image

 

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 ' 
image

 

Step 3

After this process it will be possible to install pgAmdin4, for this we execute:
 sudo apt install pgadmin4 
image

 

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.

 

image

 

Step 5

When the process ends we will see the following:

 

image

 

Step 6

It is necessary to know the IP address of Ubuntu, for this we execute ip add:

 

image

 

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:

 

 

 

 

image

 

Step 8

We can leave the email by default and then we will assign a password to it:

 

image

 

Step 9

Now in a browser we enter the given syntax and we must register the email and password created previously:

 

image

 

Step 10

We can select the desired language and click on Login to access:

 

image

 

Step 11

Once we have access we will see the following:

 

image

 

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:
  • Server name
  • Group of servers where it will be hosted
  • Comments

 

 

 

image

 

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

 

 

 

image

 

 

Step 13

We click on "Save" and this will open the PostgreSQL management:

 

image

 

Step 14

On the left side we will have access to the database tools, we can navigate through the different available options:

 

image

 

Step 15

It will be possible to go to the group roles for their control and management:

 

image

 

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.

 


by (3.5m points)

Related questions

+4 votes
1 answer
asked Jul 17, 2020 in Linux / Unix by backtothefuture (552k points) | 652 views
+4 votes
1 answer
asked Jun 4, 2020 in Linux / Unix by backtothefuture (552k points) | 703 views
+4 votes
1 answer
asked Mar 19, 2020 in Linux / Unix by backtothefuture (552k points) | 964 views
+5 votes
1 answer
+3 votes
1 answer
asked Jun 22, 2020 in Linux / Unix by backtothefuture (552k points) | 1.1k views
Sponsored articles cost $40 per post. You can contact us via Feedback

Most popular questions within the last 30 days

10,659 questions
10,791 answers
510 comments
3 users