Configure a PostgreSQL Server

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

This tutorial will explain how to configure a PostgreSQL server and troubleshoot the various issues that may arise during the configuration process. While it was developed for UNIX-based systems, like MacOS and Linux, PostgreSQL is highly portable so it will also run on other operating systems like Windows, Solaris, Tru64 Unix and FreeBSD. Because of the ways various operating system work, configuring a PostgreSQL server can create various issues on different systems. While there are sometimes problems when trying to configure a PostgreSQL server, there are fairly simple fixes for most of these issues.

Prerequisites

  • A currently supported version of PostgreSQL, 9 through 12 as of this writing, must be properly installed on the dev machine or server. Installing a 64-bit version of Postgres is recommended.

  • At least 2GB of memory and 256MB of free of hard-drive space is required.

  • Pseudo elevated privileges during terminal access must be granted in order to configure a PostgreSQL server.

  • Possess a basic familiarity with the UNIX shell-command line.

PostgreSQL setup

Note that errors may occur while attempting to connect to the psql command-line interface or when starting the Postgres server. The most common causes for these errors will be covered in this tutorial.

First, confirm that PostgreSQL was successfully installed by calling up its version number with the following command:

1
psql -V

If working properly, the system should return a response that resembles the following:

1
psql (PostgreSQL) 11.5

Now execute the following command for Postgres:

1
postgres -V

If something like a could not identify current directory or role not found error occurs, it is not an issue for concern as most these errors can be cleared up once PostgreSQL is properly configured.

If a postgres: command not found error occurs, it is typically the result of the Postgres PATH not being properly exported or that Postgres was installed using a non-standard repository.

Configure PostgreSQL

Unlike MySQL, Postgres is pretty much designed to work as is, without much configuration. However, it is generally a good idea to make some modifications in both the pg_hba.conf and postgresql.conf files.

Locate the postgresql.conf file

The locations of the .conf files will depend on the specific OS being used and the installed version of PostgreSQL.

Execute the following find command, in a UNIX terminal, to locate the postgresql.conf file:

1
sudo find / -name "postgresql.conf"

The results should resemble the following list of files:

1
2
3
4
5
6
7
/usr/local/var/postgres.old/postgresql.conf
/usr/local/var/postgres/postgresql.conf
/usr/local/var/postgresql@11/postgresql.conf
/Library/PostgreSQL/11/data/postgresql.conf
/System/Volumes/Data/usr/local/var/postgres.old/postgresql.conf
/System/Volumes/Data/usr/local/var/postgres/postgresql.conf
/System/Volumes/Data/usr/local/var/postgresql@11/postgresql.conf

Alternatively, the files may also be located with this command:

1
2
/etc/postgresql/11/main/postgresql.conf
/usr/lib/tmpfiles.d/postgresql.conf

Following is a third find method that involves using the -wholename option to search for Postgres by its complete file name:

1
sudo find /usr -wholename '*/bin/postgres'

pg_hba.conf location

The find command can also be used to obtain the location of the pg_hba.conf file, as follows:

1
sudo find / -name "pg_hba.conf"

If using MacOS, the file will probably be in one of the following locations:

1
2
3
4
5
6
7
/usr/local/var/postgres.old/pg_hba.conf
/usr/local/var/postgres/pg_hba.conf
/usr/local/var/postgresql@11/pg_hba.conf
/Library/PostgreSQL/11/data/pg_hba.conf
/System/Volumes/Data/usr/local/var/postgres.old/pg_hba.conf
/System/Volumes/Data/usr/local/var/postgres/pg_hba.conf
/System/Volumes/Data/usr/local/var/postgresql@11/pg_hba.conf

If using Postgres on a Linux server, the file will mostly likely be in the /etc/postgresql directory. Try locating it by executing the following command:

1
/etc/postgresql/11/main/pg_hba.conf

Next, configure the default settings for PostgreSQL.

Postgres configuration

Use an IDE like Sublime or a terminal-based text editor, like gedit, vi, or nano, to modify the postgresql.conf file. The following bash command uses nano to edit the file for PostgreSQL v11:

1
sudo nano /etc/postgresql/11/main/postgresql.conf

NOTE: It is always wise to create a backup of the configuration files before modifying them, in case the system ever needs to be returned to the default settings. A backup can easily be created with the copy command by executing cp postgresql.conf postgresql.conf.bak command in the same directory where the file is located.

Change the Postgres listen addresses

