<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by TheMightyRaider on Medium]]></title>
        <description><![CDATA[Stories by TheMightyRaider on Medium]]></description>
        <link>https://medium.com/@themightyraider7?source=rss-6bbc2b5c860a------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*Jw0vG8JpbQzaRxPa.jpg</url>
            <title>Stories by TheMightyRaider on Medium</title>
            <link>https://medium.com/@themightyraider7?source=rss-6bbc2b5c860a------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 19:34:27 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@themightyraider7/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Setup master and slave architecture in Postgres 14.*]]></title>
            <link>https://medium.com/@themightyraider7/setting-up-master-and-slave-architecture-in-postgres-14-c95d191c33ef?source=rss-6bbc2b5c860a------2</link>
            <guid isPermaLink="false">https://medium.com/p/c95d191c33ef</guid>
            <category><![CDATA[kubernetes]]></category>
            <category><![CDATA[master-slave-architecture]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[master-slave-replication]]></category>
            <category><![CDATA[replication]]></category>
            <dc:creator><![CDATA[TheMightyRaider]]></dc:creator>
            <pubDate>Mon, 22 Jul 2024 18:40:09 GMT</pubDate>
            <atom:updated>2024-08-16T05:46:42.533Z</atom:updated>
            <content:encoded><![CDATA[<p>In this article, We will talk about Postgres streaming replication and how to set it up! :)</p><p><strong><em>Terminology alert,</em></strong></p><ul><li>“<em>master</em>” can be called as “<em>primary</em>”</li><li>“<em>slave</em>” can be called as “<em>standby </em>”</li></ul><h3>What is streaming replication?</h3><p>PostgreSQL streaming replication is a process that replicates data from a primary PostgreSQL database to one or more standby databases in real-time, ensuring that these standby databases mirror the primary database accurately.</p><p>Some of the major benefits of having a standby server,</p><ul><li>It acts like a load balancer, that is read queries can be distributed to it.</li><li>High availability, The standby server can take over as the primary server incase the primary server fails. [ We won’t be talking about it in this article ]</li></ul><h3>How does replication work in Postgresql?</h3><p>Streaming replication works based on logs. Every transaction in postgres is written to a log called “<strong><em>Write-Ahead-Log [ WAL ]</em></strong>”. A slave database uses this log to replicate changes from its master.</p><p>Replication is basically handled by 3 process, which are wal_sender , wal_receiver and startup .</p><p>During replication, The <strong>startup</strong> process initialises and reads the necessary configuration to establish a replication connection, it triggers the creation of the wal_receiver process.</p><p><strong>The </strong><strong>wal_receiver process running in the slave db</strong> sends its <strong><em>LSN [ Log Sequence Number ]</em></strong> which essentially specifies its latest transaction in its WAL to the master db.</p><p>The<strong> </strong><strong>wal_sender process running in the master db</strong> sends its WAL data until its latest LSN starting from the LSN sent by the wal_receiver to the slave. As WAL records are received, the wal_receiver writes them to its WAL segments. The startup process applies the WAL segments to the slave data files, ensuring the database&#39;s state is synchronised with the master.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Xj5dow338POH-A5o" /><figcaption>High level overview of Postgres streaming replication!</figcaption></figure><h3>How to implement it?</h3><h4>Configuring the Master database</h4><ul><li>Create a replicator user, this user has the permission to initiate streaming replication and logical replication. This is a special privilege typically given only to roles that are intended to manage replication processes between a primary server and its replicas.</li></ul><pre>CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD &#39;&lt;somepassword&gt;&#39;</pre><ul><li>Configure the postgres.conf file</li></ul><pre># Add/Update the following variables in your config<br>wal_level =  replica # Determines the amount of information written to the WAL.<br>archive_mode =  on # Controls whether archiving of WAL files is enabled.<br>max_wal_senders =  5 # Specifies the maximum number of concurrent connections from standby servers or other replication slots.<br>listen_addresses =  &#39;*&#39; # Controls which interfaces PostgreSQL listens on for connections.<br>hot_standby =  on # Allows or disallows connections and queries</pre><blockquote><strong>Note:</strong> “hot_standby” doesn’t have any effect on master. However, when you setup your replication, parameters set on the master are automatically copied. This parameter is important to enable READS on slave. Otherwise, you cannot run your SELECT queries against slave.</blockquote><ul><li>Configure the pg_hba.conf file</li></ul><pre> <br>host replication replicator &lt;slave_ip&gt;/32 md5 # To allow replication connections from the slave</pre><ul><li>Start/Restart the postgres server!</li></ul><blockquote><strong>NOTE:</strong> You can usually find the above files located in /var/lib/postgres/data or you can find them in echo $PGDATA .</blockquote><h4>Configuring the Slave database</h4><ul><li>Shutdown the database</li><li>Using pg_basebackup, we will get the data from the master.</li><li>pg_basebackup helps us to stream the data through the wal_sender process from the master to a slave to set up replication.</li><li>Before running the below command, you need to make a backup of your existing db if needed. You can do that using, cp $PGDATA &lt;pathtobackup&gt; .</li><li>pg_basebackup expects the directory you have mentioned to be empty. So you have to delete the files inside $PGDATA . You can do that using rm -rf $PGDATA/*</li></ul><pre>pg_basebackup -h &lt;master_ip&gt; -U replicator -p 5432 -D $PGDATA -P -Xs<br><br>&#39;&#39;&#39;<br>-h: Specifies the hostname or IP address of the master server from which to take the backup.<br>-D: Specifies the directory where the backup data will be stored on the local machine (slave server).<br>-U: Specifies the username to use when connecting to the master server for replication.<br>-p : Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections.<br>-P: Prompts for the replication password of the specified user. After entering the password, the backup process begins.<br>-Xs: Specifies that the backup method should use streaming replication, allowing continuous replication from the master to the standby server.<br>&#39;&#39;&#39;</pre><ul><li>Start your postgres service!</li></ul><h4>If your slave is running as a pod</h4><ul><li>In this case, you wouldn’t be able to delete the $PGDATA content because the running postgres service uses it and you wouldn’t be able to shutdown the service as well because that would result in shutting down the pod.</li><li>To overcome this, you can either mount the $PGDATA as volume and then through the shell of the volume you can execute the steps mentioned in the above section else you can move the files inside the /data folder to a subfolder and then delete the content in the subfolder.</li><li>You can achieve the second method through the yaml config for the pod.</li></ul><pre>  env:            <br>     - name: PGDATA<br>       value: /var/lib/postgresql/data/pgdata</pre><ul><li>This would create the postgres files inside <em>pgdata </em>[ /var/lib/postgres/data/pgdata] folder, which can be deleted. You can then run the pg_basebackup command mentioned in the above section!</li></ul><p>Hooray, Streaming replication is done now! 🎉</p><h4>Verification of replication setup</h4><p>As discussed earlier, you must see the below processes running on their respective database machine.</p><pre>On Master<br>=========<br>$ ps -eaf | grep sender<br>postgres  1287  1268  0 10:40 ?        00:00:00 postgres: wal sender process replicator 192.168.0.28(36924) streaming 0/50000D68<br> <br>On Slave<br>=========<br>$ ps -eaf | egrep &quot;receiver|startup&quot;<br>postgres  1251  1249  0 10:40 ?        00:00:00 postgres: startup process   recovering 000000010000000000000050<br>postgres  1255  1249  0 10:40 ?        00:00:04 postgres: wal receiver process   streaming 0/50000D68</pre><p>Now, Any changes made to your master db will be reflected on the slave.</p><h3>Reference</h3><ul><li><a href="https://www.percona.com/blog/setting-up-streaming-replication-postgresql/">https://www.percona.com/blog/setting-up-streaming-replication-postgresql/</a></li><li><a href="https://www.postgresql.org/docs/14/app-pgbasebackup.html">https://www.postgresql.org/docs/14/app-pgbasebackup.html</a></li><li>The obvious, ChatGPT! :)</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c95d191c33ef" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Multiprocessing with SQLAlchemy in Python]]></title>
            <link>https://medium.com/@themightyraider7/multiprocessing-with-sqlalchemy-in-python-65372888cfb8?source=rss-6bbc2b5c860a------2</link>
            <guid isPermaLink="false">https://medium.com/p/65372888cfb8</guid>
            <category><![CDATA[sqlalchemy]]></category>
            <category><![CDATA[pool]]></category>
            <category><![CDATA[multiprocessing]]></category>
            <category><![CDATA[connection-pool]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[TheMightyRaider]]></dc:creator>
            <pubDate>Tue, 25 Jun 2024 18:41:43 GMT</pubDate>
            <atom:updated>2025-05-11T10:50:27.988Z</atom:updated>
            <content:encoded><![CDATA[<p>You’re using multiprocessing to run some code with connection pool across multiple processors and then boom, you are either experience one of this,</p><ul><li>You see some weird database errors which aren’t happening when you run without multiprocessing!</li><li>Your application freezes!</li><li>You see some database error and then the application just freezes!</li></ul><p>Your debugging reflex would kick in, You check the active connections, CPU usage — nothing’s happening. Everything is relaxed and chilling!</p><p>Now, the frustration reflex kicks in! 😑</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/540/1*1W7CehLFNKBq5CdQOz1b6w.gif" /></figure><h3>How does Multiprocessing work in python?</h3><p>A quick recap about multiprocessing, Imagine you have a computer with<strong> 4 core in it’s CPU.</strong> A core is defined as “<em>individual processing unit within a CPU (Central Processing Unit), responsible for executing instructions and performing calculations.</em>”</p><p>So that means we can run 4 process parallelly, aka multiprocessing!</p><p>The main question here is, <strong>How are these 4 process created?</strong></p><blockquote>A process is called child process if it is created by a another process [ the parent process ]</blockquote><p>In this article, We would be talking about child processes!</p><h4>How are child processes created in multiprocessing?</h4><p>The two commonly used methods in which a child process can be created is “<em>fork</em>” or “<em>spawn</em>”.</p><p>In fork, The subprocesses [ child ] are “<em>almost</em>” a copy of the main program which includes<strong> lock state</strong>,<strong> file descriptors</strong>, the entire memory including the initialised Python interpreter, loaded modules, and constructed objects in memory. <strong>Note that the subprocesses have their own memory space.</strong></p><pre># Creating a child process<br><br>import os<br><br>if fork(): # This would return 0 in case of child process<br>  print(&quot;Printing from the main process&quot;.format(os.getpid())<br>else:<br>  print(&quot;Printing from the child process&quot;.format(os.getpid())</pre><p><strong>Fork is fast, </strong>because they don’t have to load the application in the memory from scratch. It “<em>almost</em>” duplicates the parent process!</p><p>Now, coming to the “<em>almost</em>” part. <strong>Fork doesn’t copy one thing from the main program which are the threads. </strong>This could be a reason why your application freeze at a point.</p><p>Let’s try to understand it with an example,</p><pre>import os<br>import threading<br>import multiprocessing<br><br># Parent process<br>lock = threading.Lock()<br>lock.acquire()<br><br>def print_vamos():<br>    print(&quot;Acquiring a lock on the resources&quot;)<br>    lock.acquire()<br>    print(&quot;Acquired the lock&quot;) # the code won&#39;t get executed!<br>    print(&quot;Vamos!&quot;)<br>    lock.release()<br>    print(&quot;Horray! Released the lock&quot;)<br><br>with multiprocessing.Pool(2) as pool:<br>    pool.apply(print_vamos)<br><br>pool.close()<br>pool.join()<br><br>lock.release()</pre><p>In the above example, The parent thread acquire the lock on the CPU resources, After which we create two child processes which would “<em>almost</em>” copy everything including the lock state of the parent process as well.</p><p>Now, When the child processes tries to acquire the lock, It won’t be able to do it because the lock is already in acquired state. So it waits for the lock to be released, but it won’t get released because the thread that acquired the lock isn’t copied! Hence we got ourselves a “<em>deadlock</em>” situation.</p><p>So, <strong>Fork is unsafe! </strong>Forked processes can also turn to be heavy because the parent process can run some memory intensive operations which aren’t required for the child processes functioning.</p><pre>import multiprocessing<br><br>df = pd.read_csv(&quot;A huge file&quot;) # This will be copied to the child process,<br>                                # though it is not needed by it!<br><br>def print_vamos():<br>  print(&quot;Vamosss!&quot;)<br><br>with multiprocessing.Pool(2) as pool:<br> pool.apply(print_vamos)</pre><p>To conclude, <strong>Fork are fast, unsafe and uncompact!</strong></p><p><strong>Creating child processes through </strong><strong>spawn method helps solve the issue of “<em>unsafe</em>” and “<em>uncompact</em>”.</strong></p><p>spawn essentially re-runs the program from scratch after copying “almost” everything from the parent process. <strong>In a nutshell, it does </strong><strong>fork() followed by </strong><strong>execve() system cal</strong>l, which basically makes the child process run as a new program thereby removing all the copied stuff! That solves our problem, because lock state aren’t inherited by child processes anymore!</p><p>Hence,<strong> spawn is safe, compact and slower compared to fork!</strong></p><p><strong>NOTE: </strong>The two processes (parent and child) do not share a common memory space. The child processes only inherit the parent state as it was at the time of the fork. So changes made by a child process won’t affect other processes.</p><p>In the article, we will stick with fork method since my use case requires some of the initialisation to take place only once at the parent level!</p><p>Heads up though, If you are creating child processes using spawn then you don’t need to do worry about the below! 😌</p><h3>How to properly create child processes with connection pool?</h3><p>Quoting from the <a href="https://docs.sqlalchemy.org/en/20/core/pooling.html#using-connection-pools-with-multiprocessing-or-os-fork">docs</a>,</p><p>“<em>It’s critical that when using a connection pool, and by extension when using an </em><a href="https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Engine"><em>Engine</em></a><em> created via </em><a href="https://docs.sqlalchemy.org/en/20/core/engines.html#sqlalchemy.create_engine"><em>create_engine()</em></a><em>, that the pooled connections </em><strong><em>are not shared to a forked process</em></strong><em>. TCP connections are represented as file descriptors, which usually work across process boundaries, meaning this will cause concurrent access to the file descriptor on behalf of two or more entirely independent Python interpreter states.</em></p><p><em>Depending on specifics of the driver and OS, the issues that arise here range from non-working connections to socket connections that are used by multiple processes concurrently, leading to broken messaging (the latter case is typically the most common).</em>”</p><p>Let’s digest the above,</p><h4>What happens when you fork a pooled connection?</h4><p>“<em>TCP connections are represented as file descriptors, which usually work across process boundaries, meaning this will cause concurrent access to the file descriptor on behalf of two or more entirely independent Python interpreter states.</em>”</p><p>The keyword is the file descriptors, In Unix-like operating systems, a file descriptor is a low-level integer handle that represents an open file or a socket (including TCP connections). File descriptors are used by the operating system to manage open files and sockets.</p><p>As we discussed in the previous section, we know that during fork ,<strong> the child process copies “almost” everything which includes the file descriptors as well. </strong>This means that any connection in the pool of the parent process are also “inherited” by the child process.</p><h4>Why not to fork a pooled connection?</h4><p>“<em>Depending on specifics of the driver and OS, the issues that arise here range from non-working connections to socket connections that are used by multiple processes concurrently, leading to broken messaging (the latter case is typically the most common).”</em></p><p>After forking, the parent and child processes have independent copies of the connection pool state.</p><p>Changes made to the connection pool in one process are not reflected in the other process. This can lead to inconsistencies because both processes might believe they have control over the same set of connections.</p><pre>from sqlalchemy import create_engine<br>import multiprocessing<br>import os<br><br># Create an engine with a connection pool in the parent process<br>engine = create_engine(&#39;sqlite:///example.db&#39;, pool_size=5)<br><br>def get_stuff():<br>  &#39;&#39;&#39;<br>  The connection received here is the same <br>  as the one created in the parent process<br>  &#39;&#39;&#39;<br>  with engine.connect() as conn:<br>        # And,tada! You might start seeing weird behaviours!<br>        conn.execute(text(&quot;...&quot;))<br><br># This will create a new connection<br>with engine.connect() as conn:<br>        # Post execution, the connection is returned to the pool.<br>        conn.execute(text(&quot;...&quot;))<br><br>with multiprocessing.Pool(2) as pool:<br>  pool.apply(get_stuff)<br></pre><p>It is important to note that, File descriptors don’t have a locking mechanism. The operating system manages file descriptors and it only ensures that they point to the correct resource which in our case is the DB.</p><p>So, when a file descriptor (representing a TCP connection) is shared between multiple processes, both processes can try to read from or write to the same connection concurrently.</p><p>This would create a different issues like,</p><ul><li><strong>Non-working connection</strong>: Some database drivers or network protocols might detect that the file descriptor is being accessed by multiple processes and could close the connection, leading to errors when trying to use it.</li><li><strong>Broken messages:</strong> When multiple processes read from the same TCP stream without coordination, they can consume parts of the data stream unpredictably, leading to interleaved or mixed-up responses.</li></ul><p>A example scenario of broken messages:</p><pre># Process A<br>sock.sendall(b&quot;Request A&quot;) # Expects &quot;Hello&quot;<br><br># Process B<br>sock.sendall(b&quot;Request B&quot;) # Expects &quot;World<br><br># Process A reads the first part of the response from the server.<br>response_a_part = sock.recv(3)  # Reads &quot;Hel&quot;<br><br># Before Process A completes reading its response, Process B reads from the same socket.<br>response_a_part = sock.recv(5)  # Reads &quot;loWor&quot;<br><br># Process A attempts to read the remaining part of its response.<br>response_a_rest = sock.recv(1024)  # Reads &quot;ld&quot;</pre><p>In the end,</p><ul><li>response_a_part + response_a_rest for Process A might be &quot;Helld&quot; instead of the expected &quot;Hello&quot;</li><li>response_b_part for Process B might be &quot;loWor&quot; instead of the expected &quot;World&quot;.</li></ul><h4>How to fork a connection pool properly?</h4><p><strong>The answer is don’t fork the connections in the connection pool!</strong> If there are connections in the pool then make sure to dispose it properly before forking. SQLAlchemy allows us to achieve this using engine.dispose(close=False) method where the close=False argument ensures that active connections are gracefully removed!</p><pre>from sqlalchemy import create_engine, text<br>import multiprocessing<br>import os<br><br># Create an engine with a connection pool in the parent process<br>engine = create_engine(&#39;sqlite:///example.db&#39;, pool_size=5)<br><br>def initializer():<br>    # Connections in the pool are gracefully removed!<br>    engine.dispose(close=False)<br><br>def get_stuff():<br>  <br>  # A new connection is created!<br>  with engine.connect() as conn:<br>        &#39;&#39;&#39; <br>        Post execution, the connection is returned<br>        to the pool available in the child&#39;s memory.<br>        &#39;&#39;&#39;<br>        conn.execute(text(&quot;...&quot;))<br><br># This will create a new connection<br>with engine.connect() as conn:<br>        # Post execution, the connection is returned to the pool.<br>        conn.execute(text(&quot;...&quot;))<br><br>with multiprocessing.Pool(2,initializer=initializer) as pool:<br>  pool.apply(get_stuff)<br></pre><p>Now, The child processes have a clean connection state! Upon processes request, The engine would create fresh connections which would only be available for that process!</p><h3>Summary</h3><ul><li>The safest way to do multiprocessing with database connection is to create child processes using spawn method.</li><li>Incase of fork method, You need to make sure you dispose any existing connections using engine.dispose(close=False)<strong> </strong>and also ensure that there aren’t any acquired locks in the parent during the time of forking!</li></ul><h3>Reference</h3><ul><li>The obvious, ChatGPT for assistance! :)</li><li><a href="https://docs.sqlalchemy.org/en/20/core/pooling.html#using-connection-pools-with-multiprocessing-or-os-fork">https://docs.sqlalchemy.org/en/20/core/pooling.html#using-connection-pools-with-multiprocessing-or-os-fork</a></li><li>To understand how connection pool work in multiprocessing,<br><a href="https://stackoverflow.com/q/78669314/13855523">https://stackoverflow.com/q/78669314/13855523</a></li><li>Amazing writeup on multiprocessing stalling! <a href="https://pythonspeed.com/articles/python-multiprocessing/">https://pythonspeed.com/articles/python-multiprocessing/</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=65372888cfb8" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>