class SyncWrap::PostgreSQL

Provisions for install and configuration of a PostgreSQL server

Host component dependencies: <Distro>

Currently provided (sync/postgresql) configuration is PostgreSQL 9.4+ and 10+ compatible. You will need to provide your own complete configuration for any other versions.

Most distros provide reasonably updated PostgreSQL 9.x in more recent releases:

The latest stable and beta packages can also be obtained via the PostgreSQL Yum Repo or Apt Repo. Create your own repo component to install these repo's, then configure the PostgreSQL component accordingly for pg_version, pg_default_data_dir, and package_names, etc.

Attributes

checkpoint_segments[W]

WAL log segments (16MB each) Deprecated with PostgreSQL 9.5: Use min/max_wal_size instead

commit_delay[RW]

Commit delay in microseconds 10000 or more may be desirable on high-latency storage, at increased risk. (PG Default: 0 -> none)

effective_io_concurrency[RW]

Concurrent disk I/O operations May help to use RAID device count or similar (PG Default: 1)

initdb_cmd[RW]

A command pattern to initialize the database on first install. This is used on systemd distro's only. The pattern is expanded using pg_data_dir as the first (optional) replacement. The command is run by the postgres user.

(Default: “/usr/bin/initdb %s”)

local_access[RW]

Method used in pg_hba.conf for local (unix socket) access (PG Default: :peer)

local_network_access[RW]

Method used in pg_hba.conf for local network access. Note that :peer does not work here. (PG Default: :md5)

maintenance_work_mem[RW]

Maintenance work memory (Default: '128MB' vs PG 9.4+: '64MB')

max_stack_depth[RW]

Maximum stack depth (Default: '4MB' vs PG: '2MB')

max_wal_size[RW]

Maximum WAL size as string with units. (Default: unset, PG Default: '1GB')

min_wal_size[W]

Minimum WAL size as string with units Default: PG <9.5: “48MB”; PG 9.5+: “80MB”

network_access[RW]

Method used in pg_hba.conf for network access :md5 is a common value for password auth. If truthy, will also set listen_address = '*' in postgresql.conf (PG Default: false -> no access)

network_v4_mask[RW]

IPv4 address mask for network_access (PG Default: nil -> no IPv4 access)

network_v6_mask[RW]

IPv6 address mask for network_access (PG Default: nil -> no IPv4 access)

package_names[W]

The package names, including PostgreSQL server of the desired version to install. (Default: guess based on distro)

pg_data_dir[W]

Location of postgresql data (and possibly also config) directory. (Default: pg_default_data_dir)

pg_default_data_dir[W]

The default data dir as used by the distro package_names. (Default: as per RHEL/Debian package conventions)

pg_specify_etc_config[W]

Configuration in the '/etc' directory root? (Default: true on Debian only, per distro package conventions)

pg_version[W]

PostgreSQL MAJOR.MINOR (e.g. '9.6') or MAJOR (e.g. '10') version to install, not including the patch release number. As of PostgreSQL 10, this is the single value '10'. Since there are multiple versions in use even for default system packages across distros, this should be set the same as the version that will be installed via package_names. (Default: guess based on distro/version or '9.1')

service_name[RW]

The service name of the PostgreSQL server to start (Default: 'postgresql' )

shared_buffers[RW]

Shared buffers (Default: '256MB' vs PG: '128MB')

shared_memory_max[W]

Kernel SHMMAX (Shared Memory Maximum) setting to apply. Note that PostgreSQL 9.3 uses mmap and should not need this. Currently this only set on Debian distros. (Default: 300MB if pg_version < 9.3)

synchronous_commit[RW]

Synchronization level for commit :off may be desirable on high-latency storage (i.e. EBS), at increased risk. (PG Default: :on)

version=[W]

PostgreSQL MAJOR.MINOR (e.g. '9.6') or MAJOR (e.g. '10') version to install, not including the patch release number. As of PostgreSQL 10, this is the single value '10'. Since there are multiple versions in use even for default system packages across distros, this should be set the same as the version that will be installed via package_names. (Default: guess based on distro/version or '9.1')

work_mem[RW]

Work memory (Default: '128MB' vs PG 9.4+: '4MB')

Public Class Methods

