PGConf.Russia 2018

Rambler & Co is a lot of publications, services and projects. Appear new and grow existing. This environment requires a reliable, fault-tolerant, scalable, automated system.

I'll tell you about the structure of our PostgreSQL SaaS, what tools and technologies we use. Quorum of 3 Data Centers. A single entry point for clients based on dynamic routing. Emergency switching of the primary server. Transparent scaling for reading. Create a replica without load on the cluster. Transparent transfer of PostgreSQL cluster to other servers. Update dev environment from prod for development. Backup with compression and the use of multiple CPUs on the side of the database, the restoration of one database from basebackup. Monitoring of sql queries.

WAL-G is simple and effective disaster recovery tool for PostgreSQL using cloud storages. In its core functionality, WAL-G is the successor of WAL-E rewritten in Go. But there is one new neat feature - delate-backups. WAL-G delta-backups, whenever possible, stores only pages, changed since the previous backup. In this talk, I'm going to describe development process of this feature.

Surprisingly, most important and complicated question was the design of the interface: WAL-e is simple and comprehensive, keeping these properties was goal #1. Technical details of implementation were covering some underwater stones too. Besides these, I want to discuss the perspective of technological development and future coordination of recovery tools developers.

Tutorial will show specifics of server programming in these languages. I will present practical examples and compare the features of these languages in PostgreSQL environment from the viewpoint of solving practical tasks.

Avito is the biggest classified site of Russia, and the third largest classified site in the world (after Craigslist of USA and 58.com of China). In Avito, ads are stored in PostgreSQL databases. At the same time, for many years already the logical replication is actively used. With its help, the following issues are successfully solved: the growth of data volume and growth of number of requests to it, the scaling and distribution of the load, the delivery of data to the DWH and the search subsystems, inter-base and internetwork data synchronization etc. But nothing happens "for free" - at the output we have a complex distributed system. Hardware failures can happen - it is natural - you need to be always ready for it. There is plenty of samples of logical replication configuration and lots of success stories about using it. But with all this documentation there is nothing about samples of the recovery after crashes and data corruptions, moreover there are no ready-made tools for it. Over the years of constantly using PgQ replication, we have gained extensive experience, rethought a lot, implemented our own add-ins and extensions to restore and synchronize data after crashes in distributed data processing systems. In this report, we would like to show how our experience can be shifted to a new logical replication subsystem in 10th version of PostgreSQL. In the current implementation, these are only non-trivial solutions - there is a number of issues for the community, that come down to implementing simple recovery mechanisms - as simple as configuring the replication in 10th version.