Class: ActivePostgres::SSHExecutor

Inherits:
Object
  • Object
show all
Includes:
SSHKit::DSL
Defined in:
lib/active_postgres/ssh_executor.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(config, quiet: false) ⇒ SSHExecutor

Returns a new instance of SSHExecutor.



12
13
14
15
16
 
# File 'lib/active_postgres/ssh_executor.rb', line 12

def initialize(config, quiet: false)
  @config = config
  @quiet = quiet
  setup_sshkit
end

Instance Attribute Details

#config ⇒ Object (readonly)

Returns the value of attribute config.



10
11
12
 
# File 'lib/active_postgres/ssh_executor.rb', line 10

def config
  @config
end

Instance Method Details

#ensure_cluster_exists(host, version) ⇒ Object 



247
248
249
250
251
252
253
254
255
256
257
258
 
# File 'lib/active_postgres/ssh_executor.rb', line 247

def ensure_cluster_exists(host, version)
  execute_on_host(host) do
    data_dir = "/var/lib/postgresql/#{version}/main"

    if test(:sudo, 'test', '-d', data_dir)
      info "PostgreSQL #{version}/main cluster already exists, skipping creation"
    else
      info 'Creating PostgreSQL cluster...'
      execute :sudo, 'pg_createcluster', version.to_s, 'main', '--start'
    end
  end
end

#ensure_postgres_user(host) ⇒ Object 



129
130
131
132
133
134
135
136
137
138
139
140
 
# File 'lib/active_postgres/ssh_executor.rb', line 129

def ensure_postgres_user(host)
  postgres_user = config.postgres_user

  execute_on_host(host) do
    execute :sudo, 'groupadd', '--system', postgres_user unless test(:getent, 'group', postgres_user)

    unless test(:id, postgres_user)
      execute :sudo, 'useradd', '--system', '--home', '/var/lib/postgresql',
              '--shell', '/bin/bash', '--gid', postgres_user, '--create-home', postgres_user
    end
  end
end

#execute_on_all_hosts ⇒ Object 



39
40
41
42
 
# File 'lib/active_postgres/ssh_executor.rb', line 39

def execute_on_all_hosts(&)
  hosts = config.all_hosts.map { |h| "#{config.user}@#{h}" }
  on(hosts, in: :parallel, &)
end

#execute_on_host(host) ⇒ Object 



22
23
24
 
# File 'lib/active_postgres/ssh_executor.rb', line 22

def execute_on_host(host, &)
  on("#{config.user}@#{host}", &)
end

#execute_on_host_as(host, user) ⇒ Object 



26
27
28
 
# File 'lib/active_postgres/ssh_executor.rb', line 26

def execute_on_host_as(host, user, &)
  on("#{user}@#{host}", &)
end

#execute_on_primary ⇒ Object 



30
31
32
 
# File 'lib/active_postgres/ssh_executor.rb', line 30

def execute_on_primary(&)
  execute_on_host(config.primary_host, &)
end

#execute_on_standbys ⇒ Object 



34
35
36
37
 
# File 'lib/active_postgres/ssh_executor.rb', line 34

def execute_on_standbys(&)
  hosts = config.standby_hosts.map { |h| "#{config.user}@#{h}" }
  on(hosts, in: :parallel, &)
end

#get_postgres_status(host) ⇒ Object 



202
203
204
205
206
207
208
209
 
# File 'lib/active_postgres/ssh_executor.rb', line 202

def get_postgres_status(host)
  result = nil
  postgres_user = config.postgres_user
  execute_on_host(host) do
    result = capture(:sudo, '-u', postgres_user, 'psql', '-c', 'SELECT version();')
  end
  result
end

#install_postgres(host, version = 18) ⇒ Object 



44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
 
# File 'lib/active_postgres/ssh_executor.rb', line 44

