1

As I understand, pg_repack creates a temporary 'mirror' table (table B) and copies the rows from the original table (table A) and re-indexes them and then replaces the original with the mirror. The mirroring step creates a lot of noise with logical replication (a lot of inserts at once), so I'd like to ignore the mirror table from being replicated.

I'm a bit confused with what happens during the switch over though. Is there a risk with losing some changes? I don't think there is since all actual writes are still going to the original table before and after the switch, so it should be safe right?

We're running Postgres 10.7 on AWS Aurora, using wal2json as the output plugin for replication.

john2x
  • 22,546
  • 16
  • 57
  • 95

2 Answers2

1

I have neither used pg_repack nor logical replication but according to pg_repack Github repository there is a possible issue using pg_repack with logical replication: see https://github.com/reorg/pg_repack/issues/135

pifor
  • 7,419
  • 2
  • 8
  • 16
  • Thanks. I've seen that ticket. We don't seem to be running into that one though (we've been running pg_repack with logical replication and a few times now and logical replication was chugging along). It's also pretty old and only reported for Postgres 9.4/9.6. – john2x Apr 25 '20 at 03:32
1

To perform a repack, pg_repack will:

  1. create a log table to record changes made to the original table.
  2. add a trigger onto the original table, logging INSERTs, UPDATEs, and DELETEs into our log table.
  3. create a new table containing all the rows in the old table.
  4. build indexes on this new table.
  5. apply all changes which have occurred in the log table to the new table.
  6. swap the tables, including indexes and toast tables, using the system catalogs.
  7. drop the original table.

In my experience, the log table keeps all changes and applies them after build indexes, besides if repack needs to rollback changes applied on the original table too.

aleyasin
  • 37
  • 1
  • 8