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:
-
AmazonLinux 2015.09: 9.2 9.3 9.4
-
AmazonLinux 2017.09: 9.4 9.5 9.6
-
Debian 8: 9.4
-
Debian 9: 9.6
-
Ubuntu 14: 9.3
-
Ubuntu 16: 9.5
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
WAL log segments (16MB each) Deprecated with PostgreSQL 9.5: Use min/max_wal_size instead
Commit delay in microseconds 10000 or more may be desirable on high-latency storage, at increased risk. (PG Default: 0 -> none)
Concurrent disk I/O operations May help to use RAID device count or similar (PG Default: 1)
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”)
Method used in pg_hba.conf for local (unix socket) access (PG Default: :peer)
Method used in pg_hba.conf for local network access. Note that :peer does not work here. (PG Default: :md5)
Maintenance work memory (Default: '128MB' vs PG 9.4+: '64MB')
Maximum stack depth (Default: '4MB' vs PG: '2MB')
Maximum WAL size as string with units. (Default: unset, PG Default: '1GB')
Minimum WAL size as string with units Default: PG <9.5: “48MB”; PG 9.5+: “80MB”
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)
IPv4 address mask for network_access (PG Default: nil -> no IPv4 access)
IPv6 address mask for network_access (PG Default: nil -> no IPv4 access)
The package names, including PostgreSQL server of the desired version to install. (Default: guess based on distro)
Location of postgresql data (and possibly also config) directory. (Default: pg_default_data_dir)
The default data dir as used by the distro package_names. (Default: as per RHEL/Debian package conventions)
Configuration in the '/etc' directory root? (Default: true on Debian only, per distro package conventions)
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')
The service name of the PostgreSQL server to start (Default: 'postgresql' )
Synchronization level for commit :off may be desirable on high-latency storage (i.e. EBS), at increased risk. (PG Default: :on)
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 memory (Default: '128MB' vs PG 9.4+: '4MB')
Public Class Methods
# 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
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
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
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
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
# File lib/syncwrap/components/postgresql.rb, line 106 def pg_data_dir @pg_data_dir || pg_default_data_dir end
# 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
# 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
Reload server configuration
# File lib/syncwrap/components/postgresql.rb, line 414 def reload dist_service( service_name, 'reload' ) end
Restart the server
# File lib/syncwrap/components/postgresql.rb, line 399 def restart dist_service( service_name, 'restart' ) end
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 the server
# File lib/syncwrap/components/postgresql.rb, line 394 def start dist_service( service_name, 'start' ) end
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 the server
# File lib/syncwrap/components/postgresql.rb, line 404 def stop dist_service( service_name, 'stop' ) end
Protected Instance Methods
# File lib/syncwrap/components/postgresql.rb, line 184 def checkpoint_segments @checkpoint_segments || ( version_lt?(pg_version, [9,5]) ? 3 : 5 ) end
# File lib/syncwrap/components/postgresql.rb, line 192 def min_wal_size @min_wal_size || "#{ checkpoint_segments * 16 }MB" end
# 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
# 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
# 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
# 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