Posts Tagged PostgreSQL

Postgresql 9.0.4 replication

Postgresql 9.0.4 replication
I did a simple testing on Postgresql 9.0.4 replication configuration.
This is my flow for the setup. I hope these steps can help for your reference.

Environment:
Master = 192.168.0.82
Standby = 192.186.0.83

Install postgresql 9.0.4 on master and standby server

Edit postgresql.conf on master server

listen_addresses = '192.168.0.82'



Edit pg_hba.conf on master server

# The standby server must have superuser access privileges.
host  replication  postgres  192.168.0.83/32  trust


Edit postgresql.conf on master server

wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32


Stop postgresql on master server

tar data folder to a tar file (e.g. data.tgz)

on standby server
Stop postgresql

rename data folder
mv data data_org
untar data.tgz

Edit postgresql.conf on standby server

wal_level = minimal
max_wal_senders = 0
wal_keep_segments = 0
hot_standby = on


create and edit recovery.conf on standby server.

standby_mode          = 'on'
primary_conninfo      = 'host=192.168.0.10 port=5432 user=postgres'


Start postgresql on Master Server
Start postgresql on Standby Server

Grep the following information to verify that replication is running.
On Master:
ps -ef | grep sender

On Standby
ps -ef | grep receiver

You can use createdb to create database on master server. Then, this DB will be replicated to standby server.
You can use “psql -l” to list database on server.

VN:F [1.9.18_1163]
Rating: 0.0/5 (0 votes cast)
VN:F [1.9.18_1163]
Rating: 0 (from 0 votes)
Total views: 17,190 views

, , ,

No Comments

Performance Review to PostgreSQL 8.0 and 8.3

PostgreSQL v8.3 is faster than v8.0 while doing stress test.

VN:F [1.9.18_1163]
Rating: 0.0/5 (0 votes cast)
VN:F [1.9.18_1163]
Rating: 0 (from 0 votes)
Total views: 4,194 views

No Comments

PostgreSQL tuning (Linux kernel parameters)

Edit file /etc/sysctl.conf

Add the following lines

kernel.shmmax=2147483648
vm.overcommit_memory=2

kernel.shmmax
1. Around 1/2 of the total system memory
e.g. 4GB system memory, kernel.shmmax =  2 x 1024 x 1024 x 1024

Modify postgresql server effective cache settings
edit /var/lib/pgsql/data/postgresql.conf

effective_cache_size = 1800MB

VN:F [1.9.18_1163]
Rating: 5.0/5 (1 vote cast)
VN:F [1.9.18_1163]
Rating: 0 (from 0 votes)
Total views: 21,685 views

, ,

No Comments