Introduction
PostgreSQL, or Postgres, is an open-source, powerful, advanced, high performance, and stable relational-document database management system that provides an implementation of the SQL querying language.
It is a popular choice for many small and large projects and has the advantage of being standards-compliant and having many advanced features like reliable transactions and concurrency without read locks.
In this article, we will explain how to install PostgreSQL on an Ubuntu 18.04 server (also works on older Ubuntu releases) and learn some basic ways to use it.
Before We Begin
- Ubuntu 18.04 server/desktop with root or non-root user with sudo privileges
Step 1 — Installing PostgreSQL
Most Linux platforms such as Debian, Red Hat / CentOS, SUSE, and Ubuntu have PostgreSQL integrated with their package management.
Note:It is recommended that you install PostgreSQL this way since it ensures a proper integration with the operating system including automatic patching and other update management functionality.
PostgreSQL is available in Ubuntu main repository. However, like many other development tools, it may not be the latest version.
First check the PostgreSQL version available in Ubuntu repositories using this apt command in the terminal:
apt show postgresql
In my Ubuntu 18.04, it showed that the available version of PostgreSQL is version 10 (10+190 means version 10) whereas PostgreSQL version 13 is already released.
To check the postgreql version you can visit the download page and select your Linux distribution.
Based on this information, you can make your mind whether you want to install the version available from Ubuntu or you want to get the latest released version of PostgreSQL.
I’ll show both methods to you.
Method 1: Install PostgreSQL from Ubuntu repositories
In the terminal, use the following command to install PostgreSQL 10 as the default version in ubuntu 18.04
sudo apt update
sudo apt install postgresql postgresql-contrib
What is postgresql-contrib?
The postgresql-contrib or the contrib package consists some additional utilities and functionalities that are not part of the core PostgreSQL package. In most cases, it’s good to have the contrib package installed along with the PostgreSQL core.
Method 2: Installing the latest version 12 of PostgreSQL in Ubuntu
To install PostgreSQL 12, you need to add the official PostgreSQL repository in your sources.list, add its certificate and then install it from there.
Once visiting the PostgreSQL download for Ubuntu page, you find the script that allows you to install PostgreSQL on Ubuntu. You need to execute them one by one
First, import the repository key to your system :
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Next,create a file /etc/apt/sources.list.d/pgdg.list which stores the repository configuration with the below command:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
Now,update the package list:
sudo apt update
Everything is ready now. Install PostgreSQL with the following command:
At the time of writing this article the latest postgresql version is 12
Note :If you want a specific version, use ‘postgresql-11’ or similar instead of ‘postgresql’:
sudo apt install postgresql postgresql-contrib
Configuring PostgreSQL
Normally after installation PostgreSQL will start automatically .You can check if PostgreSQL is running by executing:
sudo service postgresql status
You should get response as below:
The PostgreSQL service is started and set to come up after every system reboot.
Via the service command you can also start, stop or restart postgresql.
Check PostgreSQL version installed
Execute below commands to verify the version:
psql -V
Where V must be in capital.
or
psql --version
Connect to the PostgreSQL database server via psql
By default, PostgreSQL creates a special user postgres that has all rights.
It means that PostgreSQL will associate its roles with the system accounts of Linux. If a role exists in PostgreSQL, the same Linux user account with the same name is able to log in as that role.
When you installed PostgreSQL, the installation process created a user account called postgres associated with the default postgres role.
To actually use PostgreSQL, you must first log in to that account:
sudo su postgres
Your prompt should change to something similar to:
postgres@docker:/home/docker
Note:Here My username and hostname are docker ,after login it changed as postgres user
Now, run the PostgreSQL Shell with the utility psql:
psql
You should be prompted with:
postgress=#
You can type in \q to quit and \? for help.
To see all existing tables, enter:
/l
The output will look similar to this (Hit the key q to exit this view):
With \du you can display the PostgreSQL users:
How to Use PostgreSQL Roles and Databases
In postgres, client authentication is controlled by the /etc/postgresql/12/main/pg_hba.conf configuration file. The default authentication method is “peer” for the database administrator, meaning it gets the client’s operating system user name from the operating system and checks if it matches the requested database user name to allow access, for local connections (as shown in the following screenshot).
Note: During the installation process, a system user account called postgres was created without a password, this is also the default database administrator user name.
In addition, under postgres database access permission management is performed via roles. A role can be considered as either a database user, or a group of database users, depending on how the role is set up.
The default role is also postgres. Importantly, database roles are conceptually fully unconnected to operating system users, but practically they may not be separate (for example when it comes to client authentication).
You can change the password of any user (including postgres) with:
ALTER USER postgres WITH PASSWORD 'my_password';
Note:Replace postgres with the name of the user and my_password with the wanted password. Also, don’t forget the ; (semicolumn) after every statement.
Accessing a Postgres Prompt Without Switching Accounts
To access the postgres shell directly, without first accessing the postgres user account, run the following command.
sudo -i -u postgres psql
You can quit/exit the postgres by typing the following command.
postgres=# \q
Conclusion
In this tutorial we have learned how to install PostgreSQL different versions on ubuntu 18.04 and discussed how to access PostgreSQL database with default user.In upcoming articles we will discuss how to configure custom user and database with remote access of our PostgreSQL database