new( opts = {} ) click to toggle source
Calls superclass method SyncWrap::Component.new
# File lib/syncwrap/components/postgresql.rb, line 270
def initialize( opts = {} )
  @pg_data_dir = nil
  @pg_default_data_dir = nil
  @pg_version = nil
  @pg_specify_etc_config = nil
  @package_names = nil
  @service_name = 'postgresql'
  @synchronous_commit = :on
  @commit_delay = 0
  @checkpoint_segments = nil
  @min_wal_size = nil
  @max_wal_size = nil
  @shared_buffers = '256MB'
  @work_mem = '128MB'
  @maintenance_work_mem = '128MB'
  @max_stack_depth = '4MB'
  @effective_io_concurrency = 1
  @local_access = :peer
  @local_network_access = :md5
  @network_access = false
  @network_v4_mask = nil
  @network_v6_mask = nil
  @shared_memory_max = nil
  @initdb_cmd = "/usr/bin/initdb %s"
  super
end

Public Instance Methods

compact_pg_version() click to toggle source

Per pg_version, but with any '.' decimal separators removed, e.g. '9.6' => '96', '10' => '10'.

# File lib/syncwrap/components/postgresql.rb, line 98
def compact_pg_version
  pg_version.gsub('.','')
end
install() click to toggle source

Calls in order: package_install, setup_data_dir, and pg_configure then ensures the server is running (via pg_start) or is restarted (via pg_restart) if there were configuration changes.

# File lib/syncwrap/components/postgresql.rb, line 300
def install
  package_install
  changes = setup_data_dir
  changes += pg_configure
  if changes.empty?
    pg_start
  else
    pg_restart
  end
  changes
end
package_install() click to toggle source

Install the package_names. In the Debian case, also install any shared_memory_max adjustment and stops the server for subsequent reconfigure or data relocation.

# File lib/syncwrap/components/postgresql.rb, line 315
def package_install
  if distro.is_a?( Debian )
    dist_if_not_installed?( package_names ) do
      dist_install( *package_names, check_install: false )
      pg_stop
    end
    if shared_memory_max
      c = rput( 'etc/sysctl.d/61-postgresql-shm.conf', user: :root )
      unless c.empty?
        sudo "sysctl -p /etc/sysctl.d/61-postgresql-shm.conf"
      end
      c
    end
  else
    dist_install( *package_names )
  end
end
pg_configure() click to toggle source

Update the PostgreSQL configuration files

# File lib/syncwrap/components/postgresql.rb, line 386
def pg_configure
  files  = %w[ pg_hba.conf pg_ident.conf postgresql.conf ]
  files += %w[ environment pg_ctl.conf ] if distro.is_a?( Debian )
  files  = files.map { |f| File.join( 'postgresql', f ) }
  rput( *files, pg_config_dir, user: 'postgres' )
end
pg_data_dir() click to toggle source
# File lib/syncwrap/components/postgresql.rb, line 106
def pg_data_dir
  @pg_data_dir || pg_default_data_dir
end
pg_initdb() click to toggle source
# File lib/syncwrap/components/postgresql.rb, line 371
def pg_initdb
  if distro.systemd?
    if initdb_cmd
      sudo <<-SH
        su postgres -c '#{initdb_cmd % [ pg_data_dir ]}'
      SH
    else
      raise ContextError, "PostgreSQL#initdb_cmd is required with systemd"
    end
  else
    dist_service( service_name, 'initdb' )
  end
end
pg_version() click to toggle source
# File lib/syncwrap/components/postgresql.rb, line 66
def pg_version
  ( @pg_version ||
    ( case distro
      when AmazonLinux
        if version_gte?( amazon_version, [2017,9] )
          '9.6'
        elsif version_gte?( amazon_version, [2015,9] )
          '9.4'
        elsif version_gte?( amazon_version, [2013,3] )
          '9.2'
        end
      when RHEL
        '9.2' if version_gte?( rhel_version, [7] )
      when Ubuntu
        if version_gte?( ubuntu_version, [16,4] )
          '9.5'
        elsif version_gte?( ubuntu_version, [14,4] )
          '9.3'
        end
      when Debian
        if version_gte?( debian_version, [9] )
          '9.6'
        elsif version_gte?( debian_version, [8] )
          '9.4'
        end
      end
      ) ||
      '9.1' )
end
Also aliased as: version
reload() click to toggle source

Reload server configuration

# File lib/syncwrap/components/postgresql.rb, line 414
def reload
  dist_service( service_name, 'reload' )
end
restart() click to toggle source

