Migrating from Slony to Streaming Replication - 2ndQuadrant

frightenedfroggeryData Management

Dec 16, 2012 (5 years and 7 months ago)


Martin Eriksson
Migrating from Slony to Streaming Replication
Albourne Partners
Who is Albourne
Albourne is a consultant, advising hedge fund, private equity and real assets investors for a
fixed fee per month. This advice includes:

Due Diligence

Manager Selection

Strategy Timing

Portfolio Construction

Risk Management
Some key Company Data

Albourne Partners Limited established on March 7th 1994 in London

100% owned by full time employees

We have over 200 staff

11 offices around the world

Albourne's mission is to be the world's leading provider of
independent high-quality research and advice on complicated
Simon Ruddick, Executive Committee Member
Our Clients

Public Pension
Corporate Pension
Family Office
Endowment and
Utah Retirement
Teacher Retirement
System of Texas
Caisse de dépot et
placement du
Weyerhaeuser Asset
Management LLC
Lockheed Martin
Management Company
Cargill Inc. and
Associated Companies
Master Pension Trust
(including Foundation)
Ferd Group
Waycrosse Inc
Acropolis Capital
Partners Limited
University of Texas
Company (UTIMCO)
Regents of the
University of California
Robert Wood Johnson
Hedge fund,
private equity and
real assets clients
amount our
clients have
invested in
Our Systems
Our Main Applications

HFDB - Hedge Fund Database. Our main internal application

Village - a public alternative investment community web site

Castle - our web portal for our clients

Moatspace - web portal for funds that wish to provide data to our clients

We follow an agile/test driven development process

Release every 2 months

All our applications are written in Java

All web applications written using the Tapestry framework
PostgreSQL and Albourne

Started using postgres in 1996 (when it was called postgres95)

Running on an Alpha at the time

Actively submitting patches between 1999-2001 (Adriaan Joubert)

We try to always run latest released version (both Major and Minor)
What we keep in our database

Most of the data in our database has been manually input

90% of all data has been verified by a person for accuracy

Audit trail, everything is audited as per legal requirements
What we use our data for


Risk/investment simulations/modeling

Contacts/business directory
Our Database

650 tables

20 GB on disk

Average 20 transactions per second

Average 50,000 tuples returned per second

Minimal inserts/updates/deletes (total average of less then 3 per second)

Average 80 backends, with peaks of 200
Tuples f etched / sec
transactions per sec
Why did we want to replicate?

Lots of reads, vs writes

Offices spread across the world and too slow to access a central database

Cost of bandwidth , when you pay almost 5,000€ per month for 1Mbit..
without SLA....

More offices planned to be added, a growing company.

Disaster Recovery site Legal demands

Offices Around the World

Started using slony in 2006 with 2 slave nodes

Been adding a new slave every year

Always used latest released version

2010 we had 6 slave nodes

Applications read from local slave and write to master
Why we wanted to move away from slony

Every two months we need to apply around 250-400 DDL changes

If the replication fails, its at least 48h to get a new slave replicated

The fact that each node talks to every other node in the cluster
Hot Standby
How we got involved

2008 Albourne started to sponsor 2ndQuadrant's Hot Standby initiative

First release did not have streaming replication which was a must for us

During 9.0 Alpha we started to test streaming replication

Worked closely with 2ndQuadrant on the repmgr/rempgrd pair
Testing we did in our test environment

repmgr and rempgrd, extensive testing for all our different use cases and
network setup

Network failure

Failover, planned and emergency

Bandwidth usage comparing slony and Hot Standby streaming using
wireshark < 1% difference, slony transferred less
The final test

Aim - running full hot standby replication in parallel to slony

Hot standby master is a slave in slony replication

Over several weeks all slave nodes were added

Few users migrated
to use hot standby

Constant monitoring
and evaluation
Hot standby going into production
Slony Vs Hot Standby – A paradigm shift

In slony you specified what got replicated

In hot standby the instance in its entirety will be replicated

Multiple databases on the same instance where only one was replicated in slony

Possible to excluded specific tables from replication
Hot Standby

Running multiple instances for different databases

Forced to either accept some tables being replicated or rework them
Things you took for granted in slony

Vacuumed/analyzed your slaves/master on a schedule that worked for that node
This you can not do any more in hot standby because:

