<?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 Denis Matveev on Medium]]></title>
        <description><![CDATA[Stories by Denis Matveev on Medium]]></description>
        <link>https://medium.com/@disson?source=rss-4086cecb427f------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*lACUL7mBXHyYeGfpxLm7nA.jpeg</url>
            <title>Stories by Denis Matveev on Medium</title>
            <link>https://medium.com/@disson?source=rss-4086cecb427f------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 18:03:55 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@disson/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[VPN Network via WireGuard]]></title>
            <link>https://faun.pub/vpn-network-via-wireguard-f0ec00275bfe?source=rss-4086cecb427f------2</link>
            <guid isPermaLink="false">https://medium.com/p/f0ec00275bfe</guid>
            <dc:creator><![CDATA[Denis Matveev]]></dc:creator>
            <pubDate>Wed, 19 Apr 2023 11:47:42 GMT</pubDate>
            <atom:updated>2023-04-19T11:47:42.272Z</atom:updated>
            <content:encoded><![CDATA[<h4>Building access to cloud network for remote users. Part 1</h4><h4>Intro</h4><p>We were recently presented with a challenging task of creating a secure VPN for all of our servers and closing the 22 port to prevent brute force attacks. The intention was to enhance security by permitting access solely through VPN, where authorization is performed by keys. While some suggest that enabling public key authentication, disabling root access, and password access is sufficient, managing these aspects becomes increasingly difficult with a growing number of servers, even with automated server creation and setup. Additionally, it becomes challenging to remain vigilant in collecting logs to a remote machine and using scanning tools daily. As a solution to ease the administrator’s burden, we considered disabling ssh and leaving access solely to the ansible machine with a static IP address. After extensive research, we opted for WireGuard, a fast and easy-to-configure VPN that we highly recommend. While installing and configuring the server-side on a dedicated machine was simple, the most challenging aspect was propagating configurations across all of our 200 servers.</p><p>Go ahead! Let’s see what’s been done!</p><h4>Network division and design</h4><p>It has been decided to take the 10.15.0.0/16 network and divide it into subnets with a /24 mask. For example, 10.15.20.0/24, 10.15.21.0/24, 10.15.22.0/24, and so on. You should divide it into the number of projects or any other logical criteria you have.</p><p>Here is a list of subnets per project:</p><ul><li>project A 10.15.20.0/24</li><li>project B 10.15.21.0/24</li><li>project C 10.15.22.0/24</li><li>project D 10.15.23.0/24</li><li>project E 10.15.24.0/24</li><li>project F 10.15.25.0/24</li><li>project G 10.15.26.0/24</li><li>project H 10.15.27.0/24</li><li>clients 10.15.30.0/24</li></ul><p>In this approach, projects can be both isolated and interconnected.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/598/1*mbvlsy1QNbOGsg_MynbnmQ.png" /><figcaption>Pic 1. Network diagram</figcaption></figure><p>It is strongly recommended to include an internal DNS server that can serve a fictitious zone. For this purpose, the ‘local’ zone is preferred. To distinguish it from actual DNS names, a postfix ‘local’ can be added to the real DNS name or a name that reflects the server’s functionality.</p><h4>Installation the server part</h4><p>The term server part means wireguard server and statically connected clients. We will install WireGuard on all such machines and generate config for the server.</p><p>Take a look:</p><pre>[Interface]<br>Address = 10.15.20.1/24, 10.15.21.1/24, 10.15.22.1/24, 10.15.23.1/24, 10.15.24.1/24, 10.15.25.1/24, 10.15.26.1/24, 10.15.27.1/24, 10.15.30.1/24, <br>PrivateKey = &lt;PrivateKey&gt;<br>ListenPort = 55000<br>[Peer]<br>PublicKey = &lt;PublicKey&gt;<br>AllowedIPs =  10.15.23.3<br>PersistentKeepalive = 6</pre><p>This is what a server’s configuration looks like. The task is to create an Ansible role that will ultimately generate such a configuration.</p><h4>Requirements for an Ansible role</h4><p>For deploying VPN across hundreds of clients, we should automate this process. Ansible is a suitable tool for this purpose.</p><p>But there is a challenge, ansible can work along servers which can be found in inventory file. In case of using cloud infrastructure such files are created by a cloud provider dynamically. It has lots of information like ip address, name, tags and grouping by tags.</p><p>Our task involves traversing all servers in all projects, installing a VPN application, and configuring it. This is reflected in picture 2 per one project. Each project has a unique token that must be exported before launching a play.</p><p>Since we have an internal DNS, we consider it to be the primary source of information about each server’s address. In other words, we must obtain the IP addresses from DNS (where we imply the IP address of a machine inside the tunnel).</p><p>DNS should be configured beforehand and should resolve names into addresses.</p><p>Then, our role must collect all data, I mean information about all installed VPN applications must be in one configuration file. That is, ansible goes to the client, configures the wireguard there, then goes to the next client (actions 1–4 in the picture 2 shows these steps), after that ansible launches tasks on server side and creates a configuration, consisting of just visited clients and previously configured (action 5).</p><p>The problem with Ansible (and the most challenging part) is that every time a play is launched, Ansible retrieves inventory from a cloud provider, and for each project, the inventory files will be distinct. Consequently, the resultant configuration will lack consistency.</p><p>We would like to develop an Ansible role which will be executed in a playbook and every time the resulting config is consistent.</p><p>Another requirement is when a new Virtual machine is up, this role should be run and add this only one machine to the WireGuard config . This property is known as idempotence.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/768/1*uOeokqEAi59bO7fHGYCvNg.png" /><figcaption>Pic 2. How Ansible configures the network</figcaption></figure><h4>Implementation</h4><p>The full workflow will be the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/924/1*jMuw4pYPrSYyV4Z71tuGGg.png" /><figcaption>Pic 3. Creating WireGuard config by Ansible</figcaption></figure><p>To achieve this goal, we will utilize jinja2 templates. Initially, we will generate a block of server-related information, which is static. In the second step, we will process an intermediary configuration that includes data pertaining to the [Interface] block and the clients that are static. In the third step, Ansible will create a configuration for [Peer] related information for each project. Subsequently, in step four, all of this information about the project’s machines will be stored in a temporary block, separated by specific delimiters. Finally, the resulting file will be produced.</p><p>Why use a temporary file? It seems possible to use Jinja2 conditional blocks or other methods, but each block will be rewritten every time the template is rendered. Another viable solution is to use external storage such as Redis, which is a nice approach. However, I prefer to avoid using additional applications and accomplish the task using other means.</p><h4>Ansible role</h4><p>Eventually, the tasks in the role will be as follows:</p><pre>$ cat tasks/main.yml<br>---<br># tasks file for wg<br>- set_fact:<br>    server_name: &quot;{{ inventory_hostname }}&quot;<br>- name: Install and configure wireguard on peer side<br>  block: <br>   - name: Install Wireguard<br>     apt: <br>       name:<br>        - wireguard<br>        - wireguard-tools<br>    <br>   - name: Generating pair of keys for wg<br>     shell: <br>       cmd: test -f privatekey &amp;&amp; cat privatekey | wg pubkey &gt; publickey || wg genkey | tee privatekey | wg pubkey &gt; publickey <br>       chdir: /etc/wireguard/<br>   - name: Reading client&#39;s privatekey<br>     shell: cat /etc/wireguard/privatekey<br>     register: privatekey_out<br>   - name: Reading client&#39;s publickey<br>     shell: cat /etc/wireguard/publickey<br>     register: publickey_out<br>   - set_fact: <br>       publickey: &quot;{{ publickey_out.stdout }}&quot;<br>       privatekey: &quot;{{ privatekey_out.stdout }}&quot;<br>       addr: &quot;{{ lookup(&#39;dig&#39;, &#39;{{ server_name }}.local&#39;, &#39;@11.11.11.11&#39;, fail_on_error=true) }}&quot;<br>       project: &quot;{{ hcloud_labels[&#39;project&#39;] }}&quot; <br>   - set_fact:<br>       addr_and_mask: &quot;{{ addr }}/{{ SUBNET_MASK }}&quot;<br>       allowed_ips: &quot;{{ addr | ansible.utils.ipsubnet(SUBNET_MASK) }}&quot;<br>   - name: Configure wireguard<br>     template:<br>         src: &quot;{{ item.src }}&quot;<br>         dest: &quot;{{ item.dest }}&quot;<br>         mode: &quot;{{ item.mode }}&quot;<br>         owner: &quot;{{ item.owner }}&quot;<br>     with_items:<br>          - { src: &#39;wg.conf.j2&#39;, dest: &#39;/etc/wireguard/wg0.conf&#39;, mode: &#39;0644&#39;, owner: &#39;root&#39;, group: &#39;root&#39; }   <br>   <br>   - name: Enable WG in systemd<br>     systemd:<br>        name: wg-quick@wg0.service<br>        enabled: true <br>        state: restarted<br>   - name: Add host to group &#39;peers&#39;<br>     add_host:<br>       name: &quot;{{ item }}&quot;<br>       localname: &quot;{{ item }}.local&quot;<br>       groups: peers<br>       publickey: &quot;{{ publickey }}&quot;<br>       addr: &quot;{{ addr }}&quot;<br>     loop: &quot;{{ groups.all }}&quot;<br><br>  when:  server_name  != &#39;wg.example.com&#39; # skip wg server<br><br>- name: Configure WG server<br>  delegate_to: &quot;{{ WG_SERVER }}&quot;<br>  run_once: true<br>  template:<br>      src: &quot;{{ item.src }}&quot;<br>      dest: &quot;{{ item.dest }}&quot;<br>      mode: &quot;{{ item.mode }}&quot;<br>      owner: &quot;{{ item.owner }}&quot;<br>  with_items:<br>       - { src: &#39;wg.server.conf.j2&#39;, dest: &#39;/etc/wireguard/wg0.conf&#39;, mode: &#39;0644&#39;, owner: &#39;root&#39;, group: &#39;root&#39; }   <br><br>- name: Adding peers into the server config file<br>  delegate_to: &quot;{{ WG_SERVER }}&quot;<br>  run_once: true<br>  blockinfile:<br>    marker: &quot;# {mark} project {{ project }}&quot;<br>    path: &quot;/etc/wireguard/wg0.conf.tmp&quot;<br>    create: true<br>    block: |<br>      # Generated by ansible for project {{ project }}<br>      {{ lookup(&#39;template&#39;, &#39;wg.server.project.conf.j2&#39;) }}<br>- name: Assemble server config<br>  delegate_to: &quot;{{ WG_SERVER }}&quot;<br>  run_once: true<br>  shell: cat /etc/wireguard/wg0.conf.tmp &gt;&gt; /etc/wireguard/wg0.conf<br>  notify: <br>    wgserver restart</pre><p>Templates in the role:</p><p>wg.conf.j2:</p><pre>[Interface]<br>Address =  {{ addr_and_mask }}<br>PrivateKey = {{ privatekey }}<br>[Peer]<br>PublicKey = {{ SERVER_PUBLICKEY }}<br>AllowedIPs = {{ allowed_ips }}, {{ ALLOWED_IPS_LIST }}<br>Endpoint = {{ WG_SERVER }}:{{ WG_PORT }}<br>PersistentKeepalive = 6</pre><p>wg.server.conf.j2:</p><pre>[Interface]<br>Address = {{ SERVER_ADDRESSES }}<br>PrivateKey = {{ SERVER_PRIVATE_KEY }}<br>ListenPort = {{ WG_PORT }}<br># Denis                                                     <br>[Peer]                                                      <br>PublicKey = <br>AllowedIPs = 10.15.30.4/32                                  <br>PersistentKeepalive = 6</pre><p>wg.server.project.conf.j2:</p><pre>{% if groups[&#39;peers&#39;] is defined %}<br>{% for peer in groups[&#39;peers&#39;] %} <br># host {{ hostvars[peer][&#39;localname&#39;] }}<br>[Peer]<br>PublicKey = {{ hostvars[peer][&#39;publickey&#39;] }}<br>AllowedIPs =  {{ hostvars[peer][&#39;addr&#39;] }}<br>PersistentKeepalive = 6<br>{% endfor %}<br>{% endif %}</pre><p>Consideration of key factors is crucial.</p><ol><li>After visiting a host, it is added to a special group named ‘peers’, which can be achieved using the ‘add_host’ module found in lines 45–52 of the main.yml file. The servers included in the ‘peers’ group have specific variables that are utilized when rendering templates.</li><li>It is vital that the connecting servers have a specific tag for proper filtering. In this case, the tag used is ‘project=name’.</li><li>The role uses blockinfile module, and Ansible generates a block that is separated by</li></ol><pre>BEGIN project &lt;name&gt;<br>END project &lt;name&gt;</pre><p>Each launch these blocks are updated, it’s important to note. More info at</p><blockquote><a href="https://docs.ansible.com/ansible/latest/collections/ansible/builtin/blockinfile_module.html">https://docs.ansible.com/ansible/latest/collections/ansible/builtin/blockinfile_module.html</a></blockquote><p>Moreover, name of the host is added as well before each peer configuration block.</p><p>4. The Ansible inventory file should have the same name as the DNS name with the postfix ‘local’, as described in the DNS section.</p><p>5. If the tmp file is deleted, the role must be run across all projects again. Or can be restored from the current wireguard config.</p><p>The final task is to create a playbook that will execute the role and configure the VPN. As for the specifics, I will leave it to you. Personally, I prefer to launch the project on a per-project basis and maintain control over the entire process. Also, I am skipping other role files like handlers, variables etc.</p><p>Continue in the part 2.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*0pKPTM0z_R4ssqBM.png" /></figure><h4>👋 If you find this helpful, please click the clap 👏 button below a few times to show your support for the author 👇</h4><h4>🚀<a href="http://from.faun.to/r/8zxxd">Join FAUN Developer Community &amp; Get Similar Stories in your Inbox Each Week</a></h4><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f0ec00275bfe" width="1" height="1" alt=""><hr><p><a href="https://faun.pub/vpn-network-via-wireguard-f0ec00275bfe">VPN Network via WireGuard</a> was originally published in <a href="https://faun.pub/">FAUN.dev() 🐾</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[VPN Network via WireGuard]]></title>
            <link>https://faun.pub/vpn-network-via-wireguard-267cf4651d0c?source=rss-4086cecb427f------2</link>
            <guid isPermaLink="false">https://medium.com/p/267cf4651d0c</guid>
            <dc:creator><![CDATA[Denis Matveev]]></dc:creator>
            <pubDate>Wed, 19 Apr 2023 11:47:35 GMT</pubDate>
            <atom:updated>2023-04-19T11:47:35.355Z</atom:updated>
            <content:encoded><![CDATA[<h4>Building access to cloud network for remote users. Part 2</h4><h4>Installation the client</h4><p>By the client we imply machines which are dynamically connected to WireGuard server (admins’ machines). See picture 1.</p><p>In Ubuntu you can use standard package manager:</p><pre>$ sudo apt install wireguard wireguard-tools</pre><p>Sometimes it requires</p><pre>$ sudo apt install wireguard-dkms package</pre><p>Wireguard has builds available for MacOS, Windows, Android, and iOS. You can find them at the official website: <a href="https://www.wireguard.com/install/">https://www.wireguard.com/install/</a></p><p>Generate keys:</p><pre>$ umask 077; wg genkey | tee privatekey | wg pubkey &gt; publickey</pre><h4>Client Template</h4><p>Here is a configuration template:</p><pre>[Interface]<br>Address = &lt;your ip&gt;<br>PrivateKey = &lt;your private key&gt;<br>[Peer]<br>AllowedIPs = 10.15.20.0/24, 10.15.21.0/24, 10.15.23.0/24, 10.15.24.0/24, 10.15.25.0/24, 10.15.26.0/24, 10.15.27.0/24, 10.15.30.1/32 <br>PublicKey = &lt;server&#39;s public key&gt;<br>Endpoint = &lt;server&#39;s public IP&gt;:55000<br>PersistentKeepalive = 6</pre><h4>Configure</h4><p>Configure wireguard, using template above. Just replace the address and insert your private key.</p><p>Save this template in /etc/wireguard directory with the name</p><pre>wg0.conf</pre><p>Optionally you can add a string</p><pre>DNS=10.15.30.1</pre><p>In the section [Interface]</p><p>Then your system will add inner DNS automatically to /etc/resolv.conf when the tunnel is up.</p><h4>Connect and test</h4><p>To up a tunnel, you can use either utility from wireguard shipment or systemd.</p><h4>Manual start</h4><pre># wg-quick up wg0</pre><p>Then check</p><pre># wg</pre><p>You’ll see something like this:</p><pre>interface: wg0<br>  public key: 1yyR01mYBG5tzXd1K1oosqj2t/kGhbc7kZq3KpmpnFQ=<br>  private key: (hidden)<br>  listening port: 52032<br><br>peer: uJqrur8qGbZiWAOp8pX1KWvJdkgoilMKN9YyUl1U7Hc=<br>  endpoint: 10.11.12.13:55000<br>  allowed ips: 10.15.20.0/24, 10.15.21.0/24, 10.15.23.0/24, 10.15.24.0/24, 10.15.25.0/24, 10.15.26.0/24, 10.15.27.0/24, 10.15.30.1/32<br>  latest handshake: 7 seconds ago<br>  transfer: 1.07 KiB received, 4.48 KiB sent<br>  persistent keepalive: every 6 seconds</pre><p>Then you can ping a router:</p><pre># ping 10.15.30.1</pre><h4>Using systemd</h4><pre># systemctl start wg-quick@wg0</pre><p>To check:</p><pre># systemctl status wg-quick@wg0</pre><p>Then you can ping remote server, for instance, 10.15.30.1</p><p>It works!</p><h4>Using DNS server</h4><p>There is an inner DNS server which serves ‘local’ DNS zone. We use tinydns as a DNS <a href="https://cr.yp.to/djbdns/tinydns.html">https://cr.yp.to/djbdns/tinydns.html</a> This server receives UDP on 53 port.</p><p>It’s important to note that TinyDNS functions solely as a DNS server and does not operate as a caching DNS. Additionally, it does not respond to AXFR requests (in other words does not support zone transfer).</p><p>For AXFR requests we use a special server from the same author. It listens to 53/tcp port. That is DNS uses 53/UDP, for zone transfer it is used 53/TCP. Two different processes.</p><p>I don’t consider implementation in detail, you can use any server you prefer.</p><p>This type of scheme enables the use of DNS as a reference.</p><p>You can ask the server by the request like this:</p><pre>denis@linux:~$ host myserver.cloud.local 10.15.30.1<br>Using domain server:<br>Name: 10.15.30.1<br>Address: 10.15.30.1#53<br>Aliases: <br><br>myserver.cloud.local has address 10.15.20.38</pre><p>To get a valid response, just add a ‘local’ postfix to the real DNS name.</p><p>If you cannot recall either the IP address or the name, you can simply request for all records in the Domain Name System (DNS).</p><pre>$ host -t axfr local  10.15.30.1 | grep postgres<br>postgres1.mycompany.cloud.local. 300      IN   A 10.15.26.4<br>postgres2.mycompany.cloud.local. 300      IN   A 10.15.20.28<br>postgres3.mycompany.cloud.local. 300      IN   A 10.15.20.29</pre><p>You will receive a list of all servers containing the word ‘postgres’ in their name, along with their corresponding IP addresses.</p><p>There is another options for ‘host’ utility to get the same behavior:</p><pre>$ host -al  local  10.15.30.1 | grep postgres</pre><h4>Conclusion</h4><p>Overall, this article explored one method for efficiently configuring multiple servers at once, with a focus on utilizing Ansible effectively. While we discussed important topics such as passing results between plays, roles, and tasks, it’s important to note that this is just one approach among many. However, by incorporating some of the strategies outlined here, you’ll be well on your way towards streamlining your server management process.</p><p>Additionally, while WireGuard is a popular VPN solution, there are other options available such as cloud-based solutions like ZeroTier, Pritunl, and Tailscale that you may want to consider. Ultimately, the key is to find the solution that best suits your needs and helps you achieve your goals.</p><p>Enjoy!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*0pKPTM0z_R4ssqBM.png" /></figure><h4>👋 If you find this helpful, please click the clap 👏 button below a few times to show your support for the author 👇</h4><h4>🚀<a href="http://from.faun.to/r/8zxxd">Join FAUN Developer Community &amp; Get Similar Stories in your Inbox Each Week</a></h4><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=267cf4651d0c" width="1" height="1" alt=""><hr><p><a href="https://faun.pub/vpn-network-via-wireguard-267cf4651d0c">VPN Network via WireGuard</a> was originally published in <a href="https://faun.pub/">FAUN.dev() 🐾</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[10 iptables features you must know]]></title>
            <link>https://faun.pub/10-iptables-features-you-must-know-4839dde46d1?source=rss-4086cecb427f------2</link>
            <guid isPermaLink="false">https://medium.com/p/4839dde46d1</guid>
            <dc:creator><![CDATA[Denis Matveev]]></dc:creator>
            <pubDate>Tue, 21 Mar 2023 16:36:02 GMT</pubDate>
            <atom:updated>2023-03-21T16:36:02.896Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6MoVyuUN5SAz3RhVgh3Jyw.jpeg" /><figcaption>Photo By Lewis Kang’ethe Ngugi, unsplash.com/@ngeshlew</figcaption></figure><p>Any firewall has many functions you probably don’t know about. They are very useful not only for network engineers, but for any IT engineer. Let’s start !</p><h4><strong>1. Filtering</strong></h4><p>This is the main function of the firewall, this is what the firewall was created for. This means you can block some traffic and allow some others to go. Linux main firewall is iptables (or modern replacement notables), which is a part of a netfilter project.</p><p>There are many criteria for creating filtering rules. You can filter by source IP address, destination IP address, source port, destination port, protocol, and so on.</p><p>Here is an example:</p><pre># iptables -A OUTPUT -d 8.8.8.8 --dport 53 -p udp -m udp -j DROP</pre><p>The rule above restricts access to Google’s public DNS.</p><h4><strong>2. Rules order</strong></h4><p>This is the most important feature, because if you are not aware of it, you can make mistakes. Every firewall applies rules from the top to down. Take into account. Here is an example of the wrong order:</p><pre># iptables -A FORWARD -s 192.168.0.0/24 -j DROP<br># iptables -A FORWARD -s 192.168.0.0/16 -j ACCEPT</pre><p>The first rule blocks traffic from the 192.168.0.0/24 network, then the rule below allows traffic from the 192.168.0.0/16 network. As you can see, the 192.168.0.0/16 network is wider than 192.168.0.0/24 and includes the latter. This means that the first rule has no effect. To fix this, you should change the order of the rules, as follows:</p><pre># iptables -A FORWARD -s 192.168.0.0/16 -j ACCEPT<br># iptables -A FORWARD -s 192.168.0.0/24 -j DROP</pre><h4><strong>3. DNAT &amp; SNAT</strong></h4><p>It is another important thing that is done by a firewall. If you are using the internet at home, you probably want your devices to be online. But you have only one “white” ip address. For this purpose, your firewall inside your router will help you. This technology is called source network address translation(<strong>SNAT</strong>). That is all ip packets going out will have the same real ip address and the firewall will remember where reply traffic should go (this is connection tracking).</p><p>Example:</p><pre># iptables -t nat -A POSTROUTING -s 10.0.0.0/16 -j SNAT --to-source xxx.xxx.xxx.xxx</pre><p>where 10.0.0.0/16 is your local network --to-source xxx.xxx.xxx.xxx — specify your external IP address</p><p>In contrast to this, there is <strong>DNAT</strong>, this technology less widely used, it is used for replacing IP address for the destination. If you want to get public access to one of your home devices, become accessible from the internet, but behind the firewall.</p><p>Example:</p><pre># iptables -t nat -A PREROUTING -i wlan0 -d xxx.xxx.xxx.xxx -j DNAT --to-destination 10.0.0.7</pre><p>where -d xxx.xxx.xxx.xxx — external IP address</p><p>10.0.0.7 — is IP of a device you want to make public</p><p>Just take into account, SNAT and DNAT are opposite things and configuration is also opposite:</p><p>POSTROUTING vs PREROUTING,</p><p>--to-source xxx.xxx.xxx.xxx vs --to-destination 10.0.0.7 and so on.</p><h4><strong>4. Shaping and QoS</strong></h4><p>Yes, a firewall like iptables can be used for shaping traffic and <strong>QoS</strong>(Quality of service).</p><p>Here is a simple example to shape bandwidth to 80 port (HTTP traffic):</p><pre># tc qdisc add dev enp0s3 root handle 1: htb (1)<br># tc class add dev enp0s3 parent 1:1 classid 1:10 htb rate 1mbit (2)<br># iptables -t mangle -A POSTROUTING  -p tcp -m tcp --dport 80 -j CLASSIFY --set-class 1:10 (3)</pre><p>In the line 1 we create a root queue discipline, then we create a class with htb backet(line 2). Modify this rule for your needs, like --dport and --sport depends on traffic direction flow.</p><p>Line 3 classifies traffic into class with major 1 and minor 10 numbers. CLASSIFY target may be used only in POSTROUTING chain of mangle table. If you need to classify somewhere else, use MARK (instead of line 3):</p><pre># iptables -t mangle -A OUTPUT  -p tcp -m tcp --dport 80 -j MARK --set-mark 0x01<br># iptables -t mangle -A POSTROUTING -m mark --mark 0x1 -j CLASSIFY --set-class 1:10</pre><p>An example of QoS (prioritizing traffic) how to minimize a latency for ssh traffic(which actually has ToS — Type of Service — set up):</p><pre># iptables -t mangle -A POSTROUTING -p tcp -m tos - tos 0x10 -m tcp - dport 22 -j CLASSIFY - set-class 1:0</pre><h4><strong>5. Brute force protection</strong></h4><p>There is a best practice to limit connecting attempts to ssh or any other service to prevent brute forcing. I am sure you’ve heard about fail2ban python script. The script adds to iptables addresses which were from unsuccessful attempts. There is a more efficient solution!</p><p>In such a case a firewall will help you out! As for iptables, there is a special modules limit, hashlimit and recent.</p><p>Example:</p><pre># iptables -A INPUT -p tcp - dport 22 -i eth0 -m state - state NEW -m recent - set (1)<br># iptables -A INPUT -p tcp - dport 22 -i eth0 -m state - state NEW -m recent - update - seconds 6 - hitcount 4 -j DROP (2)</pre><p>In the example iptables adds source IP address to a set(1), then anyone attempting to guess the ssh password more than 4 times per 6 seconds will be banned(2). It works on updating timestamps, because iptables know nothing about the protocol. Then, access from the banned address will be restored(in this example 6 seconds).</p><h4><strong>6. DROP vs REJECT</strong></h4><p>These actions with packets are similar to each other, but different.</p><p>The difference is that the</p><ul><li>REJECT action is rejecting the packet and sending a reply to a remote site.</li><li>DROP — Just throws away the packet and forgets about this.</li></ul><p>If you are using a firewall just for protecting your home network, it doesn’t matter what action to use. However, for complex networks and complex firewall settings, using DROP might have an unexpected effect.</p><p>Example of DROP action:</p><pre># iptables -A INPUT -p icmp -j DROP</pre><p>and try to ping the machine:</p><pre>denis@linux:~$ ping example.com<br>PING 8.47.17.77 (8.47.17.77) 56(84) bytes of data.<br>--- example.com ping statistics ---<br>40 packets transmitted, 0 received, 100% packet loss, time 39921ms</pre><p>Example of REJECT action:</p><pre># iptables -A INPUT -p icmp -j REJECT</pre><p>and ping the machine:</p><pre>denis@linux:~$ ping example.com<br>PING 8.47.17.77 (8.47.17.77) 56(84) bytes of data.<br>From 8.47.17.77 (8.47.17.77) icmp_seq=1 Destination Port Unreachable<br>From 8.47.17.77 (8.47.17.77) icmp_seq=2 Destination Port Unreachable<br>From 8.47.17.77 (8.47.17.77) icmp_seq=3 Destination Port Unreachable<br>From 8.47.17.77 (8.47.17.77) icmp_seq=4 Destination Port Unreachable<br>From 8.47.17.77 (8.47.17.77) icmp_seq=5 Destination Port Unreachable<br>--- example.com ping statistics ---<br>6 packets transmitted, 0 received, +5 errors, 100% packet loss, time 5000ms</pre><p>I suppose comments are unnecessary.</p><h4><strong>7. Work with large IP addresses sets</strong></h4><p>For efficient work with a large number of addresses and networks, for efficient looking for matching conditions, there is a special module ipset. This module optimized for network addressing.</p><p>Example:</p><pre># ipset -N ddos nethash<br># ipset -A ddos 157.240.0.0/16<br># iptables -I INPUT -m set --match-set ddos src -j DROP</pre><p>Rewrite the ssh brute force preventing rule using ipset:</p><pre># ipset -N ssh nethash timeout 3600<br># iptables -A INPUT -p tcp --dport 22 -i eth0 -m state --state NEW -m recent --set<br># iptables -A INPUT -p tcp --dport 22 -i eth0 -m state --state NEW -m recent --update --seconds 6 --hitcount 4 -j SET --add-set ssh src<br># iptables -A INPUT -m set --match-set ssh src -j DROP</pre><p>Banned IP address can be removed from the set manually:</p><pre># ipset del ssh 116.203.xxx.xxx</pre><p>or wait until the rule expires(1 hour).</p><h4><strong>8. Routing</strong></h4><p>Sometimes it’s necessary to direct traffic in different routes, instead of default.</p><p>So it’s also possible to do this using iptables in conjunction with tc (from iproute2 package) utility.</p><p>Mark entire connection in iptables with CONNMARK target and then let go via specific network interface, defined in created route table:</p><p>Example:</p><pre># iptables -t mangle -A INPUT -i eth0 -p tcp -m tcp –dport 22 -j CONNMARK --set-mark 0x2<br># iptables -t mangle -A OUTPUT -j CONNMARK --restore-mark<br># ip route add default via 172.31.1.1 dev eth0 table 120<br># ip rule add fwmark 0x2/0x2 lookup 120</pre><h4>9. <strong>Forwarding traffic</strong></h4><p>Imagine, your web site has been migrated and you changed DNS A record. It has a TTL and during this time some clients go to new site, others go to old site. Using iptables all clients can be forced to go to the new version of the site instead of the old one.</p><p>Take a look:</p><pre># iptables -t nat -A PREROUTING -p tcp --dport 443 -j DNAT --to-destination xxx.xxx.xxx.xxx:443<br># iptables -t nat -A POSTROUTING -j MASQUERADE</pre><p>With help of iptables you don’t need redir utility.</p><h4>10. <strong>DDoS protection</strong></h4><p>There are many kinds of DDoS, in some cases you can easily use a rule in your iptables, let’s say for syn flood protection and drop packets from an attacker:</p><pre># ipset -N ddos nethash timeout 3600<br># iptables -t mangle -A PREROUTING -p tcp --syn -m hashlimit --hashlimit-above 200/sec --hashlimit-burst 10 --hashlimit-mode srcip --hashlimit-name ddos-banned -j SET --add-set ddos src<br># iptables -A INPUT -m set --match-set ddos src -j DROP</pre><p>So, here the IP addresses that match the criteria will be put into ipset for 3600 secs. Specifying here chain PREROUTING is very important, because it is the first chain in iptables packet flow.</p><p>Of course, DDoS protection is not only limited by this rule, so. You can add other TCP flags (RST, ACK,FIN) to filter criteria or combine them.</p><p>Also, it is possible to protect your server from HTTP flood, just modify the rule from point 7 (using connlimit module) or this rule. It depends on what level of TCP you want to protect.</p><h4><strong>Conclusion</strong></h4><p>Sometimes iptables can be used instead of well known and familiar tools, but more efficiently, iptables implemented in Linux kernel and there is no need to spend lots of resources. iptables is highly efficient in dropping packets on the TCP stage. Such scripts like fail2ban scan logs, analyze and then decide to ban or not.</p><p>This article does not fully cover iptables capabilities, this one is powerful and flexible, I just wanted to show some examples of using iptables in everyday tasks without installing additional utilities. But nowadays iptables considered as legacy and you should migrate to nftables.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*LZv0HimRZJs28-BS.png" /></figure><h4>👋 If you find this helpful, please click the clap 👏 button below a few times to show your support for the author 👇</h4><h4>🚀<a href="http://from.faun.to/r/8zxxd">Join FAUN Developer Community &amp; Get Similar Stories in your Inbox Each Week</a></h4><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4839dde46d1" width="1" height="1" alt=""><hr><p><a href="https://faun.pub/10-iptables-features-you-must-know-4839dde46d1">10 iptables features you must know</a> was originally published in <a href="https://faun.pub/">FAUN.dev() 🐾</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Why you need a connection pooler]]></title>
            <link>https://faun.pub/why-you-need-a-connection-pooler-881bd7b59c2e?source=rss-4086cecb427f------2</link>
            <guid isPermaLink="false">https://medium.com/p/881bd7b59c2e</guid>
            <category><![CDATA[pgbouncer]]></category>
            <category><![CDATA[patroni]]></category>
            <category><![CDATA[highload]]></category>
            <category><![CDATA[postgres]]></category>
            <dc:creator><![CDATA[Denis Matveev]]></dc:creator>
            <pubDate>Mon, 19 Sep 2022 10:36:58 GMT</pubDate>
            <atom:updated>2022-09-19T10:36:58.488Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>An_history()</strong></h3><p>Recently, we faced the situation when many workers written in python should be connected to postgresql and do their work simultaneously.</p><p>We( I mean, me and developers) wanted to connect as many workers as we could. Because our aim was to process data fast. And we were surprised.</p><p>Initially, of course, first thought was to increase the number of workers. But when number of workers had been increased, we were noticed, postgres couldn’t handle all workers and we hit a performance ceiling. This is a bottleneck in postgres.</p><p>We decided to raise the number of connections equal to the number of workers. As we had experience with RDBMS like MySQL, we didn’t even think about any consequences.</p><p>Increasing max_connections parameter led to resources insufficiency, we observed this in our monitoring system. Then we expanded memory and CPU (it was a virtual server). Just for clarifying, max_connections default value is 100, we decided to increase it up to 1000. Yes, we had such idea to process data by 1000 workers in parallel.</p><p>The feature of Postgres is that for handling each connection, postgres allocates a new process; as a result such approach is not for free and requires many resources. Much more than in case of using threads and some syscalls like epoll().</p><p>Many programs use multiprocesses architecture instead of multi-threaded, because it is more reliable and more safe in terms of programming, if you are using global variables, because in threads global variables are shared among threads and can be modified by of them. For protecting variables there are mutexes (mutual exclusion). This concept will be useful for us in the future.</p><p>Against such approach there is a multiple processes way, where all global variables are isolated in each process (every process has its own stack, code segment and data segment) and to exchange necessary data we should use special inter-process communication. We won’t consider processes and threads inner implementations, let’s consider postgres.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/958/1*ifF-iuRHO_8MWLBbFWSP6A.png" /><figcaption>Pic 1. Number of Postgres processes is equal to connected clients</figcaption></figure><p>The same answer we can get using netstat:</p><pre>root@postgres:~# netstat -atnp | awk &#39;/EST/{print $5}&#39; | grep &#39;192.168.12.20&#39; | wc -l</pre><pre>335</pre><p>where 192.168.12.20 — is our load balancer</p><p>After a couple of weeks working in such way, and then analyzing monitoring system, we decreased number of workers and started to look up for another solution. That way with 1000 connections was suspicious for us, it was not normal.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3B7SdXiXoS2BJVCFLpv_xQ.png" /><figcaption>Pic 2. Peak number of processes</figcaption></figure><p>As you can see in the picture 2, there are peaks of 627 processes.</p><h3><strong>Connection_pool()</strong></h3><p>After reading and realizing how postres works with connections, it was decided to implement connection pooling to decrease resource consumption.</p><p>There are three main open sources successfully proved projects for postgresql:</p><ul><li>pgbouncer</li><li>pgpool-II</li><li>odyssey</li></ul><p>They have advantages and disadvantages. And to help choosing the most suitable solution, here is a table below:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/6cd1ba619478695d5d8135ec8ee26e6e/href">https://medium.com/media/6cd1ba619478695d5d8135ec8ee26e6e/href</a></iframe><p>In our opinion, the most suitable solution for us is pgbouncer, but Odyssey by Yandex is also perfect.</p><p>So as we have an HA cluster with three nodes and patroni to manage it, pgbouncer must be installed on all three nodes between a load balancer and a database.</p><h3><strong>Configuring_pgbouncer()</strong></h3><p>We assume, a postgresql cluster is configured and works. If no, please see my articles dedicated to building airflow cluster where HA postgresql clusters are described as well.</p><p>When you have installed pgbouncer</p><pre><em># apt-get install pgbouncer</em></pre><p>the time has come to configure pooler correctly to work behind load balancer (just a reminder, all traffic redirects by load balancer only to one node, health checking is performed via patroni on special port).</p><p>Open configuration file</p><pre><em># vi /etc/pgbouncer/pgbouncer.ini</em></pre><p>and modify the following strings:</p><p>in section [databases]:</p><pre>* = host=/var/run/postgresql</pre><p>in [pgbouncer]:</p><pre>listen_addr = *<br>client_tls_sslmode = require<br>client_tls_key_file = /etc/ssl/private/ssl-cert-snakeoil.key<br>client_tls_cert_file = /etc/ssl/certs/ssl-cert-snakeoil.pem<br>client_tls_protocols = tlsv1.3<br>auth_type = hba<br>auth_file = /etc/pgbouncer/userlist.txt<br>auth_hba_file = /etc/pgbouncer/hba.conf<br>auth_user = admin<br>auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1<br>admin_users = admin<br>max_client_conn = 500</pre><p>An explanation to the configuration above.</p><p>listen_addr = * — we prefer to listen on all interfaces, all communication happens in local network, the rest of interfaces closed from all world in firewall.</p><p>client_tls_sslmode = require<strong> — </strong>I suppose it is pretty clear, require the client to use SSL</p><p>The following three lines tell where to find certificates and a minimum TLS version(1.3 is the most modern now).</p><p>Certificates can be generated or you can do the same as I did:</p><p>just specify the same as specified in postgresql.base.conf</p><pre>client_tls_key_file = /etc/ssl/private/ssl-cert-snakeoil.key<br>client_tls_cert_file = /etc/ssl/certs/ssl-cert-snakeoil.pem<br>client_tls_protocols = tlsv1.3</pre><p>And now the most interesting:</p><pre>auth_type = hba<br>auth_file = /etc/pgbouncer/userlist.txt<br>auth_hba_file = /etc/pgbouncer/hba.conf<br>auth_user = admin<br>auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1<br>admin_users = admin</pre><p>In the config we specify to use auth_type as hba, then we specify users allowed to connect and query to fetch users data from database.</p><p>This is very important. We have HA cluster and only one node is working as read/write node, all the rest are just followers and replicates data. Users and their passwords are replicated too, then we have user ‘admin’ which was initially created by patroni. Just to avoid users synchronization, we decided to use internal database users. Admin is the user that used to connect by pgbouncer to check other users in postgresql.</p><p>Admin has its password hashed in md5 in/etc/pgbouncer/userlist.txt</p><p>A format of the file as follows:</p><pre>&quot;user&quot; &quot;md5hash(password+username)&quot;</pre><p>because we have chosen md5 auth</p><p>To create a password line use the following command:</p><pre># echo -n BigAv-Ond7admin | md5sum -</pre><p>We assume, user is admin and password is BigAv-Ond7</p><p>then we make a md5 hash of concatenated string and receive necessary hash 5dc9ba49b794658ea9b9561823d47c13 which will be used in userlist.txt file.</p><p>Entire string in our case will be:</p><pre>&quot;admin&quot; &quot;md55dc9ba49b794658ea9b9561823d47c13&quot;</pre><p>The last line</p><p>max_client_conn = 500 — tells how many connections to handle(it means connections from client to pgbouncer).</p><p>Then I would recommend to increase limit of open files:</p><pre><em># vi /etc/security/limits.conf<br></em>postgres        soft     nofile         30000</pre><p>This is useful, because every connection implies opening a socket and allocates a file descriptor.</p><p>So, let’s create the hba file for users and their rights on databases</p><pre><em># vi /etc/pgbouncer/hba.conf<br></em>local   all             all                                     md5<br>host    all             all             192.168.12.0/24         md5<br>host    all             all             127.0.0.1/32            md5</pre><p>Contents of the file should be clear, it allows to connect locally for all users to all databases, network connection for all users from localhost and local networks. This file has the same syntax as postgresql pg_hba.conf file. But not all key words are supported, please refer to the official documentation in case you want to use a different configuration than mine. Moreover, pay attention, now two files work: hba.conf of pgbouncer and then ‘native’ pg_hba.conf of postgres.</p><p>Take a look at pooling mode parameter, we left default value:</p><pre>pool_mode = session</pre><p>This is the most polite method, you can choose whatever you need.</p><p>Distribute these files among all cluster nodes, it can be done by Ansible.</p><h3><strong>All_right_lets_start()</strong></h3><p>It’s very easy to launch the pgbouncer:</p><pre><em># systemctl start pgbouncer</em></pre><p>First of all, we should check if we able to connect by user admin locally:</p><pre># psql -U admin -W -p 6432 -d postgres -h 127.0.0.1</pre><blockquote><strong>Note. Bear in mind, port to connect is 6432! Moreover, we connect to database ‘postgres’</strong></blockquote><p>if you successfully connected, you are able to see the databases:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/a76bbafc2a34a63ab6e7bd7894707916/href">https://medium.com/media/a76bbafc2a34a63ab6e7bd7894707916/href</a></iframe><p>Nice, launch pgbouncer, and check connection on all nodes.</p><p>Then, do it again, but to any other IP address specified in your hba file.</p><p>If you succeeded in this, it’s time to switch to using pgbouncer.</p><p>In our case, we are modifying destination port in load balancer configuration and then all queries (all traffic, actually) will go to a new port on the current leader.</p><p>The next steps are to load the servers and test your new configuration. You should see a significant reduction in the number of running processes in the output of ‘top’ like utilities.</p><p>Thanks for reading!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*FqmT4fc4qdwk2If7.png" /></figure><p><strong>If this post was helpful, please click the clap 👏 button below a few times to show your support for the author 👇</strong></p><h4>🚀<a href="http://from.faun.to/r/8zxxd">Read similar stories by joining FAUN</a>.</h4><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=881bd7b59c2e" width="1" height="1" alt=""><hr><p><a href="https://faun.pub/why-you-need-a-connection-pooler-881bd7b59c2e">Why you need a connection pooler</a> was originally published in <a href="https://faun.pub/">FAUN.dev() 🐾</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Airflow High Available cluster]]></title>
            <link>https://faun.pub/airflow-high-available-cluster-2b12460896f1?source=rss-4086cecb427f------2</link>
            <guid isPermaLink="false">https://medium.com/p/2b12460896f1</guid>
            <category><![CDATA[clustering]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[airflow]]></category>
            <category><![CDATA[automation]]></category>
            <category><![CDATA[postgres]]></category>
            <dc:creator><![CDATA[Denis Matveev]]></dc:creator>
            <pubDate>Thu, 14 Apr 2022 20:59:20 GMT</pubDate>
            <atom:updated>2022-04-14T20:59:20.127Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Part 4. Testing</strong></p><p>Previous parts: <a href="https://faun.pub/airflow-high-available-cluster-e1a4c0050607">part 1</a>, <a href="https://faun.pub/airflow-high-available-cluster-2f2d792ce900">part 2</a>, <a href="https://faun.pub/airflow-high-available-cluster-fd701f70cd67">part 3</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/1*u75yVU5Pgi0OhX7RZ0kYQA.png" /></figure><p>In this article we are going to check again our installation and test the solution to see if it works properly. We’ll consider different scenarios.<br>A reminder, the cluster has three nodes, each node has PostgreSQL with patroni, etcd, RabbitMQ, Airflow scheduler, Airflow workers, Airflow web-server on board. In case one node is down, tasks should be moved to the remaining two nodes. Also, PostgreSQL will decide to switch over the followers or exclude the failed node.<br>Let’s go!<br>Let’s check again if our components are working. Take a look at RabbitMQ:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/4ae87542914fe2a3f6398eb38e3ce0f8/href">https://medium.com/media/4ae87542914fe2a3f6398eb38e3ce0f8/href</a></iframe><p>As we can see there are no errors in the RabbitMQ cluster work.</p><p>Then, let’s see at patroni:</p><pre>root@node1:/etc/zabbix# patronictl -c /etc/patroni/patroni.yml list</pre><pre>+ Cluster: postgres-cluster (6956178562555884899) +-----------+<br>| Member      | Host     | Role    | State   | TL | Lag in MB |<br>+-------------+----------+---------+---------+----+-----------+<br>| node1      | 10.0.0.6 | Leader  | running | 31  |           |<br>| node2      | 10.0.0.5 | Replica | running | 31  |         0 |<br>| node3      | 10.0.0.7 | Replica | running | 31  |         0 |<br>+-------------+----------+---------+---------+----+-----------+</pre><p>I recommend creating an Ansible inventory file to perform actions in one ad-hoc command.</p><p>With the ansible playbook we save time and reduce manual operations. To manage systemd units, just type one command:</p><pre># ansible all -i inv -m shell -a “systemctl is-active airflow-worker airflow-webserver airflow-scheduler”</pre><p>And we’ll see the status of all airflow processes on all nodes.</p><p>Like this:</p><pre># ansible all -i inv -m shell -a &quot;systemctl is-active airflow-worker airflow-webserver airflow-scheduler&quot; -u denis -K -b</pre><pre>BECOME password:<br>&lt;IP node 1&gt; | CHANGED | rc=0 &gt;&gt;<br>active<br>active<br>active<br>&lt;IP node 2&gt; | CHANGED | rc=0 &gt;&gt;<br>active<br>active<br>active<br>&lt;IP node 3&gt; | CHANGED | rc=0 &gt;&gt;<br>active<br>active<br>active</pre><p><strong>Note.</strong> This is an important RabbitMQ cluster-related note. Don’t forget to add an option to RabbitMQ server config to handle network partitioning</p><pre># vi /etc/rabbitmq/rabbitmq.conf:</pre><pre>cluster_partition_handling = autoheal</pre><p>Now we should add at least one DAG. I have been testing Airflow for hours using two DAGS:</p><ol><li>One short tasks DAG</li><li>One long task DAG</li></ol><p>So, here is my DAG number #1 that I used:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/6d96ab15b04854cc2efb93525fa2a9c5/href">https://medium.com/media/6d96ab15b04854cc2efb93525fa2a9c5/href</a></iframe><p>And the second DAG, which was used for launching a long-time task:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/bf347873852602cda7193f0248c0a252/href">https://medium.com/media/bf347873852602cda7193f0248c0a252/href</a></iframe><p>The first one has two parallel tasks: 2 and 3; the second one has sequential tasks only.<br>As you can see DAG #1 starts every 5 minutes, DAG #2 starts every 30 minutes.</p><p>I propose to consider the following scenarios:<br>1. One node is down at the moment between tasks start and then going up at an arbitrary point in time:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/641/1*BGYFuGa0z0kqRQsM___Acw.png" /><figcaption>Pic 1. Launching short tasks</figcaption></figure><p>On picture 1 shown tasks and the moment of failing (red arrow).</p><p>2. While a long task is running, the node with the corresponding worker, fails. Then it gets back online. This is demonstrated on picture 2 (the red arrow is the moment of failure):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/641/1*viq-G2nM4eKbP4HBOsV8Xw.png" /><figcaption>Pic 2. Launching long tasks</figcaption></figure><p>Any worker status can be obtained by a query to PostgreSQL. Since we have Patroni installed, we can connect and perform a query on any node.<br>Let’s connect and check the initial status of workers and schedulers:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/4d1ac1809d3f317ef0f4d9243cdc35ea/href">https://medium.com/media/4d1ac1809d3f317ef0f4d9243cdc35ea/href</a></iframe><p><strong>Scenario 1</strong></p><p>So let’s start with point 1, if one node is failing when no running job and then going up.<br>In the meanwhile, patroni, RabbitMQ (clustered) should realize that one node is unavailable and should exclude the node from the their clusters.<br>I’m gonna start with the short DAG and then disable the network to simulate inaccessibility of the node.<br>Let’s have a look at the database:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/cff326c0364fe6169b44706702707a01/href">https://medium.com/media/cff326c0364fe6169b44706702707a01/href</a></iframe><p>A new running job appeared. It’s absolutely OK. Let’s wait until the end of all tasks and look at where these tasks were running. Perform the following query:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/0002c68b2f02f71efdfd569ba12ff45b/href">https://medium.com/media/0002c68b2f02f71efdfd569ba12ff45b/href</a></iframe><p>We see which nodes were used to run the jobs. Also, let’s grep logs:</p><p>We’ll find a log entry on node2:</p><pre>Feb 25 13:55:19 node2 Airflow: Hello World from Task 3</pre><p>then on node1:</p><pre>Feb 25 13:55:03 node1 Airflow: Hello World from Task 1<br>Feb 25 13:55:37 node1 Airflow: Hello World from Task 4</pre><p>and the last:</p><pre>Feb 25 13:55:11 node3 Airflow: Hello World from Task 2</pre><p>Task 1 and 4 were launched on node 1, task 3 was running on node 2, task 2 was working on node 3. If we compare start times, we’ll understand everything is correct. This is the way they work correctly.<br>So now, let’s try to down one node and then perform queries to investigate what happened.<br>We’ll stop a node 2 at the moment between jobs start (see pic 1).<br>On any working node type:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/60334805ea3be0709568828d4f530b4c/href">https://medium.com/media/60334805ea3be0709568828d4f530b4c/href</a></iframe><p>As you can see patroni and RabbitMQ have excluded failed node.</p><p>Then observe the database:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b0d9ab41ce2e0b3f1b7b5d85f7807109/href">https://medium.com/media/b0d9ab41ce2e0b3f1b7b5d85f7807109/href</a></iframe><p>That’s nice result! All our tasks continue working even though the service has degraded.<br>Then we up the node again and check if patroni, PostgreSQL replication, and the RabbitMQ cluster work again. I’ll skip checking cluster members status when the failed node becomes available again, you already know those steps.<br>I should say, there is one more approach to check jobs statuses in a web GUI, but I prefer cli.<br>For the right PostgreSQL replication working, you should set up storing WALs for the necessary time. It’s a question that is out of the article.</p><p><strong>Scenario 2</strong></p><p>This scenario is about long tasks and at the moment the task is being run, the node is going down.</p><p>To simulate a node failure, I ran long-time job and switched off the node. Let’s start our long-term tasks and observe what happens.</p><p>After launching the jobs we see the following:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/e52e288e45bef297621b3cb82ee8ed7c/href">https://medium.com/media/e52e288e45bef297621b3cb82ee8ed7c/href</a></iframe><p>Three schedulers and one task are working at the moment so we have tasks only sequentially dependent.<br>In the GUI we see the same, since the airflow cluster has three nodes, we can watch on any of them.<br>After finishing one task, let’s perform the query again:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/0269018354679baab0cab584a0081a13/href">https://medium.com/media/0269018354679baab0cab584a0081a13/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GzwIMYWXXq4aiSqiCznJMA.png" /></figure><p>When one node is down, there is one task failed:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BaFrnFMhWdT4LsPoQVgomg.png" /></figure><p>You can ask for failed task details in the GUI.</p><p>Despite the fact the task failed, the additional info can be found in the table ‘task_fail’, because the ‘job’ table has ‘success’ state (kinda like the task has finished):</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/53c9bde136d3894f5c5a69c3734ba0ef/href">https://medium.com/media/53c9bde136d3894f5c5a69c3734ba0ef/href</a></iframe><p>Since one node is unavailable for launching distributed tasks we have the following picture:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c7064049cfb42d5bb80f0c4c0f8db0a2/href">https://medium.com/media/c7064049cfb42d5bb80f0c4c0f8db0a2/href</a></iframe><p>This means we have two healthy airflow nodes where schedulers are waiting for the moment to start a job.</p><p>Patroni and RabbitMQ behave the same as in scenario 1; there is no non-working node in the nodes list of the cluster (they successfully excluded the broken member).<br>It says that replication in PostgreSQL and RabbitMQ works correctly.<br>Let’s wait for the next period to start a new bunch of tasks and make sure all tasks will work.<br>As for failed tasks, you can restart it via GUI, it’s possible to change state (clear the state) and this task will keep on working but on an available node:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/5810d3294d78b011fe7404dd388e9cb3/href">https://medium.com/media/5810d3294d78b011fe7404dd388e9cb3/href</a></iframe><p>In the query above there are only two nodes and two working jobs: one started by schedule and one was re-queued. Thus we can get rid of failed tasks.</p><p>All fine.</p><p><strong>Conclusion</strong>.</p><p>We assembled the Airflow cluster which can work in an uninterruptible manner, in case one node fails, the cluster will keep on working. But if node is gone away at the moment job is being run, you should re-queue the task or wait for the next launching time.<br>Any component works independently and needs a monitoring solution for reacting if something goes wrong. Even though the cluster is an HA solution. I mean, getting different metrics from PostgreSQL VPN between nodes, Airflow processes, RabbitMQ, having important triggers is absolutely a good idea.</p><p>In my opinion, you don’t need to monitor Patroni, because all essential metrics can be fetched from PostgreSQL. It is worth only tracking the number of patroni and etcd processes on each node.</p><p><strong>Notes.</strong></p><p>Instead of using RabbitMQ, you can consider using Redis Sentinel cluster where there are a Leader and Followers. Airflow has to work absolutely identically as with a RabbitMQ cluster.<br>Moreover, I found a project KeyDB which has a multi-master cluster, but this solution has not been tested by me and most likely it doesn’t work properly: Here is a bug report <a href="https://github.com/EQ-Alpha/KeyDB/issues/258">https://github.com/EQ-Alpha/KeyDB/issues/258</a></p><p>Thanks for reading!</p><figure><a href="https://faun.to/bP1m5"><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BCiLLad3dvZLwBa-B5cAVQ.png" /></a></figure><p>Join FAUN: <a href="https://faun.to/i9Pt9"><strong>Website</strong></a><strong> </strong>💻<strong>|</strong><a href="https://faun.dev/podcast"><strong>Podcast</strong></a><strong> </strong>🎙️<strong>|</strong><a href="https://twitter.com/joinfaun"><strong>Twitter</strong></a><strong> </strong>🐦<strong>|</strong><a href="https://www.facebook.com/faun.dev/"><strong>Facebook</strong></a><strong> </strong>👥<strong>|</strong><a href="https://instagram.com/fauncommunity/"><strong>Instagram</strong></a><strong> </strong>📷|<a href="https://www.facebook.com/groups/364904580892967/"><strong>Facebook Group</strong></a><strong> </strong>🗣️<strong>|</strong><a href="https://www.linkedin.com/company/faundev"><strong>Linkedin Group</strong></a><strong> </strong>💬<strong>|</strong> <a href="https://faun.dev/chat"><strong>Slack</strong></a> 📱<strong>|</strong><a href="https://thechief.io/"><strong>Cloud Native</strong> <strong>News</strong></a><strong> </strong>📰<strong>|</strong><a href="https://linktr.ee/faun.dev/"><strong>More</strong></a><strong>.</strong></p><p><strong>If this post was helpful, please click the clap 👏 button below a few times to show your support for the author 👇</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2b12460896f1" width="1" height="1" alt=""><hr><p><a href="https://faun.pub/airflow-high-available-cluster-2b12460896f1">Airflow High Available cluster</a> was originally published in <a href="https://faun.pub/">FAUN.dev() 🐾</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Who removes my files?]]></title>
            <link>https://faun.pub/who-removes-my-files-dda085551798?source=rss-4086cecb427f------2</link>
            <guid isPermaLink="false">https://medium.com/p/dda085551798</guid>
            <category><![CDATA[investigation]]></category>
            <category><![CDATA[linux]]></category>
            <category><![CDATA[syscalls]]></category>
            <category><![CDATA[auditd]]></category>
            <category><![CDATA[incident]]></category>
            <dc:creator><![CDATA[Denis Matveev]]></dc:creator>
            <pubDate>Tue, 15 Feb 2022 19:13:38 GMT</pubDate>
            <atom:updated>2022-02-15T19:30:29.536Z</atom:updated>
            <content:encoded><![CDATA[<h3>Introduction to events audit system in Linux</h3><p>Sometimes you come across a situation when it is not clear where some files on a server disappeared to.</p><p>I had the same when my teammate told me he created files, but they vanished. It looks weird and sometimes it’s unclear what happens and there are no logs to understand. It’s very often situation.</p><p>Interesting that fact, he created files again and they disappeared again. Every day … It’s a mystery. Of course, there were no logs about such events because usually user actions are not logged, except commands history, but you can disable this one by issuing:</p><pre>$ unset HISTFILE</pre><p>After a bit of investigation, it has not been got clear. It might be any processes working on the server. After googling for a while, I realized there is a kernel system to track syscalls. That is, removing files is an operation in the Linux kernel, unlink() syscall. What is syscall? Syscall(or system call) is a kernel function that can be called from the userspace.</p><h3><strong>An application to observe user activity</strong></h3><p>The system that allows tracking system calls is the kernel audit system and it is already in the Linux kernel! It is only necessary to activate this by creating essential rules.</p><p>To access the system, you should install a package and then configure that one(write your own rules) to collect special logs. Then investigation of your incident gets more manageable if you have some weird behavior of the OS.</p><p>On RedHat-like systems, you should install the package called</p><pre>audit</pre><p>On Debian OSes, you should install the package:</p><pre>auditd</pre><p>The difference is only in ad letter in the package name.</p><p>This system was introduced in kernel 2.6(many years ago!)</p><p>Let’s consider this system closer(I will use Debian or Ubuntu in the examples below).</p><h3><strong>Rules</strong></h3><p>To track necessary events we need to create rules for auditd. Custom rules located in /etc/audit/rules.d/audit.rules:</p><pre>## First rule — delete all</pre><pre>-D</pre><pre>## Increase the buffers to survive stress events.</pre><pre>## Make this bigger for busy systems</pre><pre>-b 8192</pre><pre>## Set failure mode to syslog</pre><pre>-f 1</pre><pre>## Custom rules</pre><pre>-w /home/denis -p aw -k denis<br>-w /var/spool/cron/ -p aw -k cron<br>-w /etc/ -p wa -k etc<br>-a exit,always -S unlink -S unlinkat -k rm<br>-a exit,always -S rmdir -k rmdir<br>-a exit,always -S kill -F a1=9 -k kill<br>-a exit,always -S kill -F a1=15 -k term</pre><p>The rules above are intended to track any file changes located in</p><pre>/home/denis<br>/var/spool/cron/<br>/etc</pre><p>and the rules also logging all events related to some system calls: unlink, rmdir, kill …</p><p>So, here are explanations of some rules syntax:</p><p><strong>Rules about files</strong></p><p>-w means to observe a specific file</p><p>-p aw means changes of permissions to be logged for the specified file above(attributes and writes to the file)</p><p><strong>Syscalls related rules</strong></p><p>-a exit,always means adding a rule to a kernel rules list, don’t worry about exit,always just specify them</p><p>-S a syscall name to be triggered the rule</p><p>-F a1..a4 up to 4 syscall arguments(9 is KILL, 15 is TERM signal)</p><p>-k is a unique key of the rule, which will be used for filtering output</p><p>To activate the rules, run the command:</p><pre># service auditd restart</pre><p>To show active rules, you need to type:</p><pre># auditctl -l</pre><h3><strong>Investigation</strong></h3><p><strong>Example 1</strong></p><p>Show statistics distributed by key</p><pre># aureport --key --summary</pre><p>or limited by date:</p><pre># aureport --key --summary --start this-week</pre><p>You’ll receive something like</p><pre>Key Summary Report<br>===========================<br>total  key<br>===========================<br>108424  rm<br>3642  denis<br>118  kill<br>68  rmdir<br>64  term<br>7  etc</pre><p><strong>Example 2</strong></p><p>Find out all files were deleted</p><pre># ausearch -k rm --raw | aureport -i -f</pre><p><strong>Example 3</strong></p><p>Find out exec files, which used unlink, unlinkat system calls</p><pre># ausearch -k rm --raw | aureport -i -x --summary</pre><p>(you can replace the key after -k option with the key from the rule above and remove the-x option which tells of finding only executables)</p><p><strong>Example 4</strong></p><p>Find out files were removed in home directory(it is not taking into account files with relative paths)</p><pre># ausearch -k rm -f /home/</pre><p>But if some file has no full path, there is an approach to discover this one:</p><pre># ausearch -k rm -f testfile</pre><pre>----</pre><pre>time-&gt;Thu Jan 27 18:39:57 2022</pre><pre>type=PROCTITLE msg=audit(1643308797.477:198184116): proctitle=726D002D69007465737466696C65</pre><pre>type=PATH msg=audit(1643308797.477:198184116): item=1 name=&quot;testfile&quot; inode=794599 dev=fd:00 mode=0100644 ouid=0 ogid=0 rdev=00:00 nametype=DELETE</pre><pre>type=PATH msg=audit(1643308797.477:198184116): item=0 name=&quot;/root&quot; inode=786434 dev=fd:00 mode=040550 ouid=0 ogid=0 rdev=00:00 nametype=PARENT</pre><pre>type=CWD msg=audit(1643308797.477:198184116):  cwd=&quot;/root&quot;</pre><pre>type=SYSCALL msg=audit(1643308797.477:198184116): arch=c000003e syscall=263 success=yes exit=0 a0=ffffffffffffff9c a1=22b80c0 a2=0 a3=7ffc8cac5a70 items=2 ppid=159112 pid=193259 auid=1000 uid=0 gid=0 euid=0 suid=0 fsuid=0 egid=0 sgid=0 fsgid=0 tty=pts0 ses=490412 comm=&quot;rm&quot; exe=&quot;/usr/bin/rm&quot; key=&quot;rm&quot;</pre><p><strong>Example 5</strong></p><p>Find out all actions of the user denis</p><pre># id denis<br>uid=1002(denis) gid=1002(denis) groups=1002(denis)<br># ausearch -ui 1002</pre><p><strong>Example 6</strong></p><p>Print out a list of all last logins:</p><pre># aulast</pre><p>You’ll see something like this:</p><pre># aulast<br>denis pts/0 &lt;server&gt; Fri Apr 10 16:45–16:45 (00:00)<br>git ssh &lt;server&gt; Fri Apr 10 18:22–18:31 (00:08)<br>git ssh &lt;server&gt; Fri Apr 10 22:34–22:45 (00:10)<br>git ssh &lt;server&gt; Sat Apr 11 00:29–00:41 (00:12)<br>git ssh &lt;server&gt; Sat Apr 11 02:36–02:48 (00:12)<br>git ssh &lt;server&gt; Sat Apr 11 06:21–06:31 (00:09)<br>git ssh &lt;server&gt; Sat Apr 11 10:23–10:33 (00:09)<br>git ssh &lt;server&gt; Sat Apr 11 14:23–14:33 (00:09)<br>denis pts/0 &lt;server&gt; Sat Apr 11 08:10–16:12 (08:02)<br>denis pts/1 &lt;server&gt; Sat Apr 11 08:14–16:12 (07:58)</pre><p><strong>Example 7</strong></p><p>Find out all events connected with /etc/hosts file</p><pre># ausearch -f /etc/hosts</pre><p><strong>Example 8</strong></p><p>Who sent SIGKILL signal</p><pre># ausearch -k kill --raw | aureport -i -x --summary</pre><p><strong>Example 9</strong></p><p>Who sent SIGTERM</p><pre># ausearch -k term --raw | aureport -i -x --summary</pre><p>The commands above in examples 8 and 9 allow getting statistics, then you can search for advanced information within the raw file(see example 10).</p><p><strong>Example 10</strong></p><p>Let’s consider the output of the command from example number 9:</p><pre># ausearch -k term --raw | aureport -i -x --summary<br>Executable Summary Report<br>=================================<br>total  file<br>=================================<br>51  /usr/sbin/sshd<br>3  /usr/bin/dbus-launch<br>2  /usr/bin/systemctl</pre><p>Then we wonder which arguments were passed to the systemctl executable:</p><pre># ausearch -k term -x /usr/bin/systemctl -i</pre><p>And you will receive a response:</p><pre>----</pre><pre>type=PROCTITLE msg=audit(06/03/2021 18:39:02.743:60440165) : proctitle=systemctl restart postgresql-12.service</pre><pre>type=OBJ_PID msg=audit(06/03/2021 18:39:02.743:60440165) : opid=252374 oauid=denis ouid=root oses=149728 ocomm=pkttyagent</pre><pre>type=SYSCALL msg=audit(06/03/2021 18:39:02.743:60440165) : arch=x86_64 syscall=kill success=yes exit=0 a0=0x3d9d6 a1=SIGTERM a2=0x7ffc998e7d70 a3=0x4 items=0 ppid=34546 pid=252372 auid=denis uid=root gid=root euid=root suid=root fsuid=root egid=root sgid=root fsgid=root tty=pts1 ses=149728 comm=systemctl exe=/usr/bin/systemctl key=term</pre><p>As we can see the command was:</p><pre># systemctl restart postgresql-12.service</pre><p>and the user who launched was denis. The command sent the signal a1=SIGTERM</p><p><strong>Example 11</strong></p><p>Find out who deleted a file:</p><pre># ausearch -f testfile -i -x rm</pre><p>You&#39;ll see the following:</p><pre># ausearch -f testfile -x rm -i</pre><pre>----</pre><pre>type=PROCTITLE msg=audit(01/27/2022 17:04:24.921:198161360) : proctitle=rm testfile</pre><pre>type=PATH msg=audit(01/27/2022 17:04:24.921:198161360) : item=1 name=testfile inode=3952042 dev=fd:05 mode=file,664 ouid=denis ogid=denis rdev=00:00 nametype=DELETE</pre><pre>type=PATH msg=audit(01/27/2022 17:04:24.921:198161360) : item=0 name=/home/denis inode=3932196 dev=fd:05 mode=dir,700 ouid=denis ogid=denis rdev=00:00 nametype=PARENT</pre><pre>type=CWD msg=audit(01/27/2022 17:04:24.921:198161360) :  cwd=/home/denis</pre><pre>type=SYSCALL msg=audit(01/27/2022 17:04:24.921:198161360) : arch=x86_64 syscall=unlinkat success=yes exit=0 a0=0xffffffffffffff9c a1=0x22020c0 a2=0x0 a3=0x7ffc483a3fe0 items=2 ppid=426795 pid=426919 auid=denis uid=denis gid=denis euid=denis suid=denis fsuid=denis egid=denis sgid=denis fsgid=denis tty=pts0 ses=490176 comm=rm exe=/usr/bin/rm key=denis</pre><p>It means you are searching for executable rm applied for the file testfile. As all unlink and unlinkat syscalls are registered, you can find necessary information. Bear in mind, you should specify a filename exactly(relative or full path). Also, here is another example with grep:</p><pre># ausearch -x rm | aureport -f | grep test<br>1568. 01/27/2022 17:04:24 testfile 263 yes /usr/bin/rm 1000 198161360</pre><p>That approach allows finding all unlink or unlinkat syscalls connected with a file containing the word test(only a part of a name) and then you can perform an advanced request.</p><p>Moreover, you can search a word over logfile:</p><pre># grep testfile /var/log/audit/audit.log</pre><pre>type=PATH msg=audit(1643303057.621:198161352): item=1 name=&quot;testfile&quot; inode=3952042 dev=fd:05 mode=0100664 ouid=1002 ogid=1002 rdev=00:00 nametype=CREATE</pre><pre>type=PATH msg=audit(1643303064.921:198161360): item=1 name=&quot;testfile&quot; inode=3952042 dev=fd:05 mode=0100664 ouid=1002 ogid=1002 rdev=00:00 nametype=DELETE</pre><p>But the preferred way is to use special commands.</p><p>The audit system registers syscalls in numeric format. Here is an example of how to convert to system call name:</p><pre># ausyscall 87</pre><pre>unlink</pre><h3>Conclusion</h3><p>Auditd is an irreplaceable tool in investigating incidents and in the system administration field. This gives understanding the weirdest operating system behavior allows exclude misconfiguration. And, of course, this is a powerful system for security incidents, especially if you ship your logs to remote storage.</p><p>The examples below are useful and they are workable short hints.</p><p>Finally, the investigation I told in the very beginning was completed successfully. We managed to find out why user files were wiped. This was the wrong cron task.</p><p><strong>Sources:</strong></p><ol><li>man auditd</li><li>man aureport</li><li>man ausearch</li><li><a href="http://magazine.hackinthebox.org/issues/HITB-Ezine-Issue-005.pdf">http://magazine.hackinthebox.org/issues/HITB-Ezine-Issue-005.pdf</a></li><li><a href="https://selectel.ru/blog/audit-sistemnyx-sobytij-v-linux/">https://selectel.ru/blog/audit-sistemnyx-sobytij-v-linux/</a> (Rus)</li></ol><figure><a href="https://faun.to/bP1m5"><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BCiLLad3dvZLwBa-B5cAVQ.png" /></a></figure><p>Join FAUN: <a href="https://faun.to/i9Pt9"><strong>Website</strong></a><strong> </strong>💻<strong>|</strong><a href="https://faun.dev/podcast"><strong>Podcast</strong></a><strong> </strong>🎙️<strong>|</strong><a href="https://twitter.com/joinfaun"><strong>Twitter</strong></a><strong> </strong>🐦<strong>|</strong><a href="https://www.facebook.com/faun.dev/"><strong>Facebook</strong></a><strong> </strong>👥<strong>|</strong><a href="https://instagram.com/fauncommunity/"><strong>Instagram</strong></a><strong> </strong>📷|<a href="https://www.facebook.com/groups/364904580892967/"><strong>Facebook Group</strong></a><strong> </strong>🗣️<strong>|</strong><a href="https://www.linkedin.com/company/faundev"><strong>Linkedin Group</strong></a><strong> </strong>💬<strong>|</strong> <a href="https://faun.dev/chat"><strong>Slack</strong></a> 📱<strong>|</strong><a href="https://thechief.io/"><strong>Cloud Native</strong> <strong>News</strong></a><strong> </strong>📰<strong>|</strong><a href="https://linktr.ee/faun.dev/"><strong>More</strong></a><strong>.</strong></p><p><strong>If this post was helpful, please click the clap 👏 button below a few times to show your support for the author 👇</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=dda085551798" width="1" height="1" alt=""><hr><p><a href="https://faun.pub/who-removes-my-files-dda085551798">Who removes my files?</a> was originally published in <a href="https://faun.pub/">FAUN.dev() 🐾</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Airflow High Available cluster]]></title>
            <link>https://faun.pub/airflow-high-available-cluster-fd701f70cd67?source=rss-4086cecb427f------2</link>
            <guid isPermaLink="false">https://medium.com/p/fd701f70cd67</guid>
            <category><![CDATA[automation]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[clustering]]></category>
            <category><![CDATA[airflow]]></category>
            <category><![CDATA[postgres]]></category>
            <dc:creator><![CDATA[Denis Matveev]]></dc:creator>
            <pubDate>Tue, 19 Oct 2021 21:24:52 GMT</pubDate>
            <atom:updated>2022-03-22T15:11:10.578Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Part 3. Continue working on building the HA cluster</strong></p><p>Previous parts: <a href="https://faun.pub/airflow-high-available-cluster-e1a4c0050607">part 1</a>, <a href="https://faun.pub/airflow-high-available-cluster-2f2d792ce900">part 2</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/973/1*rvfjkSOf2ib-17iSJs2aiw.png" /><figcaption>Airflow Logo</figcaption></figure><p>Well, let’s continue building the HA cluster.</p><p>The current chapter of the tutorial is installing and configuring application for DAGs synchronization and Airflow itself.</p><p><strong>1.8 csync2</strong></p><p>Install and configure csync2:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/6e9390dee6a9af819d4690c06a316fe3/href">https://medium.com/media/6e9390dee6a9af819d4690c06a316fe3/href</a></iframe><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/8a688b21241b00f36fb8e90bd4dc6771/href">https://medium.com/media/8a688b21241b00f36fb8e90bd4dc6771/href</a></iframe><p>Pre-shared keys can be generated using the following:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/abd4060aba85cfc7dd47df3474fab942/href">https://medium.com/media/abd4060aba85cfc7dd47df3474fab942/href</a></iframe><p>Then specify the file with your keys in the string</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b41ad33328d0fd20f571808d4cf067b9/href">https://medium.com/media/b41ad33328d0fd20f571808d4cf067b9/href</a></iframe><p>This is used for authentication nodes.</p><p>Which directory is synchronized should be specified in</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/5cecdb4d10e3ee02c25e9270e627aad1/href">https://medium.com/media/5cecdb4d10e3ee02c25e9270e627aad1/href</a></iframe><p>So, add a job to crontab:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/589c391cdede303c0afbdcff6b4129c4/href">https://medium.com/media/589c391cdede303c0afbdcff6b4129c4/href</a></iframe><p>Then you can create a file or directory and they will appear on all nodes(will be synchronized). Of course, only one key is used here across all nodes.</p><p>To autostart the csync2 daemon with necessary params, it’s necessary to modify the systemd unit:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/5e5877e439dcd03304e3d4528159e6b2/href">https://medium.com/media/5e5877e439dcd03304e3d4528159e6b2/href</a></iframe><p>Start csync2:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/a6f7d1f11489791d9cd92eb3291a0792/href">https://medium.com/media/a6f7d1f11489791d9cd92eb3291a0792/href</a></iframe><p><strong>1.9 Airflow</strong></p><p>And finally, we got the moment when all preparations have been done and we can install Airflow itself:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b23a8c9a1427a22f1dac021aa9c239b6/href">https://medium.com/media/b23a8c9a1427a22f1dac021aa9c239b6/href</a></iframe><p>Initialize a database:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/0802bfb43570984530d2f20add64af96/href">https://medium.com/media/0802bfb43570984530d2f20add64af96/href</a></iframe><p>Create airflow components systemd units:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/2be8aba8bcc362a8ca443946d4b41016/href">https://medium.com/media/2be8aba8bcc362a8ca443946d4b41016/href</a></iframe><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/5fb0bfdadfd42e1456140070cef5a104/href">https://medium.com/media/5fb0bfdadfd42e1456140070cef5a104/href</a></iframe><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b570580413089eb5ddc6e3d2260bdcd7/href">https://medium.com/media/b570580413089eb5ddc6e3d2260bdcd7/href</a></iframe><p>Don’t forget to enable them.</p><p>Create config file by the command in the default directory:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/fe2e53dd00f549c0e4b2f2e9d83876ac/href">https://medium.com/media/fe2e53dd00f549c0e4b2f2e9d83876ac/href</a></iframe><p>Find and modify the following settings:</p><p>in [code] section</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/4f31f308fd35e45cda32f3031d4826f1/href">https://medium.com/media/4f31f308fd35e45cda32f3031d4826f1/href</a></iframe><p>in section [celery]:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/af48ce7b1039b6237d3bb6be45cc24f7/href">https://medium.com/media/af48ce7b1039b6237d3bb6be45cc24f7/href</a></iframe><p>As for broker_url, I specified all three nodes, but it’s redundant because we suppose celery will connect to localhost and if the whole node is down, there is no reason to reconnect to anyone else node. But for reliability, I added three nodes. You can consider specifying only localhost.</p><p>Create a user:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/94d52912c72898444eef5a4e5c5527f1/href">https://medium.com/media/94d52912c72898444eef5a4e5c5527f1/href</a></iframe><p>Restart the airflow:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b5798968d7e9169e017411092a032c05/href">https://medium.com/media/b5798968d7e9169e017411092a032c05/href</a></iframe><p>Look if some errors occur.</p><p>To get access to the web interface I recommend using an ssh tunnel.</p><p>Right now we have the Airflow cluster is up and running. Double-check all settings again to make sure you didn’t make an error.</p><h3><strong>Troubleshooting</strong></h3><p>1. If you have issues with patroni, etcd, or PostgreSQL</p><p>Try the following:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/0506101aa788b08676ed8243b93f9aa5/href">https://medium.com/media/0506101aa788b08676ed8243b93f9aa5/href</a></iframe><p>sometimes you can remove all PostgreSQL data:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c61ef5e8ac685340031c2199b06e3295/href">https://medium.com/media/c61ef5e8ac685340031c2199b06e3295/href</a></iframe><p>But be careful! This can be done only if you have test environment, production servers are not for experiments, you should understand what you are doing.</p><p>And restart the services:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/037b13998df571e566c35a9eb2605e5b/href">https://medium.com/media/037b13998df571e566c35a9eb2605e5b/href</a></iframe><p>2. Issues with csync can be resolved by removing directories with a database:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/8da05b9dd12376312320e7ec9c85086d/href">https://medium.com/media/8da05b9dd12376312320e7ec9c85086d/href</a></iframe><p>Then take a look at statuses of processes by issuing the command:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/3367170bc1f34438bdf55de43bf49810/href">https://medium.com/media/3367170bc1f34438bdf55de43bf49810/href</a></iframe><p>Last but not least I’d like to say, or even strongly recommend, you should consider the implementation of monitoring of the system. What system to use is up to you. I use Zabbix and special templates for PostgreSQL, templates for tracking if processes are running, and so on. Any comments are welcome!</p><figure><a href="https://faun.to/bP1m5"><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BCiLLad3dvZLwBa-B5cAVQ.png" /></a></figure><p>Join FAUN: <a href="https://faun.to/i9Pt9"><strong>Website</strong></a><strong> </strong>💻<strong>|</strong><a href="https://faun.dev/podcast"><strong>Podcast</strong></a><strong> </strong>🎙️<strong>|</strong><a href="https://twitter.com/joinfaun"><strong>Twitter</strong></a><strong> </strong>🐦<strong>|</strong><a href="https://www.facebook.com/faun.dev/"><strong>Facebook</strong></a><strong> </strong>👥<strong>|</strong><a href="https://instagram.com/fauncommunity/"><strong>Instagram</strong></a><strong> </strong>📷|<a href="https://www.facebook.com/groups/364904580892967/"><strong>Facebook Group</strong></a><strong> </strong>🗣️<strong>|</strong><a href="https://www.linkedin.com/company/faundev"><strong>Linkedin Group</strong></a><strong> </strong>💬<strong>|</strong> <a href="https://faun.dev/chat"><strong>Slack</strong></a> 📱<strong>|</strong><a href="https://thechief.io/"><strong>Cloud Native</strong> <strong>News</strong></a><strong> </strong>📰<strong>|</strong><a href="https://linktr.ee/faun.dev/"><strong>More</strong></a><strong>.</strong></p><p><strong>If this post was helpful, please click the clap 👏 button below a few times to show your support for the author 👇</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fd701f70cd67" width="1" height="1" alt=""><hr><p><a href="https://faun.pub/airflow-high-available-cluster-fd701f70cd67">Airflow High Available cluster</a> was originally published in <a href="https://faun.pub/">FAUN.dev() 🐾</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Airflow High Available cluster]]></title>
            <link>https://faun.pub/airflow-high-available-cluster-2f2d792ce900?source=rss-4086cecb427f------2</link>
            <guid isPermaLink="false">https://medium.com/p/2f2d792ce900</guid>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[airflow]]></category>
            <category><![CDATA[clustering]]></category>
            <category><![CDATA[automation]]></category>
            <dc:creator><![CDATA[Denis Matveev]]></dc:creator>
            <pubDate>Thu, 23 Sep 2021 10:11:05 GMT</pubDate>
            <atom:updated>2022-03-22T15:09:06.244Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Part 2. Implementing the Airflow HA solution</strong></p><p>Also read previous <a href="https://faun.pub/airflow-high-available-cluster-e1a4c0050607">part</a>, next <a href="https://medium.com/faun/airflow-high-available-cluster-fd701f70cd67">part</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/973/1*2iK4mmtHZLuk4szgt21u7Q.png" /></figure><p>For simplifying installing process, I will perform all actions under root. You should use a non-privileged user with sudo for installing. We’ll skip this question as well as many security aspects.</p><p><strong>1.1 Installing etcd and patroni</strong></p><p>Let’s start the installation process by installing the PostgreSQL cluster with Patroni.</p><p>Prepare a node for installing necessary packages:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/723a82e98acb185157d444b2da90693f/href">https://medium.com/media/723a82e98acb185157d444b2da90693f/href</a></iframe><p>Configure your timezone:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/e254bfac509000e16bf49fb57ef97f65/href">https://medium.com/media/e254bfac509000e16bf49fb57ef97f65/href</a></iframe><p>Then install etcd from Ubuntu’s repos:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/4d6259f84122c5f49a674c5c1cd57f9e/href">https://medium.com/media/4d6259f84122c5f49a674c5c1cd57f9e/href</a></iframe><p>Then install patroni itself:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/10c5fdf6ea2b6d4b8bf8a8182bca321d/href">https://medium.com/media/10c5fdf6ea2b6d4b8bf8a8182bca321d/href</a></iframe><p><strong>1.2 Installing PostgreSQL from the official repository</strong></p><p>Installing PostgreSQL from the official repo allows us to install the latest stable version.</p><p>Just follow the instructions from the official website:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/92fbf4fa620c68952c813090439cfbdb/href">https://medium.com/media/92fbf4fa620c68952c813090439cfbdb/href</a></iframe><p>Remove all PostgreSQL data, installed from repository, because patroni cluster will create its own configs and databases:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9eacada3d22db3d96c98f64420a236ac/href">https://medium.com/media/9eacada3d22db3d96c98f64420a236ac/href</a></iframe><p>Disable postgresql service, since the cluster will be started by patroni:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/d06acff6b1a4722bc447f327460a38b5/href">https://medium.com/media/d06acff6b1a4722bc447f327460a38b5/href</a></iframe><p><strong>1.3 Configuring patroni, etcd, and PostgreSQL</strong></p><p>Here is an etcd config template:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/5451bb8911993aecd91b4668da78a11d/href">https://medium.com/media/5451bb8911993aecd91b4668da78a11d/href</a></iframe><p>Just replace names and IP addresses with yours.</p><p>Then, take the following Patroni config and modify it with your data(please bear in mind it’s YAML and you should use the right indents:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/51ca0b74452fabdecc145983cfc9c310/href">https://medium.com/media/51ca0b74452fabdecc145983cfc9c310/href</a></iframe><p>Further, we need to allow connecting to our PostgreSQL instances from the local network:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/f9eb51071879028b4884b0d7b739acd3/href">https://medium.com/media/f9eb51071879028b4884b0d7b739acd3/href</a></iframe><p>I added the following two strings:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/6ca130112527840f8882ec5493fb53b0/href">https://medium.com/media/6ca130112527840f8882ec5493fb53b0/href</a></iframe><p>The lines above permit connection to any of the PostgreSQL nodes from the local network. If you are not going to connect directly to the node, you can leave only a load balancer IP address.</p><p>Now the time has come to add systemd units for etcd and patroni:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9902ce3cff197f4a31a9058bdded7fa5/href">https://medium.com/media/9902ce3cff197f4a31a9058bdded7fa5/href</a></iframe><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b37b8d7c55561f252d36ea63b2ee3652/href">https://medium.com/media/b37b8d7c55561f252d36ea63b2ee3652/href</a></iframe><p>You should enable these two services.</p><p><strong>Note.</strong> As you have to install at least three nodes, there is a convenient way to install and configure the patroni cluster is to create an Ansible playbook(or use something similar).</p><p><strong>Note</strong>. Pay attention, patroni depends on etcd, if etcd fails at the start, patroni also won’t be launched. Occasionally, it’s useful to avoid starting patroni on a specific node to perform some manual actions like database recovery. Take it into account!</p><p><strong>1.4 Start PostgreSQL cluster with Patroni</strong></p><p>First of all, service etcd should be running before starting Patroni, which will start PostgreSQL.</p><p>Start it:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/e2edcb85a0b4a8ce1050106d1bae2a10/href">https://medium.com/media/e2edcb85a0b4a8ce1050106d1bae2a10/href</a></iframe><p>The action above should be done on all three nodes</p><p>Let’s check if the etcd cluster is up:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/8392616aecd99f9d60cb9fa0e236a04b/href">https://medium.com/media/8392616aecd99f9d60cb9fa0e236a04b/href</a></iframe><p>If you got something like that, it means the etcd cluster has been built and running.</p><p>Master election and other cluster algorithms are implemented in etcd, and patroni relies on it.</p><p>To see which node is master, type in a console:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/7a06f61125511c71f285baa6d86568d6/href">https://medium.com/media/7a06f61125511c71f285baa6d86568d6/href</a></iframe><p>The next step is to start Patroni with PostgreSQL. When patroni is being launched, it automatically starts PostgreSQL, which in its turn, initializes the database, then patroni creates users specified in config, replaces pg_hba.conf file, postgresql.conf is renamed to postgresql.base.conf and finally, patroni adds postgresql.conf file with specific settings and with including postgresql.base.conf.</p><p>Therefore, if you need to change some of the PostgreSQL settings, let’s say timezone, you should modify postgresql.base.conf file.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9734a702009a9474aa81b31a52288b29/href">https://medium.com/media/9734a702009a9474aa81b31a52288b29/href</a></iframe><p>Do it on all nodes!</p><p>Let’s check if PostgreSQL is up:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/73e597b04d8ad02537259529a2234fb9/href">https://medium.com/media/73e597b04d8ad02537259529a2234fb9/href</a></iframe><p>You’ll see patroni process like</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/6d5d288e073529f3f93871db3fbff8d0/href">https://medium.com/media/6d5d288e073529f3f93871db3fbff8d0/href</a></iframe><p>Moreover, you should see PostgreSQL processes.</p><p>Okay, it’s about the process, but how to check if the database cluster is working properly?</p><p>There is a command-line interface to patroni:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/0baf94278b4ef9a3099ccd898e9bc8ea/href">https://medium.com/media/0baf94278b4ef9a3099ccd898e9bc8ea/href</a></iframe><p>This utility is used for cluster managing (switchover, failover, etc) too. I won’t consider this.</p><p><strong>1.5 Load balancer</strong></p><p>One of the requirements is to use an HA load balancer. Usually, cloud providers supply load balancers and guarantee high availability.</p><p>During creating a load balancer what you should pay attention to:</p><p>1. Service. Choose TCP service and specify ports 5432</p><p>2. Health check. Choose HTTP, port 80, specify URL as/master fill in the response code field by 200.</p><p>Attach your LB to the local network, chosen targets should be your three nodes.</p><p>I’ll provide a few screenshots from Hetzner, other providers have the same settings, and interfaces look similar(AWS,GCP, etc).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*w0BMLxCrBgDtOAdNh0tv5Q.png" /><figcaption>Pic 1. Load balancer services settings</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/558/1*9Izb1hx2U-bvXDiery0aGA.png" /><figcaption>Pic 2. Load balancer health check settings</figcaption></figure><p>Test your patroni cluster:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/805022ab3a66740c71d180d0c9ffb432/href">https://medium.com/media/805022ab3a66740c71d180d0c9ffb432/href</a></iframe><p>Where 10.5.0.10 is the load balancer’s IP address.</p><p>Admin’s password you can find in patroni config.</p><p>Create a user and database for airflow:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/66db17188f22f7d0ab897ae194d29e35/href">https://medium.com/media/66db17188f22f7d0ab897ae194d29e35/href</a></iframe><p><strong>1.6 Celery</strong></p><p>Celery can be installed from OS packages and from pip repository. A preferred approach is to install using pip:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/16ff1a0a8f3aed75236ee5585638cd4a/href">https://medium.com/media/16ff1a0a8f3aed75236ee5585638cd4a/href</a></iframe><p>You should install celery on all nodes.</p><p>Installed package no need to be configured.</p><p><strong>1.7 RabbitMQ</strong></p><p>To install RabbitMQ type in a console the following(on three nodes):</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c8a13363e3882eb77b2dad1d89846e00/href">https://medium.com/media/c8a13363e3882eb77b2dad1d89846e00/href</a></iframe><p>Then enable systemd unit:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/ed938c0de4f7b2792122b6e9d6d5f2c6/href">https://medium.com/media/ed938c0de4f7b2792122b6e9d6d5f2c6/href</a></iframe><p>and start it:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/dbe29aaade47fe29c7f7abafdbba8c7e/href">https://medium.com/media/dbe29aaade47fe29c7f7abafdbba8c7e/href</a></iframe><p>Then we need to configure the RabbitMQ cluster.</p><p>To configure the broker we’ll use CLI.</p><p>The following actions should be done on one node, say on node1:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/8c48157ef8643afca9beed03abbd5b82/href">https://medium.com/media/8c48157ef8643afca9beed03abbd5b82/href</a></iframe><p>where airflow is the user and cafyilevyeHa is its password</p><p>Now, let’s create the RabbitMQ cluster.</p><p>It’s important to add all nodes to /etc/hosts file:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/e14f8087236ec690a3965fec27d595d5/href">https://medium.com/media/e14f8087236ec690a3965fec27d595d5/href</a></iframe><p>Firstly, we need to activate ssh passwordless access between cluster nodes:</p><p>generate ssh keys and put them into authorized_keys files on all three nodes.</p><p>You can use once generated keys.</p><p>Copy cookies from any node to others(in the example below we use node1):</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/d70870620b3cdeb7d64d7668281a29e9/href">https://medium.com/media/d70870620b3cdeb7d64d7668281a29e9/href</a></iframe><p>Cookies are used for authentication.</p><p>Check if nodes are working independently:</p><p>sequentially enter the command below to check the status of the cluster, you’ll see the cluster is not created:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/da06b35daacdbcd6da45156121705534/href">https://medium.com/media/da06b35daacdbcd6da45156121705534/href</a></iframe><p>After checking prerequisites, it’s time to add nodes to the cluster.</p><p>It’s necessary to perform the actions on node2 and node3:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/91a994e8ffd7407dd7e9b5ffb8c1936a/href">https://medium.com/media/91a994e8ffd7407dd7e9b5ffb8c1936a/href</a></iframe><p>When the cluster has been created, you can check its status:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/da06b35daacdbcd6da45156121705534/href">https://medium.com/media/da06b35daacdbcd6da45156121705534/href</a></iframe><p>As you’ve created the cluster, you’ll see something like this:</p><pre>root@node1:~# rabbitmqctl cluster_status<br>Cluster status of node rabbit@node1 …<br><strong>Basics</strong></pre><pre>Cluster name: rabbit@node1</pre><pre><strong>Disk Nodes</strong></pre><pre>rabbit@node1<br>rabbit@node2<br>rabbit@node3</pre><pre><strong>Running Nodes</strong></pre><pre>rabbit@node1<br>rabbit@node2<br>rabbit@node3</pre><pre><strong>Versions</strong></pre><pre>rabbit@node1: RabbitMQ 3.8.2 on Erlang 22.2.7<br>rabbit@node2: RabbitMQ 3.8.2 on Erlang 22.2.7<br>rabbit@node3: RabbitMQ 3.8.2 on Erlang 22.2.7</pre><p>Also, there is a possibility to check the status in the web interface, create an ssh tunnel:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/eaa5d2eccf09494faf093511cac54652/href">https://medium.com/media/eaa5d2eccf09494faf093511cac54652/href</a></iframe><p>In your browser’s address line insert <a href="http://localhost:15672/">http://localhost:15672</a></p><p>You’ll see the state of the cluster and nodes.</p><p><strong>Note.</strong> There is a way to enable peers auto-discovery, but it’s not the scope of the article.</p><p>See you in the third part of the tutorial.</p><figure><a href="https://faun.to/bP1m5"><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BCiLLad3dvZLwBa-B5cAVQ.png" /></a></figure><p>Join FAUN: <a href="https://faun.to/i9Pt9"><strong>Website</strong></a><strong> </strong>💻<strong>|</strong><a href="https://faun.dev/podcast"><strong>Podcast</strong></a><strong> </strong>🎙️<strong>|</strong><a href="https://twitter.com/joinfaun"><strong>Twitter</strong></a><strong> </strong>🐦<strong>|</strong><a href="https://www.facebook.com/faun.dev/"><strong>Facebook</strong></a><strong> </strong>👥<strong>|</strong><a href="https://instagram.com/fauncommunity/"><strong>Instagram</strong></a><strong> </strong>📷|<a href="https://www.facebook.com/groups/364904580892967/"><strong>Facebook Group</strong></a><strong> </strong>🗣️<strong>|</strong><a href="https://www.linkedin.com/company/faundev"><strong>Linkedin Group</strong></a><strong> </strong>💬<strong>|</strong> <a href="https://faun.dev/chat"><strong>Slack</strong></a> 📱<strong>|</strong><a href="https://thechief.io/"><strong>Cloud Native</strong> <strong>News</strong></a><strong> </strong>📰<strong>|</strong><a href="https://linktr.ee/faun.dev/"><strong>More</strong></a><strong>.</strong></p><p><strong>If this post was helpful, please click the clap 👏 button below a few times to show your support for the author 👇</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2f2d792ce900" width="1" height="1" alt=""><hr><p><a href="https://faun.pub/airflow-high-available-cluster-2f2d792ce900">Airflow High Available cluster</a> was originally published in <a href="https://faun.pub/">FAUN.dev() 🐾</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Airflow High Available cluster]]></title>
            <link>https://faun.pub/airflow-high-available-cluster-e1a4c0050607?source=rss-4086cecb427f------2</link>
            <guid isPermaLink="false">https://medium.com/p/e1a4c0050607</guid>
            <category><![CDATA[clustering]]></category>
            <category><![CDATA[automation]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[airflow]]></category>
            <dc:creator><![CDATA[Denis Matveev]]></dc:creator>
            <pubDate>Mon, 06 Sep 2021 16:13:10 GMT</pubDate>
            <atom:updated>2022-03-22T15:07:31.881Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Part 1. An overview and design</strong></p><p>Next parts: <a href="https://medium.com/faun/airflow-high-available-cluster-2f2d792ce900">part 2</a>, <a href="https://medium.com/faun/airflow-high-available-cluster-fd701f70cd67">part 3</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/973/1*2iK4mmtHZLuk4szgt21u7Q.png" /><figcaption>Apache Airflow logo</figcaption></figure><p>Nowadays many things should be launched at a specific time, the first implementation which well-known is Cron with its crontab(for repeatable tasks) and at (for one-time tasks).</p><p>What if you have one, ten, or similar independent tasks? I suppose it’s OK, but when the number of tasks increased up to hundreds or even thousands, it becomes a headache. How to track tasks, how to control tasks, their exit conditions, and build conditional tasks?</p><p>For these, there is Airflow. Not only Apache Airflow, but we will consider Airflow[1].</p><p>Initially, Airflow was developed and released at Airbnb for inner necessaries. Now it is an open-source product.</p><p>Typical airflow instance has three components:</p><ul><li>airflow worker</li><li>airflow scheduler</li><li>airflow webserver</li></ul><p>For storing results and metadata it is used database, we will consider Postgres, but also it may be MySQL, MSSQL, etc</p><p>For queues, airflow uses celery with Redis or RabbitMQ. We will use RabbitMQ with celery(since in our case Airflow works with CeleryExecutor).</p><p>A webserver, I think it’s clear the purpose of this. All airflow processes use a database.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/901/1*KjSQgIxuE4CtzzKxkkS1vA.png" /><figcaption>Pic 1. Airflow architecture</figcaption></figure><p>All these components can work on one or on different machines and communicate over a network. And then only one question arises: How to ensure uninterrupted and smooth work of the system?</p><p>If one of the airflow processes went down, then your tasks also will fail. It’s not what you expected and sometimes it might impact your business.</p><p>Now we came to the situation when it’s necessary to have an HA cluster. Also, there is a situation we came across, we want to run processes in different data centers if a provider does or does not support this option.</p><p>Fortunately, airflow since version 2, supports high availability out from a box. But this solution relies on a database, therefore not any RDBMS is suitable. More or less modern databases are good for this purpose. Airflow uses a lock mechanism in the database, it’s like mutex in multi-threaded programming. If we ran dag in one server, it cannot be started somewhere else until the database has a record regarding this task. All requirements you can find on the official website. In this tutorial, we are going to consider the PostgreSQL 13 version(the latest).</p><p>All applications considered below with specific versions will work together and they tested. Lower versions don’t guarantee to be worked. I tried to cover all questions anyone can come across during creating an HA cluster.</p><h3><strong>High Available Architecture</strong></h3><p>There are many articles and implementations of the Airflow cluster. But we are going to consider three-node cluster located in different data centers. Each node contains Airflow components, PostgreSQL, RabbitMQ, Celery. Only three of them should be working in cluster mode(synchronize its data over nodes)</p><p>Let’s see picture 2.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/763/1*grY8q5LFR61YpjSV0hC8PQ.png" /><figcaption>Pic 2. Airflow HA cluster design</figcaption></figure><p>Since a node consists of many components, it should use an encrypted connection. Configuring, issuing keys and certs for each component on each node is a bit tiring process. I recommend enabling VPN between nodes, let’s say Wireguard. It allows up secure connection only once.</p><h3><strong>PostgreSQL</strong></h3><p><strong>a. PostgreSQL multi-master</strong></p><p>As Airflow supports HA solution out of the box, it begs a native solution. We install airflow on three nodes and then install and configure PostgreSQL to run in multi-master mode. I won’t do a review of available solutions. I’ll only headline one thing, which didn’t mention in the official documentation. I came across this feature when I installed Bucardo[2] for multi-master replication:</p><p><a href="https://github.com/apache/airflow/issues/15471#issuecomment-825107547">https://github.com/apache/airflow/issues/15471#issuecomment-825107547</a></p><p><a href="https://github.com/bucardo/bucardo/issues/231#issuecomment-894837272">https://github.com/bucardo/bucardo/issues/231#issuecomment-894837272</a></p><p>Many solutions for replication trigger on</p><pre>UPDATE/INSERT/DELETE</pre><p>statements and doesn’t support</p><pre>SELECT .. FOR UPDATE</pre><p>mechanism.</p><p>So it’s not suitable for us and I decided to find something else.</p><p>Have been reading docs for hours and comparing many solutions, I stopped on patroni.</p><p>Patroni is a solution for building HA PostgreSQL cluster written in python and uses distributed systems like etcd, zookeeper, or Consul.</p><p><strong>b.PostgreSQL master-slave with Patroni</strong></p><p>Let’s have a look at pic.3</p><p>Now we have a PostgreSQL cluster with only one master and a few slaves. How does the system decide where to send queries? Let me introduce Patroni.</p><p>Patroni uses etcd for electing master, or rather not electing, just for setting master based on info from etcd. All work by electing master does etcd(in our case). The idea is that Patroni launches an HTTP server that returns 200 for master and 503 for slaves. This is used by load-balancer for checking the health and forward traffic to healthy instance(master):</p><p><a href="http://server/master">http://server/master</a> returns 200(OK)</p><p><a href="http://slave/master">http://slave/master</a> returns 503(Service unavailable)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/963/1*USLATS421TFjYj8PVYJuPA.png" /><figcaption>Pic 3. Master-slave Postgresql cluster with patroni</figcaption></figure><p>Alright, PostgreSQL cluster design has been done.</p><h3><strong>High available Celery</strong></h3><p>Actually, there is no HA for celery, celery works on each node independently and communicates with a message broker which is highly available. For Celery monitoring, there is application ‘Flower’.</p><h3><strong>HA message broker</strong></h3><p>Airflow supports two brokers: Redis and RabbitMQ.</p><p>As we are going to build an HA cluster, it requires a broker should be high available too or distributed over cluster nodes.</p><p>Initially, I decided to use Redis, because I am familiar with it. Then I found a utility from Netflix called Dynomite[3]. It looked nice to be used in my cluster. The description told it allows to build multi-master clusters. And I started to use it.</p><p>When everything was done I realized it didn’t work, because Redis had implemented new features in protocol. I had to give up using Netflix’s application.</p><h3><strong>RabbitMQ cluster</strong></h3><p>RabbitMQ supports high availability by default, also it has a web server to control the RabbitMQ cluster, you can see its status, stop, start and perform other actions on instances. For security reasons, it is not recommended to open access from the Internet.</p><h3><strong>DAGs synchronization</strong></h3><p>If you have a possibility to connect a shared volume to each of the three nodes, the further paragraph you can skip.</p><p>As we have a few nodes and each node has its own web server when a user is creating a new task, this new just created DAG will be saved on the same machine where this webserver works. Therefore it’s necessary to synchronize DAGs among all nodes in a cluster since workers can be switched over nodes or worked in parallel. For this role, I recommend using csync2[4] and crontab. From my side, I can explain why I have chosen csync2 + crontab. Initially, I saw the bundle lsync [5] + csync2 as an ideal. Let’s see, lsync can track changes on filesystem using inotify syscall, and then csync can synchronize these modified files.</p><p>There are many tools that allow syncing files between machines, but csync2 has an advantage — it can work in clusters with nodes more than two. Looks fine, doesn’t it? Further, while I was testing the solution, I found it was a bad idea.</p><p>lsyncd fails if csync2 cannot connect to a remote server, for instance, if one node is down. In this case, csync2 returns 1 and lsync fails. I didn’t manage to change this behavior. That’s why I decided to use crontab to run csync2 every minute instead of lsync.</p><p>If someone knows a better solution, please let me know.</p><p>So this is the end of part 1. See you in part 2, where we will consider the implementation and installation process step by step.</p><p>Sources:</p><p>[1] <a href="https://github.com/apache/airflow/">https://github.com/apache/airflow/</a></p><p>[2] <a href="https://github.com/bucardo/bucardo/">https://github.com/bucardo/bucardo/</a></p><p>[3] <a href="https://github.com/Netflix/dynomite">https://github.com/Netflix/dynomite</a></p><p>[4] <a href="https://github.com/LINBIT/csync2">https://github.com/LINBIT/csync2</a></p><p>[5] <a href="https://github.com/axkibe/lsyncd">https://github.com/axkibe/lsyncd</a></p><figure><a href="https://faun.to/i9Pt9"><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*t_MOgMTr_f1N4qHN_R7KVA.png" /></a></figure><p>Join FAUN: <a href="https://faun.to/i9Pt9"><strong>Website</strong></a><strong> </strong>💻<strong>|</strong><a href="https://faun.dev/podcast"><strong>Podcast</strong></a><strong> </strong>🎙️<strong>|</strong><a href="https://twitter.com/joinfaun"><strong>Twitter</strong></a><strong> </strong>🐦<strong>|</strong><a href="https://www.facebook.com/faun.dev/"><strong>Facebook</strong></a><strong> </strong>👥<strong>|</strong><a href="https://instagram.com/fauncommunity/"><strong>Instagram</strong></a><strong> </strong>📷|<a href="https://www.facebook.com/groups/364904580892967/"><strong>Facebook Group</strong></a><strong> </strong>🗣️<strong>|</strong><a href="https://www.linkedin.com/company/faundev"><strong>Linkedin Group</strong></a><strong> </strong>💬<strong>|</strong> <a href="https://faun.dev/chat"><strong>Slack</strong></a> 📱<strong>|</strong><a href="https://thechief.io/"><strong>Cloud Native</strong> <strong>News</strong></a><strong> </strong>📰<strong>|</strong><a href="https://linktr.ee/faun.dev/"><strong>More</strong></a><strong>.</strong></p><p><strong>If this post was helpful, please click the clap 👏 button below a few times to show your support for the author 👇</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e1a4c0050607" width="1" height="1" alt=""><hr><p><a href="https://faun.pub/airflow-high-available-cluster-e1a4c0050607">Airflow High Available cluster</a> was originally published in <a href="https://faun.pub/">FAUN.dev() 🐾</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[PostGIS for Greenplum under GNU/Linux Debian]]></title>
            <link>https://faun.pub/greenplum-overview-4ad2db18a847?source=rss-4086cecb427f------2</link>
            <guid isPermaLink="false">https://medium.com/p/4ad2db18a847</guid>
            <category><![CDATA[debian]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[postgis]]></category>
            <category><![CDATA[greenplum]]></category>
            <dc:creator><![CDATA[Denis Matveev]]></dc:creator>
            <pubDate>Tue, 04 May 2021 12:31:09 GMT</pubDate>
            <atom:updated>2022-01-26T08:49:31.792Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/807/1*tfUoqDfBF9mZ78cOzjH1sA.png" /></figure><ol><li><strong>Greenplum overview</strong></li></ol><p>Greenplum(hereinafter GP) is a relational database management system designed for massive parallel data processing, usually it is used for big data. Now this system is getting more and more popular among data scientists, AI developers, in machine learning field. GP is high performance database and designed as a high available solution.</p><p>GP based on PostgreSQL RDBMS. Therefore GP is open source and distributed under Apache 2 license.</p><p>A GP cluster typical architecture consists of at least one master (and another one in standby mode, but it is not required) and a different number of nodes(segments in terms of GP).</p><p>For efficient nodes work they should be connected through the high performance NIC and network equipment.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/461/1*9cyfW-OIgOAAvdav5plELw.png" /><figcaption>Typical Greenplum architecture</figcaption></figure><p>This technology allows to make easy scale up the cluster. There are many advantages of GP, but also there are some features are going to be implemented.</p><p><strong>2. Extensions for Greenplum</strong></p><p>As this is the open source project, the main OS for installing GP is GNU Linux. Linux is well known OS and divided into two big systems: rpm based and deb based.</p><p>Greenplum has pre-built packages for CentOS/Red Hat and Debian/Ubuntu available in official repository. Installation process and configuring is well covered in the official documentation, we won’t catch eyes on this and assume you have installed GP cluster. Pay attention, there are no packages for Ubuntu 20.04 in the official greenplum repository, only for Debian 18.04.</p><p>As GP is an almost postgresql, theoretically you can install any postresql extensions on GP.</p><p>But there are a few restrictions such as:</p><p>1. It is not pure postgresql and just installing necessary extension will not give effect on GP cluster</p><p>2. You should install extensions across the cluster, i.e. on each segment.</p><p>This makes installation process not trivial.</p><p>Let’s consider PostGIS extension, sources should be modified before building and installing. Official PostGIS sources for GP available at <a href="https://github.com/greenplum-db/geospatial">https://github.com/greenplum-db/geospatial</a></p><p>How to install this extension on all nodes? Firstly, you should clone sources on all segments.</p><p>Then, you should build from sources. And finally install. It is not convenient approach I consider.</p><p><strong>3. Greenplum packages</strong></p><p>Fortunately, GP developers made special package format to automate installation process across the cluster.</p><p>gppkg is a format of greenplum packages ready to install. It implies quickly installing on all nodes by one command on the master. These files are an just archive(tar) of preliminary built OS-specific packages. In other words the archive contains deb or rpm inside itself.</p><p>It works nice, but only for CentOS and Red Hat. Oops, If you have a cluster on Debian or Ubuntu, you will be surprised. This is bad news.</p><p>On the one hand, GP supports deb packages, but not completely.</p><p>On the other hand … it does not work.</p><p>Let’s see on GP supplementary cluster utility code.</p><p>Open the file below on 861 line:</p><pre># vi /opt/greenplum-db-6–6.13.0/lib/python/gppylib/operations/package.py +861</pre><p><strong>gppkg</strong> format implies installing into chroot(for greenplum it is <strong>GPHOME</strong> that is /opt/greenplum-&lt;version&gt;).</p><p>If you run</p><pre># gppkg -i postgis-ossv2.5.4+pivotal.3_pv2.5_gpdb6.0-debian-amd64.gppkg</pre><p>this will launch the following</p><pre># fakeroot dpkg — force-not-root — log=/dev/null — admindir=&quot;$GPHOME&quot;/.tmp/ — instdir=&quot;$GPHOME&quot; -i &lt;package.deb&gt;</pre><p>From man follows that for installing into instdir chroot is used. So it is irrelevant if your package doesn’t have any scripts inside like postinst or postrm. If you are going to install deb package containing postinst, postrm etc scripts, this will not work, because for chrooting it must have at least an interpreter for launching scripts (bash or sh etc).</p><p>Yes, there is an approach to install bash and minimal system into a specific directory and then chrooting there. Such approach is useful for many cases such as installing a system for a virtual machine(debootstrap). I consider it is not a suitable process for installing packages especially installing packages on all nodes in a cluster. Also, it requires scripts modification.</p><p>By the way, since gppkg installs a deb package into a specific directory as a root, deb package should contain directories tree relatively installing directory as root(/). For this reason, the approach when the deb package is installed into the system packages database(by the dpkg -i command) is more suitable at the moment because it does not require modifying greenplum sources.</p><p>As for dependencies required for a deb package inside gppkg, of course, you should install them, but Debian’s utility dpkg does not support such feature. If we are talking about the following command</p><pre># dpkg -i &lt;package&gt;</pre><p>this cannot track all dependencies in control file inside the deb package and install. Initially that command will break installation due to the fact broken dependencies. To resolve you should run</p><pre># apt-get -f install</pre><p>to force install all dependent packages and the package itself.</p><p>This has not been implemented so far in GP cluster scripts.</p><p><strong>4. Debian packages</strong></p><p>If you are using Debian or Ubuntu, there is a possibility to install PostgreSQL extensions using dpkg. These deb packages should be specifically built. Honestly speaking, after building the package once, installation should be done on all nodes, however it can be automated.</p><p><strong>5. Installation PostGIS</strong></p><p>To compile PostGIS do the following(assuming all actions under root):</p><p>a. Install necessary additional packages:</p><pre># apt-get install autoconf automake libtool gdal gdal-data expat libexpat1 libjson-c-dev geos-devel proj-devel libgeos++-dev libproj-dev libcunit1-dev libcunit1-doc gdal-bin libgdal-dev xsltproc docbook-xsl docbook-mathml libxslt1-dev libxslt1</pre><p>b. Clone PostgGIS sources prepared specially for greenplum</p><p>clone sources from:</p><pre># git clone — recursive https://github.com/denismatveev/Greenplum_PostGIS_deb.git .</pre><p>The repository above has git submodule and cloning Greenplum_PostGIS_deb.git repository will bring PostGIS repository too(repository in the submodule is cloning only that commit which was at the creating moment).</p><p>Since postgis-2.5.4 cannot be built by gcc-7 and later, it is supposed to modify one source file</p><pre>postgis/build/postgis-2.5.4/raster/rt_pg/rtpg_mapalgebra.c</pre><p>in 937 line: replace the piece</p><pre>if (arg-&gt;numraster &gt; 1) {<br>   i = 1;<br>   break;<br>}</pre><p>By the following:</p><pre>i = (arg-&gt;numraster &gt; 1) ? 1 : 0;<br>break;</pre><p>c. Build a deb package</p><pre># make deb</pre><p>You’ll see the file greenplum-db-6-postgis-2.5.4-1.x86_64.deb</p><p>Then copy the file from buildhost to the master node.</p><p>d. Copy the file from masternode among all nodes</p><p>Before copying make available the greenplum executables</p><pre># source /home/gpadmin/.bashrc</pre><p>And then run copying</p><pre># gpscp -v -f cluster_hostlist ../greenplum-db-6-postgis-2.5.4–1.x86_64.deb =:~/</pre><p>e. Install the package on all nodes</p><pre># gpssh -f cluster_hostlist &#39;dpkg -i greenplum-db-6-postgis-2.5.4–1.x86_64.deb; apt-get -y -f install&#39;</pre><p>If you want to check:</p><pre># gpssh -f cluster_hostlist -e &#39;dpkg -l | grep greenplum&#39;</pre><p>f. Restart the Greenplum database</p><pre># su -gpadmin<br>$ gpstop -ra</pre><p>g. Check if the Greenplum has PostGIS extension</p><p>Connect to any database you want to create an extension. Then type the following:</p><pre>database# CREATE EXTENSION postgis;<br>database# CREATE EXTENSION postgis; -- enables postgis and raster<br>database# CREATE EXTENSION fuzzystrmatch; -- required for installing tiger geocoder<br>database# CREATE EXTENSION postgis_tiger_geocoder; -- enables tiger geocoder<br>database# CREATE EXTENSION address_standardizer; -- enable address_standardizer<br>database# CREATE EXTENSION address_standardizer_data_us;</pre><p>Also, you should set up the environmental variables listed below. Just put them in /opt/greenplum-db-6-6.13.0/greenplum_path.sh</p><pre>export GDAL_DATA=$GPHOME/share/gdal<br>export POSTGIS_ENABLE_OUTDB_RASTERS=0<br>export POSTGIS_GDAL_ENABLED_DRIVERS=DISABLE_ALL</pre><p>The fastest way to do that is to use the following command:</p><pre># gpssh -f cluster_hostlist -e  &#39;echo -en &quot;export<br>GDAL_DATA=\$GPHOME/share/gdal\nexport<br>POSTGIS_ENABLE_OUTDB_RASTERS=0\nexport<br>POSTGIS_GDAL_ENABLED_DRIVERS=DISABLE_ALL\n&quot; &gt;&gt; /opt/greenplum-db-6-6.13.0/greenplum_path.sh&#39;</pre><p>Then ensure the database to be restarted:</p><pre>gpadmin@master:~$ gpstop -ra</pre><p><strong>7. Conclusion</strong></p><p>As shown above, GreenPlum is a powerful database with a big contribution team, but sometimes you come across some troubles not covered in the official documentation. And probably there is no obvious way to solve it and there is no workaround or something else like questions and answers on StackOverflow.</p><p>[1] <a href="https://gpdb.docs.pivotal.io/6-15/main/index.html">https://gpdb.docs.pivotal.io/6-15/main/index.html</a></p><p>[2] <a href="https://github.com/greenplum-db/geospatial">https://github.com/greenplum-db/geospatial</a></p><p>[3] <a href="https://github.com/denismatveev/Greenplum_PostGIS_deb">https://github.com/denismatveev/Greenplum_PostGIS_deb</a></p><figure><a href="https://faun.to/i9Pt9"><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*t_MOgMTr_f1N4qHN_R7KVA.png" /></a></figure><p>Join FAUN: <a href="https://faun.to/i9Pt9"><strong>Website</strong></a><strong> </strong>💻<strong>|</strong><a href="https://faun.dev/podcast"><strong>Podcast</strong></a><strong> </strong>🎙️<strong>|</strong><a href="https://twitter.com/joinfaun"><strong>Twitter</strong></a><strong> </strong>🐦<strong>|</strong><a href="https://www.facebook.com/faun.dev/"><strong>Facebook</strong></a><strong> </strong>👥<strong>|</strong><a href="https://instagram.com/fauncommunity/"><strong>Instagram</strong></a><strong> </strong>📷|<a href="https://www.facebook.com/groups/364904580892967/"><strong>Facebook Group</strong></a><strong> </strong>🗣️<strong>|</strong><a href="https://www.linkedin.com/company/faundev"><strong>Linkedin Group</strong></a><strong> </strong>💬<strong>|</strong> <a href="https://faun.dev/chat"><strong>Slack</strong></a> 📱<strong>|</strong><a href="https://thechief.io/"><strong>Cloud Native</strong> <strong>News</strong></a><strong> </strong>📰<strong>|</strong><a href="https://linktr.ee/faun.dev/"><strong>More</strong></a><strong>.</strong></p><p><strong>If this post was helpful, please click the clap 👏 button below a few times to show your support for the author 👇</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4ad2db18a847" width="1" height="1" alt=""><hr><p><a href="https://faun.pub/greenplum-overview-4ad2db18a847">PostGIS for Greenplum under GNU/Linux Debian</a> was originally published in <a href="https://faun.pub/">FAUN.dev() 🐾</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>