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
$ sudo -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	Get connection information
Example	Example	Example

Resources

Introduction to PostgreSQL for MySQL Users