{"id":95322,"date":"2020-09-28T07:42:39","date_gmt":"2020-09-28T05:42:39","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/kratkij-obzor-operatorov-postgresql-dlya-kubernetes-nash-vybor-i-opyt"},"modified":"2020-09-28T07:42:39","modified_gmt":"2020-09-28T05:42:39","slug":"kratkij-obzor-operatorov-postgresql-dlya-kubernetes-nash-vybor-i-opyt","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/kratkij-obzor-operatorov-postgresql-dlya-kubernetes-nash-vybor-i-opyt","title":{"rendered":"A Brief Overview of PostgreSQL Operators for Kubernetes: Our Selection and Experience","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><img decoding=\"async\" alt=\"A Brief Overview of PostgreSQL Operators for Kubernetes: Our Selection and Experience\" src=\"\/wp-content\/uploads\/2020\/09\/23b2db49941fa5bd5000358c8ffb991b.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nClients increasingly ask for solutions similar to \"Amazon RDS, but cheaper\"; or \"We want something like RDS, but everywhere in any infrastructure.\" To implement such a managed solution on Kubernetes, we examined the current state of the most popular operators for PostgreSQL (Stolon, operators from Crunchy Data and Zalando) and made our choice.<\/p>\n<p>This article shares our experience from both a theoretical perspective (solution overview) and a practical standpoint (what was chosen and what the outcome was). But first, let\u2019s clarify what requirements are generally expected from a potential RDS replacement\u2026<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h2>What is RDS?<\/h2>\n<p>\nWhen people mention RDS, in our experience, they refer to a managed database service that:<\/p>\n<ol>\n<li>is easy to configure;<\/li>\n<li>has the ability to work with snapshots and restore from them (preferably with support for <noindex><a rel=\"nofollow\" href=\"https:\/\/en.wikipedia.org\/wiki\/Point-in-time_recovery\">PITR<\/a><\/noindex>);<\/li>\n<li>allows for master-slave topologies;<\/li>\n<li>has a rich list of extensions;<\/li>\n<li>provides auditing and user\/access management.<\/li>\n<\/ol>\n<p>\nIn general, the approaches to implementing the task can vary significantly, but the approach with conditional Ansible is not for us. (Colleagues at 2GIS came to a similar conclusion following <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/509926\/\">their attempt<\/a><\/noindex> to create a \"tool for quickly deploying a fault-tolerant cluster based on Postgres.\")<\/p>\n<p>Operators are the widely accepted approach for solving such tasks in the Kubernetes ecosystem. More about their application for databases running within Kubernetes has already been shared by the technical director of \"Flanta\" in <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/users\/distol\/\" class=\"user_link\">distol<\/a><\/noindex>, in <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/flant\/blog\/431500\/\">one of his presentations.<\/a><\/noindex>.<\/p>\n<p><i><strong>NB<\/strong><\/i><i>: For quickly creating simple operators, we recommend checking our Open Source utility <\/i><noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/flant\/shell-operator\"><i>shell-operator<\/i><\/a><\/noindex><i>. With it, you can do this without needing knowledge of Go, using more familiar methods for sysadmins: like Bash, Python, etc.<\/i><\/p>\n<p>There are several popular K8s operators for PostgreSQL:<\/p>\n<ul>\n<li>Stolon;<\/li>\n<li>Crunchy Data PostgreSQL Operator;<\/li>\n<li>Zalando Postgres Operator.<\/li>\n<\/ul>\n<p>\nLet's take a closer look at them.<\/p>\n<h2>Operator Selection<\/h2>\n<p>\nIn addition to the important features already mentioned above, we\u2014 as infrastructure operations engineers in Kubernetes\u2014 also expected the following from the operators:<\/p>\n<ul>\n<li> deployment from Git and using <noindex><a rel=\"nofollow\" href=\"https:\/\/kubernetes.io\/docs\/concepts\/extend-kubernetes\/operator\/#deploying-operators\">Custom Resources<\/a><\/noindex>;<\/li>\n<li> support for pod anti-affinity;<\/li>\n<li> node affinity or node selector installation;<\/li>\n<li> tolerations installation;<\/li>\n<li> availability of tuning options;<\/li>\n<li> understandable technologies and even teams.<\/li>\n<\/ul>\n<p>\nWithout delving into the details of each item (feel free to ask questions in the comments if anything remains unclear after reading the entire article), I will note in general that these parameters are needed for a more detailed description of the specialization of the cluster nodes so that we can order them for specific applications. This way, we can achieve an optimal balance in performance and cost.<\/p>\n<p>Now \u2014 let's talk about the PostgreSQL operators themselves.<\/p>\n<h3>1. Stolon<\/h3>\n<p>\n<noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/sorintlab\/stolon\">Stolon<\/a><\/noindex> from the Italian company Sorint.lab in <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/flant\/blog\/431500\/\">the aforementioned report<\/a><\/noindex> was considered a benchmark among database operators. This is a rather old project: its first public release was way back in November 2015(!), and the GitHub repository boasts nearly 3000 stars and over 40 contributors.<\/p>\n<p>And indeed, Stolon is an excellent example of thoughtful architecture:<\/p>\n<p><img decoding=\"async\" alt=\"A Brief Overview of PostgreSQL Operators for Kubernetes: Our Selection and Experience\" src=\"\/wp-content\/uploads\/2020\/09\/a34625c130bfd29c5645cf2417042de2.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\nDetails about the setup of this operator can be found in the report or <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/sorintlab\/stolon\/blob\/master\/doc\/architecture.md\">project documentation<\/a><\/noindex>. In general, it is enough to say that it can do everything described: failover, proxies for transparent client access, backups\u2026 Moreover, the proxies provide access through a single service endpoint \u2014 unlike the two other solutions discussed later (which have two services for database access).<\/p>\n<p>However, Stolon <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/sorintlab\/stolon\/issues\/463#issuecomment-379666733\">lacks Custom Resources<\/a><\/noindex>, which makes it impossible to deploy it so that you can quickly and easily \u2014 \"like hotcakes\" \u2014 create database instances in Kubernetes. Management is done through the utility <code>stolonctl<\/code>, deployment is done via a Helm chart, and user definitions are set in a ConfigMap.<\/p>\n<p>On one hand, it turns out that the operator isn't really an operator (since it doesn't use CRD). But on the other hand, it's a flexible system that allows you to configure resources in K8s as you see fit.<\/p>\n<p>In summary, it didn't seem optimal for us to create a separate chart for each database. So we started looking for alternatives.<\/p>\n<h3>2. Crunchy Data PostgreSQL Operator<\/h3>\n<p>\n<noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/CrunchyData\/postgres-operator\">The operator from Crunchy Data<\/a><\/noindex>, a young American startup, looked like a logical alternative. Its public history starts with the first release in March 2017, since then the GitHub repository has gained just under 1300 stars and over 50 contributors. The latest release from September has been tested for compatibility with Kubernetes 1.15\u20141.18, OpenShift 3.11+ and 4.4+, GKE, and VMware Enterprise PKS 1.3+.<\/p>\n<p>The architecture of the Crunchy Data PostgreSQL Operator also meets the stated requirements:<\/p>\n<p><img decoding=\"async\" alt=\"A Brief Overview of PostgreSQL Operators for Kubernetes: Our Selection and Experience\" src=\"\/wp-content\/uploads\/2020\/09\/343cd0494abae42fcf5b47c5bb4eb310.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nManagement is done through the utility <code>pgo<\/code>, however, it generates Custom Resources for Kubernetes. Therefore, as potential users, we were pleased with the operator:<\/p>\n<ul>\n<li> management via CRD;<\/li>\n<li> convenient user management (also through CRD);<\/li>\n<li> integration with other components <noindex><a rel=\"nofollow\" href=\"https:\/\/access.crunchydata.com\/documentation\/crunchy-postgres-containers\/4.3.1\/\">Crunchy Data Container Suite<\/a><\/noindex> \u2014 a specialized collection of container images for PostgreSQL and utilities for working with it (including pgBackRest, pgAudit, contrib extensions, etc.).<\/li>\n<\/ul>\n<p>\nHowever, attempts to start using the operator from Crunchy Data revealed several issues:<\/p>\n<ul>\n<li>There was no option for tolerations \u2014 only nodeSelector was provided.<\/li>\n<li>The created pods were part of a Deployment, even though we were deploying a stateful application. Unlike StatefulSets, Deployments cannot create disks.<\/li>\n<\/ul>\n<p>\nThis last drawback leads to amusing moments: in the test environment, it was possible to run 3 replicas with one disk <i>local storage<\/i>, resulting in the operator reporting that 3 replicas were running (although this was not the case).<\/p>\n<p>Another feature of this operator is its built-in integration with various auxiliary systems. For example, pgAdmin and pgBounce can be easily installed, while <noindex><a rel=\"nofollow\" href=\"https:\/\/access.crunchydata.com\/documentation\/postgres-operator\/4.4.0\/installation\/other\/ansible\/installing-metrics\/\">the documentation<\/a><\/noindex> pre-configured Grafana and Prometheus are under consideration. In the recent <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/CrunchyData\/postgres-operator\/releases\/tag\/v4.5.0-beta.1\">release 4.5.0-beta1<\/a><\/noindex> the improved integration with the project <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/CrunchyData\/pgmonitor\">pgMonitor<\/a><\/noindex>, which provides the operator with a visual representation of metrics for PgSQL 'out of the box'.<\/p>\n<p>Nevertheless, the strange choice of generated Kubernetes resources led us to seek an alternative solution.<\/p>\n<h3>3. Zalando Postgres Operator<\/h3>\n<p>\nWe have been familiar with Zalando\u2019s products for a long time: we have experience with Zalenium and, of course, we have tried <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/zalando\/patroni\">Patroni<\/a><\/noindex> \u2014 their popular HA solution for PostgreSQL. One of its authors, Alexey Klyukin, talked about the company\u2019s approach to creating the <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/zalando\/postgres-operator\">Postgres Operator<\/a><\/noindex> in the broadcast of <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/flant\/blog\/479438\/\">Postgres Tuesday #5<\/a><\/noindex>, and we liked it.<\/p>\n<p>This is the youngest solution among those discussed in the article: the first release was in August 2018. However, despite the small number of formal releases, the project has come a long way, already surpassing the Crunchy Data solution with 1300+ stars on GitHub and the highest number of contributors (70+).<\/p>\n<p>\u2018Under the hood\u2019 of this operator are time-tested solutions:<\/p>\n<ul>\n<li> Patroni and <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/zalando\/spilo\">Spilo<\/a><\/noindex> for management,<\/li>\n<li> <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/wal-e\/wal-e\">WAL-E<\/a><\/noindex> for backups,<\/li>\n<li> <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/pgbouncer\/pgbouncer\">PgBouncer<\/a><\/noindex> \u2014 as a connection pool.<\/li>\n<\/ul>\n<p>\nHere is how the architecture of the operator from Zalando is presented:<\/p>\n<p><img decoding=\"async\" alt=\"A Brief Overview of PostgreSQL Operators for Kubernetes: Our Selection and Experience\" src=\"\/wp-content\/uploads\/2020\/09\/c66f10c1a818592ecaacd2db4ce5ace5.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nThe operator is fully managed through Custom Resources, automatically creating a StatefulSet from containers that can then be customized by adding various sidecars to the pod. This is a significant advantage compared to the operator from Crunchy Data.<\/p>\n<p>Since we chose Zalando's solution from the three options considered, a further description of its capabilities will be presented below, along with practical application.<\/p>\n<h2>Practice with the Postgres Operator from Zalando<\/h2>\n<p>\nThe deployment of the operator is very simple: just download the latest release from GitHub and apply the YAML files from the directory <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/zalando\/postgres-operator\/tree\/master\/manifests\">manifests<\/a><\/noindex>. Alternatively, you can also use <noindex><a rel=\"nofollow\" href=\"https:\/\/operatorhub.io\/operator\/postgres-operator\">OperatorHub<\/a><\/noindex>.<\/p>\n<p>After installation, it's important to address the configuration of <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/zalando\/postgres-operator\/blob\/master\/docs\/reference\/operator_parameters.md#aws-or-gcp-interaction\">storage for logs and backups<\/a><\/noindex>. This is done through a ConfigMap <code>postgres-operator<\/code> in the namespace where you installed the operator. Once the storages are configured, you can deploy your first PostgreSQL cluster.<\/p>\n<p>For example, our standard deployment looks like this:<\/p>\n<pre><code class=\"plaintext\">apiVersion: acid.zalan.do\/v1\nkind: postgresql\nmetadata:\n name: staging-db\nspec:\n numberOfInstances: 3\n patroni:\n   synchronous_mode: true\n postgresql:\n   version: \"12\"\n resources:\n   limits:\n     cpu: 100m\n     memory: 1Gi\n   requests:\n     cpu: 100m\n     memory: 1Gi\n sidecars:\n - env:\n   - name: DATA_SOURCE_URI\n     value: 127.0.0.1:5432\n   - name: DATA_SOURCE_PASS\n     valueFrom:\n       secretKeyRef:\n         key: password\n         name: postgres.staging-db.credentials\n   - name: DATA_SOURCE_USER\n     value: postgres\n   image: wrouesnel\/postgres_exporter\n   name: prometheus-exporter\n   resources:\n     limits:\n       cpu: 500m\n       memory: 100Mi\n     requests:\n       cpu: 100m\n       memory: 100Mi\n teamId: staging\n volume:\n   size: 2Gi\n<\/code><\/pre>\n<p>\nThis manifest deploys a cluster of 3 instances with a sidecar in the form of <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/wrouesnel\/postgres_exporter\">postgres_exporter<\/a><\/noindex>, from which we collect application metrics. As you can see, everything is very simple, and if desired, you can create literally an unlimited number of clusters.<\/p>\n<p>It's also worth noting the <b>web panel for administration<\/b> \u2014 <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/zalando\/postgres-operator\/blob\/master\/docs\/operator-ui.md\">postgres-operator-ui<\/a><\/noindex>. It comes bundled with the operator and allows you to create and delete clusters, as well as work with backups created by the operator.<\/p>\n<p><img decoding=\"async\" alt=\"A Brief Overview of PostgreSQL Operators for Kubernetes: Our Selection and Experience\" src=\"\/wp-content\/uploads\/2020\/09\/2e9ae033a9817d101c843ce061aafdb5.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<i>List of PostgreSQL clusters<\/i><\/p>\n<p><img decoding=\"async\" alt=\"A Brief Overview of PostgreSQL Operators for Kubernetes: Our Selection and Experience\" src=\"\/wp-content\/uploads\/2020\/09\/74a65f7f301da14a0cb7bea6d5ef84ec.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<i>Backup management<\/i><\/p>\n<p>Another interesting feature is the support for <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/zalando\/postgres-operator\/blob\/master\/docs\/user.md#teams-api-roles\">Teams API<\/a><\/noindex>. This mechanism automatically creates <b>roles in PostgreSQL<\/b>, based on the retrieved list of usernames. After that, the API allows for returning a list of users for whom roles are automatically created.<\/p>\n<h3>Issues and solutions<\/h3>\n<p>\nHowever, the use of the operator soon revealed several significant drawbacks:<\/p>\n<ol>\n<li> lack of nodeSelector support;<\/li>\n<li> inability to disable backups;<\/li>\n<li> using the database creation function does not grant default privileges;<\/li>\n<li> periodically, documentation is lacking or is out of date.<\/li>\n<\/ol>\n<p>\nFortunately, many of these issues can be resolved. Let\u2019s start at the end \u2014 problems with <strong>documentation<\/strong>. <\/p>\n<p>You will likely encounter that it is not always clear how to specify a backup and how to connect the backup bucket to the Operator UI. This is briefly mentioned in the documentation, while a real description can be found in <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/zalando\/postgres-operator\/pull\/481\">PR<\/a><\/noindex>:<\/p>\n<ol>\n<li>you need to create a secret;<\/li>\n<li>pass it to the operator as a parameter <code>pod_environment_secret_name<\/code> in the CRD with the operator settings or in the ConfigMap (depending on how you decided to install the operator).<\/li>\n<\/ol>\n<p>\nHowever, it turned out that at this point it is impossible. That\u2019s why we have created <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/flant\/postgres-operator\">our version of the operator<\/a><\/noindex> with some additional third-party contributions. More about it \u2014 see below.<\/p>\n<p>If you pass backup parameters to the operator, namely \u2014 <code>wal_s3_bucket<\/code> and access keys in AWS S3, then it <strong>will back up everything<\/strong>: not only databases in production but also staging. We were not satisfied with that.<\/p>\n<p>In the parameter description for Spilo, which is the basic Docker wrapper for PgSQL when using the operator, it turned out: you can pass the parameter <code>WAL_S3_BUCKET<\/code> empty, thus disabling backups. Moreover, to our great joy, we found a <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/zalando\/postgres-operator\/pull\/908\">ready PR<\/a><\/noindex>, which we immediately accepted in our fork. Now it\u2019s enough just to add <code>enableWALArchiving: false<\/code> to the PostgreSQL cluster resource.<\/p>\n<p>Yes, it was possible to do otherwise by launching 2 operators: one for staging (without backups) and the other for production. But this way we managed to get by with one.<\/p>\n<p>Okay, we learned to grant S3 access to databases, and the backups began to go into storage. How can we make the backup pages work in the Operator UI?<\/p>\n<p><img decoding=\"async\" alt=\"A Brief Overview of PostgreSQL Operators for Kubernetes: Our Selection and Experience\" src=\"\/wp-content\/uploads\/2020\/09\/46169b53707b0bbb686798c655022f40.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nIn the Operator UI, you will need to add 3 variables:<\/p>\n<ul>\n<li> <code>SPILO_S3_BACKUP_BUCKET<\/code><\/li>\n<li> <code>AWS_ACCESS_KEY_ID<\/code><\/li>\n<li> <code>AWS_SECRET_ACCESS_KEY<\/code><\/li>\n<\/ul>\n<p>\nAfter that, backup management will become available, which in our case simplifies working with staging, allowing us to deliver snapshots from production without additional scripts.<\/p>\n<p>As an additional plus, the work with the Teams API and the extensive capabilities for creating databases and roles using the operator were mentioned. However, the created <strong>roles did not have default permissions.<\/strong>Accordingly, a user with read permissions could not read new tables.<\/p>\n<p>Why is that? Despite the fact that in the code <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/zalando\/postgres-operator\/blob\/master\/pkg\/cluster\/database.go#L42\">there are<\/a><\/noindex> necessary <code>GRANT<\/code>, they are not applied consistently. There are 2 methods: <code>syncPreparedDatabases<\/code> and <code>syncDatabases<\/code>. In <code>syncPreparedDatabases<\/code> \u2014 despite the fact that in the section <code>preparedDatabases<\/code> <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/zalando\/postgres-operator\/blob\/master\/manifests\/complete-postgres-manifest.yaml#L26\">there are<\/a><\/noindex> there is a condition <code>defaultRoles<\/code> and <code>defaultUsers<\/code> for creating roles, \u2014 default permissions are not applied. We are in the process of preparing a patch to ensure these permissions are applied automatically.<\/p>\n<p>And the last point in our relevant updates \u2014 <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/zalando\/postgres-operator\/pull\/975\">patch<\/a><\/noindex>, adding Node Affinity to the created StatefulSet. Our clients often prefer to cut costs by using spot instances, and it\u2019s definitely not advisable to host database services on them. This issue could also be addressed through tolerations, but having Node Affinity provides greater assurance.<\/p>\n<h3>What has been achieved?<\/h3>\n<p>\nAs a result of resolving the aforementioned issues, we forked the Postgres Operator from Zalando into <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/flant\/postgres-operator\">our repository<\/a><\/noindex>, where it is built with such useful patches. For added convenience, we collected the <noindex><a rel=\"nofollow\" href=\"https:\/\/hub.docker.com\/r\/flant\/postgres-operator\">Docker image<\/a><\/noindex>.<\/p>\n<p>List of PRs accepted in the fork:<\/p>\n<ul>\n<li><noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/zalando\/postgres-operator\/pull\/1066\">building a secure lightweight image for the operator in Docker<\/a><\/noindex>;<\/li>\n<li><noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/zalando\/postgres-operator\/pull\/908\">disabling backups<\/a><\/noindex>;<\/li>\n<li><noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/zalando\/postgres-operator\/pull\/1121\">updating resource versions for current k8s versions<\/a><\/noindex>;<\/li>\n<li><noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/zalando\/postgres-operator\/pull\/975\">implementing Node Affinity<\/a><\/noindex>.<\/li>\n<\/ul>\n<p>\nIt would be great if the community supports these PRs so they can be included in upstream with the next version of the operator (1.6).<\/p>\n<h2>Bonus! Success story with production migration<\/h2>\n<p>\nIf you are using Patroni, you can migrate live production to the operator with minimal downtime.<\/p>\n<p>Spilo allows for creating standby clusters through S3 storage with <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/wal-e\/wal-e\">Wal-E<\/a><\/noindex>, where the PgSQL binary log is first stored in S3 and then downloaded by the replica. But what if you <i>do not<\/i> use Wal-E in the old infrastructure? The solution to this problem has already <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/true_engineering\/blog\/437318\/\">been proposed<\/a><\/noindex> on Habr.<\/p>\n<p>Logical replication of PostgreSQL comes to the rescue. However, we won't go into detail about how to create publications and subscriptions because\u2026 our plan failed.<\/p>\n<p>The issue is that there were several heavily loaded tables in the database containing millions of rows, which were also constantly being added to and deleted. <noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgrespro\/12\/sql-createsubscription\">A simple subscription<\/a><\/noindex> with <code>copy_data<\/code>, where the new replica copies all content from the master, simply couldn\u2019t keep up with the master. The content copying worked for a week, but never caught up with the master. In the end, the issue was resolved thanks to <noindex><a rel=\"nofollow\" href=\"https:\/\/medium.com\/avitotech\/recovery-use-cases-for-logical-replication-in-postgresql-10-a1e6bab03072\">article<\/a><\/noindex> colleagues from Avito: data could be transferred using <code>pg_dump<\/code>I will describe our (slightly modified) version of this algorithm.<\/p>\n<p>The idea is that you can create a disabled subscription tied to a specific replication slot and then correct the transaction number. We had replicas available for production work. This is important because the replica will help create a consistent dump and continue to receive changes from the master.<\/p>\n<p>The following notation will be used in the subsequent commands describing the migration process for hosts:<\/p>\n<ol>\n<li><i>master<\/i> \u2014 source server;<\/li>\n<li><i>replica1<\/i> \u2014 streaming replica on old production;<\/li>\n<li><i>replica2<\/i> \u2014 new logical replica.<\/li>\n<\/ol>\n<p><\/p>\n<h3>Migration Plan<\/h3>\n<p>\n1. Create a subscription on the master for all tables in the schema <code>public<\/code> of the database <code>dbname<\/code>:<\/p>\n<pre><code class=\"bash\">psql -h master -d dbname -c \"CREATE PUBLICATION dbname FOR ALL TABLES;\"\n<\/code><\/pre>\n<p>\n2. Create a replication slot on the master:<\/p>\n<pre><code class=\"bash\">psql -h master -c \"select pg_create_logical_replication_slot('repl', 'pgoutput');\"\n<\/code><\/pre>\n<p>\n3. Stop replication on the old replica:<\/p>\n<pre><code class=\"bash\">psql -h replica1 -c \"select pg_wal_replay_pause();\"\n<\/code><\/pre>\n<p>\n4. Get the transaction number from the master:<\/p>\n<pre><code class=\"bash\">psql -h master -c \"select replay_lsn from pg_stat_replication where client_addr = 'replica1';\"\n<\/code><\/pre>\n<p>\n5. Take a dump from the old replica. We will do this in multiple streams to speed up the process:<\/p>\n<pre><code class=\"bash\">pg_dump -h replica1 --no-publications --no-subscriptions -O -C -F d -j 8 -f dump\/ dbname\n<\/code><\/pre>\n<p>\n6. Load the dump onto the new server:<\/p>\n<pre><code class=\"bash\">pg_restore -h replica2 -F d -j 8 -d dbname dump\/\n<\/code><\/pre>\n<p>\n7. After loading the dump, you can start replication on the streaming replica:<\/p>\n<pre><code class=\"bash\">psql -h replica1 -c \"select pg_wal_replay_resume();\"\n<\/code><\/pre>\n<p>\n7. Create a subscription on the new logical replica:<\/p>\n<pre><code class=\"bash\">psql -h replica2 -c \"create subscription oldprod connection 'host=replica1 port=5432 user=postgres password=secret dbname=dbname' publication dbname with (enabled = false, create_slot = false, copy_data = false, slot_name='repl');\"\n<\/code><\/pre>\n<p>\n8. Obtain <code>oid<\/code> of the subscription:<\/p>\n<pre><code class=\"bash\">psql -h replica2 -d dbname -c \"select oid, * from pg_subscription;\"\n<\/code><\/pre>\n<p>\n9. Assuming you received <code>oid=1000<\/code>. Apply the transaction number to the subscription:<\/p>\n<pre><code class=\"bash\">psql -h replica2 -d dbname -c \"select pg_replication_origin_advance('pg_1000', 'AA\/AAAAAAAA');\"\n<\/code><\/pre>\n<p>\n10. Start replication:<\/p>\n<pre><code class=\"bash\">psql -h replica2 -d dbname -c \"alter subscription oldprod enable;\"\n<\/code><\/pre>\n<p>\n11. Check the subscription status; replication should be working:<\/p>\n<pre><code class=\"bash\">psql -h replica2 -d dbname -c \"select * from pg_replication_origin_status;\"\npsql -h master -d dbname -c \"select slot_name, restart_lsn, confirmed_flush_lsn from pg_replication_slots;\"\n<\/code><\/pre>\n<p>\n12. Once replication has started and databases are synchronized, switching can be performed.<\/p>\n<p>13. After disabling replication, the sequences need to be fixed. This is well described <noindex><a rel=\"nofollow\" href=\"https:\/\/wiki.postgresql.org\/wiki\/Fixing_Sequences\">in an article on wiki.postgresql.org<\/a><\/noindex>.<\/p>\n<p>With such a plan, the switch went smoothly with minimal delays.<\/p>\n<h2>Conclusion<\/h2>\n<p>\nKubernetes operators simplify various tasks by reducing them to the creation of K8s resources. However, after achieving remarkable automation with their help, it is important to remember that it can also bring unexpected nuances, so choose your operators wisely.<\/p>\n<p>After reviewing the three most popular Kubernetes operators for PostgreSQL, we chose the project from Zalando. We encountered some challenges, but the result was truly satisfying, so we plan to extend this experience to some other PgSQL installations. If you have experience with similar solutions, we would love to see the details in the comments!<\/p>\n<h2>P.S.<\/h2>\n<p>\nAlso read in our blog:<\/p>\n<ul>\n<li> \u00ab<noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/flant\/blog\/431500\/\">Databases and Kubernetes (Overview and Presentation Video)<\/a><\/noindex>\u00bb;<\/li>\n<li> \u00ab<noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/flant\/blog\/479438\/\">Postgres Tuesday #5: PostgreSQL and Kubernetes. CI\/CD. Test Automation<\/a><\/noindex>\u00bb;<\/li>\n<li> \u00ab<noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/flant\/blog\/480722\/\">A Story about the Redis Operator in K8s and a Brief Overview of Utilities for Analyzing Data from This Database<\/a><\/noindex>\u00bb.<\/li>\n<\/ul>\n<p>Source: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/flant\/blog\/520616\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u0412\u0441\u0451 \u0447\u0430\u0449\u0435 \u043e\u0442 \u043a\u043b\u0438\u0435\u043d\u0442\u043e\u0432 \u043f\u043e\u0441\u0442\u0443\u043f\u0430\u044e\u0442 \u0442\u0430\u043a\u0438\u0435 \u0437\u0430\u043f\u0440\u043e\u0441\u044b: \u00ab\u0425\u043e\u0442\u0438\u043c \u043a\u0430\u043a Amazon RDS, \u043d\u043e \u0434\u0435\u0448\u0435\u0432\u043b\u0435\u00bb; \u00ab\u0425\u043e\u0442\u0438\u043c \u043a\u0430\u043a RDS, \u043d\u043e \u0432\u0435\u0437\u0434\u0435, \u0432 \u043b\u044e\u0431\u043e\u0439 \u0438\u043d\u0444\u0440\u0430\u0441\u0442\u0440\u0443\u043a\u0442\u0443\u0440\u0435\u00bb. \u0427\u0442\u043e\u0431\u044b \u0440\u0435\u0430\u043b\u0438\u0437\u043e\u0432\u0430\u0442\u044c \u043f\u043e\u0434\u043e\u0431\u043d\u043e\u0435 managed-\u0440\u0435\u0448\u0435\u043d\u0438\u0435 \u043d\u0430 Kubernetes, \u043c\u044b \u043f\u043e\u0441\u043c\u043e\u0442\u0440\u0435\u043b\u0438 \u043d\u0430 \u0442\u0435\u043a\u0443\u0449\u0435\u0435 \u0441\u043e\u0441\u0442\u043e\u044f\u043d\u0438\u0435 \u043d\u0430\u0438\u0431\u043e\u043b\u0435\u0435 \u043f\u043e\u043f\u0443\u043b\u044f\u0440\u043d\u044b\u0445 \u043e\u043f\u0435\u0440\u0430\u0442\u043e\u0440\u043e\u0432 \u0434\u043b\u044f PostgreSQL (Stolon, \u043e\u043f\u0435\u0440\u0430\u0442\u043e\u0440\u044b \u043e\u0442 Crunchy Data \u0438 Zalando) \u0438 \u0441\u0434\u0435\u043b\u0430\u043b\u0438 \u0441\u0432\u043e\u0439 \u0432\u044b\u0431\u043e\u0440. \u042d\u0442\u0430 \u0441\u0442\u0430\u0442\u044c\u044f \u2014 \u043f\u043e\u043b\u0443\u0447\u0435\u043d\u043d\u044b\u0439 \u043d\u0430\u043c\u0438 \u043e\u043f\u044b\u0442 \u0438 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":95323,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-95322","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.2 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u0412\u0441\u0451 \u0447\u0430\u0449\u0435 \u043e\u0442 \u043a\u043b\u0438\u0435\u043d\u0442\u043e\u0432.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/kratkij-obzor-operatorov-postgresql-dlya-kubernetes-nash-vybor-i-opyt\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.2\" \/>\n\t\t<meta property=\"og:locale\" content=\"en_US\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47\u041a\u0440\u0430\u0442\u043a\u0438\u0439 \u043e\u0431\u0437\u043e\u0440 \u043e\u043f\u0435\u0440\u0430\u0442\u043e\u0440\u043e\u0432 PostgreSQL \u0434\u043b\u044f Kubernetes, \u043d\u0430\u0448 \u0432\u044b\u0431\u043e\u0440 \u0438 \u043e\u043f\u044b\u0442 | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u0412\u0441\u0451 \u0447\u0430\u0449\u0435 \u043e\u0442 \u043a\u043b\u0438\u0435\u043d\u0442\u043e\u0432.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/kratkij-obzor-operatorov-postgresql-dlya-kubernetes-nash-vybor-i-opyt\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2020-09-28T05:42:39+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-09-28T05:42:39+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47A Brief Overview of PostgreSQL Operators for Kubernetes, Our Choice and Experience | ProHoster","description":"More often from clients.","canonical_url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/kratkij-obzor-operatorov-postgresql-dlya-kubernetes-nash-vybor-i-opyt","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"en_US","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u041a\u0440\u0430\u0442\u043a\u0438\u0439 \u043e\u0431\u0437\u043e\u0440 \u043e\u043f\u0435\u0440\u0430\u0442\u043e\u0440\u043e\u0432 PostgreSQL \u0434\u043b\u044f Kubernetes, \u043d\u0430\u0448 \u0432\u044b\u0431\u043e\u0440 \u0438 \u043e\u043f\u044b\u0442 | ProHoster","og:description":"\u0412\u0441\u0451 \u0447\u0430\u0449\u0435 \u043e\u0442 \u043a\u043b\u0438\u0435\u043d\u0442\u043e\u0432.","og:url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/kratkij-obzor-operatorov-postgresql-dlya-kubernetes-nash-vybor-i-opyt","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-09-28T05:42:39+00:00","article:modified_time":"2020-09-28T05:42:39+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"95322","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 11:08:22","updated":"2022-09-30 21:12:08","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/95322","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=95322"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/95322\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media\/95323"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=95322"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=95322"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=95322"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}