PostgreSQL Configuration ChangesSo you’ve 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 i.e. MSWindows vs Linux.
  • It “depends” on the flavor of Linux i.e. Debian vs RedHat.
  • It “depends” if it’s a package install or from source code.

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

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

  1. a newly created datacluster is present
  2. a configuration file pg_hba.conf is to be edited
  3. a configuration file postgresql.conf is to be edited

There are other configuration files but we’ll work with these.

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

The first thing is to look at is the “Local Address”. Notice how it says 127.0.0.1:5432. Okay, so that means that the server is currently listening on the localhost on port 5432. But you want 0.0.0.0:5432 otherwise remote connections cannot be accepted. With an editor, open up file 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’ll cover that later.

ATTENTION: Setting the Address (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 RedHat/Centos look alike and all Debian/Ubuntu have their own similar styles too. The relevant settings are at the bottom of the file as all else above is commented documentation.

Look at the first line, where TYPE is “local”. So long as you can log in locally, via UNIX DOMAIN SOCKETS, and sudo as the superuser, postgres is the default, you can access your service without a password.

METHOD should be peer but if it uses something else, like md5, you’ll need to change the string. Alternatively, if you feel particularly trustful of the other user accounts on the host, you can use the METHOD trust permitting free access to all locally logged-in UNIX accounts.

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

So let’s fix this by assigning a password to ROLE postgres by logging via UNIX DOMAIN SOCKETS since we already permit logins by METHOD peer:

TIP: edits to pg_hba.conf requires the service to reload the file i.e. SIGHUP

Now that we’ve had connectivity for localhost connections, we’re using an IP v4 socket for this example, we can now proceed to address remote connections.

You’re going to need to add another rule which should be placed after the localhost rule:

And here’s a line you can write for IPV6:

TIP: The demonstrated example rules let everybody connect to the host. A knowledge of CIDR is key to enforcing network security.

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

You’re almost there!

Now that you’ve added a password to the superuser and updated the configuration file pg_hba.conf, it’s time to visit another configuration file postgresql.conf.

Locate the file and edit runtime parameter listen_addresses. The default setting prohibits remote connections. Resetting the value either to a nic’s IP address or just using the wild card will make it accessible.

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

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

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

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

  • Redhat distributions require dataclusters to be manually created before they can be administered.
  • PostgreSQL Debian distributions, including Ubuntu, automatically creates and starts up the datacluster.

Systemd:

  • Redhat/Centos:
  • Debian/Ubuntu:

Debian derived Linux Distributions include a collection of command-line utilities in order 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’s replication, but that’s another blog altogether.

That’s it for now!

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

Download PDF