create postgreSQL data base basil on Debian

[StefanPofahl]

This issue is the request for review and correction / modification.

Start with investigation of PostgreSQL configuration:

• Which PostgreSQL version (is a number), it is referred to afterwards as
• Which port
• Status: on/off
• Location of log file

su - postgres
postgress@host:~$ pg_lsclusters
exit

Standard port on Debian (and maybe all other linux-OS) is 5432 this is also expected from BASIL.
To see the configuration on this port type:

ss -lnpt | grep 5432

See which databases already exist:

su - postgres
postgress@host:~$ psql -c "\l" 
exit

To create the new data base, a new user has to be created.
In our case the owner with the right to login is: basil-admin and the name of the database is basil.
During the following sequence you have to type the password for your new database owner,
make sure you write it down. If successful you may see a warning regarding the password storage file:
»/var/lib/postgresql/.pgpass« , the commands are:

su - postgres
postgress@host:~$ createuser --createdb --login --pwprompt basil-admin 
postgress@host:~$ createdb --owner=basil-admin basil
postgress@host:~$ psql -d basil -c "SELECT current_database(), current_user;"
exit

Now you can check if the new owner has login privileges:

su - postgres
postgress@host:~$ psql
postgres=# SELECT rolname, rolcanlogin, rolcreatedb
postgres=# FROM pg_roles
postgres=# WHERE rolname = 'basil-admin';
postgres=# \q
postgress@host:~$ exit

Now it should be possible to login:

su - postgres
postgress@host:~$ psql -U basil-admin -d basil -h localhost -W
\q
exit

Now you can see the new database as user postgres with the command: psql -c "\l" (see above)

For your convenience
You may store the login credentials in a local password file:

nano ~/.pgpass
127.0.1.1:5432:basil:basil-admin:basil-admin-password
chmod 600 ~/.pgpass

Forgotten Password of Database Owner

su - postgres
postgress@host:~$ psql -U postgres
postgres=# ALTER ROLE "basil-admin" WITH LOGIN PASSWORD 'TempPass123!';
postgres=# SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname='basil-admin';
postgres=# \q
postgress@host:~$ exit

Troubleshooting
If you cannot login open the log file, e.g.:
nano /var/log/postgresql/postgresql-15-main.log
if you see the message:

Konfigurationsdatei »/etc/postgresql/15/main/postgresql.conf« enthält Fehler; nicht betroffene Änderungen wurden durchgeführt
or
“Configuration file ‘/etc/postgresql/15/main/postgresql.conf’ contains errors; unaffected changes were applied.”

this might not be correct, make sure you type in the correct password for the new user.
A restart might also be useful or a status output:

systemctl status postgresql
systemctl restart postgresql

A better insight into the issue might give:

su - postgres
postgress@host:~$ /usr/lib/postgresql/15/bin/postgres \
  --single -D /var/lib/postgresql/15/main \
  -c config_file=/etc/postgresql/15/main/postgresql.conf <<< "select 1;"
exit

The following massage will most likely show up:

“The lock file postmaster.pid already exists… is another postmaster running?”

It simply says that happens because you tried to start postgres manually while the cluster was already running (PID XYZ). PostgreSQL protects the data directory so you don’t start two servers on the same files.

To investigate the configuration file the following command or similar might help:

grep -n '^[^#]' /etc/postgresql/15/main/pg_hba.conf

[pellecchialuigi]

Thanks @StefanPofahl for this detailed guideline.
The only think I can add on top of this is that, if you have a postgresql server with the user created, BASIL will take care of the database creation at the time of the API initialization.

Do you think this guideline can fit into the BASIL documentation?

[StefanPofahl]

Yes, of course. :-)
How, do I active the automatic database creation in my bash-script?

P.S.:
Please could you help me also with the issue #192

[pellecchialuigi]

The database and the tables will be created at the API initialization event.
In other words, once you start the Apache server with the website configuration enabled.

[pellecchialuigi]

please look at the PR #208 to see how the deployment script is creating the postgresql database