1

I am provisioning a database server that runs on an AWS EC2 instance using Ubuntu 16.04 based on an official AMI image from Canonical. On this instance I installed Postgres 9.6 from the xenial-pgdg repository, then changed the location of the data directory from the default one because I want to use a larger capacity volume for the database cluster that is separate from the root volume. After changing the data cluster location I can't seem to successfully reload/restart the postgres service in order for any subsequent configuration change to take effect. At a minimum I need to configure the postgresql.conf and pg_hba.conf files in the /etc/postgresql/9.6/main/ directory to allow one specific other server to connect to this database server.

Here's relevant background information about the server setup.

OS version:

ubuntu@ip-10-144-96-189:~$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 16.04.2 LTS
Release:    16.04
Codename:   xenial

I created a postgres linux user and group. Mounted the new drive for the database cluster, and made postgres the owner of the data directory. Added the xenial-pgdg repository and installed the following packages:

postgresql-9.6
postgresql-contrib-9.6
python-psycopg2
postgresql-9.6-postgis-2.3
libgeos-dev
postgresql-common
s3cmd

Based on this write-up I dropped the default database cluster and created a new one in the new data directory (commands executed as the postgres user):

pg_dropcluster --stop 9.6 main
pg_createcluster -d /mnt/pg_data/pg --start 9.6 main

I edited the /etc/postgresql/9.6/main/postgresql.conf file to listen for connections from localhost and an IP address over TCP on port 5432:

listen_addresses = 'localhost, 10.112.190.57'     # what IP address(es) to listen on;
port = 5432                                       # (change requires restart)

Then after restarting the postgresql service there was no open TCP connection listening at the configured IP address, only at the default localhost:

ubuntu@ip-10-144-96-189:~$ sudo systemctl restart postgresql
ubuntu@ip-10-144-96-189:~$ netstat -nlp
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       
PID/Program name
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      -               
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      -               
tcp6       0      0 :::22                   :::*                    LISTEN      -               
udp        0      0 0.0.0.0:68              0.0.0.0:*                           -               
udp        0      0 10.144.96.189:123       0.0.0.0:*                           -               
udp        0      0 127.0.0.1:123           0.0.0.0:*                           -               
udp        0      0 0.0.0.0:123             0.0.0.0:*                           -               
udp6       0      0 fe80::2000:bff:fed5:123 :::*                                -               
udp6       0      0 ::1:123                 :::*                                -               
udp6       0      0 :::123                  :::*                                -               
Active UNIX domain sockets (only servers)
Proto RefCnt Flags       Type       State         I-Node   PID/Program name    Path
unix  2      [ ACC ]     STREAM     LISTENING     166227   23611/systemd       /run/user/1000/systemd/private
unix  2      [ ACC ]     SEQPACKET  LISTENING     8758     -                   /run/udev/control
unix  2      [ ACC ]     STREAM     LISTENING     8754     -                   /run/systemd/private
unix  2      [ ACC ]     STREAM     LISTENING     8760     -                   /run/lvm/lvmpolld.socket
unix  2      [ ACC ]     STREAM     LISTENING     8765     -                   /run/systemd/journal/stdout
unix  2      [ ACC ]     STREAM     LISTENING     8891     -                   /run/lvm/lvmetad.socket
unix  2      [ ACC ]     STREAM     LISTENING     13009    -                   /run/snapd-snap.socket
unix  2      [ ACC ]     STREAM     LISTENING     13005    -                   /var/lib/lxd/unix.socket
unix  2      [ ACC ]     STREAM     LISTENING     13008    -                   /run/snapd.socket
unix  2      [ ACC ]     STREAM     LISTENING     13010    -                   /run/acpid.socket
unix  2      [ ACC ]     STREAM     LISTENING     13011    -                   /var/run/dbus/system_bus_socket
unix  2      [ ACC ]     STREAM     LISTENING     13012    -                   /run/uuidd/request
unix  2      [ ACC ]     STREAM     LISTENING     13169    -                   @ISCSIADM_ABSTRACT_NAMESPACE
unix  2      [ ACC ]     STREAM     LISTENING     148848   -                   /var/run/postgresql/.s.PGSQL.5432

The last line in the previous output also shows the open Unix socket for a local Postgres connection. I also need to configure the client authentication from the 10.112.190.57 IP address in the pg_hba.conf file.

Question: What steps do I need to take to be able to apply new configuration from the postgres.conf and pg_hba.conf files to the postgres service?

I have considered customizing the systemd service files, and found the following recommended service file on the PostgreSQL website:

When using systemd, you can use the following service unit file (e.g., at /etc/systemd/system/postgresql.service):

[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)

[Service]
Type=notify
User=postgres
ExecStart=/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0

[Install]
WantedBy=multi-user.target

Unfortunately, the ExecStart=/usr/local/pgsql/bin/postgres -D ... entry points to a command that doesn't exist on my server.

