PostgreSQL (also called postgres) is very famous, powerful, open-source object-relational database management system that has been around for almost 30 years. PostgreSQL requires very minimum maintained efforts because of its stability.  Therefore, if you develop applications based on PostgreSQL, the total cost of ownership is low in comparison with other database management systems.

Recommended Read: Simple guide to install POSTGRESQL on Centos/RHEL

Also Read: How to setup SSH login without password

In this tutorial, we will learn to install PostgreSQL on Ubuntu. So let’s start the tutorial,

Install PostgreSQL on Ubuntu

There are two methods using which we can install Postgresql on Ubuntu systems,

1- Using the default Ubuntu repositories,

2- Using the official PostgreSQL repositories

1- Install Postgresql using default repositories

Ubuntu also maintains Postgresql on official Ubuntu repositories but they might be an older version. So if you don’t have any concern for the Postgres version, then use this method & if you want to install a particular version, then the second method is the way to go.

This method is quite simple, to install PostgreSQL  execute the following command,

$ sudo apt-get install postgresql postgresql-contrib

Once the database has been installed, we can start the database & also enable it to start at boot time,

# sudo systemctl start postgresql

# sudo systemctl enable postgresql

So our database is now up & ready to be used.

2- Install Postgresql using Official Postgresql repositories

Using this method, we will install a stable version 11 of PostgreSQL Install the official repository with the following command,

$ wget –quiet -O – | sudo apt-key add –

Next, execute the following command for add repository contents,

$ RELEASE=$(lsb_release -cs)

$ echo “deb ${RELEASE}”-pgdg main | sudo tee  /etc/apt/sources.list.d/pgdg.list

Now to install Postgresql on Ubuntu, we will run the following commands,

$ sudo apt update

$ sudo apt -y install postgresql-11

Now we will start the database service & also enable it to start at boot time,

# sudo systemctl start postgresql-11

# sudo systemctl enable postgresql-11

PostgreSQL Configuration

Set the Admin user password

To change the database admin user password, first switch to postgres user,

# su – postgres

Next, execute the following command from the terminal,

$ psql -c “alter user postgres with password ‘NEWPASSWORDhere'”

Create a new user & a new database

To create a new database, run the following command,

$ createuser new_user

$ createdb new_db -O new_user

$ grant all privileges on database new_db to new_user;

Connect to a database

To connect to a database, use the following command,

$ psql -U new_user -h localhost -d new_db

Connecting to the database from a remote system

The above command will only work from localhost but if we want to access the database from a remote host, we need to make some changes as by default we can’t connect to the database from a remote system.

To allow access from remote systems, open the following file,

$ sudo vi /etc/postgresql/11/main/postgresql.conf

& look for the following line,

listen_addresses =

& change to

listen_addresses = ‘*’

Next, we will make changes on another file,

$ sudo  vi /etc/postgresql/11/data/pg_hba.conf

& add the following lines,

# Accept from anywhere

host all all md5

# Accept from trusted subnet

host all all md5

We need to restart the database server to implement the changes.

# sudo systemctl restart postgresql-11

We now end this tutorial on how to install postgresql on Ubuntu, please also see our tutorial on some of the important Postgresql commands that you should know. Please feel free to send in any questions or queries using the comment box below.

If you think we have helped you or just want to support us, please consider these:-

Connect to us: Facebook | Twitter | Linkedin

TheLinuxGURUS are thankful for your continued support.