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

$ createdb -O puser appdb

Usage

Log into PostgreSQL console using a specific user and DB

$ sudo -iu puser
$ psql -d appdata -U puser

Create a new table

puser=> CREATE TABLE demo(
 id serial PRIMARY KEY,
 email VARCHAR (100) UNIQUE NOT NULL,
 name  VARCHAR (50) UNIQUE NOT NULL
);

PostgreSQL console commands

Command	Action	Description or Notes
\q	Quit
\conninfo	Connection information	Get current connection information
\d	List	List tables, views, and sequences

Resources

Introduction to PostgreSQL for MySQL Users