Question: What is the equivalent systemd setup that applies to Ubuntu 16.04 with PostgreSQL 9.6?

Lastly, here is the postgres service and cluster service status:

ubuntu@ip-10-144-96-189:~$ systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Wed 2017-12-06 18:44:43 UTC; 1 day 2h ago
  Process: 18307 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 18307 (code=exited, status=0/SUCCESS)
    Tasks: 0
   Memory: 0B
      CPU: 0
   CGroup: /system.slice/postgresql.service

Dec 06 18:44:43 ip-10-144-96-189 systemd[1]: Starting PostgreSQL RDBMS...
Dec 06 18:44:43 ip-10-144-96-189 systemd[1]: Started PostgreSQL RDBMS.

ubuntu@ip-10-144-96-189:~$ systemctl status postgresql@9.6-main
● postgresql@9.6-main.service - PostgreSQL Cluster 9.6-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled; vendor preset: enabled)
   Active: active (running) since Wed 2017-12-06 18:44:43 UTC; 1 day 2h ago
  Process: 18274 ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m fast %i stop (code=exited, status=1/FAILURE)
  Process: 18281 ExecStart=postgresql@%i --skip-systemctl-redirect %i start (code=exited, status=0/SUCCESS)
 Main PID: 18290 (postgres)
   CGroup: /system.slice/system-postgresql.slice/postgresql@9.6-main.service
           ├─18290 /usr/lib/postgresql/9.6/bin/postgres -D /mnt/pg_data/pg -c config_file=/etc/postgresql/9.6/main/postgresql.conf
           ├─18292 postgres: 9.6/main: checkpointer process                                                                       
           ├─18293 postgres: 9.6/main: writer process                                                                             
           ├─18294 postgres: 9.6/main: wal writer process                                                                         
           ├─18295 postgres: 9.6/main: autovacuum launcher process                                                                
           └─18296 postgres: 9.6/main: stats collector process                                                                    


Dec 06 18:44:40 ip-10-144-96-189 systemd[1]: Stopped PostgreSQL Cluster 9.6-main.
Dec 06 18:44:40 ip-10-144-96-189 systemd[1]: postgresql@9.6-main.service: Unit entered failed state.
Dec 06 18:44:40 ip-10-144-96-189 systemd[1]: postgresql@9.6-main.service: Failed with result 'exit-code'.
Dec 06 18:44:40 ip-10-144-96-189 systemd[1]: Starting PostgreSQL Cluster 9.6-main...
Dec 06 18:44:43 ip-10-144-96-189 systemd[1]: Started PostgreSQL Cluster 9.6-main.

I see the failures in the postgres@9.6-main process status, but I have no idea what they mean and how to fix this situation.

1 Answer 1

1

After some research I have found a solution to provisioning an Ubuntu 16.04 database server with PostgreSQL 9.6 that uses a custom cluster location and that allows applying subsequent configuration changes.

In my case an AWS EC2 instance was provisioned, but the process should be the same for any Ubuntu 16.04 host. I also ended up using Ansible (v2.3.1) to automate the provisioning process, but same sequence of operations as performed by Ansible should lead to the same result. Here are the major steps:

  1. Install Python 2.7 on the Ubuntu server (this is only needed by Ansible).

  2. Use the geerlingguy.postgresql Ansible role, which provides the default steps to install PostgreSQL 9.5 on the system with the database cluster initialized in the default location (/var/lib/postgresql/9.5/main directory). This role is fully parametrized for easy customization. I modified it to install PostgreSQL 9.6 with postgis v2.3.

    • Change __postgresql_version to "9.6" in vars/Ubuntu-16.yml.
    • Change __postgresql_packages list to include v9.6 package names as needed in vars/Ubuntu-16.yml. Add necessary extension packages, such as postgis, to the list.
    • Modify _tasks/setup_Debian.yml_ to add the xenial-pgdg repository, including its repository key, before installing the postgres_packages list. See https://www.postgresql.org/download/linux/ubuntu for specifics.
    • Update the defaults/main.yml file with your database, extension and database user information. You can modify the postgresql_global_config_options to customize the listen_addresses value, and modify the postgresql_hba_entries with custom connection values.
    • In tasks/databases.yml after creating your database add any extensions to your database.

Once you execute an Ansible playlist that contains the above two steps, the PostgreSQL server is up and running allowing the local postgres user to connect to your database. You can make configuration changes such as in the /etc/postgresql/9.6/main/postgres.conf file and use the systemctl command to restart the postgresql service to apply the changes.

The geerlingguy.postgresql Ansible role does allow configuring the PostgreSQL data directory, but I haven't tried that yet. Instead, I have successfully changed the data directory after the above described process by following the steps in the https://www.tutorialspoint.com/articles/how-to-change-postgresql-data-folder-location-on-ubuntu-16-04 article.

I hope this is helpful for someone coming up to speed on PostgreSQL on Ubuntu 16.04.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.