dba

Again there is another pretty long hiatus.

Thing happens and independently from how bad my childhood was, family comes first.

Anyway after a rollercoaster ride that started in July 2021 things are getting more stable.

Has been a long time since I wrote on my blog. The paths that life decides for you are strange. Often what you planned is completely scrambled by something bigger and completely unforeseen.

Back in 2018 I planned to relocate to France but the move went quite wrong for reasons that only now I can see clearly.

Anyway, finally after 10 months wandering without a real home I’m finally settled down somewhere.

Then this is is the perfect occasion to celebrate the second database administrators appreciation day.

Back in 2018 I launched the event because there were no day dedicated for this obscure and yet very important figure within the enterprises.

Therefore don’t forget 5th of July, it’s the day when you should say thank you to your DBA for all of the hard work.

Database administrators usually work behind the scenes, over the weekends and in antisocial hours. They strive to look after the database which is at the core of any business with little or no recognition.

The sad truth is that the DBA is one of the most obscure and one of the less appreciated roles within the IT organisations.

Back from Estonia after the amazing European PGConf 2016 I’m about to give a talk on the MySQL to PostgreSQL replication project I’m developing. But first things first. I want to thank all the organisers of the PGConf Europe. The talks were super interesting the location absolutely stunning and the old and new friends I met, remembered me how amazing is the PostgreSQL community. Unfortunately I missed the social events (did somebody said a DBA can have social life?

After upgrading some clusters to PostgreSQL 9.4.4 I noticed an increase of the database backup. Because the databases are quite large I’m taking the advantage of the parallel export introduced with PostgreSQL 9.3. The parallel dump uses the PostgreSQL’s snapshot export with multiple backends. The functionality requires the dump to be in directory format where a toc file is saved alongside with the compressed exports, one per each table saved by pg_dump.

With the revolutionary PostgreSQL 8.0 were introduced a different memory manager, the two queues. This algorithm uses three list of buffer page pointers called cache directory blocks (CDB). The lists T1,T2 are actually pointing buffered pages. B1 lists pages been recently in the shared buffer. The list T1 is used as LRU for the pages loaded from disk. The list T2 is used as LRU list for pages already cached and evicted from the list T1.

3.1.1 PostgreSQL 7.4, the LRU list In PostgreSQL 7.4 the free space in the shared buffer was managed using a simple last recently used list. When a page is first request from disk the buffer manager puts it into the first buffer in the free LRU list. If the first buffer is used by another page the list is shifted by one unity and the eventual last page in the list is discarded.

The PostgreSQL memory at first sight looks simple. If compared with the complex structures implemented in the other DBMS to a careless reader could seem rudimentary. However, the memory and in particular the shared buffers implementation is complex and sophisticated. This chapter will dig down deep into the PostgreSQL’s memory. 3.1 The shared buffer The shared buffer is a segment allocated at cluster’s startup. Its size is determined by the GUC parameter shared_buffers and the size can be changed only restarting the cluster.

2.4 The background writer Before the spread checkpoints the only solution to ease down the IO spike caused by the checkpoint was to tweak the background writer. This process were introduced with the revolutionary PostgreSQL 8.0. The writer, as the name suggests, works in the background searching for dirty buffers to write on the data files. The writer works in rounds. When the process awakes scans the shared buffer for dirty buffers.

The cluster in action PostgreSQL delivers his services ensuring the ACID rules are enforced at any time. This chapter will give an outlook of a ``day in the life’' of a PostgreSQL’s cluster. The chapter approach is purposely generic. At this stage is very important to understand the global picture rather the technical details. After the startup When the cluster completes the startup procedure it starts accepting the connections. When a connection is successful then the postgres main process forks into a new backend process which is assigned to the connection for the connection’s lifetime.