PostgreSQL is one of the popular, cross-platform, open-source object-relational database systems which is robust, high performing, and reliable with a strong community.

It dates back to 1986 as part of the POSTGRES project at the University of California, Berkeley, and carries 30 years of active development on its core platform. Its consistent performance over the years has proved its integrity, architecture, and extensibility for enterprise use. It is ACID-compliant and offers support for powerful addons like the popular PostGIS geospatial database extender.

We’re going to cover the installation of PostgreSQL on common platforms in this article.

So let’s get started.

Ubuntu

PostgreSQL is available for all flavors and versions of Ubuntu. You have got the choice to install and use the version available by default with your version of Ubuntu and supported for its lifetime or to use a specific version by adding the PostgreSQL repository and installing the same. We’ll be covering both options here.

To use the default supported version provided by Ubuntu’s repository, run:

$ sudo apt-get update
$ sudo apt-get -y install postgresql

PostgreSQL Apt Repository supports the current LTS versions of Ubuntu, i.e., 20.04, 18.04 and 16.04. Though it may not be fully supported, the same packages work on non-LTS versions as well by using the closest LTS version.

To use PostgreSQL apt repository, follow these steps:

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

To install a specific version from the PostgreSQL repository, instead of just postgresql which refers to the latest version, specify it like postgresql-12:

$ sudo apt-get -y install postgresql-12

The repository also contains different packages, including third-party addons. For example, commonly available packages for version 12 of PostgreSQL includes:

postgresql-client-12 client libraries and client binaries
postgresql-12 core database server
postgresql-contrib-9.x additional supplied modules (part of the postgresql-xx package in version 10 and later)
libpq-dev libraries and headers for C language frontend development
postgresql-server-dev-12 libraries and headers for C language backend development
pgadmin4 pgAdmin 4 graphical administration utility

Once installation completes, you can check the status of DB service by using the below command:

$ sudo systemctl status postgresql.service

For Ubuntu, post-installation, the service should be enabled and running like:

$ sudo systemctl status postgresql.service
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sun 2021-06-06 16:06:45 UTC; 46s ago
   Main PID: 3364 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 1113)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Jun 06 16:06:45 ubuntu20cloud systemd[1]: Starting PostgreSQL RDBMS...
Jun 06 16:06:45 ubuntu20cloud systemd[1]: Finished PostgreSQL RDBMS.
$

CentOS

Like Ubuntu, RHEL/CentOS repositories also contain a specific version of PostgreSQL, which is supported through the lifetime for the OS. You can install the same using:

$ sudo yum install -y postgresql-server

or you can use DNF if using CentOS 8.

$ sudo dnf install -y postgresql-server

Run below commands post-installation to initialize DB, enable the PostgreSQL service, and start it:

$ sudo postgresql-setup initdb
$ sudo systemctl enable postgresql.service
$ sudo systemctl start postgresql.service

Or to use the packages directly from PostgreSQL yum repositories, we have to add the repository as:

$ sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

And then to install a specific version of PostgreSQL like version 13, which is the latest stable release at the time of writing this article, use:

$ sudo yum install -y postgresql13-server

yum repository also contains additional packages for PostgreSQL, out of which some of the important ones are listed below:

postgresql-client libraries and client binaries
postgresql-server core database server
postgresql-contrib additional supplied modules
postgresql-devel libraries and headers for C language development

Run below steps post-installation to initialize DB, enable the PostgreSQL service, and start it:

$ sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
$ sudo systemctl enable postgresql-13
$ sudo systemctl start postgresql-13

You can check the status of the DB service by using the below command:

$ sudo systemctl status postgresql.service
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2021-06-06 16:39:35 GMT; 1min 12s ago
  Process: 7011 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
  Process: 7005 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/postgresql.service
           ├─7014 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
           ├─7015 postgres: logger process
           ├─7017 postgres: checkpointer process
           ├─7018 postgres: writer process
           ├─7019 postgres: wal writer process
           ├─7020 postgres: autovacuum launcher process
           └─7021 postgres: stats collector process

Jun 06 16:39:34 centos7cloud systemd[1]: Starting PostgreSQL database server...
Jun 06 16:39:35 centos7cloud systemd[1]: Started PostgreSQL database server.
$

Windows

Windows 32-bit and 64-bit installers are available from the official PostgreSQL site here.

  • You can download the applicable binary based on the required version and architecture.