def install_postgres(host, version = 18)
  execute_on_host(host) do
    info "Installing PostgreSQL #{version}..."

    if test('[ -f /etc/apt/sources.list.d/pgdg.list ]')
      execute :sudo, 'rm', '-f',
              '/etc/apt/sources.list.d/pgdg.list'
    end

    execute :sudo, 'apt-get', '-o', 'DPkg::Lock::Timeout=300', 'update', '-qq'
    execute :sudo, 'DEBIAN_FRONTEND=noninteractive', 'apt-get',
            '-o', 'DPkg::Lock::Timeout=300', 'install', '-y', '-qq',
            'gnupg', 'wget', 'lsb-release', 'locales'

    info 'Generating locales...'
    execute :sudo, 'locale-gen', 'en_US.UTF-8'
    execute :sudo, 'update-locale', 'LANG=en_US.UTF-8'

    # Check for any installed PostgreSQL server packages
    if test('command -v pg_lsclusters')
      existing_clusters = capture(:pg_lsclusters, '-h').split("\n")
      installed_versions = existing_clusters.map { |line| line.split[0].to_i }.uniq.sort

      # Check if we have different versions installed
      other_versions = installed_versions - [version]

      if other_versions.any?
        info "Found PostgreSQL version(s) #{other_versions.join(', ')}, cleaning up for fresh PostgreSQL #{version} install..."

        # Stop all PostgreSQL services
        execute :sudo, 'systemctl', 'stop', 'postgresql' if test('systemctl is-active postgresql')

        # Remove all PostgreSQL packages
        execute :sudo, 'DEBIAN_FRONTEND=noninteractive', 'apt-get',
                '-o', 'DPkg::Lock::Timeout=300', 'remove', '--purge', '-y', '-qq', 'postgresql*'
        execute :sudo, 'apt-get', '-o', 'DPkg::Lock::Timeout=300', 'autoremove', '-y', '-qq'

        # Clean up OLD version directories only (preserve target version SSL certs, etc.)
        other_versions.each do |old_version|
          execute :sudo, 'rm', '-rf', "/etc/postgresql/#{old_version}"
          execute :sudo, 'rm', '-rf', "/var/lib/postgresql/#{old_version}"
        end

        execute :sudo, 'rm', '-f', '/etc/apt/sources.list.d/pgdg.list'
        execute :sudo, 'rm', '-f', '/usr/share/keyrings/postgresql-archive-keyring.gpg'

        info 'Cleanup complete'
      elsif installed_versions.include?(version)
        info "PostgreSQL #{version} already installed, skipping cleanup"
      end
    end

    info 'Ensuring PostgreSQL GPG key is present...'
    execute :wget, '--quiet', '-O', '/tmp/pgdg.asc', 'https://www.postgresql.org/media/keys/ACCC4CF8.asc'
    execute :sudo, 'gpg', '--dearmor', '--yes', '-o', '/usr/share/keyrings/postgresql-archive-keyring.gpg',
            '/tmp/pgdg.asc'
    execute :rm, '/tmp/pgdg.asc'

    info 'Configuring PostgreSQL apt repository...'
    pgdg_repo = "'echo \"deb [signed-by=/usr/share/keyrings/postgresql-archive-keyring.gpg] " \
                'https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > ' \
                "/etc/apt/sources.list.d/pgdg.list'"
    execute :sudo, 'sh', '-c', pgdg_repo

    execute :sudo, 'apt-get', '-o', 'DPkg::Lock::Timeout=300', 'update', '-qq'
    execute :sudo, 'DEBIAN_FRONTEND=noninteractive', 'apt-get',
            '-o', 'DPkg::Lock::Timeout=300', 'install', '-y', '-qq',
            "postgresql-#{version}", "postgresql-client-#{version}"

    execute :sudo, 'systemctl', 'enable', 'postgresql'
    execute :sudo, 'systemctl', 'start', 'postgresql' unless test('systemctl is-active postgresql')
  end
end

#postgres_running?(host) ⇒ Boolean

Returns:

  • (Boolean) 


142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
 
# File 'lib/active_postgres/ssh_executor.rb', line 142

def postgres_running?(host)
  result = false
  execute_on_host(host) do
    # Check if any PostgreSQL cluster is online using pg_lsclusters
    # This works regardless of whether it's the generic postgresql service
    # or a specific postgresql@version-main service
    clusters = begin
      capture(:sudo, 'pg_lsclusters', '2>/dev/null')
    rescue StandardError
      ''
    end
    result = clusters.include?('online')
  end
  result
end

#quiet? ⇒ Boolean

Returns:

  • (Boolean) 


18
19
20
 
# File 'lib/active_postgres/ssh_executor.rb', line 18

def quiet?
  @quiet
end

#recreate_cluster(host, version) ⇒ Object 



260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
 
# File 'lib/active_postgres/ssh_executor.rb', line 260

