Manage PostgreSQL

// Install PostgreSQL
# Install dependencies
apt install bison \
            build-essential \
            clang \
            flex \
            libbsd-dev \
            libkrb5-dev \
            libldap2-dev \
            liblz4-dev \
            libossp-uuid-dev \
            libpam0g-dev \
            libperl-dev \
            libreadline-dev \
            libselinux1-dev \
            libssl-dev \
            libxml2-dev \
            libxslt-dev \
            libzstd-dev \
            llvm \
            pkg-config \
            python3 \
            python3-dev \
            python3-pip \
            tcl \
            tcl-dev \
            uuid \
            uuid-dev \
            zlib1g-dev -y

# Create user
useradd -M -s /usr/sbin/nologin postgres

# The version of your PostgreSQL instance
# To see all exists versions, go to https://ftp.postgresql.org/pub/source
version=$(curl -sL https://ftp.postgresql.org/pub/source/ | grep -oP '[0-9]*\.[0-9]+' | sort -rV | head -n 1)

wget https://ftp.postgresql.org/pub/source/v$version/postgresql-$version.tar.gz
gunzip postgresql-$version.tar.gz
tar xvf postgresql-$version.tar
rm -r postgresql-$version.tar
mv postgresql-$version /usr/local/pgsql
cd /usr/local/pgsql
rm -r .* COPYRIGHT HISTORY INSTALL README
ln -s /usr/bin/cc /usr/bin/CMD
bash configure --prefix /usr/local/pgsql --with-CC=CMD --with-llvm --with-tcl --with-perl --with-python --with-gssapi --with-pam --with-ldap --with-selinux --with-libedit-preferred --with-uuid=LIB --with-ossp-uuid --with-libxml --with-libxslt --with-lz4 --with-zstd --with-ssl=LIB --with-openssl
make install

ln -s /usr/local/pgsql/bin/* /usr/bin

chmod -R 777 /usr/local/pgsql
sudo -u postgres initdb -D /usr/local/pgsql/data
sudo -u postgres pg_ctl -D /usr/local/pgsql/data start

# Now you can login in your PostgreSQL instance with:
sudo -u postgres psql

# To login with root, do following, after the command above:
sudo -u postgres psql << EOF
CREATE USER root SUPERUSER LOGIN REPLICATION BYPASSRLS;
CREATE DATABASE root OWNER root;
EOF

# Let's create a seperate user
# Change USERNAME and PASSWORD
sudo -u postgres psql << EOF
CREATE USER USERNAME \
SUPERUSER \
CREATEDB \
CREATEROLE \
INHERIT \
LOGIN \
REPLICATION \
BYPASSRLS \
PASSWORD \
'PASSWORD'
EOF

# If extensions are needed, do the following:
cd /usr/local/pgsql/contrib/<extension_name>
make install
## Or for all extensions:
cd /usr/local/pgsql/contrib
make install
## Then this:
psql # If you have created the root user with its database
sudo -u postgres psql # If you have not created root
CREATE EXTENSION <extension_name>;

// Uninstall PostgreSQL
# Stopping PostgreSQL
sudo -u postgres pg_ctl -D /usr/local/pgsql/data stop
# Uninstall PostgreSQL
rm -r /usr/local/pgsql
for bin in $(ls /usr/local/pgsql/bin); do
rm /usr/bin/$bin
done