PostgreSQL

GreenArrow uses a PostgreSQL database to store some of its data, including configuration and statistical data.

• Table of Contents
• Versions
• Main Configuration Files
• Logs
• Other Files
• Service Management
• Global Statement Timeout
• Remote Access
  • Configure PostgreSQL to Listen for Remote Connections
  • Authorize Remote Client Access
  • Create the read-only user account
• Tuning kernel.sem

Versions

New GreenArrow installations use PostgreSQL 16 by default. Older installations are upgradable:

• Upgrades from 9.5 to 16 use this procedure
• Upgrades from 8.3 to 16 require GreenArrow technical support

To check which version of PostgreSQL you’re using, examine the contents of the PG_VERSION file. For example:

# cat /var/hvmail/postgres/default/data/PG_VERSION
16

Main Configuration Files

PostgreSQL has two main configuration files:

1. /var/hvmail/control/greenarrow.conf. The postgres_max_connections directive is used for controlling the maximum number of connections allowed by PostgreSQL.
2. /var/hvmail/control/postgres.conf. This contains most of PostgreSQL’s other configuration directives.

Changes made to either of the above files take effect during the next PostgreSQL restart.

greenarrow.conf takes precedence over postgres.conf, so if you wish to change a setting that is configurable in both locations, you should use greenarrow.conf to avoid a conflict.

Configuration guidelines can be found in our Installation Guide’s PostgreSQL Tuning section.

Logs

GreenArrow logs PostgreSQL’s output to the /var/hvmail/log/postgres/ directory using multilog.

To tail this log, run:

tail -F /var/hvmail/log/postgres/current | tai64nlocal

multilog is a message logging system. It retains up to 20MB of PostgreSQL logs by default. You can optionally adjust this limit by editing the /var/hvmail/control/logconfig.multilog file. Our Service Logs page contains more information.

Other Files

Here are some other filesystem paths which you may find useful:

• The PostgreSQL binary directory, where commands like the psql client are located at /var/hvmail/postgres/default/bin/. Here’s an example of how to use it to log in as the greenarrow user:

  /var/hvmail/postgres/default/bin/psql -U greenarrow
  

• The default PostgreSQL data directory is located at /var/hvmail/postgres/default/data/. The contents of this directory include:
  • The pg_hba.conf file, which may be edited.
  • The postgresql.conf file. This file is automatically generated each time PostgreSQL is restarted, so any changes made directly to it will be overwritten. Please edit greenarrow.conf or postgres.conf (as described in the Main Configuration Files section) instead.
• The PostgreSQL usernames and passwords created during the installation process are in the /root/.pgpass file.

Service Management

The PostgreSQL service can be managed with the hvmail_postgres_manager script.

To restart PostgreSQL, run:

hvmail_postgres_manager restart

To stop PostgreSQL, run:

hvmail_postgres_manager stop

To start a downed PostgreSQL, run:

hvmail_postgres_manager start

You can also use the svc command to manage the /service/hvmail-postgres service, including stopping, starting, and restarting PostgreSQL. We recommend using the hvmail_postgres_manager script for these tasks, though, because it also manages services that may otherwise keep PostgreSQL connections open and prevent the PostgreSQL server from shutting down.

You can use hvmail_init status to check on PostgreSQL’s current state:

hvmail_init status | grep hvmail-postgres

Global Statement Timeout

GreenArrow’s PostgreSQL is configured with a global 24-hour statement timeout to prevent any rogue long-running SQL statements from having a performance or stability impact across the system.

Any statement that is expected to take longer than 24 hours should be modified to either (preferably) work in smaller units or (not preferably) SET statement_timeout to a different value.

Remote Access

There are three steps to set up remote access to PostgreSQL.

1. Configure PostgreSQL to listen for remote connections.
2. Authorize remote client connections.
3. Create a user account.

The instructions below assume that you are granting remote access to a client with an IP address of 1.2.3.4 to a server that will listen on IP address 10.20.30.40.

Configure PostgreSQL to Listen for Remote Connections

If PostgreSQL is already listening on an IP address accessible from the client system, you can skip this step.

