Installing PostgreSQL differs from OS to OS. For the basic installation for your operating system, see some of these links:
Important: Installation of a database is most likely subject to your organization's IT policies. Please coordinate with that organization and install appropriately.
Creating Your Database
Flow Enterprise requires 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 database administrator (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 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;
CREATE EXTENSION IF NOT EXISTS ltree;
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. When inputting your password or DBA user, which are surrounded by angle brackets (<>) above, exclude the angle bracket symbols.
A note about the LTREE Extension
The LTREE extension for PostgreSQL is a standard extension for PostgreSQL that requires admin/owner 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 set up when you created your database, or a user that has been granted the appropriate permissions.
If you encounter errors while adding the ltree extension, your installation of Postgres may be missing the required contrib files. Contact your DBA to ensure those are added.
Testing Database Connection
To test your database connectivity, run the following command from a shell prompt where the psql client is installed, using properly substituted values:
psql --host=<your database host> --port=<your database port> --username=<your dba user> <your database name>
Once you have the server 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.
Tip: You might set the configuration directive listen_addresses to * if you expect the server to listen on multiple network interfaces.
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, 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 Flow Server>/32 md5
If you intend to use this PostgreSQL server for more than just Flow Enterprise or if you intend to have multiple nodes, 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 22.214.171.124/16 subnet range, you could add this line to allow access from the entire network:
host all all 126.96.36.199/16 md5
Important: There are large security implications for allowing access to a broader range of IP addresses. 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 you are running PostgreSQL on your host server alongside Flow Enterprise, there are some extra steps you may need to take. Please note that this is not recommended as it can cause performance issues. However, we realize that some users may do this for evaluation purposes.
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 database. Instead, use the full IP address of the server.
Grant Access to Docker/Kubernetes 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 and Kubernetes. You can find these networks by executing:
sudo ifconfig -a
Once you find them, you will need to add their entire subnet to the pg_hba.conf. In the case of our example below, you will note that both networks are /16 networks, so we will need to plan accordingly. You should add the following 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
# kubernetes network access
host all all 10.96.0.0/16 md5
host all all 10.32.0.0/16 md5
We recommend the following parameters as a starting point for a well-tuned database.
- shared_buffers: Because of the amount of data that is processed you must increase the shared buffers memory limit. This should be set to 25% of the host server memory. For example, if your server has 32GB memory, your shared_buffers should be 8GB.
- work_mem: Set work_mem to 256MB for typical production. That is the recommended maximum. If the host has 32GB or less, leave work_mem at 128MB.
- default_statistics_target: Increase the default number of rows that will be sampled when gathering statistics to 10,000.
- max_connections: Flow Enterprise uses a large number of DB connections for the background processing and analysis of your code, tickets, and pull requests. We recommend 1000. Please see the PostgreSQL documentation for more information and considerations about this parameter in your specific environment.
- listen_address: as noted above, ensure your listen_address is set correctly.
You or your Database Administrator can do this by editing the postgresql.conf file and then restarting the database.
If you need help, please email firstname.lastname@example.org for 24/7 assistance.