Skip to content

HOW TO: Install and Configure PostgreSQL 9.1 cluster on Freebsd 9.0 with PgPool-II , CARP and ZFS

April 13, 2012

continued from …

HOWTO: FreeBSD ZFS on root partition (zroot) (freebsd-zfs)

all thanks to: https://github.com/vpetersson/pg_ha/wiki/Setup-On-FreeBSD (real article source)

Asumming preconfigured CARP interface with share address: 192.168.30.10

1. Initial Postgresql 9.1 and FreeBSD 9.0 server configurations

1.1. Install PostgreSQL 9.1 Server on db1 and on db2 (node1 and node2 in the cluster configuration)

db1# cd /usr/ports/databases/postgresql91-server ; make install clean clean-depends

db2# cd /usr/ports/databases/postgresql91-server ; make install clean clean-depends

1.2. Make sure you have  installed ntpds , the time is very important!!!

1.3. Initdb in postgresql and start servers

db1# /usr/local/etc/rc.d/postgresql initdb

db2# /usr/local/etc/rc.d/postgresql initdb

db1# /usr/local/etc/rc.d/postgresql start

db2# /usr/local/etc/rc.d/postgresql start

1.4. Install rsync and make authorized_keys to ssh logins for pgsql user

db1# cd /usr/ports/net/rsync ; make install clean clean-depends (config it WITH ssh support)

db2# cd /usr/ports/net/rsync ; make install clean clean-depends (config it WITH ssh support)

1.5. install sudo for using one line commands, not to re-login in different usernames ( form ports) and generate pgsql authorization keys:

db1# cd /usr/ports/security/sudo ; make install clean clean-depends;  su – ; sudo -u pgsql ssh-keygen

db2# cd /usr/ports/security/sudo ; make install clean clean-depends;  su – ; sudo -u pgsql ssh-keygen

..fill in files in ~/.ssh/authorized_keys with id_rsa.pub files…

1.6. Make clients connect OK to db1 (postgresql node1 form the cluster)…please stop now postgresql on db2 , a.k.a:

db2# /usr/local/etc/rc.d/postgresql stop

db1# psql postgres pgsql

postgres=# alter user pgsql with password ‘your_password’;
ALTER ROLE

1.7. Add clients (hosts allow rules into postgresql.conf): /usr/local/pgsql/data/pg_hba.conf and add pgsql user password for password-prompt-less logins

postgresql.conf:

host replication all 192.168.30.0/24 password
host all all 192.168.30.0/24 password

prompt-less logins from db1 to db2 and from db2 to db1 postgresql with pgsql username:
sudo su - pgsql echo -e "db1:5432:*:pgsql:user_password\ndb2:5432:*:pgsql:user_password" > ~/.pgpass chmod 600 ~/.pgpass
2. Replication configuration on the PostgreSQL 9.1 Servers ( streaming replication )
 2.1. Configuration for streaming replication on db1 ( postgresql cluster node1 )
 2.1.1. Add/Edit these lines in postgresql.conf file:
listen_addresses = '0.0.0.0' 
synchronous_commit = off 
wal_level = hot_standby 
max_wal_senders = 1 
wal_keep_segments = 32 
hot_standby = on 
wal_buffers = 16MB
2.1.2. Add file: /usr/local/pgsql/data/recovery.bak and add these lines:

standby_mode = ‘on’
primary_conninfo = ‘host=db2 port=5432’
trigger_file = ‘/tmp/pgsql.trigger’

db1# /usr/local/etc/rc.d/postgresql restart  ( check login from db2, if ok – it’s ok )

db1# /usr/local/etc/rc.d/postgresql stop

2.2. Configuration for streaming replication on db2 ( postgresql cluster node2 )

2.2.1. Clone configuration from db1

db2# sudo -u pgsql rsync -aP –numeric-ids db1:/usr/local/pgsql/data/ /usr/local/pgsql/data/

db2# cd /usr/local/pgsql/data/
db2# cp recovery.bak recovery.conf

db2# /usr/local/etc/rc.d/postgresql start

db1# /usr/local/etc/rc.d/postgresql start

The streaming replication should be configured now, make some tests, make some checks … !!!!

Advertisements

From → databases, FreeBSD

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: