# Database Setup

> The flexter database setup based on Official PostgreSQL documentation

---

Site [llms.txt](/flexter/master/llms.txt)

---

## Installing PostgreSQL

Please follow the installation guidelines at [PostgreSQL Official downloads](https://www.postgresql.org/download) for your operating system.

* Required PostgreSQL version: 12+

## Flexter Metadata Database Setup

For creating the database and changing access rules, please refer to the [Official PostgreSQL documentation](https://www.postgresql.org/docs/12/auth-pg-hba-conf.html).

### Creating user and database

For creating the flexter database, use the command-line as a postgres user. 

It works the same way on all platforms, having the createuser, createdb and psql commands available in the path.

```console
$ sudo -u postgres createuser -DiRSlEP --no-replication "flex2er"
  Enter password for new role:
  Enter it again:

$ sudo -u postgres createdb -O "flex2er" -E "UTF8" "x2er" "Flexter Metadata"

$ sudo -u postgres psql -d "x2er" -c "CREATE SCHEMA flexmeta AUTHORIZATION flex2er;ALTER USER flex2er SET search_path TO flexmeta,public"
```

### Access rules

Because the postgres configuration files and the restart command vary in each platform, below is demonstrated which files are changed as an example.

#### Exposing the PostgreSQL user and database - `pg_hba.conf`

The `pg_hba.conf` file defines access across all postgresql databases and users. The changes are targeted to expose the flexter metadata user and database correctly.

```bash
# example editing the pg_hba.conf on PostgreSQL 12
sudo -u postgres vi /var/lib/pgsql/12/data/pg_hba.conf
```

```text
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    x2er            flex2er         all                     md5
```

The word "all" could also accept other options:
* **all**  => match any IP address
* **samehost** => match any of the server's own IP addresses
* **samenet** => match any address in any subnet that the server is directly connected to
* **ip[/cidr]** => IPv4 or IPv6 address and optionally the CIDR mask length. Ex: 192.168.0.10, 172.20.143.0/24
* **hostname** => DNS Hostname. Ex: foo.example.com

#### Exposing the PostgreSQL to other machines - `postgresql.conf`

In clustered environments, the cluster nodes must have access to the PostgreSQL. Because the default mode is local only, the `listen_addresses` property be must set. Based on [Official PostgreSQL documentation](https://www.postgresql.org/docs/12/runtime-config-connection.html).

```bash
# example editing the postgresql.conf on PostgreSQL 12
sudo -u postgres vi /var/lib/pgsql/12/data/postgresql.conf
```

```properties
listen_addresses = '*'
```

The expression '*' could also accept other options:
* **'\*'**  => corresponds to all available IP interfaces
* **'localhost'** => allows only local TCP/IP "loopback" connections (default)
* **'ip'** => IPv4 or IPv6 address. Ex: '192.168.0.10'
* **'host'** => A host name. Ex: 'foo.example.com'

#### Restarting the Database after the changes

```bash
# example show to restart the database
sudo systemctl restart postgresql-12
```