07.06.2023

How to install PostgreSQL on Ubuntu 20.04

What is PostgreSQL

PostgreSQL - relational database system founded on SQL-queries language. Common standards following and possibility to make high performance systems is reason why PostgreSQL is popular today.

Cloud FTP Servers from €4 / moIntel Xeon Gold 6254 3.1 GHz CPU, SLA 99,9%, 100 Mbps channel

Before installation

To install PostgreSQL you should have:

Server with at least 1 CPU cores, 1Gb of RAM and 10Gb drive space. Real requirements may be some differ and depends of your application needs and optimization;
Actual operation system (Centos 7 in this article);
Administrative account access.

Setup processing

To install PostgreSQL on your server please do all steps below:

Update current system software:

yum -y update

Reboot the server to use the newest packages;
Add exception to the base repository file
Install PostgreSQL from system repositories:

apt install postgresql postgresql-contrib -y

Enable launch at system boot and run the service:

systemctl enable postgresql.service; service postgresql start

Check the status:

service postgresql status

Data operations

To create database you should use built-in administrative account:

su postgres
psql postgres

Create the user role and database:

CREATE ROLE <username> LOGIN PASSWORD '<password>';
CREATE DATABASE <dbname> WITH OWNER = <username>;

psql -h <host> -d <dbname> -U <username> -p <PostgreSQL_port>

Let's try to create table into the database:

CREATE TABLE test_table (
item_id integer NOT NULL,
item_name character varying(80) NOT NULL,
item_desc character varying(80) DEFAULT NULL,
PRIMARY KEY (item_id)
);

I created a table with three columns, item_id (digit), item_name (text row) and item_desc (text, optional).

Adding data:

INSERT INTO test_table (item_id, item_name, item_desc)
VALUES('1','toy car','Red sportcar model');

INSERT INTO test_table (item_id, item_name, item_desc)
VALUES('2','toy soldier','tin soldier figure');

INSERT INTO test_table (item_id, item_name, item_desc)
VALUES('3','ball','Original soccer ball');

Show table content:

SELECT <content> from <table_name>

Conclusion

In this article I described how to install PostgreSQL on Ubuntu 20.04 LTS and expained some base SQL operations.