How to install PostgreSQL 13 18.04 on Ubuntu 20.04

The
You can download this article in PDF format to support us through the following link.

Download the guide in PDF format

turn off
The

The
The

PostgreSQL is a very popular open source object-relational database management system (DBMS), which was created to ensure reliability, efficiency, and data integrity. With more than 30 years of development work now, the project has earned a good reputation for its reliability, power and performance. Thousands of companies use PostgreSQL to drive payment transactions, huge website traffic and e-commerce platforms. It also enables you to add custom functions developed using programming languages ​​such as Java, Python, C/C++, etc.

In this tutorial, we will explain how to install and configure PostgreSQL 13 database server on Ubuntu 20.04 | 18.04 Linux server. At the time of this update, the latest release of PostgreSQL is 13 Beta 12, which is not used for production purposes, but only for testing. Release Notes.

Step 1: Update the Ubuntu system

We are always committed to the latest version of the OS to ensure that there are no old dependencies. Log in to your Ubuntu server and run the following command to update all installed packages.

sudo apt update
sudo apt -y upgrade

After the system is updated, it is recommended that you reboot to allow the new kernel to run during the update.

sudo reboot

Step 2: Add PostgreSQL 13 repository to Ubuntu 20.04 | 18.04

Now that we have updated and restarted the system, let’s add the APT repository required to pull the package from the PostgreSQL repository.

sudo apt -y install vim bash-completion wget
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

After importing the GPG key, add the contents of the repository to the Ubuntu 20.04 | 18.04 system:

echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee  /etc/apt/sources.list.d/pgdg.list

Also add a test repository.

echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg-testing main 13" |sudo tee  /etc/apt/sources.list.d/pgdg-testing.list

The added repository contains many different software packages, including third-party plugins. They include:

  • PostgreSQL client
  • PostgreSQL
  • libpq development
  • PostgreSQL server development
  • pgadmin package

Step 3: Install PostgreSQL 13 on Ubuntu 20.04 / 18.04 Linux

After adding the repository, we can install the PostgreSQL 13 package on the Ubuntu 20.04 | 18.04 Linux server. But first update the package index for this version to make it available at the operating system level.

sudo apt update

Run the following command to install PostgreSQL 13 | 18.04 Linux on Ubuntu 20.04.

sudo apt install postgresql-13 postgresql-client-13

The PostgreSQL service is started and set to start after every system reboot.

$ systemctl status postgresql.service 
 ● postgresql.service - PostgreSQL RDBMS
    Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
    Active: active (exited) since Sun 2020-10-07 10:23:46 UTC; 6min ago
  Main PID: 8159 (code=exited, status=0/SUCCESS)
     Tasks: 0 (limit: 2362)
    CGroup: /system.slice/postgresql.service

$ systemctl status [email protected] 

$ systemctl is-enabled postgresql
enabled

Step 4: Test PostgreSQL connection

During the installation process, a postgres user will be automatically created. The user is full Super Administrator Access the entire PostgreSQL instance. Before switching to this account, the system user you log in with should have sudo privileges.

sudo su - postgres

Let’s reset this user password to a strong password that we can remember.

psql -c "alter user postgres with password '[email protected]'"

Use the following command to start the PostgreSQL prompt:

$ psql

Get the following connection details.

$ psql
psql (12.0 (Ubuntu 12.0-1.pgdg18.04+1))
Type "help" for help.

postgres=# conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

Let’s create a test database and user to see if it is working properly.

postgres=# CREATE DATABASE mytestdb;
CREATE DATABASE
postgres=# CREATE USER mytestuser WITH ENCRYPTED PASSWORD '[email protected]';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE mytestdb to mytestuser;
GRANT

List the created database:

postgres=# l
                               List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |    Access privileges    
-----------+----------+----------+---------+---------+-------------------------
 mytestdb  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/postgres           +
           |          |          |         |         | postgres=CTc/postgres  +
           |          |          |         |         | mytestuser=CTc/postgres
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres            +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres            +
           |          |          |         |         | postgres=CTc/postgres
(4 rows)

Connect to the database:

postgres-# c mytestdb
You are now connected to database "mytestdb" as user "postgres".

Installed other PostgreSQL utilities such as Create user with Created Can be used to create databases and users.

[email protected]:~$ createuser myuser --password
Password:
[email protected]:~$ createdb mydb -O myuser
[email protected]:~$ psql -l 

We can create and connect to the database on the PostgreSQL server.

Step 5: Configure remote connection (optional)

Installing PostgreSQL 13 on Ubuntu only accepts connections from localhost. In an ideal production environment, you would have a central database server and remote clients connected to it – of course, in a Private network (local area network).

To enable remote connection, edit the PostgreSQL configuration file:

sudo nano /etc/postgresql/12/main/postgresql.conf 

Uncomment lines 59 And change the listening address to accept the connection within the network.

# Listen on all interfaces
listen_addresses = '*'

# Listen on specified private IP address
listen_addresses = '192.168.10.11'

After the change, restart the postgresql service.

sudo systemctl restart postgresql

Confirm the listening address.

# netstat  -tunelp | grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      111        112837     11143/postgres      
tcp6       0      0 :::5432                 :::*                    LISTEN      111        112838     11143/postgres      

Step 6: Install pgAdmin4 management tool

If you want to manage the PostgreSQL database server from the web interface, install pgAdmin4.

Install pgAdmin4 on Ubuntu

Your system can help us eliminate any errors or other problems that may exist

Enjoy using PostgreSQL 13 on Ubuntu 20.04 / 18.04 / 16.04. Share other guidelines related to the database in the list below.

How to install PostGIS on Ubuntu

Install PostgreSQL 13 on CentOSS 7

Install PostgreSQL on CentOS 8/RHEL 8

label:

  • Install PostgreSQL 13 on Ubuntu 20.04/18.04
  • Install PostgreSQL 13 on Ubuntu 16.04
  • Install PostgreSQL 13 on Ubuntu Linux
  • Install PostgreSQL 13 on Ubuntu 20.04/18.04/16.04
  • Install PostgreSQL 13 on Ubuntu 20.04

The
You can download this article in PDF format to support us through the following link.

Download the guide in PDF format

turn off
The

The
The

Sidebar