In this tutorial, we will learn the commands to install and set up a PostgreSQL database server on Ubuntu 20.04 LTS Linux.
What is PostgreSQL?
PostgreSQL is an object-relational database system (ORDBMS). Being an open-source database project it is freely available and usable without a license fee. PostgreSQL was originally developed at the University of California and uses the simple BSD license.
It is a very advanced database system in the open-source area. PostgreSQL supports most parts of the SQL2003 standard and has a large number of its own extensions.
The user can expand the system with self-defined data types, operators, and functions. Apart from the support of referential integrity and advanced transaction management, PostgreSQL offers definitions of triggers and rules to regulate access to database objects.
Contents
The steps given here will work for Ubuntu 21.04/19.04/18.04 and other Linux OS based on Ubuntu as well.
We need to access the command line to install Postgresql on Ubuntu, for which open ‘Terminal’ on your system either from the Application launcher or using the keyboard shortcut command– CTRL+ALT+T. CLI users are already on the terminal.
 
Now, the first thing we have to do is running of system update, to make sure all the existing system packages and repository cache are up to date.
 
The packages to set up PostgreSQL on Ubuntu 20.04 are available in its base repository, hence we don’t need to add any third-party repo at all. However, currently while writing this article the version available to install from the base repo was PostgreSQL 12, therefore if you want 14 or 13, then we need to add its repository.
Add GPG Key:
Add repository in Ubuntu 20.04:
Paste the below-given command block and hit the Enter key.
Run system update command:
 
Now, we can install all the latest stable versions of the PostgreSQL Database on our Ubuntu 20.04 server or desktop.
Choose the version of the Database you want to install:
For Version 12
For Verison 13
For other versions such as 11 / 10 / 9.6, simply change the number in the above command with the one you want to install.
 
PostgreSQL service will automatically start after the installation, however, let’s confirm that is working fine without any errors, run:

 
To make sure our PostgreSQL is secured with a strong password, set a password for its system user and then change the default database admin user account using below given commands-
Change user password
Login using Postgres system account-
Now, change the Admin database password-
Note: Replace your-password in the above with a secure password that you want to set for the admin database user.
 
Let’s say you want to create a database on PostgreSQL to use with some application, then here are the commands to follow-
Login with Postgre system user-
Create a new user. Here we are creating a demo user protected by a password. Change them.
Create new PostgreSQL database-
Now, let’s Grant all rights or privileges on created database to the user:
However, the owner of the Database still will be the default Admin user of PostgreSQL to change that you can use the command-
To check and confirm whether the database has been created or not simply type
You will get the following kind of output

 
By default, this database server will listen only to the local host, to access it remotely, we need to perform some changes in its configuration file. We can limit it to some particular IP address or open it for all.
If you are using some other version of the Database then change number 13 with that in the above command.
In the Configuration file, find – listen_addresses and first, remove the # given in front of it. After that replace localhost with * sign to allow all Ip-address to connect to the Database server, whereas to limit it to some particular Ip-addresses then type them instead of ‘*‘. Multiple addresses need to be separated with commas.
Save the file by pressing Ctrl+X, Y, and hit the Enter key.

Edit pg_hba.conf
Change the host value as shown in the below screenshot.
Save the file Ctrl+X, Y, and press the Enter key.

 
Restart the Database server to apply the changes:
Check whether it listening for all addresses or not, for that you can run:
output:
Finally, open port ‘5432’ in the firewall:
 
To access created database on localhost, you can use the following command syntax:
psql -h localhost -d DB-name -U DB-User
Replace DB-name and DB -User with the one you want to connect.
Well, to connect  PostgreSQL from some remote Linux system, you have to install this database client, first.
Now, change the bold values in the below command to connect remote PostgreSQL running server.
Example:

 
Ending note:
This was the quick way to set up and start working with PostgreSQL on Ubuntu 20.04 Linux servers or Desktop systems. To understand more about you can refer to the official documentation guide.
 
Other Articles:
 
 
 
Heyan Maurya Ubuntu
1659 Views
Heyan Maurya Linux

Heyan Maurya Linux, Ubuntu
46923 Views
Heyan Maurya Linux, Ubuntu

Save my name, email, and website in this browser for the next time I comment.

Δdocument.getElementById( “ak_js_1” ).setAttribute( “value”, ( new Date() ).getTime() );
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Linux Shout crafted as a part of our main portal H2S Media to publish Linux related tutorials and open source Software.

source