Getting started with PostgreSQL
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+"