EC2 and pgpool

Some tips how to make PostgreSQL backends with pgpool work at Amazon EC2:

First of all I made script witch keeps all IP's all useful backends:
name: machine.rb
#v+

require 'resolv'

class Machine

 IMAGES   = {
         'www'           => ['ami-12345678'],
         'db'            => ['ami-23456789'],
         'pooler'        => ['ami-34567890'],
 }

 MACHINES = {
  'www'  => [],
  'db'  => [],
  'pooler' => [],
 }

 def self.getips
  output=`ec2-describe-instances`
  raise 'Not working: ' + output if !output.match(/^RESERVATION/)
  output.split("\n").each do |line| 
   IMAGES.each do |type, amis|
    amis.each do |ami|
     if line.match('\s' + ami + '\s.*?running')
      ip = Resolv.getaddress(line.split[3])
      MACHINES[type] << ip 
     end
    end
   end
  end
  MACHINES
 end
end

#v-

So after call 'Machine.getips' I will receive table with www,db,pooler and IPs. Now lets say I wanna create /etc/hosts with current list all my backends (useful for csync2 witch need hosts not IP)

name: createhosts.rb
#v+

#!/usr/bin/ruby

require '/path/to/machine.rb'
require "ftools"

def command_output(command)
 output = nil
 IO.popen(command) do |f| output = f.readlines end
 output
end

ips = Machine.getips
local_ip = command_output('/sbin/ip addr show eth0')[2][/inet (.*?)\//,1]

if File.exists?("/etc/hosts") == false
 f = File.new("/etc/hosts", "w+")
 f.close
end

f = File.open('/etc/hosts.tmp','w')

f.puts "127.0.0.1 localhost\n" +
 ips['pooler'][0] + " db.pooler\n" +
 local_ip + " " + local_ip.gsub(/\./,'_') + ".domain " + local_ip.gsub(/\./,'_') + "\n" +
 local_ip + " pooler.domain pooler\n"

ips['www'].each do |ip|
 f.puts ip + " " + ip.gsub(/\./,'_')
end

ips['db'].each do |ip|
     f.puts ip + "   " + ip.gsub(/\./,'_')
end


f.close

File.move("/etc/hosts.tmp", "/etc/hosts")

#v-

Also it's easy to create some script for certain use like this:

name: getpooler.rb
#v+

#!/usr/bin/ruby

require '/path/to/machine.rb'

puts Machine.getips['pooler'][0]

#v-

Witch gimme IP of host with pgpool.

Now let's say I got fresh Fedora Core 9 with postgresql. At /etc/rc.d/rc.local of my image put such line:

echo "host    all         all         `/path/to/getpooler.rb` 255.255.254.0   trust" >> /var/lib/pgsql/data/pg_hba.conf

So trust access will be from my pgpool host. Postgresql.conf file it's goot to keep in some SVN, so easy to change there options. So correct create image should give us always fresh image of postgresql witch know where is pgpool.