Both InnoDB and PostgreSQL - as well as many other databases - use a technique called multi-version concurrency control (MVCC) to provide transaction isolation: transactions should not see the work of other, uncommitted transactions. MVCC means that, when a row is updated, the database stores both the old and new versions of the row.


Showing posts with label postgres. Show all posts
Showing posts with label postgres. Show all posts

Tuesday, March 15, 2011

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..

Saturday, November 27, 2010

PostgreSql Backup Script - Bash

If you are one who is using PostgreSql as your Back-end Database Server for your work, then obviously you need a backup solution, so here this is my try for a small solution for Automated Backup Job in  Bash Shell Script.

Sunday, November 21, 2010

Read Only User In Postgresql

Read only users in databases servers are frequently required, and generally  the method to create such user in different Database servers are very well known, such like in Mysql, Oracle, MSSQL etc...

Saturday, November 20, 2010

Comparing MySQL and Postgres 9.0 Replication


As this post is not mine... but i am glad to share this post with every one.
This one is amazing about mysql vs postgresql replication way.

Click below Link:
Comparing MySQL and Postgres 9.0 Replication

Thursday, October 14, 2010

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               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@  (run this command after the slave’s Postgresql installation)

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= 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= 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();
(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

PGCluster - Postgres Clustering & Load Balancing

PGCluster - Postgres Clustering & Load Balancing

Download it from here

# useradd postgres
# useradd -M -g postgres postgres
# mkdir /home/postgres

PostgreSql Server Migration Using PITR

Migrate PostgreSQL Server to New Server With Minimal Downtime using PITR (Point In Time Recovery)

Source Server

mkdir /archive; chown postgres.postgres /archive;
vi /usr/local/postgres/data/postgresql.conf
Add the following lines:
archive_mode = on
archive_command = 'cp -i %p /archive%f'