How to install PostgreSQL 12 and pgAdmin 4 on Ubuntu 20.04

PostgreSQL or Postgres is a Relational Database Management System (RDBMS) software with free & open source license. Starting as a POSTGRES project at the University of California, Berkeley in 1986, then in 1996 the name was changed to PostgreSQL which signifies supporting SQL. PostgreSQL runs on all major operating systems, Linux, FreeBSD, OpenBSD, macOS, and Windows.

*1. Install PostgreSQL*

Install PostgreSQL from the default Ubuntu repositories.

[INPUT]

1 sudo apt install postgresql

Default data directory is */var/lib/postgresql/12/main*, configuration files are stored in */etc/postgresql/12/main *, and the postgresql service runs on the port *5432*.

Check if PostgreSQL service is active, running, and enabled.

[INPUT]

1 2 3 sudo systemctl is-active postgresql sudo systemctl is-enabled postgresql sudo systemctl status postgresql

Also check whether PostgreSQL is ready to accept connections from clients.

[INPUT]

1 sudo pg_isready

The result of the above command.

[INPUT]

1 /var/run/postgresql:5432 – accepting connections

*2. Access PostgreSQL*

The PostgreSQL installation process create an account with the name *postgres* which becomes the PostgreSQL superuser. For PostgreSQL shell use *psql*.

Switch to postgres and login to psql.

[INPUT]

1 sudo -u postgres psql

The result of the above command.

[INPUT]

1 2 3 4 psql (12.4 (Ubuntu 12.4-0ubuntu0.20.04.1)) Type “help” for help.   postgres=#

*3. Create Database*

Create database, user, and grant database access rights to user.

[INPUT]

1 2 3 4 CREATE USER dbuser WITH PASSWORD ‘dbpass’; CREATE DATABASE dbname; GRANT ALL PRIVILEGES ON DATABASE dbname TO dbuser; \q

*4. Create New User*

Login to psql with user postgres and create a new user.

[INPUT]

1 sudo -u postgres createuser –interactive

The result of the above command.

[INPUT]

1 2 Enter name of role to add: musaamin Shall the new role be a superuser? (y/n) y

When running *psql*, the PostgreSQL authentication system by default will use the active system user as the login user to psql, and that user must have a database with the same name as the user name.

For example, the username I use is *musaamin*, meaning there must also be a database with the name *musaamin*.

Creating a database with the name musaamin.

[INPUT]

1 sudo -u postgres createdb musaamin

Login test with user musaamin.

[INPUT]

1 psql

The result of the above command.

[INPUT]

1 2 3 4 psql (12.4 (Ubuntu 12.4-0ubuntu0.20.04.1)) Type “help” for help.   musaamin=#

Login to psql with another database connected.

[INPUT]

1 psql -d namadatabase

Check the current connection to display the database name and user.

[INPUT]

1 \conninfo

The result of the above command.

[INPUT]

1 You are connected to database “namadatabase” as user “musaamin” via socket in “/var/run/postgresql” at port “5432”.

*5. Install pgAdmin4*

pgAdmin 4 is a management tool for the PostgreSQL database. pgAdmin supports Windows, Linux, and macOS operating systems. pgAdmin 4 was rewritten using Python and Javascript (jQuery).

pgAdmin 4 is not available in the default repositories, add the pgAdmin 4 repository.

[INPUT]

1 2 curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add sudo sh -c ‘echo “deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main” > /etc/apt/sources.list.d/pgadmin4.list && apt update’

Install pgAdmin 4.

[INPUT]

1 sudo apt install pgadmin4

The command above will install pgAdmin 4 and Apache web server to run pgadmin4-web. pgAdmin4 is stored at */usr/pgadmin4*.

Create a user login for pgAdmin.

[INPUT]

1 sudo /usr/pgadmin4/bin/setup-web.sh

The result of the above command. Enter the email address and password to log into pgAdmin, and configure Apache for pgadmin-web.

[INPUT]

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Setting up pgAdmin 4 in web mode on a Debian platform… Creating configuration database… NOTE: Configuring authentiction for SERVER mode.   Enter the email address and password to use for the initial pgAdmin user account:   Email address: musaamin@localhost.lan Password: Retype password: pgAdmin 4 – Application Initialisation ======================================   Creating storage and log directories… We can now configure the Apache Web server for you. This involves enabling the wsgi module and configuring the pgAdmin 4 application to mount at /pgadmin4. Do you wish to continue (y/n)? y The Apache web server is running and must be restarted for the pgAdmin 4 installation to complete. Continue (y/n)? y Apache successfully restarted. You can now start using pgAdmin 4 in web mode at http://127.0.0.1/pgadmin4

The pgAdmin login page.

pgAdmin 4 login

The pgAdmin dashboard page.

pgAdmin dashboard

Click *Add New Server*.

*General* tab, enter *Name* for server name, for example localhost.

Tab *Connection*, enter *Hostname/address* for the PostgreSQL server hostname or IP address, for example localhost. Enter *Username* and *Password /strong>.*

Then *Save*.

pgAdmin – create connection

pgAdmin is already connected to the PostgreSQL server on localhost.

pgAdmin connected

Good luck 🙂

Leave a Reply

Your email address will not be published.