def recreate_cluster(host, version)
  execute_on_host(host) do
    info 'Ensuring clean cluster state...'
    begin
      execute :sudo, 'systemctl', 'stop', 'postgresql'
    rescue StandardError
      nil
    end
    begin
      execute :sudo, 'pg_dropcluster', '--stop', version.to_s, 'main'
    rescue StandardError
      nil
    end
    begin
      execute :sudo, 'rm', '-rf', "/etc/postgresql/#{version}/main"
    rescue StandardError
      nil
    end
    begin
      execute :sudo, 'rm', '-rf', "/var/lib/postgresql/#{version}/main"
    rescue StandardError
      nil
    end

    info 'Creating fresh PostgreSQL cluster...'
    execute :sudo, 'pg_createcluster', version.to_s, 'main'
    execute :sudo, 'systemctl', 'start', 'postgresql'
  end
end

#restart_postgres(host, version = nil) ⇒ Object 



158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
 
# File 'lib/active_postgres/ssh_executor.rb', line 158

def restart_postgres(host, version = nil)
  execute_on_host(host) do
    if version
      begin
        execute :sudo, 'pg_ctlcluster', version.to_s, 'main', 'restart'
      rescue StandardError => e
        error "Failed to restart PostgreSQL cluster #{version}/main"
        info 'Checking systemd logs...'
        logs = begin
          capture(:sudo, 'journalctl', '-xeu', "postgresql@#{version}-main", '-n', '50',
                  '--no-pager')
        rescue StandardError
          'Could not get systemd logs'
        end
        info logs
        info 'Checking PostgreSQL logs...'
        pg_logs = begin
          capture(:sudo, 'tail', '-100',
                  "/var/log/postgresql/postgresql-#{version}-main.log")
        rescue StandardError
          'Could not get PostgreSQL logs'
        end
        info pg_logs
        info 'Checking cluster status...'
        cluster_status = begin
          capture(:sudo, 'pg_lsclusters')
        rescue StandardError
          'Could not get cluster status'
        end
        info cluster_status
        raise e
      end
    else
      execute :sudo, 'systemctl', 'restart', 'postgresql'
    end
  end
end

#run_sql(host, sql, postgres_user: config.postgres_user, port: nil, database: nil, tuples_only: true, capture: true) ⇒ Object 



211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
 
# File 'lib/active_postgres/ssh_executor.rb', line 211

def run_sql(host, sql, postgres_user: config.postgres_user, port: nil, database: nil, tuples_only: true,
            capture: true)
  result = nil
  executor = self
  execute_on_host(host) do
    backend = self
    result = executor.run_sql_on_backend(backend, sql,
                                postgres_user: postgres_user,
                                port: port,
                                database: database,
                                tuples_only: tuples_only,
                                capture: capture)
  end
  result
end

#run_sql_on_backend(backend, sql, postgres_user: config.postgres_user, port: nil, database: nil, tuples_only: true, capture: true) ⇒ Object 



227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
 
# File 'lib/active_postgres/ssh_executor.rb', line 227

def run_sql_on_backend(backend, sql, postgres_user: config.postgres_user, port: nil, database: nil,
                       tuples_only: true, capture: true)
  # Use a temporary file to avoid shell escaping issues with special characters
  temp_file = "/tmp/active_postgres_#{SecureRandom.hex(8)}.sql"
  backend.upload! StringIO.new(sql), temp_file
  backend.execute :chmod, '600', temp_file
  backend.execute :sudo, 'chown', "#{postgres_user}:#{postgres_user}", temp_file

  cmd = [:sudo, '-u', postgres_user, 'psql']
  cmd << '-t' if tuples_only
  cmd += ['-p', port.to_s] if port
  cmd += ['-d', database.to_s] if database
  cmd += ['-f', temp_file]

  result = capture ? backend.capture(*cmd) : backend.execute(*cmd)
  result
ensure
  backend.execute :sudo, 'rm', '-f', temp_file
end

#stop_postgres(host) ⇒ Object 



196
197
198
199
200
 
# File 'lib/active_postgres/ssh_executor.rb', line 196

def stop_postgres(host)
  execute_on_host(host) do
    execute :sudo, 'systemctl', 'stop', 'postgresql'
  end
end

#upload_file(host, content, remote_path, mode: '644', owner: nil) ⇒ Object 



118
119
120
121
122
123
124
125
126
127
 
# File 'lib/active_postgres/ssh_executor.rb', line 118

def upload_file(host, content, remote_path, mode: '644', owner: nil)
  execute_on_host(host) do
    temp_file = "/tmp/#{File.basename(remote_path)}"
    upload! StringIO.new(content), temp_file

    execute :sudo, 'mv', temp_file, remote_path
    execute :sudo, 'chown', owner, remote_path if owner
    execute :sudo, 'chmod', mode, remote_path
  end
end