Modifying PostgreSQL Database Settings in ServBay

ServBay offers macOS developers a powerful local web development environment, featuring integrated PostgreSQL database support. This guide details how to modify PostgreSQL configuration settings within ServBay, highlighting the recommended approach using the UI, and providing information on manual file editing for advanced or temporary use.

Depending on the PostgreSQL version you have installed in ServBay, the main configuration files are typically located in the /Applications/ServBay/etc/postgresql/<version> directory. For example, configuration files for PostgreSQL 16 are found at /Applications/ServBay/etc/postgresql/16.

Important Notice

ServBay manages most configuration settings via its graphical user interface (UI) and automatically generates the corresponding configuration files. It is strongly recommended to use the ServBay UI for making changes. Directly editing configuration files could result in your changes being overwritten the next time ServBay manages or updates its configurations. Manual file editing is for temporary testing or for advanced users who understand the underlying setup; it is not recommended for persistent or production configurations.

Modifying Configurations via ServBay UI (Recommended)

ServBay provides an intuitive graphical management interface, making it the easiest and recommended way to adjust PostgreSQL settings. Configuration changes made through the UI are automatically applied and usually take effect immediately, with no need to restart services unless prompted by ServBay.

How to Edit Settings

1. Launch the ServBay application.
2. In the sidebar, select Database.
3. In the list of databases, locate PostgreSQL and click on the specific version you wish to configure.
4. Once in the PostgreSQL settings panel, you’ll see detailed information and configuration options for the selected version.

Configurable Options

Within this interface, ServBay displays your current default PostgreSQL username and password (typically visible on this page). You can also directly modify common configuration parameters such as:

• Listening Addresses (listen_addresses): Controls which network interfaces PostgreSQL listens to. The default is often *, meaning all available interfaces.
• Port (port): The network port PostgreSQL uses for connections. The default standard port is 5432 (note this differs from MySQL/MariaDB’s 3306).
• Maximum Connections (max_connections): Limits the maximum number of client connections to the database.
• Work Memory (work_mem): Sets the amount of memory each query process can use for sorting and hash tables.

Additionally, you can add or modify other postgresql.conf parameters in the Additional Parameters area. Enter one parameter per line in the format parameter_name = value.

Applying Your Changes

After making changes, click the Save button at the bottom of the screen. ServBay automatically applies your modifications. For most settings, changes take effect immediately. In rare cases, a manual PostgreSQL service restart may be required, and ServBay will provide instructions if needed.

Modifying Settings via Manual Configuration Files (Not Recommended for Persistent Changes)

As mentioned earlier, manual file editing isn’t recommended for regular config management, as your changes may be overwritten by ServBay’s UI-driven configuration system. However, knowing the locations and contents of these files is helpful for understanding PostgreSQL’s backend setup.

WARNING

Manual file editing should only be used for temporary troubleshooting or advanced diagnostics. ServBay strongly recommends using the UI for configuration, ensuring consistency and persistence. Manually edited files and their paths may be reset by ServBay’s internal management system.

Configuration File Locations

The main configuration files for PostgreSQL in ServBay are postgresql.conf and pg_hba.conf. The exact paths depend on your PostgreSQL version, but are typically found at:

• postgresql.conf: /Applications/ServBay/etc/postgresql/<version>/postgresql.conf
• pg_hba.conf: /Applications/ServBay/etc/postgresql/<version>/pg_hba.conf

Note: If you see the path /db/postgresql/16/pg_hba.conf mentioned in older docs or for internal ServBay purposes, be aware that the ServBay UI mainly manages configurations under /Applications/ServBay/etc. When making manual edits, always verify the actual files in use. To stay consistent with the ServBay UI, /Applications/ServBay/etc/postgresql/<version> is your most reliable location.

Example Common Configuration Items

Below are common configuration options you’ll encounter in postgresql.conf and pg_hba.conf. Edit these files with a text editor (like VS Code, Sublime Text, Nano, etc.) using administrator privileges.

postgresql.conf

postgresql.conf is PostgreSQL’s main configuration file and controls the majority of the server’s behavior.

# Listening addresses: which network interfaces the server should bind to. '*' means all interfaces.
listen_addresses = '*'

# Port: the network port used by the server. The standard PostgreSQL port is 5432.
port = 5432

# Maximum connections: maximum number of client connections allowed to the database.
max_connections = 100

# Shared buffers: the amount of shared memory PostgreSQL uses to cache database data.
shared_buffers = 128MB

# Log directory: location where log files are stored.
log_directory = 'log'

# Log file name pattern: naming format for log files.
log_filename = 'postgresql-%Y-%m-%d.log'

# Slow query log: record queries that run longer than specified milliseconds.
# Set a positive integer, e.g., 2000 means log queries over 2 seconds.
# Set to -1 to disable slow query logging.
log_min_duration_statement = 2000

pg_hba.conf

The pg_hba.conf (Host-Based Authentication) file configures how clients authenticate and their access permissions.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Allow local connections via Unix domain sockets, using trust (no password) method.
# "local" is for Unix domain socket connections only
local   all             all                                     trust

# Allow all IPv4 addresses to connect via TCP/IP, using md5 password authentication.
# IPv4 local connections:
host    all             all             0.0.0.0/0               md5

# Allow connections from a specific IPv4 subnet (e.g., 192.168.1.0/24) via TCP/IP with md5 authentication.
# IPv4 local connections from a specific subnet:
# host    all             all             192.168.1.0/24          md5

When editing pg_hba.conf, ensure you fully understand what each column (TYPE, DATABASE, USER, ADDRESS, METHOD) means. Incorrect settings can block access to your database or pose security risks.

Applying Manual Configuration Changes

After manually saving changes to config files, you need to restart the PostgreSQL service for them to take effect.

Restarting via ServBay’s Management UI

1. Open the ServBay management interface.
2. In the sidebar, select Database.
3. Locate PostgreSQL and click on the corresponding version.
4. On this page or through the main "Packages"/"Services" list, find the correct version of the PostgreSQL service and click the Restart button.

Restarting via the servbayctl Command Line Tool

ServBay's command-line tool servbayctl lets you conveniently manage services, including restarting PostgreSQL:

servbayctl restart postgresql <version>

Replace <version> with the specific PostgreSQL version you are using (for example, 16).

Summary

ServBay offers a convenient UI for editing PostgreSQL settings, and this is the recommended way to avoid the risk of files being overwritten. You can adjust key settings like listening addresses, ports, connection limits, and view default credentials via the ServBay UI. For rare cases where manual editing is needed (such as temporary troubleshooting), PostgreSQL’s main configuration files (postgresql.conf and pg_hba.conf) are found under /Applications/ServBay/etc/postgresql/<version>. Regardless of how you make changes, you’ll typically need to restart the PostgreSQL service for them to take effect; this can be done via the ServBay UI or the servbayctl CLI tool.

Understanding these settings and how to modify them will help you tailor your ServBay PostgreSQL environment to suit your development needs.