Upgrading 9.2 to 9.6 questions - Mailing list pgsql-general

From Lucas Possamai
Subject Upgrading 9.2 to 9.6 questions
Date
Msg-id CAE_gQfVhsfpc6YAuuS=v6_P_7RigXbmfT9uchrvm07a0U6mBfQ@mail.gmail.com
Whole thread Raw
List pgsql-general
Hi.

We have a setup of 3 Postgres 9.2 nodes (1x master, 2x slaves) running on EC2 instances in AWS. We want to upgrade to 9.6 so we can move to Amazon RDS (it requires at least a 9.3.5 version).
As far as I know, we have 3 options here:

  1. From 9.2 on EC2 to RDS
    1. pg_dump on 9.2
    2. Restore it in RDS (might not work; haven't tested it)
  2. Get Slony replication working
    1. Setup slony in our 9.2 nodes.
    2. Get a fresh Postgres installation running Postgres 9.6
    3. Copy the database over (pg_basebackup)
    4. Start the replication (master 9.2 --> slave 9.2 --> slave 9.6)
    5. failover to 9.6
    6. re-create slaves from 9.6
  3. From 9.2 to 9.6 on EC2; then to RDS:
    1. upgrade the master 9.2 to 9.6
    2. re-create all slaves with 9.6
    3. after a while (months/weeks), use DMS or pg_dump/pg_restore and go for RDS
Because of downtime, Step 2 seems more with what we wanna go forward.

Questions are: 
  • At the moment I am using native Postgres streaming replication with two slaves. Do I need to install Slony on my master, and get rid of the native tool? and then re-create the slaves with slony?
  • Slony or Bucardo are trigger-based replication solutions. That means DDL changes won't be propagated. This is not good as new tables can be added at any time. Isn't there a workaround for this?
Thanks!

pgsql-general by date:

Previous
From: "David Kremer"
Date:
Subject: Java's org.postgresql.util.PSQLState is missing common PostgreSQLError Codes
Next
From: Howard News
Date:
Subject: Re: Old tsearch functions