Developer's Guide to PostgreSQL on Linux: Installation
By
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 (you're here)
- psql interactive shell
- Creating Databases
- Creating Accounts and Making Connections
- Changing the Default Data Directory
- Using Explain and Analyze (coming soon)
- Utilitizing Indexes (coming soon)
- Creating Backups (coming soon)
- Restoring From Backups (coming soon)
Installation
Ubuntu 18
For things first, update then upgrade the system.
# apt update && apt upgradeNext 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 -yNow 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 updateNow 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 -yUnlike 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 ... OKNow 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.confCentOS 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.pidResources for Learning More About PostgreSQL
- A great, example driven, reference book on robust management of PostgreSQL is PostgreSQL High Availability Cookbook - Second Edition
- A good companion book that is again example driven but focusing on performance is PostgreSQL High Performance Cookbook
thecodinginterface.com earns commision from sales of linked products such as the books above. This enables providing continued free tutorials and content so, thank you for supporting the authors of these resources as well as thecodinginterface.com
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.