Install Postgresql 12 on Linux Mint

Overview

Linux mint is a Linux distro based on a popular debian based Linux known as Ubuntu. One reason this Linux is popular, is that it fits on many older computers with lower end hardware specifications, and have support for a wide array of monitors. At the same time, this Linux supports many of the modern hardware drivers.

The current Linux Mint is version 19.3 with the code name "Tricia", is released on 18th December 2019. This is developed from Ubuntu version named Bionic. Among the features that one should know about Tricia includes:

• It is a long term support (LTS) which means, patches will be maintained till 2023.
• Installation of Linux Mint have better hardware detection and supports modern BIOS installation to utilise password at BIOS.
• Include a movie player that utilises hardware optimisation.
• Its Linux desktop environment called Cinnamon, is light weight enough for many lower spec computer. Computers with lower speed hard disk have always suffered in a windows environment, Cinnamon greatly improves any windows experience.
• Higher level of security for those who are paranoid about PC security.

Postgresql 12

Among the world's most popular open sourced relational database management system (RDBMS) is Postgresql. Default installation will also create a database called postgres and a user with the same name, postgres. Installation comes with a separate application for the server, and a client. A tool, psql is to manage the server from the command line. There are many other applications that can manage Postgresql with a graphical point and click approach.

Version 12 is released on 3rd October 2019 with the following features:

• Improved table indexing features. Such as rebuild index table without blocking writes to an index, and this reduces down time.
• Partitioning of tables to improved queries from a limited set of data.
• JSON document query support.
• Just-in-time compilation to process large data (data warehouse) more efficiently.
  

Installation

Linux Mint 19.x by default installs Postgresql version 10. In order to install version 12, Linux Mint must have the latest packages installed to reduce chances of package dependency errors. These are summary of steps in order to install Postgresql version 12.

1. Add Postgresql repository
2. Update existing system
3. Install Postgresql 12 server. This includes the server (postgresql-12) and client (postgresql-client-12) application.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main" > /etc/apt/sources.list.d/postgresql.list'
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
sudo apt update
sudo apt upgrade

sudo apt install postgresql-12

Once installed, it will prompt

pg_ctlcluster 12 main start

Following are among the files and locations at default install.

• The database will be stored at /var/lib/postgresql/12/main
• Logging information at /var/log/postgresql/postgresql-12-main.log
  

Documentation can be display with the command;

man postgresql

Accessing the server

Default installation includes the user postgres. Access the server using this user.

sudo -u postgres psql

Here are a list of psql commands to get you started.

Once in psql, here are several commands to browse the database;

Display server version (the Uppercase is a matter of good practice)
SELECT version();

List database schema
\l

Switch database schema
\c database_name

Describe a table
\d table_name

Get the last command
\g

Show history of commands
\s

Help with a command
\h command_name

Exit Postgresql client
\q

You can refer to previous posting "Create database and user" to further try the database.

* Install PHP 7.4 notes (not related to postgresql)

sudo apt-get install -y php7.4-{bcmath,bz2,intl,gd,mbstring,pgsql,zip}

sudo apt-get install -y php7.4-{curl,common,xml,xsl}