Developer's Guide to PostgreSQL on Linux: Installation

By Adam McQuistan in PostgreSQL  09/27/2019 Comment

Devs Guide to PostgreSQL on Linux - Installation

Introduction

This is the first article of a series featuring a collection of useful tasks for managing, or administering, PostgreSQL version 11 on the Linux operating systems Ubuntu 18 LTS as well as CentOS 7. For those unfamiliar, PostgreSQL is a powerful and robust Object Relational Database which is freely available as open source software and widely popular. In this article I cover how to install and initialize the PostgreSQL database cluster. I also cover the key configuration files as well as their purpose.

Series Contents

Installation

Ubuntu 18

For things first, update then upgrade the system.

# apt update && apt upgrade

Next I take a look at the version of PostgreSQL that is available in the Ubuntu packages list.

# apt show postgresql
Package: postgresql
Version: 10+190
Priority: optional
Section: database
Source: postgresql-common (190)
Origin: Ubuntu
Maintainer: Ubuntu Developers <ubuntu-devel-discuss@lists.ubuntu.com>
Original-Maintainer: Debian PostgreSQL Maintainers <pkg-postgresql-public@lists.alioth.debian.org>
Bugs: https://bugs.launchpad.net/ubuntu/+filebug
Installed-Size: 63.5 kB
Depends: postgresql-10
Suggests: postgresql-doc
Task: postgresql-server
Supported: 5y
Download-Size: 5784 B
APT-Sources: http://mirrors.digitalocean.com/ubuntu bionic/main amd64 Packages
Description: object-relational SQL database (supported version)
 This metapackage always depends on the currently supported PostgreSQL
 database server version.
 .
 PostgreSQL is a fully featured object-relational database management
 system.  It supports a large part of the SQL standard and is designed
 to be extensible by users in many aspects.  Some of the features are:
 ACID transactions, foreign keys, views, sequences, subqueries,
 triggers, user-defined types and functions, outer joins, multiversion
 concurrency control.  Graphical user interfaces and bindings for many
 programming languages are available as well.

From the package details I see that the latest version available is 10.19 but, for this tutorial series I would like to focus on PostgreSQL 11 so I must pull in the desired version as described in the official PostgreSQL Linux downloads site.  Following their instructions I add the desired version to the local packages then update the PostgreSQL package.

# echo "deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main" > /etc/apt/sources.list.d/pgdg.list
# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
OK
# sudo apt-get update
Get:1 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]
Get:2 http://mirrors.digitalocean.com/ubuntu bionic InRelease [242 kB]                                        
Hit:3 http://mirrors.digitalocean.com/ubuntu bionic-updates InRelease                                            
Get:4 http://apt.postgresql.org/pub/repos/apt bionic-pgdg InRelease [46.3 kB]
Hit:5 http://mirrors.digitalocean.com/ubuntu bionic-backports InRelease             
Get:6 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 Packages [159 kB]
Fetched 536 kB in 1s (516 kB/s)    
Reading package lists... Done

The PostgreSQL packages have been updated and I can go ahead and installed version 11 like so.

# apt install postgresql-11 -y

Now check that it is installed and running.

# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Sat 2019-09-28 03:18:45 UTC; 1min 33s ago
 Main PID: 17504 (code=exited, status=0/SUCCESS)
    Tasks: 0 (limit: 1152)
   CGroup: /system.slice/postgresql.service

Sep 28 03:18:45 ubuntu-s-1vcpu-1gb-nyc3-01 systemd[1]: Starting PostgreSQL RDBMS...
Sep 28 03:18:45 ubuntu-s-1vcpu-1gb-nyc3-01 systemd[1]: Started PostgreSQL RDBMS.

CentOS 7

Again, the first thing to do is update the system.

# yum update

Now I can again look up what version of postgresql is avaiable in the default packages list.

# yum list postgresql-server*
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.cogentco.com
 * extras: mirror.metrocast.net
 * updates: mirror.math.princeton.edu
Available Packages
postgresql-server.x86_64                                       9.2.24-1.el7_5                                        base

 

It looks like the version available on this particular server is 9.2 so, I will again download the specific version as an RPM package for PostgreSQL 11.

# yum install wget -y
# wget https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7.6-x86_64/pgdg-centos11-11-2.noarch.rpm

Now what remains is to install the rpm package.

# yum install pgdg-centos11-11-2.noarch.rpm
# yum install postgresql11-server postgresql11-contrib -y

Unlike the Ubuntu installation, when on an CentOS / RHEL operatoring system you must initialize the database before the postgresql service can be started.

# /usr/pgsql-11/bin/postgresql-11-setup initdb
Initializing database ... OK

Now I start and enable the service.

# systemctl start postgresql-11
# systemctl status postgresql-11
● postgresql-11.service - PostgreSQL 11 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; disabled; vendor preset: disabled)
   Active: active (running) since Sat 2019-09-28 03:41:01 UTC; 11s ago
     Docs: https://www.postgresql.org/docs/11/static/
  Process: 18174 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 18179 (postmaster)
   CGroup: /system.slice/postgresql-11.service
           ├─18179 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
           ├─18181 postgres: logger   
           ├─18183 postgres: checkpointer   
           ├─18184 postgres: background writer   
           ├─18185 postgres: walwriter   
           ├─18186 postgres: autovacuum launcher   
           ├─18187 postgres: stats collector   
           └─18188 postgres: logical replication launcher   

