Wednesday, January 16

Improvements in repmgr 3.1.4

The recently released repmgr 3.1.4 update incorporates several changes which improve usability and lay out the groundwork for enhanced compatibility with 2ndQuadrant’s barman product.

New configuration option restore_command

It’s now possible to specify a restore_command in repmgr.conf, which will be included in the recovery.conf file generated by repmgr standby clone, making it easier to configure a more robust replication setup by enabling PostgreSQL to fall back to a WAL archive source if streaming replication is interrupted. See Gabriele Bartolini’s recent blog post “Speed up getting WAL files from Barman” for an example on how to do this.

CSV output for repmgr cluster show

The repmgr cluster show command now accepts the optional parameter --csv, which outputs the status of the replication cluster in CSV format, suitable for parsing by scripts. Current “human-readble” output will look something like this:

    $ repmgr -f  /path/to/repmgr.conf cluster show

    Role      | Name  | Upstream | Connection String
    ----------+-------|----------|---------------------------------------------------
      FAILED  | node1 |          | host=localhost dbname=repmgr user=repmgr port=5501
    * master  | node2 |          | host=localhost dbname=repmgr user=repmgr port=5502
      standby | node3 | node2    | host=localhost dbname=repmgr user=repmgr port=5503

The equivalent CSV output is this:

   $ repmgr -f  /path/to/repmgr.conf cluster show --csv
   1,-1
   2,0
   3,1

with node ID as the first column, and status (0 = master, 1 = standby, -1 = failed) as
the second.

conninfo strings now accepted

repmgr operations which require the provision of database connection information, such as repmgr standby clone, now accept conninfo-style strings. This brings repmgr into line with other PostgreSQL utilities and is more than a convenient syntax – it provides much more granular control over the primary_conninfo string in the recovery.conf file generated by repmgr. This is particularly relevant when setting up SSL-based replication.

Typical conninfo string usage is something like this:

  repmgr standby clone -d 'host=foo port=6543 user=bar dbname=repmgr' -f /path/to/repmgr.conf

or in URL format:

  repmgr standby clone -d 'postgresql://[email protected]:6543/repmgr' -f /path/to/repmgr.conf

Other parameter keywords (see here for the full list) can be added as required – for example to force SSL-only replication add sslmode=require:

    $ repmgr
       -D /path/to/standby/data \
       -f /path/to/standby/repmgr.conf \
       -d 'host=localhost dbname=repmgr user=repmgr port=5501 sslmode=require' \
       -R someuser \
       standby clone

which will result in the following recovery.conf being generated:

    $ cat /path/to/standby/data/recovery.conf
    standby_mode = 'on'
    primary_conninfo = 'user=repmgr host=localhost port=5501 application_name=node2 sslmode=require sslcompression=1'
    recovery_target_timeline = 'latest'

(This does of course require that your PostgreSQL servers are configured with SSL – for more detailed information see the documentation and pages 188 ~ 195 of the PostgreSQL 9 Administration Cookbook).

Please be aware that any PostgreSQL-specific environment variables, if set, will be incorporated into the generated primary_conninfo string unless they’re explicitly overridden in the provided conninfo string.

What’s next for repmgr?

Our thanks goes out to all the users whose feedback has helped improve repmgr. For the upcoming 3.2 release, we’ll be working on integrating the replication enhancements provided by the upcoming PostgreSQL 9.6 release as well as greater integration with barman.

Leave a Reply

Your email address will not be published. Required fields are marked *