PostgreSQL – Replication

postgresqlreplication

I have several questions about the (streaming) replication in PostgreSQL 10:

  1. In case of a failover, how should I rebuild the standby ? Do I
    have to take a backup of the new primary and rebuild the new standby (in this case, I'm sure to have the latest version of all the file in my standby) ? Or do I have to take the backup of the old primary, restore it and apply some WAL (the restore operation will be shorted in this case) ? Both solutions work and both solutions have theirs pros and cons.
  2. The modifications in the configurations files
    aren't replicated (postgresql.conf, pg_hba.conf). Do you have any
    solutions/feedbacks to keep the configuration up to date in the
    standby database ?

Thanks for your help,

Best Answer

For converting a failed master into a new replica, you could look into pg_rewind. I have not used it, but my understanding is that this is its entire reason for being. I think it would be simpler and safer to simply throw away the failed master and reclone from the new master with pg_basebackup, but that could certainly be a bit hard on the network bandwidth.

For keeping configuration files in sync, I don't think it would be reasonable to create an elaborate tool just for PostgreSQL. There are already-built elaborate tools for configuration file management, such as Chef, CFEngine, and Puppet, and they should work for PostgreSQL config files the same way as they work for apache, sendmail, etc. Personally, every time I deploy a config change to production master (maybe twice a year?) I just ask myself "Should this change also go to the replica?" and if the answer is "yes" I go make the change. If I wanted to go beyond that, I would use an established configuration management tool. But there is one trick I use for backing up the config files. When you use pg_basebackup -Ft -z -D backup_dir for a single-tablespace database, it creates a directory backup_dir with just a single base.tar.gz file in it. If I change a config file and don't feel like taking a new base backup, I just copy the config file into that directory to live beside the base.tar.gz file. If you have a restore/clone script, it is pretty easy to have it copy any 'loose' files it finds in the backup directory to overwrite any that it just unpacked from tar.

Related Question