Deployment scenarios

1 Physical server pair to serve all Airflow instances

This involves using two physical servers that are already deployed but were previously earmarked for hosting the Analytics MySQL Meta databases that are currently hosted on an-coord100[1-2]

The ticket for this planned migration work is: T284150: Bring an-mariadb100[12] into service

Pros

• High performance option
• Instant hardware provision (because it's already installed and insetup)
• Ready for HA with a pair of servers

Cons

• Introduces further delay to the splitting of MariaDB from the an-coord role

2 Virtual server pair to serve all Airflow instances

This would require us to create a new pair of VMs to support PostgreSQL (primary and standby) - Due to the nature of ganeti, we would be limited to a lower specification of server

Pros

• Low waiting time to deploy VMs
• No impact on MariaDB splitting role from the an-coord role
• Ready for HA with a pair of servers

Cons

• Lower performance/capacity option

3 PostgreSQL instance per airflow instance

This would require us adding a postgreSQL instance to each airflow VM

Pros

• No shared database backend across Airflow instances

Cons

• Lower performance option
• PostgreSQL shares the same resources as the machine hosting the airflow scheduler, web server, executor
• Not HA ready

Going with Option 1

1 Physical server pair to serve all Airflow instances

This involves using two physical servers that are already deployed but were previously earmarked for hosting the Analytics MySQL Meta databases that are currently hosted on an-coord100[1-2]

The ticket for this planned migration work is: T284150: Bring an-mariadb100[12] into service

Pros

• High performance option
• Instant hardware provision (because it's already installed and insetup)
• Ready for HA with a pair of servers

Cons

• Introduces further delay to the splitting of MariaDB from the an-coord role

As stated in T319440#8320425 we have met to discuss these options and decided to use the existing an-db100[1-2] servers for the new shared PostgreSQL instance. That's option 1 above.
We will replace an-db100[1-2] with an-mariadb100[1-2] (being purchased in T319437) so that the existing plan to extract the analytics mysql meta database from the an-coord role is not negatively impacted by this decision.

We did like the de-coupling of Airflow instances inherent in option 3 and if we already had a way to run PostgreSQL in containers (on Kubernetes) then we might well have taken this option. However, we didn't feel that running at least five new PostgreSQL instances on the Airflow VMs was a worthwhile trade-off for this decoupling. Perhaps when we have support for PersistentVolumeClaims in Kubernetes this might be an even more suitable option, but for now option 1 seems like the best pragmatic decision.

I will get on with creating patches to deploy PostgreSQL to an-db100[1-2].

Thanks for all the notes!!
One question: I don't understand what replacing an-db100[1-2] with an-mariadb100[1-2] means. Does it mean that we will use the already provisioned machines an-db100[1-2] for PostreSQL and then purchase 2 new machines, an-mariadb100[1-2], for all the mariadb databases? (sorry I have no permits for T319437)

In T319440#8322081, @mforns wrote:

Thanks for all the notes!!
One question: I don't understand what replacing an-db100[1-2] with an-mariadb100[1-2] means. Does it mean that we will use the already provisioned machines an-db100[1-2] for PostreSQL and then purchase 2 new machines, an-mariadb100[1-2], for all the mariadb databases? (sorry I have no permits for T319437)

Yes, that's exactly right @mforns. We previously purchased an-db100[1-2] and they are ready for use now. They are in the insetup role in puppet, which means we can just apply a new role to them and start customizing.
They were ready for the project to move MariaDB away from an-coord100[1-2] in T284150: Bring an-mariadb100[12] into service but that project get de-prioritized and as such these servers have been unused since being racked.

By purchasing a new pair for this project (an-mariadb100[1-2]) then we can still proceed with migrating MariaDB away from an-coord100[1-2], but at a more convenient time. In the meantime we can use the existing hardware (an-db100[1-2]) for PostgreSQL.

I think that this ticket and T319502: Consider migrating the Airflow MariaDB databases to Postgres are probably duplicates and should be merged. @EChetty?

Thanks @BTullis!

In T319440#8324569, @BTullis wrote:

I think that this ticket and T319502: Consider migrating the Airflow MariaDB databases to Postgres are probably duplicates and should be merged. @EChetty?

It seems the above ticket is more about trying to migrate the data to Postgres

Change 844460 had a related patch set uploaded (by Btullis; author: Btullis):

[labs/private@master] Add postgresql replication password for new an-db servers

https://gerrit.wikimedia.org/r/844460

Change 844460 merged by Btullis:

[labs/private@master] Add postgresql replication password for new an-db servers

https://gerrit.wikimedia.org/r/844460

Change 843502 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/puppet@production] Add initial manifests for postgresql on an-db100[1-2]

https://gerrit.wikimedia.org/r/843502

Cookbook cookbooks.sre.hosts.reimage was started by btullis@cumin1001 for host an-db1001.eqiad.wmnet with OS bullseye

Cookbook cookbooks.sre.hosts.reimage started by btullis@cumin1001 for host an-db1001.eqiad.wmnet with OS bullseye completed:

• an-db1001 (PASS)
  • Downtimed on Icinga/Alertmanager
  • Disabled Puppet
  • Removed from Puppet and PuppetDB if present
  • Deleted any existing Puppet certificate
  • Removed from Debmonitor if present
  • Forced PXE for next reboot
  • Host rebooted via IPMI
  • Host up (Debian installer)
  • Host up (new fresh bullseye OS)
  • Generated Puppet certificate
  • Signed new Puppet certificate
  • Run Puppet in NOOP mode to populate exported resources in PuppetDB
  • Found Nagios_host resource for this host in PuppetDB
  • Downtimed the new host on Icinga/Alertmanager
  • Removed previous downtime on Alertmanager (old OS)
  • First Puppet run completed and logged in /var/log/spicerack/sre/hosts/reimage/202210191339_btullis_2240267_an-db1001.out
  • Checked BIOS boot parameters are back to normal
  • configmaster.wikimedia.org updated with the host new SSH public key for wmf-update-known-hosts-production
  • Rebooted
  • Automatic Puppet run was successful
  • Forced a re-check of all Icinga services for the host
  • Icinga status is optimal
  • Icinga downtime removed
  • Updated Netbox data from PuppetDB

Cookbook cookbooks.sre.hosts.reimage was started by btullis@cumin1001 for host an-db1002.eqiad.wmnet with OS bullseye

Cookbook cookbooks.sre.hosts.reimage started by btullis@cumin1001 for host an-db1002.eqiad.wmnet with OS bullseye completed:

• an-db1002 (PASS)
  • Downtimed on Icinga/Alertmanager
  • Disabled Puppet
  • Removed from Puppet and PuppetDB if present
  • Deleted any existing Puppet certificate
  • Removed from Debmonitor if present
  • Forced PXE for next reboot
  • Host rebooted via IPMI
  • Host up (Debian installer)
  • Host up (new fresh bullseye OS)
  • Generated Puppet certificate
  • Signed new Puppet certificate
  • Run Puppet in NOOP mode to populate exported resources in PuppetDB
  • Found Nagios_host resource for this host in PuppetDB
  • Downtimed the new host on Icinga/Alertmanager
  • Removed previous downtime on Alertmanager (old OS)
  • First Puppet run completed and logged in /var/log/spicerack/sre/hosts/reimage/202210191601_btullis_2267084_an-db1002.out
  • Checked BIOS boot parameters are back to normal
  • configmaster.wikimedia.org updated with the host new SSH public key for wmf-update-known-hosts-production
  • Rebooted
  • Automatic Puppet run was successful
  • Forced a re-check of all Icinga services for the host
  • Icinga status is optimal
  • Icinga downtime removed
  • Updated Netbox data from PuppetDB

Change 843502 merged by Btullis:

[operations/puppet@production] Add postgresql to an-db100[1-2]

https://gerrit.wikimedia.org/r/843502

OK, this first change is now deployed so we have a postgresql 13 service running on an-db100[1-2]

btullis@an-db1001:~$ sudo su - postgres
postgres@an-db1001:~$ psql
psql (13.8 (Debian 13.8-0+deb11u1))
Type "help" for help.

postgres=#

I will check the replication and monitoring aspects. @jcrespo is helping me to check the backup elements.

Once these are working, I will make another CR to add the various netbox databases.

I have initiated the replication with the following command.

btullis@cumin1001:~$ sudo cookbook sre.postgresql.postgres-init --replica an-db1002.eqiad.wmnet --task-id T319440 --reason "Initial setup of postgres replication"

One thing Re: Ensure that we have appropriate availability guarantees in place for PostgreSQL - (i.e. replication, failover, backup, restore, monitoring)

Data persistence team does not support backups of postgres, and in fact there are huge issues with the current WMF postgres backup workflow (T316655), so -given we have not been involved in this decision-, we won't be able to support this- it will be data engineering's responsability to handle monitoring, backups and testing. Unlike with PostgreSQL, we have proper tooling for automating, monitoring and backing up MySQL or MariaDB hosts, due to MediaWiki needs, but that is not true for postgres. If backups fail to recover, it will be the sole DE's team responsibility.

Having said that, external file backups ran yesterday at 4am:

root@backup1001:~$ check_bacula.py an-db1001.eqiad.wmnet-Monthly-1st-Thu-productionEqiad-data-engineering-postgres
id: 479917, ts: 2022-10-21 04:05:00, type: F, status: T, bytes: 3488
✔️

A restore job was just run and sent to an-db1001:/var/tmp/bacula-restores please check everything that should be there is there and is able to be restored.

Please delete the restore files when done.

Thanks Jaime.

Data persistence team does not support backups of postgres, and in fact there are huge issues with the current WMF postgres backup workflow (T316655), so -given we have not been involved in this decision-, we won't be able to support this- it will be data engineering's responsability to handle monitoring, backups and testing. Unlike with PostgreSQL, we have proper tooling for automating, monitoring and backing up MySQL or MariaDB hosts, due to MediaWiki needs, but that is not true for postgres. If backups fail to recover, it will be the sole DE's team responsibility.

This is fully understood and we appreciate the clarity. Apologies if you feel that we should have involved the Data Persistence team more in the decision making process.
Sadly, the requirement to switch Airflow from MariaDB to PostgreSQL came to us rather out of the blue, so this is something of a pragmatic decision on our part, but we will take full responsibility for the availability guarantees of the service.

I've checked the restored files and I'm happy that they can be reloaded to postgres. I've subsequently deleted an-db1001:/var/tmp/bacula-restores as requested.

Great!

The main issue I wanted to surface is that there are ongoing issues with postgres backup workflow, and wanted you and your team to be aware of those! We are not ready to provide a guarantee that those would work. We can provide it with other technologies after long running (multi-year) projects to prepare and build them properly.

Change 845559 had a related patch set uploaded (by Btullis; author: Btullis):

[labs/private@master] Add dummy passwords for the airflow database users

https://gerrit.wikimedia.org/r/845559

Change 845560 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/puppet@production] Add a simple mechanism for creating postgresql users and databases

