PostgreSQL Configuration Changes You Need to Make Post-Installation

So you have installed Postgres onto your machine, and you want to start working with it.

How?

The key to understanding the post-installation procedure is to realize that it depends.

• It depends on the OS, such as Microsoft Windows vs. Linux.
• It depends on the flavor of Linux, such as Debian vs. Red Hat.
• It depends on whether it is a package install or installed from source code.

Let’s start by working with the most basic steps common to all installs, then break it down further from there.

A successfully installed Postgres instance, no matter the version, is characterized by the following:

1. A newly created data cluster is present.
2. The configuration file pg_hba.conf is available to edit.
3. The configuration file postgresql.conf is available to edit.

There are other configuration files, but we will work with these.

For the purposes of discussion, let’s further assume you have started the cluster and Postgres is running on the host. Here is an example of what you can see when you run a utility, such as netstat, that reports network connections:

The first thing to look at is the “Local Address”. Notice that it says 127.0.0.1:5432. This means the server is currently listening on localhost on port 5432. But you want 0.0.0.0:5432; otherwise, remote connections cannot be accepted. With an editor, open pg_hba.conf and look at the default rules. Keep in mind that the configuration file can be located in one of several locations. We will cover that later.

ATTENTION: Setting the address, or CIDR, to 0.0.0.0 is for connectivity purposes only. As soon as you know everything works, you should restrict this to as few permitted connections as possible. This is not something you should do on a production machine.

The actual rules per line can vary from one type of Postgres installation to another. The good news is that Red Hat/CentOS installs look similar, and Debian/Ubuntu installs have their own similar style. The relevant settings are at the bottom of the file, because everything above is commented documentation.

Look at the first line, where TYPE is local. As long as you can log in locally via Unix domain sockets and use sudo as the superuser, postgres by default, you can access your service without a password.

METHOD should be peer. If it uses something else, such as md5, you will need to change the string. Alternatively, if you trust the other user accounts on the host, you can use the trust method, which permits free access to all locally logged-in Unix accounts.

Looking at the second line, you can see that TYPE is IPv4. This rule, as well as the rule for IPv6, prevents localhost logins unless one knows the password:

Let’s fix this by assigning a password to the postgres role. Log in via Unix domain sockets, since we already permit logins using the peer method:

Tip: Edits to pg_hba.conf require the service to reload the file, also known as SIGHUP.

Now that we have connectivity for localhost connections, using an IPv4 socket for this example, we can proceed to address remote connections.

You need to add another rule, which should be placed after the localhost rule:

Here is a line you can write for IPv6:

Tip: The example rules shown here let everybody connect to the host. Knowledge of CIDR is key to enforcing network security.

Keeping in mind that your system will be unique, here is what pg_hba.conf should start to look like:

You are almost there.

Now that you have added a password to the superuser and updated the configuration file pg_hba.conf, it is time to visit another configuration file: postgresql.conf.

Locate the file and edit the runtime parameter listen_addresses. The default setting prohibits remote connections. Resetting the value either to a NIC’s IP address or to the wildcard makes it accessible.

Tip: As the postgres user, execute the following in a psql session to locate your configuration files.

For those feeling fancy, you can bind the Postgres service to more than one IP address as a comma-separated list:

An alternate method for updating runtime parameters can be accomplished using the SQL statement:

The final step, restarting the service, is where we start splitting hairs again:

• Red Hat distributions require data clusters to be manually created before they can be administered.
• PostgreSQL Debian distributions, including Ubuntu, automatically create and start the data cluster.

Systemd:

• Red Hat/CentOS:
  
  /usr/pgsql-12/bin/postgresql-12-setup initdb

  1	/usr/pgsql-12/bin/postgresql-12-setup initdb

  
  
  systemctl start postgresql-12 systemctl stop postgresql-12

  1 2	systemctl start postgresql-12 systemctl stop postgresql-12

• Debian/Ubuntu:
  
  systemctl restart postgresql

  1	systemctl restart postgresql

Debian-derived Linux distributions include a collection of command-line utilities to administer the PostgreSQL service:

After a successful service restart, you should get something similar to the following:

And finally, the remote connectivity test:

Then there is replication, but that is another blog altogether.

That is it for now.

Our white paper, “Why Choose PostgreSQL?”, looks at the features and benefits of PostgreSQL and presents practical usage examples. We also examine how PostgreSQL can be useful for companies looking to migrate from Oracle.

Download PDF