Installation_PostgreSQL.txt

# Notes

PostgreSQL Server is a full database service solution (RDBSM). Handling interaction, like multiple enterprise schemas, databases, users and backups.
These all are and can be handled outside the scope of the LifeLog application.
However, it is not required or recommended to have a fully managed database system service, just to use for the LifeLog app.


Modify the following anon driver property tag in dbLifeLog/main.cnf to specify:
<<DBI_SOURCE<DBI:Pg:host=localhost>

The alias is by default assumed the actual database name as well as the user.

Modify the following anon property, to have the SQL Server provide multiple users as different aliases for the loging into a specified single database.
<<DBI_MULTI_USER_DB<0>
to something like, to make logins now for the database users:
<<DBI_MULTI_USER_DB<lifelog>
Where 'lifelog' is the server managed database.

Hope all works for you, and happy logging!


# Install PostgreSQL
sudo mkdir /usr/include/postgresql
## Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

## Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

## Update the package lists:
sudo apt-get update -y
sudo apt upgrade -y

## Install the latest version of PostgreSQL.
## If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt-get -y install libpq-dev
sudo apt-get -y install postgresql-12

## Optional -> Enable port 5432 for PG server for local network on server.
sudo uwf allow 5432/tcp
sudo vim /etc/postgresql/12/main/postgresql.conf to modify--> listen_addresses = '*'
sudo vim pg_hba.conf ->
host    all             all              0.0.0.0/0                       md5
host    all             all              ::/0                            md5

# New PG version: If want remote client access add in server /etc/postgresql/14/main/pg_hba.conf:
host    all             all             {client-server-access-ip}/24      scram-sha-256


sudo service postgresql restart

## Install required libpq-dev to compile test perl driver, later. 
sudo apt install libpq-dev

## Install perl driver.
sudo cpan DBD::Pg;


# To assign default Postgres SQL user roles.
sudo -u postgres psql -c "ALTER USER  postgres PASSWORD 'postgres';"
sudo -u postgres psql -c "CREATE ROLE lifelog WITH LOGIN SUPERUSER CREATEDB CREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT -1 PASSWORD 'xxxxxxx'";
sudo -u postgres psql -c "CREATE DATABASE lifelog;"
sudo -u postgres psql -c "grant all privileges on database lifelog to lifelog;"



## Start stop Postgres SQL
sudo service postgresql start

## Manually Obtain dep. if encountering lib problems. with i.e.:
wget https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-12/libpq-dev_12.4-1.pgdg20.04+1_amd64.deb
dpkg -i libpq-dev_12.4-1.pgdg20.04+1_amd64.deb
###Than run again driver install
sudo cpan DBD::Pg;

# Installing pgAdmin4
sudo apt install python3-pip
sudo python3 -m pip install https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v4.30/pip/pgadmin4-4.30-py3-none-any.whl

# Data directory location for backup
sudo -u postgres psql -c "SHOW data_directory;" | grep -G /
## Quick tar all
sudo tar czvf ~/postgresql.db.backup.tar $(sudo -u postgres psql -c "SHOW data_directory;" | grep -G /)

# Backup/Restore
# You must setup an ~/.pgpass, and change the following cat string to your systems setup.

cat "hostname:port:database:username:password" >> ~/.pgpass; chmod 600 ~/.pgpass;

# Backup with:
pg_dump --file "~/dev/LifeLog/dbLifeLog/bck-pg-$DB_NAME.sql"  $DB_NAME

# Now you can run your backup utility. To include your ~/dev/ directory.
# Or use and setup mine @see -> https://github.com/wbudic/B_L_R_via_sshfs
# p.s. If this saved you one day, let me know. :)

# Restore with:
psql $DB_NAME < "~/dev/LifeLog/dbLifeLog/bck-pg-$DB_NAME.sql"

###############################
    Important pgAdmin4 Notes
###############################

What pgAdmin4 and their documentation and installation doesn't describe, 
that the default web server is python based. The web based package will install the apache web server.
You most likely don't want that. And the pgAdmin-desktop, you possibly don't need that.
(Can use any browser instead) :)

I recommend to only install pgAdmin via python pip utility ONLY.


pip install flask

To run locally (you):

$ sudo mkdir /var/lib/pgadmin
$ sudo mkdir /var/log/pgadmin
$ sudo chown $USER /var/lib/pgadmin
$ sudo chown $USER /var/log/pgadmin
$ python3 -m venv pgadmin4
$ source pgadmin4/bin/activate
(pgadmin4) $ pip install pgadmin4
...
(pgadmin4) $ pgadmin4
NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

Email address: user@domain.com
Password: 
Retype password:
Starting pgAdmin 4. Please navigate to http://127.0.0.1:5050 in your browser.
 * Serving Flask app "pgadmin" (lazy loading)
 * Environment: production
   WARNING: Do not use the development server in a production environment.
   Use a production WSGI server instead.
 * Debug mode: off

# Have you forgotten master password to enter pgadmin4? Recreate by:
$ rm -rf /var/lib/pgadmin/*.db
$ pgadmin4

Before upgrades first:

(Notice - the previous user and password might not work anymore after:)
pip uninstall pgadmin4
pip install pgadmin4




(Sorry, I use, but don't program in python, to document here any further)

My Criticism:
From admin perspective, anything not systemd configurable, tend leave files allover the place.
This makes things harder to manage. Local user installed or developer system, are different thing on linux.
PostgreSQL as database system is server, and pgAdmin only a client. Don't mix in installation, two different placings.
This is neither secure, neither healthy to trouble shoot.