Monitor PostgreSQL with Queries

(Not All written by me.... )Some time just monitoring a DB servers from monitoring tools is just not enough, in that case we need some tool that let us know, what is DB is holding, processing, parameters etc..

Postgresql 9.0 Replication - Step by Step

Postgresql 9.0 Streaming Replication - A Big Competition  

 Now Postgresql 9.0 has come with in-built replication features.  NOW A BIG COMPETITOR FOR MYSQL

This method for those who wants to satisfy them by implementing Asynchronous mode of replicationWho wants Synchronous method, and then have to wait for 9.1 to come in existence.I tried to build a Postgresql Master-Slave replication system with new feature “Streaming Replication (SR)”, and this I have to say, this is very easy and stable method for asynchronous method for replication.While building the SR, I felt, this type of SR is like Mysql Master-slave replication,  Slaves are getting updates from master from log files (binlog in cas e of mysql and xlog in postgres)

The above statement can be taken as rough idea when comparing  Mysql and Postgresql replication method

Steps Included:

1.       Download and build, install Postgresql from source.

2.       Initialize database on master

3.       Some tweaks in postgresql.conf (On Master)

4.       Start backup mode on master

5.       Take snapshot of Data (/usr/local/postgres/data) directory on master

6.       Sync snapshot to slave and remove postmaster.pid  on slave

7.       Some tweaks in postgresql.conf and recovery.conf (On Slaves)

8.       At end start the Database on Master and slave

Download the Database:  (On Master)

# wget http://wwwmaster.postgresql.org/redir/198/h/source/v9.0.0/postgresql-9.0.0.tar.bz2
# tar xzf postgresql-9.0.0.tar.bz2

If you have fresh CentOS/other linux version, then maybe you have to download and install  required packages…  In my case I am Using CentOS 5.3 64 Bit

Run the below command as “ROOT”

# yum install -y glibc gcc libtermcap-devel readline-devel zlib-devel
# useradd postgres
# chown –R postgres:postgres postgresql-9.0.0
# su – postgres
# cd postgresql-9.0.0  (extracted directory)

Run the following command to compile and install Postgresql

# ./configure –prefix=/usr/local/postgres –with-perl
# gmake
# su –
# gmake install
# chown -R postgres:postgres /usr/local/postgres
# mkdir /usr/local/postgres/data
# chown postgres /usr/local/postgres/data
# su  - postgres
# /usr/local/postgres/bin/initdb -D /usr/local/postgres/data

After the initializing the data directory, we have to tweak---

“/usr/local/postgres/data/postgresql.conf“ file

I am showing you required things only

listen_addresses = '*'        
max_connections = 500
shared_buffers = 32MB
wal_level = hot_standby
archive_mode = on
archive_command = 'cp %p /usr/local/postgres/data/pg_xlogarch/%f'
max_wal_senders = 1    ####( this setting for  1 slave can connect with Master)
wal_keep_segments = 32
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'

# mkdir  /usr/local/postgres/data/pg_xlogarch/
# su – postgres
# cd /usr/local/postgres/
# vi data/pg_hba.conf (add the below line)

host    replication             all             172.32.1.2/32            trust

Now start the Database Instance on Master:

As user postgres

# /usr/local/postgres/bin/pg_ctl -D /usr/local/postgres/data -l logfile start

After starting the database Instance, start the backup mode on DB Master

# cd/usr/local/postgres/bin/
# ./psql –c "SELECT pg_start_backup('initial backup for SR')" = database name for which you want replication

# cd /usr/local/postgres/

# tar  -czf data.tar.gz        data/

 
Exit from Backup mode 
# /usr/local/postgres/bin/psql –c "SELECT pg_stop_backup()"

# rsync –avz  data.tar.gz postgres@172.32.1.2:/usr/local/postgrsql/  (run this command after the slave’s Postgresql installation)

On SLAVE:
 
Same as master download and install Postgresql on slave
 
Just do not initialize the database:

As user Postgres
# cd  /usr/local/postgrsql
# tar xzf data.tar.gz
# chown –R postgres:postgres  /usr/local/postgres/data/pg_xlogarch

# cd data

# rm –rf postmaster.pid

# vi  /usr/local/postgrsql/data/Postgresql.conf

In short:

listen_addresses = '*'          # Which IP address(es) to listen on
max_connections = 500        # (change requires restart)shared_buffers = 32MB    #min 128kB
hot_standby = ondatestyle = 'iso, mdy'lc_messages = 'en_US.UTF-8'   # locale for system error message
lc_monetary = 'en_US.UTF-8'        # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'          # locale for number formatting
lc_time = 'en_US.UTF-8'  # locale for time formatting
default_text_search_config = 'pg_catalog.english'

Now create a “recovery.conf” under /usr/local/postgres/data/ and put the below lines: (In Slave)

standby_mode = 'on'
primary_conninfo = 'host=172.32.1.1 port=5432 user=readonly'
restore_command = 'cp /usr/local/postgres/data/pg_xlog/%f  %p'
 

That's it we are done with configurations.

Start the database instance on slave machine

# /usr/local/postgres/bin/pg_ctl -D /usr/local/postgres/data -l logfile start

If all goes well, the on master, you will see like this on Master:

LOG: replication connection authorized: user=readonly host=172.32.1.2 port=55811

And on slave’s log file (logfile)

LOG: streaming replication successfully connected to primary

If fails, check "logfile" and send me error (if in case you feel so)

New Addition: If you want to check, which pgsql server is Master or Slave, then run this command:

#  /usr/local/postgres/bin/pg_controldata /usr/local/postgres/data/   (running on Master)
pg_control version number:            903
Catalog version number:               201008051
Database system identifier:           5572911747953590263
Database cluster state:               in production  (This shows, it is master server)
pg_control last modified:             Fri 06 May 2011 05:09:20 PM IST
Latest checkpoint location:           2/97482E60
............

On Slave:

Same Line will show as:

Database cluster state:               in archive recoveryYou can also use below one: to check

Slave Status:

Postgres=# select pg_last_xlog_replay_location();
 pg_last_xlog_replay_location
------------------------------
 7B/35F312B0
(1 row)

isten_addresses = '*'          # what IP address(es) to listen on;
max_connections = 500                   # (change requires restart)
shared_buffers = 32MB                   # min 128kB
hot_standby = on
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'                     # locale for system error message
lc_monetary = 'en_US.UTF-8'                     # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'                      # locale for number formatting
lc_time = 'en_US.UTF-8'                         # locale for time formatting

Postgrsql 9.0 beta - Relased with New Features

Postgresql 9.0 beta - relased

PostgreSQL 9.0 has been released with new interesting features, hot standby & streaming replication

 How it works