Postgresql is an opensource relational database. It is highly available and versatile. PGAdmin is a commonly used database administration tool used to manage the Postgresql installations.
In this tutorial, you’ll install Postgresql 12 on Ubuntu 20.04.
If you want to install Postgresql with High availability, refer the tutorial How to Set Up a Highly Available PostgreSQL Cluster Using Patroni and HAProxy on Ubuntu? If you want a install a standalone Postgresql, read below.
This tutorial uses the AWS EC2 instances for demonstration. Hence you’ll install postgresql on Ubuntu in AWS EC2. However, this will work in any Ubuntu servers.
Before you start, you need the following.
- If you do not have servers in the cloud, Create an AWS EC2 Ubuntu server instance by following the guide How to launch an EC2 Instance.
- [Important] – Update the packages list in the server which is upgrade-able using
sudo apt update
- [Important] – Upgrade the packages in the server to the latest versions using
sudo apt upgrade
Step 1 – Installing Postgresql
In this step, you’ll install postgresql.
Use the apt command along with -contrib to install postgresql database with additional optional utilities.
sudo apt install postgresql postgresql-contrib
- sudo apt install – To install the packages
- postgresql – to install the postgresql package
- postgresql-contrib – to install the postgresql-contrib packages
Postgresql is installed along with the contributions.
By default, the installation creates a Postgresql cluster which is a collection of databases managed by a single instance of the server. This cluster will contain database called postgres which is default database for utilities, users and the third party services.
Default data directory for postgresql is /var/lib/postgresql/12/main and configuration files such as pg_hba.conf are stored in /etc/postgresql/12/main directory. Ensure the appropriate version of the postgresql is used while checking the data or the configuration directory.
The systemctl command is used to manage the systemd services.
You can check if the Postgresql service is active by using the systemctl is-active command as below.
sudo systemctl is-active postgresql
You can check if the Postgresql service is enabled by using the systemctl is-enabled command.
sudo systemctl is-enabled postgresql
enabled state ensures that the Postgresql starts automatically at the system boot. It will be enabled by default.
You can check the current status of the Postgresql cluster using Systemctl status command.
sudo systemctl status postgresql
It’ll show the running status of the postgresql as below.
[email protected]:~$ sudo systemctl status postgresql ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor pr> Active: active (exited) since Wed 2020-12-09 00:41:39 UTC; 23s ago Main PID: 41214 (code=exited, status=0/SUCCESS) Tasks: 0 (limit: 1164) Memory: 0B CGroup: /system.slice/postgresql.service Dec 09 00:41:39 ip-172-31-13-67 systemd: Starting PostgreSQL RDBMS... Dec 09 00:41:39 ip-172-31-13-67 systemd: Finished PostgreSQL RDBMS. lines 1-10/10 (END)
You can also check if the Postgresql is ready to accept connections from the clients by using the below command.
You’ll see the message accepting connections as below.
/var/run/postgresql:5432 - accepting connections
Postgresql is installed. Now, you’ll create new roles.
Step 2 – Creating a New Role
In this step, you’ll create new roles to handle authorization to your postgresql database.
By default, Postgresql is setup to use the ident authentication. It means the the Postgresql roles are associated with the Unix/linux system account. If a roles with the name of the system account exists in the Postgresql database, then the user will be able to login to the Postgresql using the same user name.
AWS EC2 Ubuntu instances have the user ubuntu created by default with the sudo privilege. You’ll create a postgresql role called ubuntu which can be used to login to PostgreSQL database.
By default, the PostgreSQL installation also creates a role called Postgres. You can use the Postgres account to create new roles.
Use the below command to create role called ubuntu.
sudo -u postgres createuser ubuntu
- sudo -u postgres – To execute the command as the user postgres
- createuser – command to create user
- ubuntu – new role/user name to be created
Now the new role called ubuntu is created in your postgresql database.
You can move on to step 3 to create database with the same name ubuntu.
If you want to create additional roles, you can use the below commands.
Switch over to the postgres account using sudo -i -u command.
sudo -i -u postgres
Now you’ll be logged in to the Postgresql database and postgres prompt will be displayed.
Use the command createuser –interactive –pwprompt to create a new role.
createuser --interactive --pwprompt
- createuser – command to create user
- –interactive – to create user using a interactive mode
- –pwprompt – to create a user with a password
The script will prompt for some interactive questions to create user with your specifications.
Enter name of role to add:vikram
Add your preferred username. Press Enter.
Now you’ll be asked to enter the password and confirm it again. (Passwords you type are always invisible)
Enter password for new role: Enter it again:
Now, you’ll be prompted if you would want the new role to be a super user. Super user is a user with all the database privileges. Press Y if you want he new role to be a super user.
Shall the new role be a superuser? (y/n) y
The new role is created with your desired role name. You’ll create a database now.
Step 3 – Creating a New Database
In this step, you’ll create database.
Database is logical group of the user defined objects such as tables, views, triggers and other database objects. When you create a database, you can also provide special access to this specific database to the different roles.
You’ll create a database called ubuntu under the role ubuntu. It means the role ubuntu will be the owner of the database ubuntu.
Use the below command to create the database.
sudo -u postgres createdb -O ubuntu ubuntu
- sudo -u postgres – denotes the command should be executed as the postgres user.
- createdb – denotes a database to be created.
- ubuntu – To owner/role of the database to be created.
- ubuntu – name of the database to be created.
Now the database called ubuntu is created under the role ubuntu. You can login to the
You can connect to the ubuntu database by typing psql in the shell, if all the steps are followed appropriately.
You are connected to the ubuntu database and you can try to create a database table if you want.
psql > create table mytesttable(test varchar);
this will show the below message which means the table is created successfully.
You have created database and created database tables inside it.
Step 4 – Configuring the Postgresql Client Authentication(Optional)
In this step, you’ll configure the PostgreSQl client authentication. You can learn about the various authentication methods available in PostgreSQL in the official page.
By default, it is configured to use the peer authentication. This allows to connect with the same role name from ubuntu.
If you want to use different role names which is not the system user and connect to that different roles with the password, you need to update the authentication method in the /etc/postgresql/12/main/pg_hba.conf file.
Use the below command to open the file.
sudo vim /etc/postgresql/12/main/pg_hba.conf
It will open the file as below. Press i to enter the edit mode in vim editor. Change the highlighted peer to md5. All the other users except postgres will be using the password method.
Do not change the authentication method for the user postgres(first line of the below code). If you change it, then you will not be able to login to the postgres user with the peer authentication method.
If you want to login to ubuntu with peer authentication, copy and paste a similar line for ubuntu in the place of the postgres. Otherwise You’ll also not be able to login to ubuntu role with peer authentication.
local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" Is for Unix Domain Socket Connections Only local all all md5 # IPv4 Local Connections: host all all 127.0.0.1/32 md5 # IPv6 Local Connections: host all all ::1/128 md5 # Allow Replication Connections From Localhost, by a User With The # Replication Privilege. local replication all peer host replication all 127.0.0.1/32 md5 host replication all ::1/128 md5
Save the file and exit the editor.
You have configured the authentication methods of the postgres to use various authentication methods.
You have successfully installed PostgreSQL 12. You’ve learnt how to install PostgreSQL on Ubuntu 20.04 AWS EC2 Instance. You have also created roles and databases with the various authentication methods.
You can create a highlighly available PostgreSQL cluster using the tutorial How to Set Up a Highly Available PostgreSQL Cluster Using Patroni and HAProxy on Ubuntu.
<Watch this space for more updates on Blog>
How do I start postgresql on Ubuntu
use the command sudo systemctl start postgresql
How install and configure PostgreSQL on Ubuntu?
Follow the steps of this tutorial. You’ll successfully install and configure Postgresql.
Where is PostgreSQL installed on Ubuntu?
When installing PostgreSQL using apt, it is installed in the location /var/lib/postgresql/.