How to Install PostgreSQL on Ubuntu Linux

announcement 4156536 640

This post shows students and new users steps to install PostgreSQL on Ubuntu Linux with pgAdmin4 web based management tool. PostgreSQL is a general purpose and object-relational database management system, probably the most advanced open source database system.

With PostgreSQL, one can also add custom functions using different programming languages such as C/C++, Java, etc.

pgAdmin is a web-based interface for managing of PostgreSQL database instances easily from your web browser. If you’re not a seasoned database administrator and you want to manage PostgreSQL easily via your favorite web browser, then use pgAdmin4.

PostgreSQL packages are included in Ubuntu default repositories, however, the versions in Ubuntu repositories aren’t the latest. In order to install the latest, you will have to install PostgreSQL package repository on Ubuntu Linux, and this tutorial will show you how.

Also, for students and new users learning Linux, the easiest place to start learning is on Ubuntu Linux. Ubuntu is the modern, open source Linux operating system for desktop, servers and other devices.

To learn how to install PostgreSQL and pgAdmin4 Ubuntu Linux, follow the steps below

How to add PostgreSQL repository on Ubuntu Linux

Adding PostgreSQL repository to Ubuntu is relatively easy. Simply run the commands below to add PostgreSQL repository key and file.

The commands below will add the repository key and the repository file to Ubuntu Linux.

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

How to install PostgreSQL on Ubuntu Linux

Now that the repository and key are added, run the commands below to update and install the latest PostgresSQL packages.

To install PostgreSQL 11, run the commands below

sudo apt update
sudo apt-get install postgresql postgresql-contrib

The commands above will also install PostgreSQL contrib package which provides several additional features for the PostgreSQL database system.

After installing PostgreSQL, the commands below can be used to stop, start and enable.

sudo systemctl stop postgresql.service
sudo systemctl start postgresql.service
sudo systemctl enable postgresql.service

To validate that PostgreSQL is installed and running, run the commands below.

sudo systemctl status postgresql.service

When you run the commands above, it should show something similar to the one below.

● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese
   Active: active (exited) since Wed 2018-10-31 11:58:09 CDT; 12s ago
 Main PID: 7930 (code=exited, status=0/SUCCESS)
    Tasks: 0 (limit: 4663)
   CGroup: /system.slice/postgresql.service

Oct 31 11:58:09 ubuntu1804 systemd[1]: Starting PostgreSQL RDBMS.
Oct 31 11:58:09 ubuntu1804 systemd[1]: Started PostgreSQL RDBMS.

How to access PostgreSQL shell

When you install PostgreSQL, a postgres user is automatically created. This user is the superuser for the PostgreSQL instance, and it is equivalent to the MySQL root user.

To access PostgreSQL interactive shell and manage the database, you need switch user and login to the shell as postgres user.

sudo su - postgres

Then use the psql command to invoke the interactive shell when you want to create and manage PostgreSQL databases.

psql

Set password for DB administrator (postgres)

Another way to access the PostgreSQL prompt without switching users, is to use the sudo command.

To create a change PostgreSQL database administrator password, login as postgres user and invoke the psql command shell using the commands below.

sudo -u postgres psql

On psql shell, run below commands to change database admin password. Either command below should work.

\password
OR
\password postgres

After that, quit and exit.

\q
exit

How to install pgAdmin4 web portal

Now that PostgreSQL is installed, run the commands below to install pgAdmin4 to use to manage your PostgreSQL server.

sudo apt-get install pgadmin4 pgadmin4-apache2

During the installation you will be prompted to enter PostgreSQL user password.

PostgreSQL pgAdmin4

Create a password for pgAdmin4 web service.

PostgreSQL pgAdmin4

After installing, open your web browser and browse to the server hostname or IP address followed by pgAdmin4 URI

http://example.com/pgadmin4

Enter the web interface initial user account as shown in the image above.

PostgreSQL pgadmin4

Login and add a new PostgreSQL server.

postgresql ubuntu setup pgadmin4 3

Begin setting up your environment.

PostgreSQL pgadmin4

That’s it! You may want to restrict access to only local IP address.

Conclusion:

This post showed you how to install PostgreSQL on Ubuntu Linux and its web based tool to manage databases. If you find any error above or have something to add, please use the comment form below.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.