PostegreSQL 9.2 to 9.6

[Lucas Possamai]

Hi guys.

We need to upgrade (asap) our PostgreSQL 9.2 cluster. We run our cluster on
AWS EC2 instances, and want to upgrade to RDS 9.6. Once in RDS, it makes
easier for us to major upgrade to 11.

Since this upgrade must be done with minimal downtime, my original idea was
to use Bucardo .

Does anybody have any better idea? please share.
Just wanna make sure I'm going on the right path.

Cluster size is 2TB.

Cheers
Lucas

PG 9.2 slave restarted - cache not impacted

[Lucas Possamai]

Hi guys,

I'm a bit confused about PG cache.

I have a PostgreSQL 9.2 cluster (yes, we're planning on upgrading it to 12)
with a master and a slave database.

The application is sending all read requests to the slave, where the master
processes the writes.

A while ago we had to restart the master database server, and the
application was slow for a couple of days while PG cache was warmed up.
Yesterday, we had to restart the slave database server and we did not have
the same problem.

I would like to understand why?

Thanks!

Upgrading 9.2 to 9.6 questions

[Lucas Possamai]

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!

PostgreSQL Dependency tree

[Lucas Possamai]

Hi community!

I need to understand the dependency tree in my DB. The order of INSERT into
each table based on FKs.

I know there is the pg-depend catalog
, but I
figured before spending some time writting the SQL statement, I would ask
if anybody has any query or tooling that provides the dependency tree ready
to go?

Thanks in advance!
Lucas