Can I subscribe to an aiven_extras publisher using a pglogical subscriber?

PostgreSQL
1

Howdy,

Found aiven-extras and this is a pretty neat product.

My use case for this extension is to yank a database out of DigitalOcean and put it into RDS. My plan, thus far, is to:

  • Set up aiven-extras on DO SQL
  • Set up postgreql on an EC2 instance
  • Set up aiven-extras on the EC2 instance
  • Create a publication and a subscription

All of this works, but then I:

  • Set up pglogical on the EC2 instance
  • Set up pglogical on the RDS instance
  • Create a publication and a subscription

If I set up a publication for just one table, it works, but if I try to set up a publication for all tables, pglogical yells at me and tells me that I can’t set up a publication for tables with no primary key.

This isn’t my database, so the best I can do is advocate to change the tables.

I thought I might get cute and create a subscriber using pglogical, that points to aiven-extras’s publication? Then I could go DO → RDS, without the EC2 instance? Maybe bypass the table issue? Is this even possible?

Thanks!

2

Hi @Greg_Swallow!

I unfortunately think this error message is correct and that you’re on the right track – DO-hosted databases specifically behave poorly when there isn’t a primary key and I suspect you won’t have much success until that happens :frowning:

Is the goal to migrate (+ eventually replace) the DO Postgres with RDS, or to do some other kind of replication?

3

I got it to work, I think. I did this:

  • Dump the schema from DO
  • Import the schema on PostgreSQL on EC2
  • Add primary keys to the tables that didn’t have them on EC2
  • Replicate from DO → EC2 with aiven-extras
  • Replicate from EC2 → RDS with logical replication