Re-installing software Part II : Postgres

Re-installing software Part II : Postgres

2018, Oct 09    

One of the first things that has to be installed is a database to support NextCloud, Owncloud, Gogs and other tools that might need it.

First thing is to make the jail:

iocage create -n postgres dhcp=on allow_sysvipc=1 bpf=yes vnet=on -t ansible_template boot="on"
# add in the shared storage
/mnt/vol01/apps/postgres/ /mnt/vol01/iocage/jails/postgres/root/var/db/postgres nullfs rw 0 0

I had previously looked at installing postgresdb, and did up a standalone playbook that is hosted on github: https://github.com/coetzeer/freebsd_postgres_ansible .

For the impatient

If you’re not willing to step through the detailed breakdown below, simply run the freebsd_postgres_ansible.yml playbook against localhost:

git clone https://github.com/coetzeer/freebsd_postgres_ansible
cd freebsd_postgres_ansible

# crack open play.yml and change the following to values that suit you:
# pgadmin_password: "changeme"
# pgadmin_username: "coetzeer@gmail.com"
# pgadmin_db_username: pgadmin
# pgadmin_db_password: pgadmin

pkg install ansible
ansible-playbook -i localhost play.yml

This sets up a single instance postges instance, with pgadmin running through an nginx on port 80. Log into pgadmin with the top two values you changed above. Pgadmin will be connecting to your postgres database with the bottom two credentials that you changed above.

Blow by blow

This playbook was based on the instructions over at: https://www.howtoforge.com/tutorial/how-to-install-postgresql-and-pgadmin-on-freebsd-11/

Step 1: Install the packages

The following step installs each of the listed packages via the pkg program. They’re the postgres server and client, python dependencies for pgadmin and some other dependencies to make life easier.


- name: "Install packages from pkg"
      pkgng:
        name: ""
        state: present
      with_items:
      - postgresql96-client
      - postgresql96-server
      - py27-pip
      - py27-virtualenv
      - wget
      - py27-sqlite3
      - rinetd
      - ca_root_nss

Step 2: Install the remaining python dependencies

These two dependencies are mainly to support this playbook (although I think psycopg2 is required by pgadmin). This step uses the pip python package manager rather than the system package manager.

  - name: Pip install into system python
    pip:
       name: ""
    with_items:
       - psycopg2
       - pexpect

Step 3: Enable the postgres database

This is a FreeBSD specific step that enables the postgres service i.e. it adds an instruction to /etc/rc.conf that instructs the init system to invoke the postgres init script. Which should start it.

  - name: Enable the postgres service
    shell: "sysrc postgresql_enable=yes"

Step 4: initialise the postgresdb

This step uses the postgres init script to initialize the new postgresqldb installation. This creates the postgres data directory that stores all the configuration, data file, WAL files (write ahead logs) - all the things that make a database a database:

- name: Intialize the database
  shell: service postgresql initdb
  args:
    creates: "/var/db/postgres/data96"

It won’t run twice provided that the first run successfully created the /var/db/postgres/data96 directory.

Step 5: Start the postgres database.

  - name: Start the db
    service:
       name: postgresql
       state: started
       enabled: yes

Step 6: Open up the db to allow people to connect

- lineinfile:
    path: /var/db/postgres/data96/postgresql.conf
    regexp: '^listen_addresses '
    insertafter: '^#listen_addresses '
    line: "listen_addresses = '*'"

- lineinfile:
    path: /var/db/postgres/data96/pg_hba.conf
    line: "host    all             all             0.0.0.0/0               md5"

- name: Start the db
  service:
    name: postgresql
    state: restarted
    enabled: yes

Set postgres to listen on all network cards, and allow all hosts to connect to this db using the md5 password mechanism. This is obviously a bit more open that you’d typically want for a production installation but for our home server, it reduces the fiddling when we want to create new users and schemas.

Restart the postgres server to pick up the config changes.

Step 7: Create the pgadmin user and database

Create the pgadmin databse and user using the built in ansible postgres functions.


- postgresql_db:
  name: pgadmin

- postgresql_user:
  db: pgadmin
  name: ""
  password: ""
  role_attr_flags: CREATEDB,SUPERUSER,CREATEROLE

This is more or less equivalent to

su postgres -c 'createuser -sdrP pgadmin_db_username'
su postgres -c 'createdb pgadmin'
su postgres -c  "echo 'GRANT ALL PRIVILEGES ON DATABASE pgadmin_db_username TO pgadmin' | psql"

Step 8: Create the pgadmin virtualenv and put some libraries into it

Although it’s not strictly required, installing pgadmin into a separate virtual environment (venv) does provide a degree of isolation between versions of pgadmin, and allow easy rollbacks (if you use a new venv). These two steps first create the virtual env, and then uses the built in pip package wrapper provided by ansible (that allows you to specify a virtual environment) to install some dependencies.

- name: create the pgadmin virtualenv
  shell: virtualenv pgadmin4
  args:
    chdir: /usr/local/
    creates: "/usr/local/pgadmin4"

