Gülçin Yıldırım 156398IASM
Master's Thesis, June 2017
Supervisor: Tarmo Robal, PhD
PostgreSQL
252
Database
208
Cloud
116
Upgrade
50
Offline Conversion
Online Conversion
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Metric (1st case) | pg_dump/pg_restore | pg_upgrade | pglupgrade |
---|---|---|---|
Primary Downtime | 00:24:27 | 00:16:25 | 00:00:03 |
Partial cluster HA | 00:24:27 | 00:28:56 | 00:00:03 |
Full cluster capacity | 01:02:27 | 00:28:56 | 00:38:00 |
Length of upgrade | 01:02:27 | 00:28:56 | 01:38:10 |
Extra disk space | 800 MB | 27 GB | 10 GB |
Metric (2nd case) | pg_dump/pg_restore | pg_upgrade | pglupgrade |
---|---|---|---|
Primary Downtime | 00:23:52 | 00:17:03 | 00:00:05 |
Partial cluster HA | 00:23:52 | 00:54:29 | 00:00:05 |
Full cluster capacity | 00:23:52 | 03:19:16 | 00:00:05 |
Length of upgrade | 00:23:52 | 03:19:16 | 01:02:10 |
Extra disk space | 800 MB | 27 GB | 10 GB |
Database clusters can be upgraded with minimal downtime without users being affected while the upgrade is happening.
An application can still respond to the request only with a small drop in performance.
Thesis and the case studies prove that pglupgrade minimizes the downtime to the level of 3-5 seconds.
THE GOAL: Minimize the downtime for database upgrades, specifically on PostgreSQL
METHODOLOGY: Engineer logical replication as an upgrade method
IMPLEMENTATION: Develop pglupgrade tool to automate the upgrade process using Ansible
CASE STUDIES: Benchmark on two different PostgreSQL clusters in cloud
RESULTS & CONCLUSION: Near-Zero downtime achieved without affecting the users
[old-primary]
54.171.211.188
[new-primary]
54.246.183.100
[old-standbys]
54.77.249.81
54.154.49.180
[new-standbys:children]
old-standbys
[pgbouncer]
54.154.49.180
Inventory file hosts.ini
$ ansible-playbook -i hosts.ini pglupgrade.yml
Running pglupgrade playbook
- name: Switch the pgbouncer (and applications) to new primary
hosts: pgbouncer
become: true
become_user: postgres
vars_files:
- config.yml
roles:
- role: pgbouncer
tags:
- pgbouncer
pgbouncer play in pglupgrade playbook
remove
add