Postgres replication


Being DevOps requires you to have an expertise as a Database Administrator (DBA). For that reason I started a project where I had to implement Postgres database replication. It has proven to be not as easy as I anticipated. Postgres documentation has indepth info about bits and pieces of Postgres administration, however there was no straight forward guide that explained how to do a certain thin from the beginning to the end.

Environment

For this task I used a ready-made vagrant box for python development that already has a plenty of databases installed inside. The repo:

  • https://github.com/wharton/python-vagrant-centos7

I made two identical vagrant repos with only difference being the hostnames and folder names. They are bridged to my home 192.168.1.0/24 LAN which they use to replicate the database one to another.



The database is located in regular folder with distinct permissions, that is only postgres user has access to it and a root. Config files describe the database handling for postgres.


Write ahead log (WAL) is the mechanism for database replication in postgres. Before changes are applied to the database, they are logged ahead, hence the name. This config manipulates the WAL. In this instance I configured master to have the max of 8 connections.


Here I configure the whitelist for database connections. From another hosts, that be slaves.


There are three modes for database replication, hot-standby, replica, streaming. From stack overflow: "Hot Standby" is the state the replica is in and means it is available for read-only queries. A replica can also "just" be a standby, but then it does not allow queries, it just sits there and waits to take over. A replica needs to have exactly the same data as the master and the way the data is sent from the master to the replica is what "streaming replication" refers to. A hot standby can also be setup using log shipping (sometimes also referred to as "continuous WAL archiving") or even streaming replication together with log shipping. n standby mode, the server continuously applies WAL received from the master server. The standby server can read WAL from a WAL archive or directly from the master over a TCP connection (streaming replication).


It wasn't particularly easy to figure replication for postgres. I used a lot of references.



Here's the full list of references:

  • https://github.com/wharton/python-vagrant-centos7
  • https://medium.com/@perwagnernielsen/vagrant-ansible-and-postgresql-10-398e1e472349
  • https://www.postgresql.org/files/documentation/pdf/12/postgresql-12-A4.pdf
  • https://wiki.dieg.info/rezervnoe_kopirovanie_i_vosstanovlenie_v_postgresql
  • https://cloud.google.com/community/tutorials/setting-up-postgres-hot-standby
  • https://severalnines.com/database-blog/postgresql-replication-setup-maintenance-using-ansible
  • https://www.postgresql.fastware.com/blog/where-and-how-is-your-data-actually-stored-on-disk
  • https://www.opsdash.com/blog/postgresql-wal-archiving-backup.html
  • https://www.opsdash.com/blog/postgresql-streaming-replication-howto.html
  • http://www.postgresqltutorial.com/
  • https://www.linode.com/docs/databases/postgresql/how-to-install-postgresql-relational-databases-on-centos-7/
  • https://youtube.com/watch?v=5BeC1aD4z8E
  • https://estl.tech/replication-and-failover-in-postgresql-5ad68704d834
  • http://philipmcclarence.com/setting-up-a-postgres-test-cluster-in-vagrant/
  • https://wiki.postgresql.org/wiki/PostgreSQL_For_Development_With_Vagrant
  • https://www.howtoforge.com/tutorial/postgresql-replication-on-ubuntu-15-04/
  • https://portworx.com/postgres-kubernetes/
  • https://medium.com/@singh.umesh30/best-30-postgresql-interview-questions-and-answers-2019-5de922b4b298
  • https://www.postgresql.org/docs/10/tutorial.html
  • https://www.onlineinterviewquestions.com/postgresql-interview-questions/
  • https://www.zeolearn.com/interview-questions/postgres
  • https://www.2ndquadrant.com/en/blog/managing-postgresql-devops-environment/
  • https://medium.com/mrx-technical-journey/devops-postgres-part-1-97d7111e3ca1
  • https://www.oreilly.com/library/view/postgresql-9-administration/9781849510288/
  • https://momjian.us/main/writings/pgsql/administration.pdf
  • http://www.postgresqltutorial.com/what-is-postgresql/