Modify PostgreSQL Settings

ServBay includes a PostgreSQL database and offers flexible configuration options. This article will detail 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/etc/postgresql/<version> directory, for example, the configuration files for PostgreSQL 16 are in the /Applications/ServBay/etc/postgresql/16 directory.

Important Note

Please do not manually modify the configuration files. All configuration files are automatically generated by ServBay, and manual modifications risk being overwritten. Please make modifications through the UI interface.

Modifying via the ServBay UI Interface

ServBay comes with a powerful graphical management interface that allows users to directly modify different configuration parameters in the management interface. After clicking save, the modifications are automatically applied and take effect in real-time, eliminating the hassle of manually editing parameters.

Users can edit PostgreSQL settings by selecting Database-PostgreSQL from the left navigation bar in ServBay, then choosing the corresponding version.

In the graphical editing interface, users can modify PostgreSQL's listen_addresses and port (default is *:3306) settings and get the default PostgreSQL username and root password. Additionally, users can configure PostgreSQL's max_connections, work_mem, and other settings.

Moreover, users can configure additional parameters in Additional Parameters to meet the needs of development business.

After making the modifications, click the Save button to apply the changes and make them effective immediately.

Modifying by Manually Editing Configuration Files

WARNING

Manually editing configuration files is only suitable for temporary changes to some configuration items.

We do not recommend manually editing the PostgreSQL configuration files as all modifications will be overwritten by ServBay.

Overview

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

Configuration File Locations

• postgresql.conf: /Applications/ServBay/etc/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 runtime options for the database server.

Common Configuration Items

1. Modify Listen Addresses:

   listen_addresses = '*'

2. Modify Port Number:

   port = 5432

3. Modify Maximum Connections:

   max_connections = 100

4. Modify Shared Buffers Size:

   shared_buffers = 128MB

5. Modify Log File Path:

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

6. Enable Slow Query Logging:

   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 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 the PostgreSQL configuration files, you need to restart the PostgreSQL service to make the changes effective. This can be done via the ServBay management interface or using the servbayctl command-line tool.

Restarting via ServBay Management Interface

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

Restarting via servbayctl

Using the command-line tool servbayctl, you can easily restart the PostgreSQL service:

servbayctl restart postgresql 16

Summary

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