Installing PostgreSQL differs from OS to OS. For the basic installation for your operation system, see some of these links:
Important: Installation of a database is most likely subject to your organizations IT policies. Please coordinate with that organization and install appropriately.
Creating Your Database
Flow Enterprise 2020.1.x and higher needs a PostgreSQL 12.1 database provisioned with a user that can alter the database and schema. The following steps are required:
- Create a new database on the server called "gitprime" using your DBA credentials.
- Create an application user called "gitprime_app_user" that has ownership of the new database. This user should be the "owner" of the database and all of the objects within it. Upgrades can fail in the future if ownership is not consistent.
- Grant all permissions to the database to both the gitprime_app_user and the DBA (data base administrator) user.
To accomplish this task, open an SQL client to your database server using the DBA user that has privileges to create new databases and users. Then, simply execute the following script*:
DROP DATABASE IF EXISTS gitprime;
CREATE DATABASE gitprime TEMPLATE template0 ENCODING 'UTF8' LC_CTYPE 'en_US.UTF-8' LC_COLLATE 'en_US.UTF-8';
DROP USER IF EXISTS gitprime_app_user;
CREATE ROLE gitprime_app_user PASSWORD '<A_SECURE_PASSWORD_PLEASE>' LOGIN;
GRANT ALL PRIVILEGES ON DATABASE gitprime TO gitprime_app_user;
GRANT ALL PRIVILEGES ON DATABASE gitprime TO <your DBA user>;
ALTER DATABASE gitprime OWNER TO gitprime_app_user;
Note: Be sure that you replace the password with a secure password that you create. Also be sure you replace the name of your DBA user.
Adding the LTREE Extension
The LTREE extension for PostgreSQL is a standard extension for PostgreSQL that requires super user permissions to install. In most PostgreSQL installations, this is the "postgres" user, or another use that has been designated as a "super user". In RDS installations, this will be the DBA user you setup when you created your database, or a user that has been granted the appropriate permissions.
To connect to your database correctly, using the super user, the following command can be executed from your shell window, with properly substituted values:
psql --host=<your database host> --port=<your database port> --username=<your dba user> <your database name>
Once you have successfully connected to the database using the super user, you should execute the following SQL:
CREATE EXTENSION IF NOT EXISTS ltree;
Once you have the server up and running and have created your database, you will need to allow access to your database user. This is accomplished by following these steps:
Listen on the Correct Network Interface
PostgreSQL, by default on most OSes, only accepts connections on "localhost." This means that your Flow Enterprise server will most likely not be able to connect to the server. To remedy this, you need to edit the main postgresql.conf configuration file. The location of this file will vary from OS to OS, please use your OS's specific documentation to locate the file.
Once you have located it, edit it and find the configuration directive 'listen_addresses'. This setting needs to be set to the IP address of the server or "*". This will enable PostgreSQL to listen on the IP addresses that your other servers will use to connect to it.
Once you have completed the change, restart PostgreSQL.
Allowing Access to the Flow Enterprise User
Now that PostgreSQL is listening on the correct IP and port, you need to allow access to the server from the Flow Enterprise server and user. By default, PostgreSQL does not allow access to any servers or users but the localhost and the DBA.
To remedy this, edit the file pg_hba.conf. This file's location will vary from OS to OS, please see your documentation to locate the file. You will need to add the following lines to your file, substituting the correct IP address and username of the Flow Enterprise Server and the database user it will use to authenticate with the server:
host all <username> <IP address of GPE>/32 md5
If you intend to use this PostgreSQL server for more than just Flow Enterprise, you can open the access to a wider audience by using a network subnet mask in place of the IP address and allowing multiple usernames. For example, if your internal network lives in the 184.108.40.206/16 subnet range, you could add this line to allow access from the entire network:
host all all 220.127.116.11/16 md5
Important: There are large security implications here. Please only open the most narrow access you require. Make sure that that access is acceptable according to your organizations security policies and practices.
Running PostgreSQL on the Host Server
If, for testing purposes, you are running PostgreSQL on your host server alongside Flow Enterprise, there are some extra steps you may need to take. Please not that this is NOT recommended as it can cause performance issues. However, we realize that some users may do this for evaluation purposes. If you have chosen this option, please take note of the following:
Important: You must NOT use "localhost" as the hostname of the database in the settings screen. This causes a loopback problem with Docker that won't allow Flow Enterprise to communicate with the DB. Instead, use the full IP address of the server.
Grant Access to Docker Networks
In the pg_hba.conf file, you will need to grant two networks access to the DB. These are the docker virtual networks created by docker. You can find these networks by executing:
sudo ifconfig -a
This will give you output similar to the following:
docker0 Link encap:Ethernet HWaddr 02:42:5c:47:5b:e7
inet addr:172.17.0.1 Bcast:0.0.0.0 Mask:255.255.0.0
inet6 addr: fe80::42:5cff:fe47:5be7/64 Scope:Link
UP BROADCAST MULTICAST MTU:1500 Metric:1
RX packets:0 errors:0 dropped:0 overruns:0 frame:0
TX packets:4 errors:0 dropped:0 overruns:0 carrier:0
RX bytes:0 (0.0 B) TX bytes:348 (348.0 B)
docker_gwbridge Link encap:Ethernet HWaddr 02:42:a0:2c:f5:d7
inet addr:172.18.0.1 Bcast:0.0.0.0 Mask:255.255.0.0
inet6 addr: fe80::42:a0ff:fe2c:f5d7/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:246541 errors:0 dropped:0 overruns:0 frame:0
TX packets:290819 errors:0 dropped:0 overruns:0 carrier:0
RX bytes:43064878 (43.0 MB) TX bytes:2120434096 (2.1 GB)
You will be looking for two networks: docker0 and docker_gwbridge. Once you find them, you will need to add their entire subnet to the pg_hba.conf. In the case of our example above, you will not that both networks are /16 networks, so we will need to plan accordingly. You should add the folowing to the file, changing the IP address and mask to match your networks:
# docker0 network access
host all all 172.17.0.0/16 md5
# docker_gwbridge network access
host all all 172.18.0.0/16 md5
Because of the amount of data that is processed it is necessary to increase the shared buffers memory limit. You can do this by editing the postgresql.conf file. Find the shared_buffers option and set it to 4GB.
# RESOURCE USAGE (except WAL)
# - Memory -
shared_buffers = 4GB # min 128kB
Flow Enterprise uses a large number of DB connections for the background processing and analysis of your code, tickets, and pull requests. Depending on how you install and configure PostgreSQL, you may find that you hit issues with the number of connections to the database. In many installations, the default setting is 100.
To change this, you need to edit the postgresql.conf file. This file's location will vary from OS to OS, please see your documentation to locate the file. To change the maximum connections, simply find the "max_connections" setting and change it to a higher number. We recommend 1000. It should also be noted that, if you change this settings, there can be some memory considerations. Please see the PostgreSQL documentation for more information.
Validate Database Connectivity
The last step to the database setup is to validate that the docker server can connect to the new database server. To validate the setup, ssh to your docker server.
# psql -h <database server ip/hostname> gitprime gitprime_app_user
You will be prompted for the database password. Make sure you can successfully connect to the database as the gitprime_app_user.
If you need help, please email firstname.lastname@example.org for 24/7 assistance.