Do replication test of foreign keys in new tables

[mlissner]

Here's a question. Say we have a table we're replicating. Then we add another table that FKs to the first one, but we don't migrate the schema of the new table to the destination server.

Does replication break? This issue is to find out.

I think the procedure will be:

Set up:

• New virtualenv
• Install latest django
• Create a table in a new DB in a new django project
• Create a new AWS RDS server
• Set up replication to that server for the table
• Replicate a few records

Test:

• Add a new table that FKs to the first table
• Migrate it using Django migrations
• Attempt to replicate a few more records in the first table
• Add records to the new table
• Attempt to replicate a few more records in the first table

Hypothesis:

• All records will replicate properly.

Measurement:

• The number of replicated records that get sent to the RDS server

Hopefully this won't take too long. I need to know what happens in this instance because I am about to add "Claims Registers" for bankruptcy proceedings in PACER. They will FK into the search_docket table, which is one of our replicated tables. If we can avoid having to talk to all our replicated customers immediately to get them this table...that'd be good.

[mlissner]

As you might expect, the hardest part of this is the networking. My original idea, above, was to use an RDS instance as a subscriber and to have my laptop as the publisher. Of course, that only works if I properly forward ports from my router, which is theoretically possible, but I run enterprise-grade routers in my house, and frankly, while they work wonderfully and I love them, forwarding a port is surprisingly difficult. So that was out.

There's an easier way, anyway: Docker, and what do you know, there's even a postgresql image. So, to get this set up, I launched one docker image on port 5432 (the publisher) and another on 5433 (the subscriber). A couple tricks here:

1. You can pass port numbers into your run command like this:

    docker run -p 5432 postgres -p 5432
   

2. Once you've got the two docker images up, you won't know what their internal IPs are. Get them by running:

    # Get their internal names for the next step
    docker container ls 
    # Look up their IPs in their network
    docker network inspect bridge
   

3. With that done, you need to tweak the settings to enable replication in both servers, so really, you have to start them with:

    docker run -p 5432 postgres -p 5432 -c wal_level=logical
   

   And:

    docker run -p 5433 postgres -p 5433 -c wal_level=logical
   

From there, it's just a matter of running the right SQL commands from issue #932, and running the test.

[mlissner]

Good news: This works fine.

First, I created this model and got it replicating from one container to the other:

class Question(models.Model):
    question_text = models.CharField(max_length=200)
    pub_date = models.DateTimeField('date published')

Then, to confirm functionality, I created a couple items via the admin portal and checked that they got replicated. They did (instantly).

Then I made this model:

class Choice(models.Model):
    question = models.ForeignKey(Question, on_delete=models.CASCADE)
    choice_text = models.CharField(max_length=200)
    votes = models.IntegerField(default=0)

And migrated it using manage.py migrate. After that, I created a few items in this model via the admin UI. Then I created a Question item as well. Then another Question.

• The Question before the Choice was migrated fine.
• The Choice did not migrate or throw errors.
• The Question after the Choice migrated fine.

Seems like all is good here. Tearing things down and proceeding with real work.