czwartek, 31 lipca 2008

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.

czwartek, 17 lipca 2008

Fatboy Slim: Big Beach Boutique II - Live On Brighton Beach DVD


Świetna impreza, genialnego Fatboy Slim-a. Jak imprezować to tylko na imprezach z jego udziałem. Gorąco polecam.

ps. Poszukuje gdzie można kupić _oryginalną_ płytkę z Fatboy Slim - In Brazil - Incredible Adventures. Jak ktoś ma jakiekolwiek namiary proszę o informacje.

środa, 9 lipca 2008

PostgreSQL + pgpool-II - replication mode + online-recovery

Since a lot ppl translate that to English with google translator I decide to make them little help.

After few weeks trail-end-error method find out that replication in postgresql with multimaster and online-recovery is really simple. So:

I use:
- pgpool-II
- PostgreSQL 8.3.3 from Fedora Core 9

Testing environment was: Amazon EC2

Start configuration:
- Hostname: db, IP: 10.0.0.1, Use for: Pgpool-II node
- Hostname: pghost1, IP: 10.0.0.x, Use for: PostgreSQL node 1
- Hostname: pghost2, IP: 10.0.0.z, Use for: PostgreSQL node 2

First from pgpool-a side (/etc/pgpool.conf):


listen_addresses = '*'
port = 5432
pcp_port = 9898
socket_dir = '/var/run'
pcp_socket_dir = '/var/run'
backend_socket_dir = '/var/run'
pcp_timeout = 10
num_init_children = 32
max_pool = 4
child_life_time = 300
connection_life_time = 0
client_idle_limit=600
authentication_timeout=0
child_max_connections = 0
logdir = '/var/log'
replication_mode = true
replication_strict = true
replication_timeout = 5000
load_balance_mode = true
replication_stop_on_mismatch = true
replicate_select = false
reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
print_timestamp = true
master_slave_mode = false
connection_cache = true
health_check_timeout = 20
health_check_period = 60
health_check_user = 'hchecker'
failover_command = ''
failback_command = ''
insert_lock = false
ignore_leading_white_space = true
recovery_user='pgrecov'
recovery_password = ''
recovery_1st_stage_command = 'base-backup.sh'
recovery_2nd_stage_command = 'pgpool-recovery'
log_statement = true
log_connections = true
log_hostname = true
parallel_mode = false
enable_query_cache = false
pgpool2_hostname = 'db'
enable_pool_hba = false

backend_hostname0 = '10.0.0.x'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/data'

backend_hostname1 = '10.0.0.z'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/data'


also /etc/pcp.conf:
admin:a8555f20b804e53934ee112121ed3r5

From postgresql side:
Account for pgpoola:
  • User: pooler
  • Password: NONE
  • Permissions: superuser.
In pg_hba.conf:
  • host all pooler 10.0.0.1/32 trust
At all postgresql servers:
  • psql -f pgpool-recovery.sql template1 (in source of pgpool you can find that file)
  • also should be compile pgpool function for online-recovery
  • should be turn on PITR in postgresql server
  • for each postgresql server data dir should contain 3 scripts: base-backup.sh, pgpool-recovery, pgpool_remote_start.
base-backup.sh:
#!/bin/sh
# PGDATA path for master node
DATA=$1
# Recovery host name
RECOVERY_TARGET=$2
# PGDATA path for recovery node
RECOVERY_DATA=$3
# ok, this needs to be issued on some running backend, so I
# personally use pcp_node_count and pcp_node_info to get the
# ip address of a master (script follows, as soon as the
# cluster is running well
psql -c "select pg_start_backup('pgpool-recovery')" postgres
# I would favor /tmp/pgpool-recovery instead of /data,
# as it contains live data
echo "restore_command = 'scp $HOSTNAME:/data/archive_log/%f %p'" > /var/lib/pgsql/data/recovery.conf
# I guess pgsql is the datadir I've with /var/lib/postgresql/8.3/main
# under debian?
tar -C /mnt -zcf pgsql.tar.gz pgsql
psql -c 'select pg_stop_backup()' postgres
scp /mnt/pgsql/data/pgsql.tar.gz $RECOVERY_TARGET:/mnt/pgsql/

In my version of that script, data dir is unpack in /mnt cause I keep there postgresql server. It's place where I mount Amazon EC2 storage. Links to /var/lib/pgsql -> /mnt/pgsql

pgpool-recovery:
#!/bin/sh
# Archive a current xlog.
psql -c 'select pg_switch_xlog()' postgres


pgpool_remote_start:
#!/bin/sh
DEST=$1
DESTDIR=$2
PGCTL=/usr/bin/pg_ctl

# Expand a base backup
ssh -T $DEST 'cd /mnt/; tar zxf pgsql/pgsql.tar.gz' 2>/dev/null 1>/dev/null < /dev/null # Startup PostgreSQL server ssh -T -l root $DEST /etc/init.d/postgresql restart 2>/dev/null 1>/dev/null < /dev/null &


As far as I notice doesn't work yet restart when postgresql server is working.

Run pgpool:

# /usr/bin/pgpool -f ../etc/pgpool.conf -F ../etc/pcp.conf -n  > /var/log/pgpool/pgpool.log 2>&1 & 

