Showing posts from May, 2017

Reusing an old master (as slave) after a switchover

Todays blogpost is old news but it's not very well known and it has to do with how to add an old master after a slave has been promoted.  Fujii Masao  explained the situation in his patch back in the day. So in todays post i will be demonstrating a combination of replication slots for retaining the writes on a promoted slave and how to re-introduce an old master to the replication. Say that we have a master-slave setup, stop the master gracefully and create and activate a replica slot on the slave : monkey=# SELECT * FROM pg_create_physical_replication_slot('this_is_a_replica_slot');        slot_name        | xlog_position ------------------------+---------------  this_is_a_replica_slot | (1 row) postgres@bf9823730feb:~$ pg_receivexlog -D . -S this_is_a_replica_slot -v -h -U repuser pg_receivexlog: starting log streaming at 0/4000000 (timeline 1) ^C pg_receivexlog: received interrupt signal, exiting pg_receivexlog: not renaming "000000010000000

PostgreSQL statistics as data

 Postgres provides a lot of information when it comes to statistics. Only problem is that all the statistics are frozen in time the moment you query the system catalog views. Sometimes you need to see whats happening over time or in the past,and this is easily solvable with a script and a crontab entry. At  OmniTI we use a project called system_monitoring a lot. This is basically a perl scheduler that runs as a deamon exporting useful information from postgres (and more) to tab delimited text files for later investigation. If you have decent one liner skills this is very helpful: you can answer questions about what happened yesterday night, what was running, who locked who etc. It's very good but data analysis can be a pain in the ass sometimes, especially if you need to reimport to a db and analyze with SQL. So I decided to approach this in a different way, by storing the statistics to a schema. Thats why i wrote statz .  Statz is a very simple python script that will gather al