How To Install PostgreSQL 12 On CentOS 8

This blog post outline the steps on how to install PostgreSQL 12 on CentOS 8.

[01]. Integrate Postgres Yum repo with CentOS. This is needed for systems and patch management, and provide automatic updates for all supported versions of PostgreSQL.

$ sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

[02]. Disable the built-in PostgreSQL 12 on CentOS 8.

$ sudo dnf -qy module disable postgresql

[03]. Install both server and client packages

$ sudo dnf -y install postgresql12 postgresql12-server

Below steps outline how to initialize the postgres cluster with custom data and wal locations.

[04]. Create a dir for data location and change the permission

$ sudo mkdir -p /srv/pgsql/12/data/
$ sudo chown postgres:postgres /srv/pgsql/12/data/

[05]. Create a dir for log location and change the permission

$ sudo mkdir -p /pgwal/data/
$ sudo chown postgres:postgres /pgwal/data/

Note: You can use separate mount points for above data and wal locations. Please make sure the dirs created above are empty as well.

[06]. Initialize the cluster You can use initdb to intialize the cluster by passing additional args to state data and wal locations as shown below with env variable, PGSETUP_INITDB_OPTIONS.

$ sudo PGSETUP_INITDB_OPTIONS="-D /srv/pgsql/12/data/ -X /pgwal/data/" /usr/pgsql-12/bin/postgresql-12-setup initdb

Note: After initializing the cluster you could see data and wal dirs have files on it.

[07]. Modify the original unit file The original unit file is /usr/lib/systemd/system/postgresql-12.service but it is not recommended to modify it directly. We need to modify the unit file to specify the $PGDATA to the custom dir that we created above. To do this you can use the command below:

$ sudo systemctl edit postgresql-12

Once you get the editor you can paste the contents below and save the file. This file will be saved as override.conf in /etc/systemd/system/postgresql-12.service.d/ dir.

[Service]
Environment=PGDATA=/srv/pgsql/12/data/

You can see the contents of override.conf file as below:

$ cat /etc/systemd/system/postgresql-12.service.d/override.conf

[08]. Reload the daemon Since we modified the original unit file which creates the postgres service, you need to reload the daemon using the command below:

$ sudo systemctl daemon-reload

[09]. Enable postgres service

$ sudo systemctl enable postgresql-12

[10]. Start the service

$ sudo systemctl start postgresql-12

You’re done. At this point, you have postgresql installed, up and running, the next step is to connect to it.

You can double check whether postgres service is still running by using the command below:

$ systemctl status postgresql-12

[11]. Connect to Postgres The psql is the command line interface which you can use to interact with postgres. Use the below command to connect to it.

$ psql -U postgres 

Probably you’ll see this error message.

psql: error: could not connect to server: FATAL:  Peer authentication failed for user "postgres"

That is because, by default postgres will deny access due to the rules in pg_hba.conf file located in the data dir. So you need to change the file as below:

# "local" is for Unix domain socket connections only
local   all             all                                     trust

Change the local connection METHOD from peer to trust and restart postgresql-12 service. Once that is done, you should be able to login as stated above with psql.

If everything is successful, you will see the postgres prompt which you can execute commands. However, we just completed the installation, there are few more things to do as part of post installation steps such as confifuring client connections (pg_hba.conf), changing server configs (postgresql.conf), probably to install & setup client connection pooling (pg_pool or pgbouncer), etc.

About the post header picture: I took this pic during the Salt Lick Trail hike on Sep, 2020.