Now small test:
  1. Turn off one postgresql server,
  2. Made some changes at base,
  3. Online-recovery: pcp_recovery_node 5 localhost 9898 admin secret_pass 1
  4. pgpool made backup at working node(node0) - checkpoint + tar data dir,
  5. Backup move at unworking node(node1),
  6. Second checkpoint copy WALs, connections from client side are stop,
  7. Untar,
  8. Turn on postgres at node1.


Debug:

2008-07-10 15:03:43 DEBUG: pid 15173: starting health checking
2008-07-10 15:03:43 DEBUG: pid 15173: health_check: 0 the DB node status: 2
2008-07-10 15:03:43 DEBUG: pid 15173: health_check: 1 the DB node status: 3
2008-07-10 15:03:49 DEBUG: pid 15207: I am PCP 15207 accept fd 5
2008-07-10 15:03:49 DEBUG: pid 15207: pcp_child: received PCP packet type of service 'M'
2008-07-10 15:03:49 DEBUG: pid 15207: pcp_child: salt sent to the client
2008-07-10 15:03:49 DEBUG: pid 15207: pcp_child: received PCP packet type of service 'R'
2008-07-10 15:03:49 DEBUG: pid 15207: pcp_child: authentication OK
2008-07-10 15:03:49 DEBUG: pid 15207: pcp_child: received PCP packet type of service 'O'
2008-07-10 15:03:49 DEBUG: pid 15207: pcp_child: start online recovery
2008-07-10 15:03:49 LOG: pid 15207: starting recovering node 1
2008-07-10 15:03:49 DEBUG: pid 15207: exec_checkpoint: start checkpoint
2008-07-10 15:03:49 DEBUG: pid 15207: exec_checkpoint: finish checkpoint
2008-07-10 15:03:49 LOG: pid 15207: CHECKPOINT in the 1st stage done
2008-07-10 15:03:49 LOG: pid 15207: starting recovery command: "SELECT pgpool_recovery('base-backup.sh', '10.0.0.y', '/var/lib/pgsql/data')"
2008-07-10 15:03:49 DEBUG: pid 15207: exec_recovery: start recovery
2008-07-10 15:04:43 DEBUG: pid 15173: starting health checking
2008-07-10 15:04:43 DEBUG: pid 15173: health_check: 0 the DB node status: 2
2008-07-10 15:04:43 DEBUG: pid 15173: health_check: 1 the DB node status: 3
2008-07-10 15:05:08 DEBUG: pid 15207: exec_recovery: finish recovery
2008-07-10 15:05:08 LOG: pid 15207: 1st stage is done
2008-07-10 15:05:08 LOG: pid 15207: starting 2nd stage
2008-07-10 15:05:08 LOG: pid 15207: all connections from clients have been closed
2008-07-10 15:05:08 DEBUG: pid 15207: exec_checkpoint: start checkpoint
2008-07-10 15:05:09 DEBUG: pid 15207: exec_checkpoint: finish checkpoint
2008-07-10 15:05:09 LOG: pid 15207: CHECKPOINT in the 2nd stage done
2008-07-10 15:05:09 LOG: pid 15207: starting recovery command: "SELECT pgpool_recovery('pgpool-recovery', '10.0.0.y', '/var/lib/pgsql/data')"
2008-07-10 15:05:09 DEBUG: pid 15207: exec_recovery: start recovery
2008-07-10 15:05:09 DEBUG: pid 15207: exec_recovery: finish recovery
2008-07-10 15:05:09 DEBUG: pid 15207: exec_remote_start: start pgpool_remote_start
2008-07-10 15:05:33 DEBUG: pid 15207: exec_remote_start: finish pgpool_remote_start
2008-07-10 15:05:33 LOG: pid 15207: 1 node restarted
2008-07-10 15:05:33 LOG: pid 15207: send_failback_request: fail back 1 th node request from pid 15207
2008-07-10 15:05:33 LOG: pid 15207: recovery done
2008-07-10 15:05:33 DEBUG: pid 15173: failover_handler called
2008-07-10 15:05:33 DEBUG: pid 15173: failover_handler: starting to select new master node
2008-07-10 15:05:33 LOG: pid 15173: starting fail back. reconnect host 10.252.178.18(5432)
2008-07-10 15:05:33 LOG: pid 15173: failover_handler: do not restart pgpool. same master node 0 was selected
2008-07-10 15:05:33 LOG: pid 15173: failback done. reconnect host 10.252.178.18(5432)
2008-07-10 15:05:33 DEBUG: pid 15207: pcp_child: received PCP packet type of service 'X'
2008-07-10 15:05:33 DEBUG: pid 15207: pcp_child: client disconnecting. close connection
2008-07-10 15:05:43 DEBUG: pid 15173: starting health checking
2008-07-10 15:05:43 DEBUG: pid 15173: health_check: 0 the DB node status: 2
2008-07-10 15:05:43 DEBUG: pid 15173: health_check: 1 the DB node status: 1


Any suggestions, mistakes, improves ? I would be really happy if you let me know.

#UPDATE 1 (24.09.2008):
- Notice that gzip PostgreSQL base dir isn't most smart idea. Better just tar it, otherway will take ages to create archive if database got few GB.
- Access to 'template1' at recovery process is blocked, not really know yet what to do with that.