opendbteam workspace

opendbteam workspace
opendbteam workspace

Monday, May 23, 2016 partenaire des #PGDAY France 2016 #postgresql

Le PGDay France est un moment de rencontres et de conférences pour la communauté francophone de PostgreSQL. Les conférences s'adressent à tous les utilisateurs du logiciel : étudiants, administrateurs systèmes, DBA, développeurs, chefs de Projets, décideurs, etc
Plus d'information sur:

Friday, May 13, 2016

#NewReleases: #PostgreSQL #9.6 #Beta1 is out!

The new PostgreSQL 9.6 Beta1 is out!
The new version includes features such as:

  • Parallel sequential scans, joins and aggregates
  • Support for consistent, read-scaling clusters through multiple synchronous standbys and "remote_apply" synchronous commit.
  • Full text search for phrases
  • postgres_fdw can now execute sorts, joins, UPDATEs and DELETEs on the remote server
  • Decreased autovacuum impact on big tables by avoiding "refreezing" old data.

More information at: PostgreSQL 9.6 Beta1 - annoucement

Other minor releases are out too, aiming mostly at fixing bugs.
More information at: minor updates - release notes

Tuesday, May 3, 2016

Streaming replication slots in #PostgreSQL 9.4 / 9.5

Streaming replication slots are a pending feature in PostgreSQL 9.4, as part of the logical changeset extraction feature.
What are they for, what do you need to know, what changes?

What are replication slots?

Streaming replication slots are a new facility introduced in PostgreSQL 9.4. They are a persistent record of the state of a replica that is kept on the master server even when the replica is offline and disconnected.
They aren’t used for physical replication by default, so you’ll only be dealing with them if you enable their use. This post explains what they do, why they’re useful for physical replication, and why they’re necessary for logical replication.

Why slots?

As part of the ongoing work Andres has been doing on log-streaming logical replication, changeset extraction, and bidirectional replication, there’s been the need to better control WAL retention.
As streaming replication currently works, the master doesn’t make any effort to retain extra WAL for a standby that’s fallen behind. If the standby gets too far behind, the master will delete WAL segments the standby still needs to replay, and the standby cannot recover. You get an error like:
ERROR:  requested WAL segment 00000001000000010000002D has already been removed
To prevent this, you are currently expected to configure continuous archiving and provide a restore_command to give the replica access to the archive, or be willing to re-create a standby that falls behind.
A less reliable alternative is to set wal_keep_segments to a “high enough” value to ensure that the replica never falls too far behind – but there’s no real way to guess what is high enough, and the higher the value is set the greater the risk the master will be able to run out of space in pg_xlog. So generally archiving is preferred.
The master could retain WAL for a replica while it’s connected, but as soon as the replica disconnects for any reason the master forgets all about it. As transient disconnects are common, this means there’s not much point in trying to retain WAL just for connected replicas.
We’ve gotten away with this so far because the option of falling back to continuous archiving has been available for when the master discards WAL the replicas still need. When combined with or scripts likeWAL-E, a shared network file system, or a shared host to scp archives to/from, it’s done the job pretty well. It’s a bit confusing for new users, but it works well.
With log streaming logical replication we can’t fall back on continuous archiving anymore, because the master must read WAL and decode it to send the results to the replicas; it can’t just send raw WAL records. Replicas cannot just read raw WAL from the master and extract what they require themselves because only the master has a record of transaction ID statuses, the local oids of objects, etc.
We could just teach the master to run restore_command (something people seem to assume happens anyway) – but that could get very inefficient when many replicas want the same WAL archive. We’d need a caching system and other complexity. It also doesn’t get rid of the need for the admin to manage retention for archived WAL.
Unlike with physical replication, the option of just re-seeding a replica that has fallen too far behind is not acceptable for logical replication. There may be other databases on the replica host that contain their own original data. In the bidirectional replication case, the replica database may also contain newdata not yet replicated to other nodes. So we can’t just write the replica off and make a new pg_basebackup from the master or re-rsync it like we do with physcial replication.
The introduction of slots is intended to deal with this issue by giving the master persistent knowledge of the state of replicas, even when they are disconnected. While it was added for logical replication, the concept is useful in physical replication as well, as it lets the master retain as much WAL as is required by all its standbys and no more. There’s no guesswork like there is with wal_keep_segments. There’s no need to set up a separate WAL archiving system and manage retention in it. So long as you have enough space in pg_xlog for the extra WAL, it just works. If a replica disconnects the master can keep WAL for it until it reconnects and replays the WAL.
The downside of this approach is that it adds another way for unbounded WAL growth to fill pg_xlog – when a replica cannot receive or replay WAL fast enough or remains offline for a long time, the master will keep on retaining WAL until pg_xlog fills up and it starts reporting errors to clients. This can already happen on a master with long checkpoint intervals under heavy load, but it’s much easer to trigger with slots as a replica that goes ofline indefinitely will cause pg_xlog to fill up.
While monitoring and adjustment of pg_xlog capacity is already necessary for a busy PostgreSQL server, it is much more so for a server that is using slots to manage WAL retention for replication.