By default, PostgreSQL will only “listen” to the localhost (or 127.0.0.1 address), but can be modified to listen to all IP addresses by replacing localhost with an asterisk (*) as follows:

1
listen_addresses = '*'

This command is especially useful for permitting remote connections to the PostgreSQL server over different domains. It is also helpful when wanting to run Postgres on a local network and allow all devices connected to the network to access the system. The following screenshot provides and example:

Screenshot of how to configure PostgreSQL by changing the listen_addresses value in postgresql conf

NOTE: Settings in a .conf file can be commented out by placing a hashtag (#) at the beginning of the line.

Other parameters, such as the default Postgres port of 5432, can also be modified in the postgresql.conf file. However, the PostgreSQL server must be restarted when finished for the changes take effect.

pg_hba.conf postgresql

The pg_hba.conf, or host-based authentication, file is used to permit Postgres connections for users and roles. Execute the following command to modify this file with nano on an Ubuntu installation of PostgreSQL v11:

1
sudo nano /etc/postgresql/11/main/pg_hba.conf

NOTE: In MacOS, depending on how Postgres was installed, the file will typically be located at /usr/local/var/postgres. Execute the following shell command to open the file using the Sublime IDE: sudo subl /usr/local/var/postgres/pg_hba.conf.

Depending on the PostgreSQL installation, the default settings should resemble the following:

1
2
3
4
5
6
# "local" is for Unix domain socket connections only
local   all         all                               ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

Now scroll to the bottom of the file, just below the line that states # Database administrative login by Unix domain socket. As shown in the following image, the Postgres roles that are authorized for the server should be visible:

Screenshot of how to configure PostgreSQL by editing the pg_hba configuration file

Now, to enable the postgres admin superuser role and instruct Postgres to “trust” the role to modify the database, append the following line to the file:

1
local all postgres trust

Postgres md5 vs peer

The peer setting enables Postgres to always trust the user, without the need for a password. Conversely, the following md5 settings will always prompt for a password and process it using md5 hash encryption:

1
host all all 0.0.0.0/0 md5

NOTE: The 0.0.0.0 IP address acts as a “wildcard” for all IPv4 addresses. Be certain to replace this if Postgres is running on a server with a static IP address. If the host all permission is already set, and not commented out, then just change peer to md5 to enable password protection.

Peer authentication failed error

A FATAL: Peer authentication failed for user "postgres" error, means either permissions haven’t been given to the user in the pg_hba.conf file or the server hasn’t been restarted after making changes.

Start the Postgres server

After the Postgres database has been initialized, start the server on a MacOS Homebrew installation with the following command:

1
brew services start postgresql

Restart Postgres in Ubuntu

Execute the following command to restart the server if running Postgres on an Ubuntu distro of Linux:

1
sudo service postgresql restart

Executing the following enable command will ensure the server will start whenever Linux is rebooted:

1
sudo systemctl enable postgresql.service

Execute the following systemctl status command to obtain the status of the Postgres server:

1
sudo systemctl status postgresql.service

Restart Postgres on a Mac

For a Homebrew installation of PostgreSQL on MacOS, execute the following command to start the service:

1
brew services start postgresql

To run Postgres as a temporary background service, execute the following pg_ctl command:

1
pg_ctl -D /usr/local/var/postgres start

NOTE: Since Homebrew puts its packages in the /usr/local directory by default, the above command will only work for a Homebrew installation of Postgres on a MacOS.

Execute the following command to obtain more information on the Homebrew Postgres server:

1
brew info postgres

If the Postgres server is already running, and there is access to the psql command-line interface, execute the following SELECT SQL statement to reload the configuration settings:

1
SELECT pg_reload_conf();

Troubleshooting PostgreSQL

This section will cover some of the more common issues that can arise when trying to connect to the psql CLI for Postgres.

FATAL error: postmaster.pid already exists

An error may occur while executing a pg_ctl command that resembling the following:

1
lock file "postmaster.pid" already exists

Here it is not recommended to try to remove the postmaster.pid file with the rm command. Instead, attempt to “kill” the process by sending it a number 15 sigterm. Once the process has been terminated, the PID (process ID for the Postgres service) must be obtained by using the following lsof command to find all of the processes running on port 5432:

1
sudo lsof -i:5432

Once the PID has bee located, use the sudo kill command to safely shutdown the Postgres server by passing its PID number to the command as follows:

1
sudo kill {PID}

NOTE: The default sigterm is 15 when no numerical flag has been passed to the kill command. To immediately force the process to quit, without giving it time to shutdown, use the sudo kill -9 {PID} command.

Screenshot of install PostgreSQL on a Mac example starting the server

Add Postgres to ‘PATH’

A command-not-found error for pg_ctl or postgres is usually an indication that the path for Postgres hasn’t been exported yet.

Following is an example of the basic syntax needed to export the bin path for a Postgres installation in a UNIX terminal:

1
export PATH=$PATH:/path/to/bin

Execute the above export command to append the bin path for the Postgres installation to the system’s PATH variable. Typing echo $PATH will verify the exportation was successfully.

Note that the full path for the Postgres installation should be located in /opt/PostgreSQL/, /usr/local/pgsql/bin, /usr/local/var/postgres, /var/lib/postgresql/ or /usr/local/Cellar/postgresql/12.1/bin for a Homebrew installation. The path may vary depending on the specific machine or the server’s OS, hardware architecture or on the installed version of PostgreSQL.

psql command not found

If a psql: command not found error occurs, try exporting the path in MacOS with the following command:

1
export PATH=/Library/PostgreSQL/11/bin:$PATH

Note that the path for executable files in Linux is usually located in /usr/bin.

Export the path for PostgreSQL

The PATH line to the Bourne shell for the OS, such as the ~/.bash_profile, ~/.bashrc, or ~/.zprofile file, can be append to the Bourne file for a MacOS installation of Postgres as shown in the following example:

1
PATH="/Library/PostgreSQL/11/bin:$PATH"

When finished, be certain to save the changes and have the new settings take effect by executing the following source command, followed by the file name, in a terminal prompt:

1
source ~/.zprofile

Show the Postgres data directory

Use the -c option in a UNIX terminal to execute the following psql command to return the Postgres data directory:

1
sudo su postgres -c "psql -c 'SHOW data_directory;'"

The psql CLI can also be accessed directly using a superuser, like postgres, and then executing the following Postgres command:

1
SHOW data_directory;

In a UNIX-based OS, such as Linux or MacOS, the command should return /var/lib/postgresql/. It may include another subdirectory representing the Postgres version number, as shown here:

1
2
3
4
5
postgres=# SHOW data_directory;
       data_directory
-----------------------------
 /var/lib/postgresql/11/main
(1 row)

Connect to Postgres

After the configuration process is complete, the final test is to see if a successfully connection to the psql CLI for Postgres can be established.

If a user hasn’t been created, execute the following command to pass an username to the createuser file:

1
/usr/local/opt/postgres/bin/createuser -s testuser

The following command can be executed in Linux to create a user, provider the user has been given permissions in the pg_hba.conf file:

1
sudo -u postgres createuser testuser

A database can then be created for the user with the following command:

1
sudo -u postgres createdb -O testuser new_db

Connect to psql from command line

If a password has already been set for the postgres user, access can be obtained with the following su command:

1
su - postgres

Connection to the postgres user may also be attempted by invoking sudo with the following command:

1
sudo -u postgres -i

Use psql to connect to a database

Connection to psql may also be obtained by specifying a username and database with the -U and -d flags as shown here:

1
psql -U testuser -d db_name

If a user or a database has not been created, use the following postgres user and the default template1 database command to connect to psql:

1
psql -U postgres -d template1

Set the postgres user password

Once inside of psql, set a password for the postgres user using the following psql command:

1
\password postgres

Create a new Postgres user and database

The following SQL commands will also create a user with a password and grant the user permissions to modify the new database:

1
2
3
CREATE USER newuser PASSWORD '1234';
CREATE DATABASE db_name;
GRANT ALL PRIVILEGES ON DATABASE db_name TO newuser;

Type \q to exit the psql interface when finished.

Conclusion

This tutorial explained how to configure a PostgreSQL server and troubleshoot various issues that sometimes occur during the configuration process. The tutorial explained how to set up and configure PostgreSQL, locate the postgresql.conf file, change the Postgres listen addresses and troubleshoot PostgreSQL address-configuration errors. The article then explained how to start and restart the Postgres server, add Postgres to ‘PATH’, export the path for PostgreSQL and show the Postgres data directory. Finally, how to connect to Postgres and a database and set the postgres user password were explained. Bear in mind that reviving a FATAL: Peer authentication failed for user "postgres" error means that permissions haven’t been given to the user in the pg_hba.conf file or that the server has not been restarted after changes were made.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.