Modifying PostgreSQL Settings

ServBay comes with a PostgreSQL database and provides flexible configuration options. This article details how to modify PostgreSQL settings in ServBay, including the location of configuration files and common configuration items. Depending on the PostgreSQL version, these files are located in the /Applications/ServBay/db/postgresql/<version> directory, for example, the configuration files for PostgreSQL 16 are located in the /Applications/ServBay/db/postgresql/16 directory.

Overview

In ServBay, PostgreSQL configuration files are stored in different directories based on the version. For example, the configuration files for PostgreSQL 16 are located in the /Applications/ServBay/db/postgresql/16 directory. The main configuration files include postgresql.conf and pg_hba.conf.

Configuration File Location

• postgresql.conf: /Applications/ServBay/db/postgresql/16/postgresql.conf
• pg_hba.conf: /Applications/ServBay/db/postgresql/16/pg_hba.conf

Common Configuration Items and Modification Methods

postgresql.conf

postgresql.conf is the main configuration file for PostgreSQL, used to set the runtime options for the database server.

Common Configuration Items

1. Modify listening address:

   listen_addresses = '*'

2. Modify port number:

   port = 5432

3. Modify maximum number of connections:

   max_connections = 100

4. Modify shared buffer size:

   shared_buffers = 128MB

5. Modify log file path:

   log_directory = 'log'
   log_filename = 'postgresql-%Y-%m-%d.log'

6. Enable slow query log:

   log_min_duration_statement = 2000  # In milliseconds, record queries that take more than 2 seconds to execute.

pg_hba.conf

The pg_hba.conf file is used to configure the client connection authentication methods.

Common Configuration Items

1. Allow local connections:

   # "local" is for Unix domain socket connections only
   local   all             all                                     trust

2. Allow remote connections from all IP addresses:

   # IPv4 local connections:
   host    all             all             0.0.0.0/0               md5

3. Allow remote connections from specific IP addresses:

   # IPv4 local connections:
   host    all             all             192.168.1.0/24          md5

Restarting PostgreSQL

After modifying PostgreSQL configuration files, you need to restart the PostgreSQL service to apply the changes. You can restart it through the ServBay management interface or using the servbayctl command-line tool.

Restart via ServBay Management Interface

1. Open the ServBay management interface.
2. Navigate to Services.
3. Find the corresponding version of the PostgreSQL service and click the Restart button.

Restart via servbayctl

You can easily restart the PostgreSQL service using the servbayctl command-line tool:

servbayctl restart postgresql 16

Conclusion

In ServBay, PostgreSQL configuration files are located in the /Applications/ServBay/db/postgresql/<version> directory, including postgresql.conf and pg_hba.conf. After modifying these configuration files, you can restart the PostgreSQL service through the ServBay management interface or using the servbayctl command-line tool to apply the changes.