Document how to set up local Postgres instance

[ConradJohnston]

To create the database, verdi quicksetup needs to be able to connect to Postgres. On most systems, where Postgres is installed system-wide, only the user postgres can connect to the database, or system users with superuser permissions in Postgres. Quicksetup tries to connect as the current user. If this user is not a Postgres superuser or the postgres user, then it tries to use sudo to become the postgres user.

This works well assuming that the user has sudo rights. However, for many academic users it may be the case that they do not have sudo rights on their institutional machines and so this becomes a barrier to setting up and using AiiDA.

To lower the barrier to use, the functionality should be provided to setup a local instance of Postgres so that the user can create databases without sudo.
In principle the user can create their own instance of a Postgres server via initdb, setting the directory to a location in their home directory, and then modifying the configuration file to use a socket other than the default. They can then start this server and connect to it and can create new databases. The difficulty here is ensuring that this private instance of Postgres is restarted on start-up of the machine. This is already partially solved in the Docker script, although the user's home directory could be used for the socket and postgres instance files instead. Some inspiration about adding services to systemd for user can be found here. The way of starting services is, of course, OS dependant.

Some discussion is needed to decide if we should support this mode of running AiiDA,

Related to #1382 , #2004 .

[ltalirz]

Just mentioning that this concerns rabbitmq as well, and that this issue is very relevant to installing aiida (including rabbitmq and postgres) via conda, where the user will need to manage the postgres and rabbitmq services.

[zhubonan]

Having user instance of the postgres server would be nice. At the moment we can have separate aiida environments via AIIDA_PATH but the database is sill shared as system wide.
It is hence possible to step onto the db of another profile in a different environment if the same dbname/dbusername are used.

[ltalirz]

Just mentioning that one can already install postgresql and rabbitmq via

conda install -c conda-forge aiida-core.services

One can then use Chris's activate-aiida package to manage the DB/rabbitmq server.
It's on my to-do list to strip down this package a bit so that it does what is needed.

[sphuber]

I personally feel that writing documentation on how to install a PostgreSQL instance is beyond the scope of the AiiDA documentation. With the recent documentation revamp, I feel that we have a few ways of installing AiiDA that are well documented so I would be in favor of closing this issue. Feel free to reopen if you think there still is a need for more documentation of this additional install method