PostgreSQL Zero to Hero: Getting Started with RPMs -Part 1

One of the most important things to using PostgreSQL successfully in your development and production environments is simply getting started! One of the most popular ways to install PostgreSQL is by using RPM packages. The PostgreSQL RPM packages work across many Linux distributions, including, RedHat Enterprise Linux (RHEL), CentOS, Fedora, Scientific Linux, and more, and the PostgreSQL community provides installers for these distributions.

This guide will help you get started with installing and configuring PostgreSQL for a CentOS / RHEL 7 based system, which will also work for Fedora 29. We will be installing PostgreSQL 11, which is the latest major release of PostgreSQL as of this writing.

Installation

Installing yum / dnf repository setup rpm

Visit https://yum.postgresql.org/repopackages.php and scroll down to PostgreSQL 11 section, right click on your your distribution link and copy link.

sudo yum install <paste your link>

RHEL

sudo yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-redhat11-11-2.noarch.rpm

Above command installs yum repo file for PostgreSQL in /etc/yum.repos.d/

CentOS

sudo yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm

Above command install yum repo file for PostgreSQL in /etc/yum.repos.d/

Fedora

sudo dnf install https://download.postgresql.org/pub/repos/yum/11/fedora/fedora-29-x86_64/pgdg-fedora11-11-2.noarch.rpm

Above command install yum repo file for PostgreSQL in /etc/yum.repos.d/ which is used my dnf.

Installing PostgreSQL server and contrib packages

Following command will install these packages:

1. postgresql11 - common binaries, man pages, translations, etc
2. postgresql11-libs - shared libraries
3. postgresql11-server - Main server binary and others
4. postgresql11-contrib - Additional extensions libraries to add some awesome features

RHEL / CentOS

sudo yum install postgresql11-server postgresql11-contrib

Fedora

sudo dnf install postgresql11-server postgresql11-contrib

Initializing default cluster

PGSETUP_INITDB_OPTIONS='-k' sudo -E /usr/pgsql-11/bin/postgresql-11-setup initdb

Above command will create PostgreSQL cluster in /var/lib/pgsql/11/data/. PGSETUP_INITDB_OPTIONS env is used to set initdb parameters.

• -k Enable checksum on data pages
• -E preserve env with sudo
• initdb initialize cluster

Enabling service

Enable PostgreSQL to start automatically on server reboot.

sudo systemctl enable postgresql-11

Starting service

For now, start PostgreSQL service manually.

sudo systemctl start postgresql-11

Connect to PostgreSQL database

sudo -u postgres psql

psql (11.2) Type "help" for help.
postgres=# select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.2.1 20181215 (Red Hat 8.2.1-6), 64-bit
(1 row)

postgres=# show data_directory;
data_directory
------------------------
/var/lib/pgsql/11/data
(1 row)

postgres=# \du
List of roles
 Role name | Attributes                                                 | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# \q

You are connected to PostgreSQL, database postgres as postgres user (superuser).

Stopping service

sudo systemctl stop postgresql-11

Customizing PGDATA location

Many times it is a desire or requirement (IT Policies based on another database environment) to setup database in different directory.

Let us change PGDATA for our PostgreSQL database to /data01/11/data directory.

systemd drop-in override

sudo systemctl edit postgresql-11

Add following in editor and save.

[Service]
Environment="PGDATA=/data01/11/data"

Initialize cluster

PGSETUP_INITDB_OPTIONS='-k' sudo -E /usr/pgsql-11/bin/postgresql-11-setup initdb

Above command will create PostgreSQL cluster in /data01/11/data. PGSETUP_INITDB_OPTIONS env is used to set initdb parameters.

• -k Enable checksums on data pages
• -E preserve env with sudo
• initdb initialize cluster

Enabling service

Enable PostgreSQL to start automatically on server reboot.

sudo systemctl enable postgresql-11

Starting service

For now, start PostgreSQL service manually.

sudo systemctl start postgresql-11

Connect to PostgreSQL database

sudo -u postgres psql

psql (11.2) Type "help" for help.

postgres=# select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.2.1 20181215 (Red Hat 8.2.1-6), 64-bit
(1 row)


postgres=# show data_directory;
data_directory
-----------------
/data01/11/data
(1 row)


postgres=# \du
List of roles
 Role name | Attributes                                                 | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# \q

You are connected to PostgreSQL, database postgres as postgres user (superuser).

Stopping service

sudo systemctl stop postgresql-11

Customizing PGDATA, User, Group

Many times it is a desire or requirement (IT Policies based on another database environment) to setup the database in a different directory and the database cluster started using a different Group and User.

Let us change PGDATA for our PostgreSQL database to /data02/11/data directory and use Group, User to pgdba.

systemd drop-in override

sudo systemctl edit postgresql-11

Add following in editor and save.

[Service]
User=pgdba
Group=pgdba
Environment="PGDATA=/data02/11/data"

Create user and group

sudo useradd -m pgdba

Initialize cluster

PGSETUP_INITDB_OPTIONS='-k -U pgdba' sudo -E /usr/pgsql-11/bin/postgresql-11-setup initdb

Above command will create PostgreSQL cluster in /data02/11/data. PGSETUP_INITDB_OPTIONS env is used to set initdb parameters.

• -k Enable checksums on data pages
• -U pgdba will create superuser pgdba rather than postgres
• -E preserve env with sudo
• initdb initialize cluster

Caveats

There mainly two issues that we have to deal with

1. Unix Socket Location

Default for unix_socket_directories is set to /var/run/postgresql/ and this directory is owned by postgres user/group.

sudo chown -R pgdba:pgdba /var/run/postgresql/

2. Directory and Files ownership

Due to hard coding /usr/pgsql-11/bin/postgresql-11-setup has User and Group set to postgres:postgres. I have a working patch to fix this, which I plan to submit soon. For now we manually fix the permissions:

sudo chown -R pgdba:pgdba /data02/11/data

Enabling service

Enable PostgreSQL to start automatically on server reboot.

sudo systemctl enable postgresql-11

Starting service

For now, start PostgreSQL service manually.

sudo systemctl start postgresql-11

Connect to PostgreSQL database

sudo -u pgdba psql -d postgres

Pass database name as it is different than owner name.

psql (11.2) Type "help" for help.
postgres=# select version();
 version
--------------------------------------------------------------------------------------------------------
 PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.2.1 20181215 (Red Hat 8.2.1-6), 64-bit
(1 row)

postgres=# show data_directory;
 data_directory
-----------------
 /data02/11/data
(1 row)

postgres=# \du
List of roles
 Role name | Attributes                                                 | Member of
-----------+------------------------------------------------------------+-----------
 pgdba     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# \q

You are connected to PostgreSQL, database postgres as pgdba user (superuser).

Stopping service

sudo systemctl stop postgresql-11

This guide gets you started for running a single PostgreSQL instance using an RPM-based installation. But what if you want to run multiple instances of PostgreSQL in the same environment? Stay tuned for Part 2 "Running Multiple Instances"