PostgreSQL

Initial set-up

Install (the default is v10.23 as of 2023-10-13)

• Last tested on 2023-10-13 (RHEL 8.8 (Ootpa))

$ sudo yum module list | grep postgresql
$ sudo yum install @postgresql

to install a specific version

$ sudo yum install @postgresql:9.6

for the initial set-up

$ sudo postgresql-setup --initdb
 * Initializing database in '/var/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

for setting up a password for the postgres account

$ sudo passwd postgres
Changing password for user postgres.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.

enable PostgreSQL server at the boot time

$ sudo systemctl enable postgresql

Prerequisite for psql console

change to postgres user account

$ sudo -iu postgres

log into postgres console

$ psql
psql (10.23)
Type "help" for help.

postgres=#

Configuration

In order to force password when logging into postgresql console change ident to scram-sha-256 in /var/lib/pgsql/data/pg_hba.conf for IP local connections entries. This is the HBA (Host-based Authentication) configuration file for PostgreSQL. Restart afterwards.

Create a new PostgreSQL user account and a new DB

Create a Linux user account

$ sudo useradd puser
$ sudo passwd puser

log in as the admin PostgreSQL user

$ sudo -iu postgres

create a new PostgreSQL role for puser Linux user

$ createuser --interactive --pwprompt

while still logged in as postgres user (admin PostgreSQL user) create a new database named appdb for puser

$ created -O puser appdb

Usage

PostgreSQL

Command	Action	Description or Notes
\q	Quit
Example	Example	Example
Example	Example	Example

\q

Resources

Introduction to PostgreSQL for MySQL Users