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
);
| Command | Action | Description or Notes |
|---|---|---|
| \q | Quit | |
| \conninfo | Connection information | Get current connection information |
| \d | List | List tables, views, and sequences |
| \dt | List tables |