Sep 28 03:41:01 centos-s-1vcpu-1gb-nyc3-01 systemd[1]: Starting PostgreSQL 11 database server...
Sep 28 03:41:01 centos-s-1vcpu-1gb-nyc3-01 postmaster[18179]: 2019-09-28 03:41:01.455 UTC [18179] LOG:  listening on IPv6 address "::1", port 5432
Sep 28 03:41:01 centos-s-1vcpu-1gb-nyc3-01 postmaster[18179]: 2019-09-28 03:41:01.455 UTC [18179] LOG:  listening on IPv4 address "127.0.0.1", port 5432
Sep 28 03:41:01 centos-s-1vcpu-1gb-nyc3-01 postmaster[18179]: 2019-09-28 03:41:01.459 UTC [18179] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Sep 28 03:41:01 centos-s-1vcpu-1gb-nyc3-01 postmaster[18179]: 2019-09-28 03:41:01.463 UTC [18179] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
Sep 28 03:41:01 centos-s-1vcpu-1gb-nyc3-01 postmaster[18179]: 2019-09-28 03:41:01.479 UTC [18179] LOG:  redirecting log output to logging collector process
Sep 28 03:41:01 centos-s-1vcpu-1gb-nyc3-01 postmaster[18179]: 2019-09-28 03:41:01.479 UTC [18179] HINT:  Future log output will appear in directory "log".
Sep 28 03:41:01 centos-s-1vcpu-1gb-nyc3-01 systemd[1]: Started PostgreSQL 11 database server.

Configuration Files

In general PostgreSQL provides fairly reasonable default settings but, there are still likely to be certain circumstances when you will want to tweak certain behaviors or features of PostgreSQL. To make peristable changes you will want to edit the configuration files. The most commonly edited config files are listed below.

  • postgresql.conf - is the main general global settings and configuration file
  • pg_hba.conf - is the host based authentication used for specifying the client connection rules
  • pg_ident.conf - is used to match linux users to postgresql database users when their names do not match

Ubuntu 18

For an Ubuntu 18 system the configuration files are located in /etc/postgresql/11/main/

# ls -l /etc/postgresql/11/main/
total 52
drwxr-xr-x 2 postgres postgres  4096 Sep 28 03:18 conf.d
-rw-r--r-- 1 postgres postgres   315 Sep 28 03:18 environment
-rw-r--r-- 1 postgres postgres   143 Sep 28 03:18 pg_ctl.conf
-rw-r----- 1 postgres postgres  4686 Sep 28 03:18 pg_hba.conf
-rw-r----- 1 postgres postgres  1636 Sep 28 03:18 pg_ident.conf
-rw-r--r-- 1 postgres postgres 24049 Sep 28 03:18 postgresql.conf
-rw-r--r-- 1 postgres postgres   317 Sep 28 03:18 start.conf

CentOS 7

For a CentOS 7 system the configuration files are located at /var/lib/pgsql/11/data/

# ls -l /var/lib/pgsql/11/data/
total 60
drwx------. 5 postgres postgres    41 Sep 28 03:40 base
-rw-------. 1 postgres postgres    30 Sep 28 03:41 current_logfiles
drwx------. 2 postgres postgres  4096 Sep 28 03:40 global
drwx------. 2 postgres postgres    32 Sep 28 03:41 log
drwx------. 2 postgres postgres     6 Sep 28 03:40 pg_commit_ts
drwx------. 2 postgres postgres     6 Sep 28 03:40 pg_dynshmem
-rw-------. 1 postgres postgres  4269 Sep 28 03:40 pg_hba.conf
-rw-------. 1 postgres postgres  1636 Sep 28 03:40 pg_ident.conf
drwx------. 4 postgres postgres    68 Sep 28 03:40 pg_logical
drwx------. 4 postgres postgres    36 Sep 28 03:40 pg_multixact
drwx------. 2 postgres postgres    18 Sep 28 03:41 pg_notify
drwx------. 2 postgres postgres     6 Sep 28 03:40 pg_replslot
drwx------. 2 postgres postgres     6 Sep 28 03:40 pg_serial
drwx------. 2 postgres postgres     6 Sep 28 03:40 pg_snapshots
drwx------. 2 postgres postgres     6 Sep 28 03:40 pg_stat
drwx------. 2 postgres postgres    25 Sep 28 03:42 pg_stat_tmp
drwx------. 2 postgres postgres    18 Sep 28 03:40 pg_subtrans
drwx------. 2 postgres postgres     6 Sep 28 03:40 pg_tblspc
drwx------. 2 postgres postgres     6 Sep 28 03:40 pg_twophase
-rw-------. 1 postgres postgres     3 Sep 28 03:40 PG_VERSION
drwx------. 3 postgres postgres    60 Sep 28 03:40 pg_wal
drwx------. 2 postgres postgres    18 Sep 28 03:40 pg_xact
-rw-------. 1 postgres postgres    88 Sep 28 03:40 postgresql.auto.conf
-rw-------. 1 postgres postgres 23873 Sep 28 03:40 postgresql.conf
-rw-------. 1 postgres postgres    58 Sep 28 03:41 postmaster.opts
-rw-------. 1 postgres postgres   104 Sep 28 03:41 postmaster.pid

Resources for Learning More About PostgreSQL

Conclusion

In this article I demonstrated how to install and initialize a PostgreSQL database cluster on Linux operating systems Ubuntu and CentOS as well as discussed the important configuration files one should be familiar with.

Share with friends and colleagues

[[ likes ]] likes

Community favorites for PostgreSQL

theCodingInterface