Getting started with PostgreSQL

PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.

Getting started with PostgreSQL
Photo by Caspar Camille Rubin / Unsplash

Install the PostgreSQL from YUM repository

Install the repository RPM:

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

Install PostgreSQL:

$ yum install -y postgresql15-server

Create a Database Cluster

Before you can do anything, you must initialize a database storage area on disk. We call this a database cluster.

$ su - postgres

-bash-4.2$ /usr/pgsql-15/bin/pg_ctl --help
pg_ctl is a utility to initialize, start, stop, or control a PostgreSQL server.

Usage:
  pg_ctl init[db]   [-D DATADIR] [-s] [-o OPTIONS]
  pg_ctl start      [-D DATADIR] [-l FILENAME] [-W] [-t SECS] [-s]
                    [-o OPTIONS] [-p PATH] [-c]
  pg_ctl stop       [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
  pg_ctl restart    [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
                    [-o OPTIONS] [-c]
  pg_ctl reload     [-D DATADIR] [-s]
  pg_ctl status     [-D DATADIR]
  pg_ctl promote    [-D DATADIR] [-W] [-t SECS] [-s]
  pg_ctl logrotate  [-D DATADIR] [-s]
  pg_ctl kill       SIGNALNAME PID

Common options:
  -D, --pgdata=DATADIR   location of the database storage area
  -s, --silent           only print errors, no informational messages
  -t, --timeout=SECS     seconds to wait when using -w option
  -V, --version          output version information, then exit
  -w, --wait             wait until operation completes (default)
  -W, --no-wait          do not wait until operation completes
  -?, --help             show this help, then exit
If the -D option is omitted, the environment variable PGDATA is used.

Options for start or restart:
  -c, --core-files       allow postgres to produce core files
  -l, --log=FILENAME     write (or append) server log to FILENAME
  -o, --options=OPTIONS  command line options to pass to postgres
                         (PostgreSQL server executable) or initdb
  -p PATH-TO-POSTGRES    normally not necessary

Options for stop or restart:
  -m, --mode=MODE        MODE can be "smart", "fast", or "immediate"

Shutdown modes are:
  smart       quit after all clients have disconnected
  fast        quit directly, with proper shutdown (default)
  immediate   quit without complete shutdown; will lead to recovery on restart

Allowed signal names for kill:
  ABRT HUP INT KILL QUIT TERM USR1 USR2

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

Create the data directory:

$ mkdir -p /mnt/pgsql15/data
$ chown -R postgres /mnt/pgsql15

Create the database cluster:

$ su - postgres -c "/usr/pgsql-15/bin/initdb -D /mnt/pgsql15/data"

$ ls /mnt/pgsql15/data
base log pg_dynshmem pg_ident.conf pg_multixact pg_replslot pg_snapshots  pg_stat_tmp pg_tblspc PG_VERSION pg_xact postgresql.conf
global pg_commit_ts pg_hba.conf pg_logical pg_notify pg_serial pg_stat pg_subtrans pg_twophase pg_wal postgresql.auto.conf

Start and stop the database server:

-bash-4.2$ /usr/pgsql-15/bin/pg_ctl -D /mnt/pgsql15/data -l logfile start
waiting for server to start.... done
server started

-bash-4.2$ /usr/pgsql-15/bin/pg_ctl -D /mnt/pgsql15/data stop
waiting for server to shut down.... done
server stopped

-bash-4.2$ /usr/pgsql-15/bin/pg_ctl -D /mnt/pgsql15/data -l logfile start
waiting for server to start.... done
server started

-bash-4.2$ ls -ltr
total 4
drwx------ 4 postgres postgres  51 Feb 13 20:48 15
-rw------- 1 postgres postgres 374 Feb 13 22:26 logfile

Restart the database server:

-bash-4.2$ /usr/pgsql-15/bin/pg_ctl -D /mnt/pgsql15/data -l logfile restart
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started

Check the database service:

-bash-4.2$ /usr/pgsql-15/bin/pg_ctl -D /mnt/pgsql15/data status
pg_ctl: server is running (PID: 77038)
/usr/pgsql-15/bin/postgres "-D" "/mnt/pgsql15/data"

-bash-4.2$ ps aux | grep postgres | grep -v grep
root     73095  0.0  0.0 191900  4348 pts/0    S    21:49   0:00 su - postgres
postgres 73096  0.0  0.0 115560  3456 pts/0    S    21:49   0:00 -bash
postgres 77434  0.1  0.0 401372 23652 ?        Ss   22:26   0:00 /usr/pgsql-15/bin/postgres -D /mnt/pgsql15/data
postgres 77435  0.0  0.0 253204  5692 ?        Ss   22:26   0:00 postgres: logger
postgres 77436  0.0  0.0 401524  5728 ?        Ss   22:26   0:00 postgres: checkpointer
postgres 77437  0.0  0.0 401508  5784 ?        Ss   22:26   0:00 postgres: background writer
postgres 77439  0.0  0.0 401508 10372 ?        Ss   22:26   0:00 postgres: walwriter
postgres 77440  0.0  0.0 402992  8864 ?        Ss   22:26   0:00 postgres: autovacuum launcher
postgres 77441  0.0  0.0 402976  6904 ?        Ss   22:26   0:00 postgres: logical replication launcher
postgres 77480  0.0  0.0 155468  3784 pts/0    R+   22:27   0:00 ps aux

Using psql

psql is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file or from command line arguments. In addition, psql provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks.

Create a database:

$ su - postgres
Last login: Mon Feb 13 20:12:36 UTC 2023 on pts/0

-bash-4.2$ psql
psql (15.2)
Type "help" for help.

postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
(3 rows)


postgres=# create database testdb;
CREATE DATABASE

postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
(4 rows)

Drop database:

postgres=# drop database if exists testdb;
DROP DATABASE
postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
(3 rows)

Manipulate the database remotely:

psql --host=10.13.121.243 --port=5432 --username=postgres -w -c "create database testdb"
psql --host=10.13.121.243 --port=5432 --username=postgres -w -c "\l"
psql --host=10.13.121.243 --port=5432 --username=postgres -w -d testdb -c "\dt+"

Reference