Showing posts from 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 anyt

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

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 physic