To begin super admin as user postgres

sudo su postgres
[enter your password]
psql

To allow user to login you have to change in /etc/postgresql/10/main/pg_hba.conf

# "local" is for Unix domain socket connections only
local   all             all                                     md5

The original value for local is peer and you have to change it to md5 which means to ask a password what ever is the user asking to connect to the database.

To connect to the database digiclear as steph user:

psql -d digiclear -U steph

\list List the databases
\q Quit psql
\c digiclear Connect to a spécific database digiclear
\dt List tables of the database

To create a backup of the database digiclear you can use pg_dump command. By default the output is going to the screen. So if you want to store the output to a file you have to redirect it to a file. It is quite useful to test the dump or to clone a database. The output of pg_dump is text with SQL commands. So it is very easy to change it.
Exemple: user steph, database digiclear, the dump of the database is stored in DB.SQL

pg_dump -O --clean -U steph digiclear > DB.SQL

Note: there is no -d to give database name to pg_dump.

You can inject a dump of the database as a text file. It is no more than SQL commands. For a database digiclear with the user steph and with a SQL file commands as DB.SQL, you have to write the following command:

psql -U steph -d digiclear < ./DB.SQL

To create a database digiclear and a user steph :

create database digiclear;
create user steph;

SELECT datname FROM pg_database WHERE datistemplate = FALSE;

to set the permissions for a specific user to access a database

exemple with the user steph and the database digiclear

GRANT ALL privileges ON DATABASE digiclear TO steph;

To give permission to login PostgreSQL database for user steph to the database digiclear

GRANT CONNECT ON DATABASE digiclear TO steph;

To change the password (????????) of a user that is allowed to connect to a database

alter user digiclear with password '????????';

To change the owner of a database you can ALTER the properties

ALTER DATABASE digiclear_db OWNER TO user_steph;

The command ALTER can do much more things to change configuration of a database

To change a value in a column of a table you can use the command UPDATE. Example is given for a table users and a column admin.

update users set admin=1 where user_id=163;

https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-delete/

DELETE FROM table_name WHERE table_name.id_to_delete > 'value condition' and table_name.id_to_delete_also != 'not this value' RETURNING table_name.idline;

To be able to change or rename some data in the database:

update operation_data set key = replace(key,'process_time','runningtime');

[ For more help just read: Replace instruction in PostGreSQL

exemple of manipulation regex to change a whole string in the database using regular expression

update machines set description = regexp_replace(description, '<img.+?>', '<div class="alert alert-warning">Images have been temporarily removed</div><!--<img>-->','g');

Be aware that the regexp_replace is a little bit tricky and a lot of case have been reported to idle the machines. We do not understand why.

the main command to use is pg_upgrade
all the details are there : https://www.postgresql.org/docs/current/pgupgrade.html

a summary of the command to do as exemple for upgrade from 12 to 14 (ubuntu 20.04 to ubuntu22.04)

sudo pg_dropcluster --stop 14 main
sudo pg_upgradecluster 12 main
sudo pg_dropcluster --stop 12 main
sudo update
sudo reboot

If you need it can be useful to start an old version of the server manually for safety reason

sudo su postgres
/usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main/ -c config_file=/etc/postgresql/10/main/postgresql.conf&