Intro

Howdy Folks! This post covers the process of installing the PostgreSQL database on Linux and a few of the common details around configuration and operation.

Software Versions Used in this Post

  • PostgreSQL - 13.1
  • Ubuntu - 20.04
  • Centos - 8.2.2004

Installation

Ubuntu

This is almost a direct copy from the documentation for Ubuntu Linux 2004.

cmd
# Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists:
sudo apt update

# Install the latest version of PostgreSQL along with development libraries.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt -y install postgresql postgresql-contrib libpq-dev

Centos

This is almost a direct copy from the documentation for Redhat Linux.

cmd
# Install the repository RPM:
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable the built-in PostgreSQL module:
sudo dnf -qy module disable postgresql

# Install PostgreSQL and the development libraries:
sudo dnf install -y postgresql13-server postgresql13-devel postgresql13-contrib libpq-devel

Verification

Version

Verify the version of Postgres that is installed.

cmd
# Ubuntu

/usr/lib/postgresql/13/bin/postgres -V
postgres (PostgreSQL) 13.1 (Ubuntu 13.1-1.pgdg20.04+1)

# Centos

/usr/pgsql-13/bin/postgres -V
postgres (PostgreSQL) 13.1

Service

Ubuntu

On Ubuntu distributions the postgresql service is started and enabled to run at start up automatically. Verify with the systemctl status command.

cmd
systemctl status postgresql.service

# output 

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Fri 2020-11-20 22:59:44 UTC; 51s ago
   Main PID: 138906 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 2248)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Nov 20 22:59:44 devbox systemd[1]: Starting PostgreSQL RDBMS...
Nov 20 22:59:44 devbox systemd[1]: Finished PostgreSQL RDBMS.

Centos

On Centos distributions the postgresql-<version> database needs to be initialized and the service started and enabled.

Initialize the database.

cmd
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb

# output 

Initializing database ... OK

Start and enable the service.

cmd
sudo systemctl start postgresql-13
sudo systemctl enable postgresql-13

# output 

Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-13.service → /usr/lib/systemd/system/postgresql-13.service.

Verify with the systemctl status command.

cmd
systemctl status postgresql-13.service

# output 

● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2020-11-23 14:38:14 AEST; 1min 1s ago
     Docs: https://www.postgresql.org/docs/13/static/
  Process: 8091 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 8096 (postmaster)
    Tasks: 8 (limit: 23803)
   Memory: 17.7M
   CGroup: /system.slice/postgresql-13.service
           ├─8096 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
           ├─8098 postgres: logger
           ├─8101 postgres: checkpointer
           ├─8102 postgres: background writer
           ├─8103 postgres: walwriter
           ├─8104 postgres: autovacuum launcher
           ├─8105 postgres: stats collector
           └─8106 postgres: logical replication launcher

Nov 23 14:38:14 centos8.homelab.local systemd[1]: Starting PostgreSQL 13 database server...
Nov 23 14:38:14 centos8.homelab.local postmaster[8096]: 2020-11-23 14:38:14.921 AEST [8096] LOG:  redirecting log output to logging collector process
Nov 23 14:38:14 centos8.homelab.local postmaster[8096]: 2020-11-23 14:38:14.921 AEST [8096] HINT:  Future log output will appear in directory "log".
Nov 23 14:38:14 centos8.homelab.local systemd[1]: Started PostgreSQL 13 database server.

IP/Port

The default IP address and port combination that Postgres listens on is localhost:5432 using the TCP protocol.

Verify that the host ip/port combination is open with the ss command.

cmd
ss -tunlap | grep 5432

# output 

tcp   LISTEN     0      244             127.0.0.1:5432             0.0.0.0:*

Configuration Files

Configuration files are stored at the folling locations

Ubuntu

/etc/postgresql/13/main/postgresql.conf

Centos

/var/lib/pgsql/13/data/postgresql.conf

Postgres Login

Switch to the postgres user and enter the Postgres application.

cmd
sudo -u postgres psql

Database Creation

Create a database named testing123_db using the template template1 .

cmd
create database testing123_db template template1;

User Creation

Create a database user user_name .

cmd
create user user_name with encrypted password "mypassword";

Apply permission for the database testing123_db to user user_name .

cmd
grant all privileges on database testing123_db to user_name;

Outro

You did it! PostgreSQL is installed and ready to rock and roll as your RDBMS.