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.
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+"
