Wednesday, July 13, 2011

how to replication postgresql 9.0 in FreeBSD

Install postgresql 9.0 on both server using port
#cd /usr/ports/database/postgresql90-server
#make install clean


Start service postgresql on both server
#/usr/local/etc/rc.d/postgresql start

Then configure both server;
Master (192.168.0.1)

Edit postgresql.conf
listen_address='*'
wal_level = hot_standby
max_wal_sender = 1
wal_keep_segments = 32
archive_mode = on
archive_command = 'cp -i %p /usr/local/pgsql/data/archives/%f'

Save and exit.

Add an entry to accept a connection from the slave in pg_hba.conf.
The database name must be "replication" here, and you have to specify IP addresses of the slave nodes
host replication all 192.168.0.2/32 trust
Save and exit.

Syncronize DB in Master to Slave
# psql -U postgres
postgres# select pg_start_backup('clone', true);
postgres# \! rsync -av --exclude pg_xlog --exclude postgresql.conf --exclude postmaster.pid /usr/local/pgsql/data/* 192.168.0.2:/usr/local/pgsql/data/
postgres# select pg_stop_backup();
postgres# \! rsync -av /usr/local/pgsql/data/pg_xlog 192.168.0.2:/usr/local/pgsql/data/


Slave (192.168.0.2)
Edit postgresql.conf for standby mode on;
hot_standby = on
Save and Exit.

Create file recovery.conf
standby_mode = on
primary_conninfo = 'host=firsthost port=5432 user=postgres password=postgres'
trigger_file = '/usr/local/pgsql/data/trigger'
restore_command = 'cp /usr/local/pgsql/data/archives/%f %p'


Save and Exit
Chmod pgsql:pgsql recovery.conf

Restart the master and slave nodes
#/usr/local/etc/rc.d/postgresql restart

Check using ps ax in both nodes
Master
12073 ?? Ss 0:00.58 /usr/local/bin/postgres -D /usr/local/pgsql/data
12075 ?? Rs 0:03.83 postgres: writer process (postgres)
12076 ?? Ss 0:01.37 postgres: wal writer process (postgres)
12077 ?? Ss 0:00.56 postgres: autovacuum launcher process (postgres)
12078 ?? Ss 0:01.29 postgres: archiver process (postgres)
12079 ?? Ss 0:00.62 postgres: stats collector process (postgres)
12083 ?? Ss 0:03.58 postgres: wal sender process postgres 192.168.0.2(54395) (postgres)


Slave
38359 ?? Ss 0:00.28 /usr/local/bin/postgres -D /usr/local/pgsql/data
38360 ?? Ss 0:07.62 postgres: startup process (postgres)
38362 ?? Ss 0:11.53 postgres: writer process (postgres)
38363 ?? Ss 0:00.55 postgres: stats collector process (postgres)
39207 ?? Ss 0:02.92 postgres: wal receiver process (postgres)

2 comments:

cheapelectroniccigarette said...

I love your cyberthug.blogdetik.com
cheap electronic cigarette

betathome said...

I like cyberthug.blogdetik.com, bookmarked
bet and home