Waiting for PostgreSQL 10 – Logical replication

On 20th of January, Peter Eisentraut committed patch:

Logical replication
 
- Add PUBLICATION catalogs and DDL
- Add SUBSCRIPTION catalog and DDL
- Define logical replication protocol and output plugin
- Add logical replication workers
 
From: Petr Jelinek
Reviewed-by: Steve Singer
Reviewed-by: Andres Freund
Reviewed-by: Erik Rijkers
Reviewed-by: Peter Eisentraut

Continue reading Waiting for PostgreSQL 10 – Logical replication

Major-version upgrading with minimal downtime

There is this idea that from 9.5 we will be able to upgrade pg clusters to newer, major, version, without downtime (well, almost), thanks to magic of logical replication.

Sounds very promising, so I figured – I'll test it. To some extent at least.

Continue reading Major-version upgrading with minimal downtime

Concurrent REINDEX of all indexes in database

Recent release of new versions of PostgreSQL suggests that you do reindex of all indexes. But this will take a while, and since we don't actually have ‘REINDEX CONCURRENTLY' command – it's a bit tricky.

So, since I will be doing this on several databases, decided to write a script that will handle the work for me.

Continue reading Concurrent REINDEX of all indexes in database

Waiting for 9.0 – pg_upgrade

On May, 12ve, Bruce Momjian committed new contrib module for 9.0 – pg_upgrage.

As I understand – this is what was available before as pg-migrator.

If you're not familiar with it – it's a tool that allows upgrade of $PGDATA from some version to some version. What's the use case? Let's assume you have this 200GB database working as 8.3, and you'd like to go to 8.4 (or 9.0). Normal way is pg_dump + pg_restore – which will take some time. With pg-migrate/pg_upgrade it should be faster, and easier. So, let's play with it.

Continue reading Waiting for 9.0 – pg_upgrade