Operational impact

By default, physical replication will not use slots. Nothing changes. You must use WAL archiving on top of streaming to ensure reliable replication.
You can enable the use of replication slots for physical replication with the primary-slotnameparameter in recovery.conf. The physical replication slot must already have been created on the master with the pg_create_physical_replication_slot(...) function; see the docs.
When using slots to manage physical replication, WAL archiving is no longer required for replication (though you may wish to retain it for PITR anyway). You don’t need to deal with the difficulties of managing WAL retention amongst multiple replicas that may or may not be online, with replay positions that are not known to the master when offline. You don’t need to manage the space in the archive store.
Instead, you need to keep an eye on space in pg_xlog. WAL retention is managed automatically for you, but you must ensure there’s enough space available.
If a replica is retired from service or unrecoverably lost due to failure of the host, you must manually remove the slot from the master because the master won’t delete any xlog until you do. If you have a system where the master produces xlog rapidly, this may require a lot of pg_xlog space or fairly quick action. A monitoring system like Icinga or Zabbix is strongly recommended.
A replica that is unable to keep up the master must be fixed or retired to prevent the master from running out of xlog space.
With slots you no longer need to monitor the replicas to determine their state, as accurate and up to date information is always available in the pg_replication_slots view. So all you need to do is monitor the master’s pg_xlog space and the status of all replication slots.
Any wal_keep_segments parameter will be respected as a minimum for WAL retention.
So: using slots for physical replication is a trade-off. You don’t need archiving anymore, but you have to monitor the state of the system more closely to avoid disrupting the master.

Future work

To reduce the need for a large pg_xlog space allocation on what could be expensive high-performance storage, in future slots may be extended to support moving WAL to a separate location once it’s no longer required for the master’s own checkpointing.

Introducing the transform your #Database Tour #postgresql

Are you facing the challenge to meet growing IT requirements while operating on a limited budget?

Learn more about why you should transform your database management system (DBMS) and make open source part of your strategic business and IT choices at the ‘Transform your Database Tour’ on 19th May at ICAB in Brussels.
An open source DBMS offers you various benefits, including cost reduction, liberation from vendor lock-in, and a large development community. Paired with enterprise-class services, 24x7 support and reliable management tools, open source is a viable alternative to traditional proprietary DBMSs.
We are organising this event with the Opendb Team which has 8 years of experience in the open source database market and will explain why an open source database should be part of your business and IT strategy. One of Opendb’s customers will then tell you all about their DBMS transformation.

After these interactive presentations, you can share your own experience, and discuss the challenges your organization may face with the implementation of a new DMBS strategy. At the end of the day, you will leave with considerable knowledge about open source databases and new insights about the opportunities to apply this in your own IT environment.
We look forward to meeting you there. In the meantime, please feel free to contact me ( or my colleague Axelle Dervaux at

Kind regards,
Gilles Fauvie

A little #Reminder on #PostgreSQL #GoodPractice (1)

Time and time again, people ignore good practice and terrible things happen. In this first post, Josh Berkus reviews the directories you should not play with and the possible consequences of not following good practice advice:

Don't delete pg_xlog