- pip:
    name: ""
    virtualenv: "/usr/local/pgadmin4"
  with_items:
    - cryptography
    - pyopenssl
    - ndg-httpsclient
    - pyasn1

Step 9: Create the pgadmin virtualenv and put some libraries into it

Download the new version of pgadmin. This comes down as a whl file (a wheel file), and then use the ansible pip wrapper to install it into your new venv.

- name: download pgadmin
  shell: wget --no-check-certificate \
 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v3.3/pip/pgadmin4-3.3-py2.py3-none-any.whl
  args:
    chdir: /usr/local/pgadmin4
    creates: "/usr/local/pgadmin4/pgadmin4-3.3-py2.py3-none-any.whl"

- pip:
    name: file:///usr/local/pgadmin4/pgadmin4-3.3-py2.py3-none-any.whl
    virtualenv: "/usr/local/pgadmin4"

Step 10: Intialize pgAdmin

So with some neat hackery here, the pgadmin4/setup.py script is invoked. The clever bit is that

  1. It’s invoked inside a virtualenv
  2. It has a guard that stops it running twice by checking to see if the /var/lib/pgadmin/storage directory is created
  3. The ‘expect’ module is used to feed the setup script a username (email address) and password for pgadmin. This allows us to run this script in batch mode, without user interaction.
- file:
  path: /var/lib/pgadmin
  state: directory

- name: initialize pgadmin
  expect:
    command: bin/python2.7 lib/python2.7/site-packages/pgadmin4/setup.py
    chdir: /usr/local/pgadmin4
    creates: "/var/lib/pgadmin/storage"
    responses:
      "Email address:": ""
      "Password:": ""
      "Retype password:": ""

Step 11: Configure pgAdmin

pgAdmin comes with a default config file called ‘config_local.py’. This step makes a copy of that file (only if the copy hasn’t been made already), and sets 2 values: the listen port (5050) and the listen address (0.0.0.0 - all adapters).

- name: Create the local config
  shell: "cp config.py config_local.py"
  args:
    chdir: ""
    creates: "/config_local.py"

- lineinfile:
  path: "/config_local.py"
  regexp: '^DEFAULT_SERVER ='
  line: "DEFAULT_SERVER = '0.0.0.0'"

- lineinfile:
  path: "/config_local.py"
  regexp: '^DEFAULT_SERVER_PORT ='
  line: "DEFAULT_SERVER_PORT = 5050"

Step 12: setup rinetd

Because I will never remember port 5050, I set up rinetd to forward traffic from port 80 to port 5050. First though I enable it in rc.conf and tell it to product a log file. Lastly I start rinetd.

- name: Enable the rinetd service
  shell: "sysrc rinetd_enable=YES"

- lineinfile:
  path: /usr/local/etc/rinetd.conf
  regexp: '.*5050$'
  line: " 80  5050"
  create: True

- lineinfile:
  path: /usr/local/etc/rinetd.conf
  line: "logfile /var/log/rinetd.log"

- lineinfile:
  path: /usr/local/etc/rinetd.conf
  line: "logcommon"

- name: Start the rinetd daemon
  service:
    name: rinetd
    state: started
    enabled: yes  

Step 13: Setup the service artefacts for pgadmin

These steps setup the FreeBSD service file, configure the rc.conf file and then start the service. As I am very proud of the service file (it’s my first one), I’m including it here verbatim, in all it’s glory.

- template:
    src: service.j2
    dest: /usr/local/etc/rc.d/pgadmin
    owner: root
    group: wheel
    mode: "u=rwx,g=r,o=r"

- name: Enable the pgadmin service
  shell: "sysrc pgadmin_enable=YES"

- name: Start the pgadmin daemon
  service:
    name: pgadmin
    state: started
    enabled: yes

The service file below is probably the simplest service file possible. Some notable aspects are:

  1. The name pgadmin4 is important, and it’s important to keep it consistent throughout the rest of the file e.g. in the start command and the rc var.
  2. the start function touches the pid file and then invokes the /usr/sbin/daemon command to start the pgadmin progress in the background and harvest it’s pid files.
  3. the daemon command executes the venv python binary against the pgAdmin4.py file. This usually starts pgAdmin in the foreground, but daemon neatly stashes it in the background and sets the pid file for you.
  4. the stderr and stdout are sent to /var/log/pgadmin.log
#!/bin/sh
# PROVIDE: pgadmin4

. /etc/rc.subr

name=pgadmin4
rcvar=pgadmin4_enable

start_cmd="pgadmin4_start"

pidfile="/var/run/${name}.pid"
cd /root/pgadmin4

pgadmin4_start()
{
  touch ${pidfile}
  touch /var/run/pgadmin2.pid
  /usr/sbin/daemon -P ${pidfile} \
      -p /var/run/pgadmin2.pid   \
      -u root                    \
      /root/pgadmin4/bin/python \
      /root/pgadmin4/lib/python2.7/site-packages/pgadmin4/pgAdmin4.py \
        >> /var/log/pgadmin.log 2>&1

}

load_rc_config $name
run_rc_command "$1"

And there you have it folks - a postgres database with pgadmin in 2 minutes.

Viola!
Tada!