Vacuum is now only run on the master and it will generate data to be transferred
to all the slaves so do it with care though regular vacuum with analyze will mostly
be ok. It will still generate data that might cause replication lag..
This you could run happily in slony for a slave as you needed it to speed up
indexes, you can only run this on the master now, and this will generate a lot of
data. We generated 16 gigs of data on a 18 gig database when we clustered
our whole database.
Hot Standby Gotchas
Queries on slaves
We have found that the default setting of the parameter

max_standby_streaming_delay = 30” might not always be that good.
Using pg_dump for backing up database on slave will hit the
“max_standby_streaming_delay and dump will be canceled.
Largest value that can be set on max_standby_streaming_delay without
restart is 35 min
You will need more then you think, we currently keep default 5000
segments which does add up 80 gigs of logs. For us this equals about 3-4 days.
But we don't really worry too much, re-syncing is trivial.
Monitoring Hot Standby
In slony all you really had to care about was the number of events you were behind.
This would give you a good indication of the status of the slave.
Logging in hot standby
One way is to use the repmgrd on all the slaves which will write records to the master
database (every 3 seconds)
It is a separate process and will require its own monitoring to make sure its running
With hot standby there are basically 3 things you need to monitor

Replication lag – Lag spikes, slow network

Application lag – Something is locking a slave database or overloading it

When last monitor record was sent – if the whole machine falls off, or if repmgrd isn't running
Be aware of build up in the repl_monitor table. Since each node writes a record every 3
seconds having a few nodes, this table will grow quickly, we do cron job once a week to
truncate the table
Keeping things running

Adding a new slave node, start an rsync with a bandwidth limit, when
done use the repmgr to clone with force, and you are more or less done

Slave node has fallen too far behind, it will never catch up. Shut it down and
repmgr with force, you might want to do this a couple of times so what it will
need to replicate is minimized, then just start it up again.

When doing extremely large data changes it might be faster to shut down slave
nodes and use repmgr with force when done, have found that this actually can go
much faster then waiting for it to finish the replication

Developers can easily sync up their local databases to the master test
database by following a few steps using repmgr

How hot standby has made my life easier

I only need to administrator database roles on one machine

Emergency database schema changes are a breeze

I sleep much better before each release, knowing if all goes to crap I can
still re-sync a slave node much faster

Database release script has become much easier to handle since its just sql

Shorter database outage during release

Much shorter database release testing, as much as 2-3 work days less
work per release.