How to Install PostgreSQL on Ubuntu, CentOS and Windows? Database Sysadmin

  • We’ll be downloading the latest version available, which is v13.3 for Windows 64-bit platform. Once the download completes, launch the installer by double-clicking on it.

How to Install PostgreSQL on Ubuntu, CentOS and Windows? Database Sysadmin

  • Click Next to proceed.

How to Install PostgreSQL on Ubuntu, CentOS and Windows? Database Sysadmin

  • Click Next to continue with the default path or specify your custom installation path.

How to Install PostgreSQL on Ubuntu, CentOS and Windows? Database Sysadmin

  • You can keep the default components selected and click Next to proceed.

How to Install PostgreSQL on Ubuntu, CentOS and Windows? Database Sysadmin

  • Next, it asks for the data directory location; this can be kept as the default unless you have a separate dedicated disk or directory assigned for it. Click Next to proceed.

How to Install PostgreSQL on Ubuntu, CentOS and Windows? Database Sysadmin

  • Specify a strong password that will be assigned to postgres user. Click Next when done.

How to Install PostgreSQL on Ubuntu, CentOS and Windows? Database Sysadmin

  • Default port 5432 should be good for most cases. Click Next to go to the next page.

How to Install PostgreSQL on Ubuntu, CentOS and Windows? Database Sysadmin

  • Select the locale for the DB and click Next to continue.

How to Install PostgreSQL on Ubuntu, CentOS and Windows? Database Sysadmin

  • All the selected settings for the installation wizard will be listed for you to review and confirm. If anything needs to be changed, go back and change the same or click Next to proceed to the next page.

How to Install PostgreSQL on Ubuntu, CentOS and Windows? Database Sysadmin

  • Setup will show that it’s ready to proceed with the installation. When ready, click Next to start the installation.

How to Install PostgreSQL on Ubuntu, CentOS and Windows? Database Sysadmin

  • Wait for the installation to complete. A confirmation will be shown for the same.

How to Install PostgreSQL on Ubuntu, CentOS and Windows? Database Sysadmin

  • Once completed, you can uncheck Stack Builder and click Finish to exit from the installation wizard. Click on Start Menu and search for SQL Shell (psql) and click on its icon to open the psql shell.

How to Install PostgreSQL on Ubuntu, CentOS and Windows? Database Sysadmin

  • SQL Shell (psql) opens up where you can interact with your PostgreSQL instance after specifying the connection details. All of the items can be selected as the default by simply pressing Enter except for the password, where you need to input the password specified for postgres user during the installation wizard. This will land you in postgres=# prompt.

How to Install PostgreSQL on Ubuntu, CentOS and Windows? Database Sysadmin

Connect to PostgreSQL

A postgres user gets created automatically post-installation, which has superadmin access to the DB instance. On Linux, as a privileged user, you can switch to this account as:

$ sudo su - postgres

Once in, we can reset postgres user password as:

$ psql -c "alter user postgres with password '[email protected]'"

And then launch PostgreSQL prompt with psqlas:

$ psql

This will land you inside PostgreSQL prompt:

$ psql
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1))
Type "help" for help.

postgres=#

At this prompt, you can interact with the database and do DB admin or user tasks. As a demo, the below steps create a DB and a user and then assign privileges to the new user to work on the new database:

postgres=# CREATE DATABASE geekflaredb;
CREATE DATABASE
postgres=# CREATE USER geekuser WITH ENCRYPTED PASSWORD '[email protected]';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE geekflaredb to geekuser;
GRANT
postgres=#

We can then list the DBs using l command:

postgres=# l
                               List of databases
    Name     |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
------------- ---------- ---------- --------- --------- -----------------------
 geekflaredb | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/postgres          
             |          |          |         |         | postgres=CTc/postgres 
             |          |          |         |         | geekuser=CTc/postgres
 postgres    | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0   | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres           
             |          |          |         |         | postgres=CTc/postgres
 template1   | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres           
             |          |          |         |         | postgres=CTc/postgres
(4 rows)

postgres=#

Now you can simply connect to a DB using:

postgres=# c geekflaredb
You are now connected to database "geekflaredb" as user "postgres".
geekflaredb=#

Summary

PostgreSQL is one of the widely adopted databases in the enterprise world because of its robustness, feature set, and scalability. It’s easy to learn and tries to be compatible with SQL standards.

There’s a tutorial available for PostgreSQL on its official site.