You can also use these instructions to add or update the IPs where GreenArrow will listen for incoming connections.

1. Configure /var/hvmail/control/postgres.conf with the IP addresses to listen on, and enable TLS/SSL. Edit that file to add these lines (replacing 10.20.30.40 with your server’s IP address):

   listen_addresses = 'localhost, 10.20.30.40'
   ssl = on
   

   By default, PostgreSQL only listens on localhost. Removing localhost from this configuration will cause GreenArrow to break, so you should verify that localhost is listed in the listen_address list.
2. If this is the first time setting up remote access to PostgreSQL, copy Apache’s TLS certificate and key:

   cat /var/hvmail/var/tls/default_certificates/httpd.crt > /var/hvmail/postgres/default/data/server.crt
   cat /var/hvmail/var/tls/default_certificates/httpd.key > /var/hvmail/postgres/default/data/server.key
   chmod 600 /var/hvmail/postgres/default/data/server.key
   chown hvpostgres:hvpostgres /var/hvmail/postgres/default/data/server.key
   

3. Restart PostgreSQL, and verify that it comes back up.

Authorize Remote Client Access

1. Add a line to /var/hvmail/postgres/default/data/pg_hba.conf to allow password authentication from authorized IP addresses. Examples that limit access to a single IP and allow access from any IP are shown below:

   hostssl    all         all      1.2.3.4/32     password # Allow connections from this one IP
   hostssl    all         all      0.0.0.0/0      password # Allow connections to any IP
   

   hostssl is supported by PostgreSQL 16 and 9.5. It is not supported by PostgreSQL 8.3.
2. Reload the configuration file without restarting PostgreSQL (no downtime required):

   su - hvpostgres -c "/var/hvmail/postgres/default/bin/pg_ctl reload -D /var/hvmail/postgres/default/data"
   

3. Review your firewall configuration to allow the authorized IPs to connect to PostgreSQL on the IP you configured it to listen on.

Create the read-only user account

The following commands will create the database account greenarrow_ro, will grant read access to all tables in the public schema, and will update privileges to grant access to future tables in the public schema.

NOTE: Change the password variable before running the commands:

password=TEST_PASSWORD

echo "CREATE USER greenarrow_ro WITH PASSWORD '$password'" | /var/hvmail/postgres/default/bin/psql -U postgres greenarrow
echo "GRANT SELECT ON ALL TABLES IN SCHEMA public TO greenarrow_ro;" | /var/hvmail/postgres/default/bin/psql -U postgres greenarrow

echo "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO greenarrow_ro;" | /var/hvmail/postgres/default/bin/psql -U greenarrow
echo "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO greenarrow_ro;" | /var/hvmail/postgres/default/bin/psql -U postgres greenarrow

Tuning kernel.sem

For systems with a lot of memory or are otherwise configured to use a lot of Postgres connections (for example, increasing the value of apache_max_clients increases the dynamic default value of postgres_max_connections) some tuning of the kernel.sem sysctl parameter may be required.

This sysctl parameter has four parts, and looks like this:

[[email protected] ~]# sysctl kernel.sem
kernel.sem = 32000  1024000000  500 32000

The four numbers observed above are:

• SEMMSL (32000)
• SEMMNS (1024000000)
• SEMOPM (500)
• SEMMNI (32000)

Postgres provides some good documentation about what these parameters mean and how they are used.

GreenArrow checks these values when you run greenarrow_config validate and will warn you if postgres_max_connections is being artificially reduced due to kernel.sem.

Here’s some guidance on how to set these values:

• SEMMSL - This value must be set to at least 17 for GreenArrow to function.
• SEMMNS - This value must be set to at least SEMMSL * SEMMNI.
• SEMOPM - It is safe to leave this value at whatever your operating system’s default is.
• SEMMNI - This value must be set to at least ceil(postgres_max_connections/16) + 250.

For each of the above values, if your current value is greater than the minimum described above, we recommend you leave it at its current value. If your current value is less than the minimum described above, we recommend you raise it to at least the minimum value.