Maintenance is more or less non-existent which leaves more time for other
Streaming Replication is one of the best things about PostgreSQL
Please check with your regional head for appropriate
disclaimer to see if different from below
The information in this presentation (the “Information”) is for informational
purposes regarding the Albourne group, which includes Albourne Partners
Limited, Albourne America LLC, Albourne Partners Japan, Albourne
Partners (Asia) Limited, Albourne Partners (Singapore) Pte. Ltd., Albourne
Partners Deutschland AG, and
Albourne Partners (Cyprus) Limited
an “Albourne Group Company and collectively, the “Albourne Group”).
The Information is an invitation communicated by the relevant Albourne
Group Company, as more fully described below, to subscribe to such
Albourne Group Company’s investment advisory services in jurisdictions
where such invitation is lawful and authorised. The Information does not
constitute an invitation, inducement, offer or solicitation in any jurisdiction
to any person or entity to acquire or dispose of, or deal in, any security, any
interest in any fund, or to engage in any investment activity, nor does it
constitute any form of investment, tax, legal or other advice.
In the United States, the Information is being furnished, subject to United
States law, by Albourne America LLC (registered as an investment adviser
with the United States Securities and Exchange Commission) to persons
that Albourne America LLC believes to be an “Accredited Investor” as that
term is defined in Regulation D under the Securities Act of 1933, and a
“Qualified Purchaser” as that term is defined in Section 2(a)(51) of the
Investment Company Act of 1940. In Canada, the Information is being
furnished, subject to Canadian law, by Albourne America LLC (registered
as an international adviser under National Instrument 31-103) to persons
that Albourne America LLC believes to be a “Permitted Client” within the
meaning of the National Instrument 31-103. In the United Kingdom, the
Information is being furnished, subject to English law, by Albourne
Partners Limited (authorised and regulated by the Financial Services
Authority with registered number 175725) to persons categorised as
eligible counterparties or professional clients as those terms are defined
by the UK Financial Services Authority.
In each of Japan, Hong Kong, Singapore and Germany the Information is
being furnished respectively by: Albourne Partners Japan (authorised and
regulated by Director of Kanto Local Financial Bureau, with reference
number 1528) subject to Japanese law; Albourne Partners (Asia) Limited
(regulated by the Securities and Futures Commission of Hong Kong with
Central entity number AKX858) subject to Hong Kong law; Albourne Partners
(Singapore) Pte. Ltd. (registered with the Monetary Authority of Singapore)
subject to Singapore law and Albourne Partners Deutschland AG, and in all
cases, to persons whom the relevant Albourne Group Company believes to
be a financially sophisticated, high net worth and institutional investors
capable of evaluating the merits and risks of hedge funds, private equity
funds and/or any other alternative investment securities (collectively the
“Funds”). To the extent that the Information is supplied in any jurisdiction
other than the United States, Canada, the United Kingdom, Japan, Hong
Kong or Singapore, the relevant Albourne Group Company is Albourne
Partners Limited and the Information is supplied subject to English law.
If you are not the kind of investor described above in the jurisdictions listed
above, or if in your jurisdiction it would be unlawful for you to receive the
Information, the Information is not intended for your use. The Information
and the services provided by any Albourne Group Company is not provided
to and may not be used by any person or entity in any jurisdiction where the
provision or use thereof would be contrary to applicable laws, rules or
regulations or where any Albourne Group Company is not authorized to
provide such Information or services.
In the United States, interests in Funds are made through private offerings
pursuant to one or more exemptions provided under the United States
Securities Act of 1933, as amended. You should carefully review the
relevant offering documents before investing in any Funds.
You are solely responsible for reviewing any Fund, the qualifications of its
manager, its offering documents and any statements made by a Fund or
its manager and for performing such additional due diligence as you may
deem appropriate, including consulting your own legal, tax and
compliance advisers.
To the extent any of this presentation’s Information contains information
obtained from third parties, (a) the Albourne Group makes no
representations or warranties, express or implied, as to the accuracy or
completeness of information in this presentation; and (b) the Albourne
Group and all third party contributors disclaim all liability for any loss or
damage, which may arise directly or indirectly from any use of or reliance
upon any such data, forecasts or opinions or the Information generally.
This document has been supplied free of charge and shall not form part of
the services provided under any service agreement you may have with any
relevant Albourne Group Company.
Potential conflict of interest: Each Albourne Group Company advises
clients that are affiliates with or are connected with the management
company of hedge funds or private equity funds that are the subject of its
research reports, which may create an incentive for the Company to
favour the management company in its reports. The Albourne Group will
take reasonable steps to manage any potential conflict of interest but
regards it as in the best interest of all of its clients that it should not be
restricted in relation to the Funds taken into review as a result of any
potential conflict of interest. In appropriate cases, the relevant Albourne
Group Company will decline to act for one or more potential or existing
No part of the Information in this presentation is intended as an offer to
sell or a solicitation to buy an security or as a recommendation of any
firm, Fund or security. You should be aware that any offer to sell, or
solicitation to buy, interests in any such Funds may be unlawful in
certain states or jurisdictions.
There can be no assurance or guarantee that the Albourne Group’s
performance record or any Albourne Group Company’s performance
record will be achievable in future. There is no assurance that any
client of an Albourne Group Company will necessarily achieve their
investment objective or that such client will make any profit, or will be
able to avoid incurring losses. Funds are speculative, involve a high
degree of risk, and are illiquid: you could lose all or a substantial
amount of any investment you make in such Funds. Furthermore, such
Funds are not subject to all the same regulatory requirements as are
mutual funds; may involve complex tax structures and delays in the
distribution of important tax information; often charge higher fees than
mutual funds and such fees may offset the Funds’ trading profits; may
have a limited operating history; may be highly volatile, and there may
not be a secondary market for interests in such Funds. There may be
restrictions on redemptions and transfer of interests in such Funds,
and such interests may otherwise be illiquid. Such Funds may also be
highly leveraged and may have a fund manager with total investment
and/or trading authority over the Fund. It should also be noted that in
the case of hedge funds, there may be a single adviser applying
generally similar trading programs with the potential for a lack of
diversification and concomitantly higher risk; hedge funds may also
effect a substantial portion of trades on foreign exchanges, which have
higher trading costs. On the other hand, private equity funds may have
a limited number of holdings and concomitantly higher risk.