Remember to Set Correct System Locale before install PostgreSQL. This will make life easier.

Install PostgreSQL

sudo apt -y install postgresql postgresql-contrib

• config file: sudo vim /etc/postgresql/14/main/postgresql.conf
• config file: sudo vim /etc/postgresql/14/main/pg_hba.conf

Restart PostgreSQL

sudo /etc/init.d/postgresql restart
sudo /etc/init.d/postgresql status

Create user and database

Create user and database by SQL statements

sudo -u postgres psql postgres -c "CREATE USER {{ username.value }} WITH PASSWORD '{{ password.value }}';"
sudo -u postgres psql postgres -c "CREATE DATABASE {{ database.value }} ENCODING 'UTF8' OWNER {{ username.value }};"

Create user and database using command line tools (interactive)

sudo -u postgres createuser {{ username.value }}
sudo -u postgres createdb --encoding='UTF8' --owner={{ username.value }} {{ database.value }}

Manage PostgreSQL database

PostgreSQL management console: psql

sudo -u postgres psql postgres
sudo -u postgres psql {{ database.value }}

# Show all databases
\l+
# Connect to database
\c {{ database.value }}
# SHOW TABLES
\d+
# DESCRIBE TABLE
\d+ table
# Quit
\q

Backup PostgreSQL database {{ database.value }}

sudo -u postgres pg_dump --format=custom -U postgres {{ database.value }} > {{ filename }}

Restore PostgreSQL database {{ database.value }}

sudo -u postgres pg_restore -d {{ database.value }} {{ filename }}
sudo -u postgres pg_restore --no-owner --role={{ username.value }} -d {{ database.value }} {{ filename }}

Run SQL statement on the database {{ database.value }}

sudo -u postgres psql {{ database.value }}
sudo -u postgres psql {{ database.value }} -c '{{ sql.value }}'

How to Connect PostgreSQL from another machine (optional)

1) Listen to *

sudo {{ texteditor.value }} /etc/postgresql/14/main/postgresql.conf

listen_addresses = '*'

2) Allow remote user access

sudo {{ texteditor.value }} /etc/postgresql/14/main/pg_hba.conf

host    all              all            0.0.0.0/0               md5

3) Restart PostgreSQL

sudo /etc/init.d/postgresql restart

Set postgresql admin password (optional)

The admin role is postgres, we normally manage the database locally, thus that do not need any password. However, if connecting from another server to manage the database, the password may be required.

sudo -u postgres psql postgres

\password postgres
\q

Desktop PostgreSQL Management Tool

pgAdmin