PostgreSQL
GreenArrow uses a PostgreSQL database to store some of its data, including configuration and statistical data.
Adjusting PostgreSQL’s configuration improperly can render your GreenArrow server inoperable and/or cause loss of data.
GreenArrow performs PostgreSQL performance tuning for each server that we install. That initial tuning is all that’s needed in most cases. Please contact GreenArrow technical support if you would like us to verify that this initial tuning that we performed is still appropriate for your server’s current configuration.
You should only modify PostgreSQL’s configuration if you know what you’re doing with PostgreSQL, and should always have a rollback plan in place before making any changes. If you cause an outage while modifying PostgreSQL’s configuration, that does not qualify as a critical malfunction for support purposes.
- Table of Contents
- Versions
- Main Configuration Files
- Logs
- Other Files
- Service Management
- Global Statement Timeout
- Remote Access
- Tuning kernel.sem
Versions
New GreenArrow installations use PostgreSQL 9.5 by default. Older installations may be running 8.3 and can be converted to 9.5 by contacting technical support.
To check which version of PostgreSQL you’re using, look at what directory the /var/hvmail/postgres/default
symlink points to. For example, the following server is running PostgreSQL 9.5:
# ls -l /var/hvmail/postgres/default
lrwxrwxrwx 1 root root 24 Mar 16 10:33 /var/hvmail/postgres/default -> /var/hvmail/postgres/9.5
Main Configuration Files
PostgreSQL has two main configuration files:
-
/var/hvmail/control/greenarrow.conf
. The postgres_max_connections directive is used for controlling the maximum number of connections allowed by PostgreSQL. -
/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 which could normally be specified in either location, you should specify it in 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 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 can be found at/var/hvmail/postgres/default/bin/
. Here’s an example of how to use it to log in as thegreenarrow
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 to made directly to it will be overwritten. You should make changes togreenarrow.conf
orpostgres.conf
(as described in the previous section) instead.
- The
- The PostgreSQL usernames and passwords that are created during the installation process can be viewed in the
/root/.pgpass
file.
Service Management
This step shows you how to manage GreenArrow’s PostgreSQL service. Stopping or restarting this service will cause complete downtime for any other services that depend on access to the database until PostgreSQL starts back up.
A restart typically takes somewhere between 15 and 60 seconds, but yours could take longer. If your business model is sensitive to downtime, we recommend restarting this service during your low peak hours.
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. This includes performing tasks like stopping, starting and restarting PostgreSQL. We recommend using the hvmail_postgres_manager
script for these tasks though because it also manages services which 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. This is 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 setup remote access to PostgreSQL.
- Configure PostgreSQL to listen for remote connections.
- Authorize remote client connections.
- 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 11.22.33.44
.
Configure PostgreSQL to Listen for Remote Connections
If PostgreSQL is already listening on an IP address accessible from the client system then you can skip this step.
These instructions can also be used to add or update the IPs where GreenArrow will listen for incoming connections.
- Configure
/var/hvmail/control/postgres.conf
with the IP addresses to listen on, and enable TLS/SSL. Edit that file to add these lines (replacing11.22.33.44
with your server’s IP address):listen_addresses = 'localhost, 11.22.33.44' ssl = on
- By default, PostgreSQL only listens on
localhost
. Removinglocalhost
from this configuration will cause GreenArrow to break, so you should verify thatlocalhost
is listed in thelisten_address
list (as shown in the example in the previous step). - 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
- Restart PostgreSQL, and verify that it comes back up.
Authorize Remote Client Access
- Add a line to
/var/hvmail/postgres/default/data/pg_hba.conf
to allow password authentication from authorized IP addresses. Examples which limit access to a single IP, and allow access from any IP are shown below:hostssl is only supported if you’re using PostgreSQL 9.5 (GreenArrow’s default PostgreSQL version) with any supported Linux distribution or PostgreSQL 8.3 with CentOS 7.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
- 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"
- 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
These instructions only work on GreenArrow servers running PostgreSQL version 9.5 or higher.
If your GreenArrow server is running an older version of PostgreSQL and you do not yet have a read-only account, contact GreenArrow technical support to have one set up for you.
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 leastSEMMSL * 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 leastceil(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.