How to Install, Configure and Use latest PostgreSQL Version on Ubuntu

Postgres is an open-source relational database. It has many advanced features like enterprise-class performance, triggers, Atomicity, Consistency, Isolation, Durability, and many other features that will make Postgres a good choice for many complex applications.

PostgreSQL Installation:

In this tutorial, we download the latest version of Postgres from official PostgreSQL website https://www.postgresql.org/download/linux/ubuntu/

PostgreSQL is also available in Ubuntu's repository by default. By using PostgreSQL apt repository from its official site we can install a specific version of our choice.

To use the apt repository, follow these steps:

Choose your Ubuntu version

My Ubuntu version is Bionic (18.04)

Create the file PostgreSQL repository.

nano /etc/apt/sources.list.d/pgdg.list 

and add a line for the repository in pgdg.list

deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main

If you use a different Ubuntu version that 18.04 (Bionic), then replace the word 'bionic' with the name of the used Ubuntu version. Import the repository signing key, and update the package lists

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

Now install Latest PostgreSQL Server in our Ubuntu system using the following commands.

sudo apt-get install postgresql postgresql-contrib

After the Installation PostgreSQL server will start automatically.

Check the status of PostgreSQL service

systemctl status postgresql

Now verify PostgreSQL connection and PostgreSQL server version.

sudo -u postgres psql -c "SELECT version();"

This will connect to PostgreSQL using psql and give server version

 version 
-----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit

Using PostgreSQL roles and Authentication Methods

PostgreSQL uses the concept of the role of database access permission. A role can be applied to a database user or a group of database users.

PostgreSQL supports multiple authentication methods.

The following are the most commonly used authentication methods of PostgreSQL.

1.Trust

Trust authentication Methods can be defined in pg_hba.conf, using this method, the role can connect with a database without a password.

2.Password

A role can connect with the database by proving the password.

3.Ident

Ident Authentication Methods works on TCP/IP connections only. 

4. Peer

Peer Authentication Methods used for local connection only.

pg_hba.conf file define client authentication methods. we can change authentication methods by editing the following files.

nano /etc/postgresql/11/main/pg_hba.conf

In this file, PostgreSQL uses the peer authentication method by default. we can change authentication methods by changes peer to other methods.

Configure authentication methods in Postgres

At the time of the PostgreSQL installation, postgres user will be created by default, postgres is the superuser of PostgreSQL database, similarly root user in MYSQL database server. this 'postgres' user also creates in ubuntu.

Connect to PostgreSQL

The following command to connect with PostgreSQL.

sudo -i -u postgres

Now we can connect to a Postgres prompt like MySQL by typing following command.

psql

We will get the following prompt and we are ready to run PostgreSQL queries on it. 

Please note that we can directly connect to the database server by simply typing following command.

sudo -u postgres psql

Creating roles and database in PostgreSQL

We already have the postgres role in the database, now we create a new role using the following commands.

[email protected]:/home# sudo -u postgres createuser --interactive

Enter name of role to add: sohan
Shall the new role be a superuser? (y/n) y

We can list existing user roles using the following command.

postgres=# \du

or run the following command from the postgres prompt.

SELECT rolname FROM pg_roles;

Connect to postgres

We can drop any existing user role using DROP ROLE command.

postgres=# DROP ROLE sohan;
DROP ROLE

Creating a PostgreSQL Database

Here we can log in as the postgres account and run the following command.

[email protected]ntu-pc:~$ createdb dummu

or we can simply run following commands with normal ubuntu users.

sudo -u postgres createdb dummu 

Login to postgres prompt using the new role

run directly from your system terminal to create a new user.

sudo adduser sohan

After creating a new user in the system, simply run

sudo -u sohan psql

or type the following command

sudo -i -u sohan
psql

Login to postgres

We can connect to a different database by simply providing database name like

psql -d dummu

After connecting to the above database we can get that database information by a simple command

dummu=> \conninfo
output
You are connected to database "dummu" as user "sohan" via socket in "/var/run/postgresql" at port "5432"UPDATE dummu SET password = 'postgres' where username = 'sohan';

Creating tables in PostgreSQL

After creating roles, database and creating a connection with the database server, now we will create a table.

CREATE TABLE dummu(user_id serial PRIMARY KEY,username VARCHAR (50) UNIQUE NOT NULL, password VARCHAR (50) NOT NULL, email VARCHAR (355) UNIQUE NOT NULL, created_on TIMESTAMP NOT NULL, last_login TIMESTAMP);

Above command will create new table dummy just like MySQL, we can check this new table by typing

\d

Create tables in Postgres

Here, we will see dummu_user_id_seq table also, which will keep track of the next number in the sequence.

If we just want to display table name without the sequence, run following command.

\dt

This will only display dummu table

sohan=> \dt
Output

          List of relations
 Schema |    Na    | Type  | Owner
--------+------------+-------+-------
 public | dummu      | table | soha

Adding data into the table

Now we will add data into dummu table by typing,

Insert into dummu(user_id,username,password,email,created_on,last_login) values (1,'sohan','123456','[email protected]','2019-06-24 11:11:11','2019-06-24 11:11:11');

Now we will fetch data from dummu table,

SELECT * FROM dummu;

Insert data into table

Delete Column from table

Now let's delete data from this table

DELETE FROM dummu WHERE email = '[email protected]';

Delete a column from database table

Alter PostgreSQL table

Similar way we can modify the table by using alter query as

ALTER TABLE dummu DROP email;

This will remove email field from dummu table.

verify it by

SELECT * FROM dummu;
output:-

user_id | username | password |     created_on      |     last_login      
---------+----------+----------+---------------------+---------------------
       1 | sohan    | 123456   | 2019-06-24 11:11:11 | 2019-06-24 11:11:11

Updating Data in a table

Now we will see how to update data into a table

run following command to update password into dummu table

UPDATE dummu SET password = 'postgres' where username = 'sohan';

verify changes by simply typing

SELECT * FROM dummu;
output

user_id | username | password |     created_on      |     last_login      
---------+----------+----------+---------------------+---------------------
       1 | sohan    | postgres | 2019-06-24 11:11:11 | 2019-06-24 11:11:11

In short, In this tutorial we learn how to install PostgreSQL, creating role and database and basic database command.

Share this page:

Suggested articles

1 Comment(s)

Add comment

Comments

By: franz at: 2019-10-27 14:25:02

Two mistakes:1. Package postgresql-contrib does not exists any more since PostgreSQL 10. Its content is since already included in postgresql package. Check the table in  "Included in distribution" section at: https://www.postgresql.org/download/linux/ubuntu/2. Command systemctl status postgresql is not correct command to check if PostgreSQL is up. Do the test: a) start the server: sudo systemctl start postgresql b) corrupt postgresql.conf file (just change some setting to invalid one). c) restart server: sudo systemctl restart postgresqld) now check if service is up:  systemctl status postgresql This command incorrectly states server is stared. Correct command to check if PostgreSQL is started on Ubuntu is: pg_lsclusters