I assume you started from my guide for hardened Raspberry Pi.
First steps
PostgreSQL is a powerful open-source relational database.
sudo apt update
sudo apt install postgresql
Check if the service is running.
systemctl status postgresq
Add firewall rule to allow connection.
ufw allow 5432/tcp
Hardening users and network configuration
Connect with user postgres and create new accounts.
sudo su postgres
createuser -P --interactive <new user login>
exit
Connect with the new user to control.
psql -d postgres -U vrampal -W
Remove the shell of user postgres.
sudo chsh -s /usr/sbin/nologin postgres
By default PostgreSQL is only available on loopback (localhost/127.0.0.1). Either you use an SSH tunnel to connect or change the configuration.
sudo vim /etc/postgresql/15/main/postgresql.conf
# change listen_addresses = 'localhost'
# into listen_addresses = '*'
sudo vim /etc/postgresql/15/main/pg_hba.conf
# add the following line
# host all <new user login> 0.0.0.0/0 md5
sudo systemctl restart postgresql
I generally continue configuring the database with graphical client like DBeaver.
Where are the data stored?
MariaDB will store the data in the folder /var/lib/postgresql and it’s a good idea to backup these data regularly.