Database Setup

The flexter database setup based on Official PostgreSQL documentation

Installing PostgreSQL

Please follow the installation guidelines at PostgreSQL Official downloads for your operation system.

  • Required PostgreSQL version: 12+

Flexter Metadata Database Setup

For creating the database and changing access rules please refer to the Official PostgreSQL documentation.

Creating user and database

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

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

$ su - postgres

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

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

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 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 targeting expose the flexter metadata user and database correctly.

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

# 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.

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

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

# example show to restart the database
$ systemctl restart postgresql-12