Restart the server

# File lib/syncwrap/components/postgresql.rb, line 399
def restart
  dist_service( service_name, 'restart' )
end
Also aliased as: pg_restart
setup_data_dir() click to toggle source

Initialize or move the server data directory as per pg_data_dir.

# File lib/syncwrap/components/postgresql.rb, line 334
def setup_data_dir
  changes = []

  case distro

  when RHEL
    unless pg_data_dir == pg_default_data_dir
      changes = rput( 'etc/sysconfig/pgsql/postgresql', user: :root )
    end

    sudo_if( "[ ! -d '#{pg_data_dir}/base' ]" ) do
      sudo <<-SH
        mkdir -p #{pg_data_dir}
        chown postgres:postgres #{pg_data_dir}
        chmod 700 #{pg_data_dir}
      SH
      pg_initdb
    end

  when Debian
    unless pg_data_dir == pg_default_data_dir
      sudo <<-SH
        if [ ! -d '#{pg_data_dir}/base' ]; then
           mkdir -p #{pg_data_dir}
           chown postgres:postgres #{pg_data_dir}
           chmod 700 #{pg_data_dir}
           mv #{pg_default_data_dir}/* #{pg_data_dir}/
        fi
      SH
    end
  else
    raise ContextError, "Distro #{distro.class.name} not supported"
  end

  changes
end
start() click to toggle source

Start the server

# File lib/syncwrap/components/postgresql.rb, line 394
def start
  dist_service( service_name, 'start' )
end
Also aliased as: pg_start
status() click to toggle source

Output the server status (useful via CLI with –verbose)

# File lib/syncwrap/components/postgresql.rb, line 409
def status
  dist_service( service_name, 'status' )
end
stop() click to toggle source

Stop the server

# File lib/syncwrap/components/postgresql.rb, line 404
def stop
  dist_service( service_name, 'stop' )
end
Also aliased as: pg_stop

Protected Instance Methods

checkpoint_segments() click to toggle source
# File lib/syncwrap/components/postgresql.rb, line 184
def checkpoint_segments
  @checkpoint_segments || ( version_lt?(pg_version, [9,5]) ? 3 : 5 )
end
min_wal_size() click to toggle source
# File lib/syncwrap/components/postgresql.rb, line 192
def min_wal_size
  @min_wal_size || "#{ checkpoint_segments * 16 }MB"
end
package_names() click to toggle source
# File lib/syncwrap/components/postgresql.rb, line 156
def package_names
  ( @package_names ||
    ( distro.is_a?( Debian ) && [ "postgresql-#{pg_version}" ] ) ||
    ( distro.is_a?( AmazonLinux ) &&
      ( ( version_gte?( amazon_version, [2014,9] ) &&
          [ "postgresql#{compact_pg_version}-server" ] ) ||
        [ "postgresql9-server" ] ) ) ||
    [ "postgresql-server" ] )
end
pg_config_dir() click to toggle source
# File lib/syncwrap/components/postgresql.rb, line 257
def pg_config_dir
  case distro
  when RHEL
    pg_data_dir
  when Debian
    "/etc/postgresql/#{pg_version}/main"
  else
    raise ContextError, "Distro #{distro.class.name} not supported"
  end
end
pg_default_data_dir() click to toggle source
# File lib/syncwrap/components/postgresql.rb, line 124
def pg_default_data_dir
  ( @pg_default_data_dir ||
    ( case distro
      when AmazonLinux
        '/var/lib/pgsql9/data'
      when RHEL
        if version_lt?( rhel_version, [7] )
          '/var/lib/pgsql9/data'
        end
      when Debian
        "/var/lib/postgresql/#{pg_version}/main"
      end ) ||
    '/var/lib/pgsql/data' )
end
pg_restart()
Alias for: restart
pg_specify_etc_config() click to toggle source
# File lib/syncwrap/components/postgresql.rb, line 143
def pg_specify_etc_config
  if @pg_specify_etc_config.nil?
    distro.is_a?( Debian )
  else
    @pg_specify_etc_config
  end
end
pg_start()
Alias for: start
pg_stop()
Alias for: stop
shared_memory_max() click to toggle source
# File lib/syncwrap/components/postgresql.rb, line 253
def shared_memory_max
  @shared_memory_max || ( version_lt?(pg_version, [9,3]) && 300_000_000 )
end
version()

Deprecated

Alias for: pg_version