PgConf.Russia 2017

Do you test your PostgreSQL releases prior to Production in a dedicated test environment? Are you sure that your test environment (shortly Test) is equal to Production and in an appropriate state?

In Skype we were facing multiple challenges associated with database testing:
- Simplifying complex Production architecture of thousands of PostgreSQL instances, interconnected with RPCs and replications, infrastructure servers and external DB scripts, into their Test counterparts.
- Constantly growing hardware requirements, insufficient cleanup of data generated in Test.
- Differences between Test and Production were appearing and accumulating. Recognizing and fixing them required lots of effort.

"1C:Enterprise" is the most popular in Russia/Eastern Europe business applications development framework supports PostgreSQL for 10+ years. We'll review some cases and pecularities of 1C and PostgreSQL tandem.

This talk is intended for Oracle DBAs whose are considering to start using PostgreSQL as another RDBMS engine in their DB portfolio or for PostgreSQL DBAs, who wants to learn some of similarities and differences between Oracle and PostgreSQL. This talk is not intended to be a migration guideline neither advocating one platform over another. General database structure overview for both platforms will be compared in terms of memory configuration parameters; logical to physical structure as well as we’ll touch some security point of view and backup & recovery tools available. Oracle container databases are not covered. The talk want’s to help create a bridge for DBA and avoid some frustrations at the first Oracle DBA step to discover, learn and start using PostgreSQL as reliable fully functional database.

VIDEO

< Query failed: ERROR: deadlock detected
< DETAIL: Process 17371 waits for ShareLock on transaction 102733872; Blocked by process.
< Process 10414 waits for ShareLock on transaction 102733874; Blocked by process 17371.

Such "unpleasant" messages from the server can seriously puzzle the developer. When working with locks, in particular, with transactions in general, it is necessary to take into account the features of the implementation of client libraries, which can cause the above exception.

In the short talk, the mechanics of the interaction of locks will be explained, main attention being paid to causes of deadlocks. References to the relevant documentation pages will be given. A technique of "bypassing" this problem of concurrent data access will be described and illustrated with some generalized examples from practice are shown. The talk will be interesting to database developers and administrators as well as the client-side application developers.