Monday, 30 December 2013

pgreplay-ing logfiles

Recently I wanted to find something to rerun a workload (from logfiles) in postgres. the reasons ? benchmark , testing , troubleshooting.
My colleague Denish reminded me a project called pgreplay. pgreplay reads a PostgreSQL logfile , extracts the SQL statements and executes them in the same order and with (or without) the original timing against a PG database.

I download it, compiled it and went straight to the testing.

The setup :

log_min_messages = error  (or more)
   (if you know that you have no cancel requests, 'log' will do)
log_min_error_statement = log  (or more)
log_connections = on
log_disconnections = on
log_line_prefix = '%m|%u|%d|%c|'  (if you don't use CSV logging)
log_statement = 'all'
lc_messages must be set to English (the encoding does not matter)
bytea_output = escape  (from version 9.0 on, only if you want to replay
                        the log on 8.4 or earlier)

It is highly recommended that you use CSV logging, because anything that
the PostgreSQL server or any loaded modules write to standard error will
be written to the stderr log and might confuse the parser.

For small and simple workloads it seemed that it worked fine. Bellow the output from a simple workload :

Replay statistics

Speed factor for replay: 1.000
Total run time: 29.700 seconds
Maximum lag behind schedule: 0 seconds
Calls to the server: 8
(0.269 calls per second)
Total number of connections: 1
Maximum number of concurrent connections: 1
Average number of concurrent connections: 1.000
Average session idle percentage: 99.680%
SQL statements executed: 6
(0 or 0.000% of these completed with error)
Maximum number of concurrent SQL statements: 1
Average number of concurrent SQL statements: 0.003
Average SQL statement duration: 0.016 seconds
Maximum SQL statement duration: 0.038 seconds
Statement duration histogram:
0 to 0.02 seconds: 83.333%
0.02 to 0.1 seconds: 16.667%
0.1 to 0.5 seconds: 0.000%
0.5 to 2 seconds: 0.000%
over 2 seconds: 0.000%

So far so good, i wanted to see what will happen when concurrency occurred so i tried with pgbench , with 2 concurrent everything was ok, so i tried to scale it up a bit to 16 concurrent transactions, and then these nice messages started showing :
"Execution is 30 minutes behind schedule"

The reason ? Locks Lots and lots of Locks ! but let's see closer what happened...
While pgreplay was running trying to handle my logfile the best way it could, a simple lock investigating query was enough to show me that i would never get the successful prompt that i wanted :

monkey=#   SELECT     AS blocked_pid,
monkey-#          a.usename  AS blocked_user,
monkey-#     AS blocking_pid,
monkey-#          ka.usename AS blocking_user,
monkey-#          a.query    AS blocked_statement
monkey-#    FROM  pg_catalog.pg_locks         bl
monkey-#     JOIN pg_catalog.pg_stat_activity a  ON =
monkey-#     JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND !=
monkey-#     JOIN pg_catalog.pg_stat_activity ka ON =
monkey-#    WHERE NOT bl.granted;

 blocked_pid | blocked_user | blocking_pid | blocking_user |                          blocked_statement                          
        7398 | vasilis      |         7403 | vasilis       | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;
        7398 | vasilis      |         7399 | vasilis       | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;
        7398 | vasilis      |         7404 | vasilis       | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;
        7398 | vasilis      |         7402 | vasilis       | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;
        7402 | vasilis      |         7398 | vasilis       | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;
        7409 | vasilis      |         7404 | vasilis       | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2;

Deadlock, the reason ?
(quote from pgreplay documentation) :

While pgreplay makes sure that commands are sent to the server in the order in which they were originally executed, there is no way to guarantee that they will be executed in the same order during replay: Network delay, processor contention and other factors may cause a later command to "overtake" an earlier one. While this does not matter if the commands don't affect each other, it can lead to SQL statements hitting locks unexpectedly, causing replay to deadlock and "hang". This is particularly likely if many different sessions change the same data repeatedly in short intervals.

And that was my case, lots of processes were changing the same data.
For the record, i also set up a simple troubleshooting project that works on postgres, with 2 users doing their own thing pgreplay worked fine.

conclusion :
Ok , pgreplay didn't do the job for me, i am pretty sure that if i got the logs from a production DB i would run into deadlocks, still messing a bit with it was good because i could use this for replaying simple workload logfiles to a different machine or just because its a cool project that does what it does and you never know when you might need something like this..

Wednesday, 18 December 2013

A Problem Geeked Into Big Data

Today my article got published in OmniTI Seeds. it was a very interesting project, i had a lot of fun while doing it, and even if its not exactly Postgres related, I wouldn't be able to do it without Postgres. It begun from the basic everyday operation that all dba's are doing (log parsing / analysis) but it got to a whole different level. I hope you like it.

A Problem Geeked Into Big Data

cooking with fdw

Back in 9.1 a feature called foreign data wrappers was introduced, you could basically connect a PostgreSQL Server to remote data stores (other dbs, csv files etc). Today i plan to play with this FDW and see what and how we can use it.

First of all there are many FDW's, a list can be found here.

For the sake of this post i used the following data wrappers :

     Name     | Version |   Schema   |                    Description                    
 file_fdw     | 1.0     | public     | foreign-data wrapper for flat file access
 postgres_fdw | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
 redis_fdw    | 1.0     | public     | Foreign data wrapper for querying a Redis server

file_fdw and postgres_fdw are both available in contrib, redis fdw can be found in the following link. you will also need to compile and install hiredis which can be found here. I wont go much into detail on how to install this, but there is plenty documentation available.

The plan is to connect a redis server a remote postgreSQL server and a csv and interact with everything.

First thing that has to be done after the extensions have been created is to create foreign servers and tables, the following example shows what i did with the remote postgreSQL server :

test_redis=# CREATE SERVER node_2
OPTIONS (host '', dbname 'postgres');     
test_redis=# CREATE FOREIGN TABLE test_pg (id int)
SERVER node_2 OPTIONS (table_name 'test_pg');

csv and redis foreign tables were really similar to create, so when i was done i had this :

 public | db15       | foreign table | vasilis
 public | pgcsv      | foreign table | vasilis
 public | test       | table         | vasilis
 public | test2_pg   | foreign table | vasilis

one local table and one for each of my foreign wrappers. So, lets start playing around.

test_redis=# begin;
test_redis=# insert into test2_pg values ('koko','lala');
test_redis=# commit;

Yep, i could insert into a remote PG server, in a transactional way, goodbye dblink ? (oh yes!)
Unfortunately this wouldn't work on redis, but only because the fdw doesn't support it (yet?) .
Now lets join them all and see how that works.

test_redis=# select * from db15,pgcsv,test2_pg,test where db15.key=test2_pg.key and pgcsv.key=db15.key and db15.key=test.key and test2_pg.key=db15.key;
 key  | value | id | key  |    date    |   time   | num | key  | value | key  | value 
 koko | koko1 |  1 | koko | 2013-12-01 | 00:01:01 | 234 | koko | lala  | koko | ninini

the statement is not something that is actually usable, but it demonstrates by the execution plan that all foreign tables have been accessed :

                                                          QUERY PLAN                                                          
 Nested Loop  (cost=111.24..156.43 rows=1 width=244) (actual time=0.981..1.031 rows=1 loops=1)
   Join Filter: (test2_pg.key = db15.key)
   Rows Removed by Join Filter: 2
   ->  Nested Loop  (cost=101.24..143.39 rows=1 width=180) (actual time=0.857..0.862 rows=1 loops=1)
         ->  Hash Join  (cost=101.11..142.55 rows=5 width=116) (actual time=0.833..0.835 rows=1 loops=1)
               Hash Cond: (test2_pg.key = pgcsv.key)
               ->  Foreign Scan on test2_pg  (cost=100.00..137.90 rows=930 width=64) (actual time=0.767..0.768 rows=1 loops=1)
               ->  Hash  (cost=1.10..1.10 rows=1 width=52) (actual time=0.049..0.049 rows=3 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 1kB
                     ->  Foreign Scan on pgcsv  (cost=0.00..1.10 rows=1 width=52) (actual time=0.033..0.042 rows=3 loops=1)
                           Foreign File: /home/vasilis/lala.csv
                           Foreign File Size: 98
         ->  Index Scan using kokoako on test  (cost=0.12..0.16 rows=1 width=64) (actual time=0.019..0.021 rows=1 loops=1)
               Index Cond: (key = test2_pg.key)
   ->  Foreign Scan on db15  (cost=10.00..13.00 rows=3 width=64) (actual time=0.071..0.162 rows=3 loops=1)
         Foreign Redis Table Size: 3
 Total runtime: 2.184 ms
(17 rows)

This is awesome from many aspects, imagine that you have a heterogeneous environment with mongo, redis, postgres , oracle ,csv logs etc.. you can put a postgreSQL in the middle, acting like a bridge and create supersets, subsets, aggregations, views,temp tables, report tables and so many other things. You could even limit users to access only the "bridge postgres" so they can see whatever you want from many different systems. you can use SQL in redis since redis commands are so limited. and because i am starting getting a bit paranoid i will leave it up to you on how to use this :)

Thanks for reading
-- Vasilis

Tuesday, 17 December 2013

scaling postgres with pgpool-II

Recently a new release of pgpool-II was released, 3.2.7 (released 2013-12-06). For connection pooling I mostly have experience with pgbouncer and in OmniTI we use it a lot. I heard that pgpool has some issues and because of its increased complexity i don't know if i would recommend it as a connection pool but pgpool does much more than that.

What is pgpool-II (from pg-pool wiki) :

pgpool-II is a middleware that works between PostgreSQL servers and a PostgreSQL database client. It is licensed under BSD license. It provides the following features.
  • Connection Pooling
pgpool-II saves connections to the PostgreSQL servers, and reuse them whenever a new connection with the same properties (i.e. username, database, protocol version) comes in. It reduces connection overhead, and improves system's overall throughput.
  • Replication
pgpool-II can manage multiple PostgreSQL servers. Using the replication function enables creating a realtime backup on 2 or more physical disks, so that the service can continue without stopping servers in case of a disk failure.
  • Load Balancing
If a database is replicated, executing a SELECT query on any server will return the same result. pgpool-II takes an advantage of the replication feature to reduce the load on each PostgreSQL server by distributing SELECT queries among multiple servers, improving system's overall throughput. At best, performance improves proportionally to the number of PostgreSQL servers. Load balance works best in a situation where there are a lot of users executing many queries at the same time.
  • Limiting Exceeding Connections
There is a limit on the maximum number of concurrent connections with PostgreSQL, and connections are rejected after this many connections. Setting the maximum number of connections, however, increases resource consumption and affect system performance. pgpool-II also has a limit on the maximum number of connections, but extra connections will be queued instead of returning an error immediately.
  • Parallel Query
Using the parallel query function, data can be divided among the multiple servers, so that a query can be executed on all the servers concurrently to reduce the overall execution time. Parallel query works the best when searching large-scale data.

The feature that im mostly interested and that i want to explore in this post is load ballancing. I like this feature because without changing the application you can elevate your replica one step and make it even more useful.

I setup 3 virtual machines that i called pgpool1, pgnode1, pgnode2.
I setup a basic installation of debian and i compiled pgpool-II (3.3.2) and PostgreSQL 9.3.2.
As a first step i setup a basic streaming replication between pgnode1 and pgnode2 and i set pgpool1 for Load ballancing, all configurations can be found in my github. I wont go much into detail on how to set pgpool up, my configuration files should be a good guide and i found that pgpool documentation is very informative and up to date, also many before me have blogged about how to configure it.
I will stand more on the design and what could you do with this.
As we all know streaming replication is almost instant so separating "read" from "write" can be beneficial in a large variety of applications, but especially for more reads than writes, so lets see how this works :

root@pgpool1:~# psql -p 9999 -U postgres
postgres=# create database monkey;
postgres=# \connect monkey;
You are now connected to database "monkey" as user "postgres".
monkey=# create table test (id int);
monkey=# insert into test values (1);
monkey=# select * from test;
(1 row)

With full logging enabled in the dbs lets see the logs from pgnode1 and pgnode2 :

LOG:  duration: 342.044 ms  statement: create database monkey;
LOG:  duration: 4.646 ms  statement: create table test (id int);
LOG:  duration: 1.733 ms  statement: insert into test values (1);

LOG:  duration: 1.018 ms  statement: select * from test;

OK it works as expected, now i heard that this doesnt work if select has a space in the begining so i tried to test that as well :

on pgnode2 (replica) :
LOG:  duration: 0.757 ms  statement:   selecT * from test;

LOG:  duration: 0.215 ms  statement: SeLeCt   * from test /* this is my test */;

something more complicated maybe? something like pgbench sounds appropriate.

I wont spam more logs, it worked as it should.
But what would happen with 2 replicas ? i know that read operations should be scaled and that they should be distributed among the slaves, i created another database cluster on pgnode2 (port 5433) and made it replica to pgnode1,
so now i had one master running on pgnode1 and two slaves running on pgnode2 (5432 and 5433).
I changed pgpool configuration to add the new db :

backend_hostname0 = ''
backend_port0 = 5432
backend_weight0 = 0
backend_data_directory0 = '/opt/pgdata'

backend_hostname1 = ''
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/opt/pgdata'

backend_hostname2 = ''
backend_port2 = 5433
backend_weight2 = 1
backend_data_directory2 = '/opt/pgdata2'

and from pgpool1 i run :
while true; do psql -p 9999  -c 'selecT * from test;'  -U postgres ;done

and Voilà , i had my selects distributed on my 2 slaves.

pgpool has A LOT of cool features, im not in position to say if they all work as they should but from my tests on load ballancing everything was smooth, pgpool has also a web based gui worth checking and some system views that can be used to monitor the activity, also supports automatic failover, watchdog and many many other neat features that i might explore in another post.

Next time i will explore Parallel query.

all the configurations can be found in my github (including the postgres configs that were used).

Thanks for reading
- Vasilis

Tuesday, 15 October 2013

Parallel pg_dump backups



The test:

> time pg_dump -Fc -f out.sql -d dump
real    4m58.831s
now with -Fd -j
real    1m10.280s
real    0m43.596s
real    0m38.801s
real    0m33.708s
real    0m31.040s
real    0m30.501s
real    0m30.522s



Thanks for reading
- Vasilis

Tuesday, 8 October 2013

The cost of Reliability


Checksums are set immediately prior to flush out of shared buffers
and checked when pages are read in again.


No checksums:

INSERT 0 10000000
Time: 26226.654 ms
UPDATE 10000000
Time: 51903.185 ms
UPDATE 10000000
Time: 70125.720 ms

With checksums:

INSERT 0 10000000
Time: 23724.373 ms
UPDATE 10000000
Time: 74446.178 ms
UPDATE 10000000
Time: 78987.092 ms




-- Thank for reading

Tuesday, 27 August 2013

Backing up PostgreSQL in HDFS


Let me give some details about tools used:



The project includes these modules:

OmniPITR :



Setup :

Software Versions: PostgreSQL 9.3 (beta2) , Hadoop 1.2.1


Configuration :



Let's make a script:

#!/usr/bin/env bash
hadoop dfs -put - /user/hduser/$1



Let's try to backup straight into Hadoop:

/usr/local/omnipitr/omnipitr-backup-master -D $PGDATA -l dl-backup.log  -dp ~/test/ -x /mnt/backup/xlog -v


To verify the backup is actually there :

 $hadoop dfs -ls /user/hduser

Found 2 items




To restore the cluster, you would need something like :





Thanks for reading !!

Setting shared_buffers the hard way



What is shared buffers though ?




-- buffers per relation and size
pg_size_pretty(count(*) * 8192) as buffered,
round(100.0 * count(*) /
(SELECT setting FROM pg_settings
AS buffers_percent,
round(100.0 * count(*) * 8192 /
AS percent_of_relation
FROM pg_class c
INNER JOIN pg_buffercache b
ON b.relfilenode = c.relfilenode
INNER JOIN pg_database d
ON (b.reldatabase = d.oid AND d.datname = current_database())
where pg_relation_size(c.oid) >0
GROUP BY c.oid,c.relname

-- buffers per usage count
c.relname, count(*) AS buffers,usagecount
FROM pg_class c
INNER JOIN pg_buffercache b
ON b.relfilenode = c.relfilenode
INNER JOIN pg_database d
ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.relname,usagecount
ORDER BY usagecount,c.relname

PostgreSQL backup and recovery








pg_dump and pg_dumpall


i have a database for this posts sake called testbackup



export PGDATA=/opt/db/data
export PGPASSFILE=/opt/db/data/pgpass.conf


for db in `echo "select datname from pg_database
"|$psql -A -t postgres`



Standalone hot physical database backup

I will use the following directories and variables for examples sake


postgres@pglab1:/opt$ ls -l
total 8

mkdir /opt/BACKUP/archives

Set an archive_command. In postgresql.conf  and restart the server:
wal_level = archive 
archive_mode = on

mkdir /opt/BACKUP/archiving/
touch /opt/BACKUP/archiving/archiving_active

now run :
psql -c "select pg_start_backup('BACKUP')"
psql -c "select pg_stop_backup(), current_timestamp"

now, lets crash and restore

then on $PGDATA edit a file called recovery.conf and add :



the recovery.conf will also be automatically renamed to recovery.done.

Hot physical backup & Continuous Archiving


 edit postgresql.conf and enable archiving :

wal_level = archive
archive_mode = on
restart the server

would work


psql -c "select pg_stop_backup(), current_timestamp"


notice that the archives directory now has WALs
total 49156


so we have :

         datetime          | count
 2013-03-27 14:05:05.999257 |  1000
 2013-03-27 14:05:14.911462 |  1000
 2013-03-27 14:05:19.419173 |  1000
 2013-03-27 14:05:25.631254 |  1000
 2013-03-27 14:06:39.97177   |  1000
 2013-03-27 14:09:53.571976 |  1000


edit a recovery.conf as we did before :


Now lets Recover from a crush
once again , rm -rf /opt/PGDATA/*

untar the basebackup , place recovery.conf with or without the recovery_target_time
and start the database.
and the table now has :
 1 | 2013-03-27 13:56:49.163269



Thanks for reading

Migrating Oracle to PostgreSQL

And here are somethings to take under consideration before starting!
the dumpfile is 20gb, what happens if something goes wrong ?
You drop the database fix the ddl script and reapply the data
ERROR: invalid input syntax for integer: "26.19"

might end up fucking you up !

shared_buffers = 2GB
synchronous_commit = off
wal_buffers = 16MB
wal_writer_delay = 1000ms
checkpoint_segments = 64  


thanks for reading
to be continued...

PostgreSQL Partitioning

  • Range
  • List
implementation will just need the following steps.
  1. Enable constraint exclusion in config file
  2. Create a master table
  3. Create child tables WITHOUT overlapping table constraints
  4. Create indexes , pk's
  5. Create function and trigger to insert data to child tables

First , check into postgresql.conf for this parameter:

constraint_exclusion = partition 

Now , lets create the master and child tables :

    sales_id serial NOT NULL,
    description text

CREATE TABLE sales_2013_p1 (
 ) INHERITS (sales);

 CREATE TABLE sales_2013_p2 (
 ) INHERITS (sales);

 CREATE TABLE sales_2013_p3 (
 ) INHERITS (sales);

notice the keyword INHERITS here :)
next, PK's and indexes on child tables,


CREATE INDEX idx_2013_p1 ON sales_2013_p1 (sales_date);
CREATE INDEX idx_2013_p2 ON sales_2013_p2 (sales_date);
CREATE INDEX idx_2013_p3 ON sales_2013_p3 (sales_date);


        INSERT INTO sales_2013_p1 VALUES (NEW.*);
        INSERT INTO sales_2013_p2 VALUES (NEW.*);
        INSERT INTO sales_2013_p3 VALUES (NEW.*);
        RAISE EXCEPTION 'Date out of range.!';
    END IF;
LANGUAGE plpgsql;

CREATE TRIGGER insert_on_sales
    FOR EACH ROW EXECUTE PROCEDURE sales_trig_func();


CREATE TABLE sales_2014 (
 ) INHERITS (sales);


CREATE INDEX idx_2014 ON sales_2014 (sales_date);

        INSERT INTO sales_2013_p1 VALUES (NEW.*);
        INSERT INTO sales_2013_p2 VALUES (NEW.*);
        INSERT INTO sales_2013_p3 VALUES (NEW.*);

        INSERT INTO sales_2014 VALUES (NEW.*);

        RAISE EXCEPTION 'Date out of range.!';
    END IF;
LANGUAGE plpgsql;

and we are done!

 This is how the master table would look after these operations :

partition=# \d+ sales
                                                   Table "public.sales"
   Column    |  Type   |                        Modifiers                                               | Storage  | Stats target | Description
 description | text    |                                                                                         | extended |              |
Child tables: sales_2013_p2,
Has OIDs: no


Thanks for reading

PostgreSQL, Binary replication in practice

 A couple of days ago I started making a short howto about streaming replication in PostgreSQL 9.2. Most of these things are well documented but in this howto i will also try to experiment with switch overs and switchbacks. It aims to show how easy it is to set it up right out of the box.

Streaming replication PostgreSQL 9.2

For my example i will use 2 debian VM's (pglab1) and (pglab2)

- not mandatory -

exchange ssh keys for passwordless ssh ,might be used if we need to scp scripts , rsync or whatever.

if you don't know how to do it, follow these steps :

after you create a postgres cluster using initdb,
edit master's postgresql.conf and change the following :
listen_addresses = '*'
wal_level = hot_standby #(could be archive too)
max_wal_senders = 5
hot_standby = on

create a replication user :
create user repuser replication password 'passwd';

edit pg_hba.conf and add :

host all all trust
host replication repuser md5


Now ssh to slave and from $PGDATA run :

pg_basebackup -D /opt/data/ -v -h -U repuser
 enter password , this will transfer a full copy of your cluster from your master. check the documentation of pg_basebackup for compression and other options available.

In $PGDATA edit a file called recovery.conf containing :
standby_mode = on
 primary_conninfo = 'host= port=5432 user=repuser password=passwd'

with the master up, start slave , it should say :
LOG: database system is ready to accept read only connections

At this point you will have 2 nodes running with your master accepting read/write operations and your slave accepting only read only operations (reporting goes here maybe ?) now, lets say the master crashes and you need to failover and promote slave as the new master.


shutdown master 
on slave, execute:
pg_ctl promote

that's it, your slave (pglab2) is now master accepting all kinds of connections.

now lets say that the ex-master (pglab1) , is fixed and is ready to come up again,

- Switch back to original Master -

on the current master (pglab2) :

echo "select pg_start_backup('clone',true);" |psql pgbench
rsync -av --exclude /opt/data/*
echo "select pg_stop_backup();"|psql pgbench

this will sync all data from my current master (pglab2) to my current -to be- slave (pglab1), should be currently down.

edit recovery.done and fix the ip of the current master
rename recovery.done to recovery.conf
start ex-master (pglab1), now as slave , promote it with the slave (pglab2) down,
recreate slave (pglab2) with rsync, edit recovery.conf and start it again, 
the servers now have their original roles.

note that you can have 2 master databases but this might (and probably will) create a mess, so be sure that you bring down the correct server before promoting.

if you want to check if a server is acting as master or slave , run :

select pg_is_in_recovery()
If it's true, you're on a slave.

Thanks for reading.

PostgreSQL proper installation

Recently at work, i got assigned to upgrade some (about 10)
postgres 8.1.x to 9, I always liked compiling basically because I like the flexibility that compile offers, and thats what i proposed to the guys that are in charge of the project. They gave me a test system (vm) to play with, in all fairness they were a bit skeptical with the idea of compiling the rdbms. Mostly for reliability issues (don't ask me why). I explained that upgrading from source would be much easier later on and that the last year PostgreSQL developers are doing so much work that it cannot be ignored (pg_basebackup for example).Since the latest centos package was 9.1.x they agreed and i started working.
PostgreSQL is so easy to compile, no strange dependencies not many libraries needed. and about these reliability concerns ? Because developers are so neat, and i quote from

"While upgrades always have some risk, PostgreSQL minor releases fix only frequently-encountered security and data corruption bugs to reduce the risk of upgrading. The community considers not upgrading to be riskier than upgrading."

You should never find an unexpected change that breaks an application in a minor PostgreSQL upgrade. Bug, security, and corruption fixes are always done in a way that minimizes the odds of introducing an externally visible behavior change, and if that's not possible, the reason why and the suggested workarounds will be detailed in the release notes. What you will find is that some subtle problems, resulting from resolved bugs, can clear up even after a minor version update. It's not uncommon to discover a report of a problem to one of the PostgreSQL mailing lists is resolved in the latest minor version update compatible with that installation, and upgrading to that version is all that's needed to make the issue go away.

so don't be afraid to upgrade , DO IT

If you want to compile postgres you will need (except from the basic development tools) the following 2 libraries :

zlib1g-dev (compression library needed if you want to compress directly from postgres, pg_dump -Z for example)

libreadline6-dev (used by the client to support history on psql)

You could call both these libraries optional and you actually can compile postgres without them, but meh , don't do that...

Other than that its pretty much straight forward,
make install (check the documentation for extra flags , change of prefix etc)

postgres will install the binaries by default in /usr/local/pgsql/
Packages from the other hand will install in /usr/bin (MEGA SUPER LAME)

SO ! lets see the current situation. I inherited a postgresql server that had a package installation, the binaries located in /usr/bin and postgres home in /var/lib.
If it was compiled i could go to /usr/local/pgsql , rename it to /usr/local/pgsql8 remove it from the path and let it be, but yeah, i couldn't do that, i fixed the path, put /usr/local/pgsql before /usr/bin so the first postgres binaries in path order were 9's so i was ok. But still ! the packages tend to inter-grade deep into the OS making any other option (like running 2 postgres servers at the same time) more difficult that it should be.

I like adding some of the contrib packages into postgres , for logging, monitoring and tuning reasons pg_buffercache, pgbench, pg_stat_statements, pg_freespacemap are some examples.
In a compiled environment there is nothing easier than that, nothing extra to download not much to do, just compile the contrib module and add it to the database with CREATE EXTENSION.
Now lets say that after a while a new release of postgres comes and i wanna upgrade to that just because i want to stay current or because a new wow feature was added or a bug was resolved, all i have to do is compile the new version (assuming its a minor release) replace the binaries and restart the server. Package installed postgres server would have to wait till the distribution released a new package of the new server usually a few versions behind current, which means what ? a month after the release date? maybe more!?

Basically the only thing that i can imagine being easier with a package installation are the init scripts, and yeah , ok i don't think that is a huge pro comparing to a compiled version.

SO my suggestion is , compile the damn thing.. COMPILE !

Thanks for reading

Managing xid wraparound without looking like a (mail) chimp

My colleague Payal came across an outage that happened to mailchimp's mandrill app yesterday, link can be found HERE . Since this was P...