https://gerrit.wikimedia.org/r/845560

Change 845559 merged by Btullis:

[labs/private@master] Add dummy passwords for the airflow database users

https://gerrit.wikimedia.org/r/845559

Change 845560 merged by Btullis:

[operations/puppet@production] Add a simple mechanism for creating postgresql users and databases

https://gerrit.wikimedia.org/r/845560

Change 849122 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/puppet@production] Open up the postrges service to the analytics vlans

https://gerrit.wikimedia.org/r/849122

Change 849122 merged by Btullis:

[operations/puppet@production] Open up the postrges service to the analytics vlans

https://gerrit.wikimedia.org/r/849122

Change 849500 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/puppet@production] Add a postgres user with an IPv6 RFC 4193 host match

https://gerrit.wikimedia.org/r/849500

Change 849500 merged by Btullis:

[operations/puppet@production] Add a postgres user with an IPv6 RFC 4193 host match

https://gerrit.wikimedia.org/r/849500

Change 850185 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/puppet@production] Add a postgres user with our IPv6 network address

https://gerrit.wikimedia.org/r/850185

Change 850185 merged by Btullis:

[operations/puppet@production] Add a postgres user with our IPv6 network address

https://gerrit.wikimedia.org/r/850185

Change 851690 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/puppet@production] Add a postgresql database for the airflow development

https://gerrit.wikimedia.org/r/851690

Change 851690 merged by Btullis:

[operations/puppet@production] Add a postgresql database for the airflow development

https://gerrit.wikimedia.org/r/851690

Change 891804 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/puppet@production] Ensure that the airflow database names match existing conventions

https://gerrit.wikimedia.org/r/891804

Change 891804 merged by Btullis:

[operations/puppet@production] Ensure that the airflow database names match existing conventions

https://gerrit.wikimedia.org/r/891804