A long time ago, I read in a medical book that One can learn about a system when things go wrong. So diseases are a good way to learn more about how the human body functions. That sentence stuck with me and this is about a story where someone made a mistake and learned something (someone, not me, I don’t make mistakes).

~~It was friday afternoon~~ (why do all tech disaster stories start on friday? This one doesn’t start on friday) It was a less dramatic Wednesday afternoon, the smell of a freshly baked migration was in the air. I was rubbing my hands with joy, waiting to press merge once I got that LGTM. I had worked hard on the migration and I was quite proud that I thought a lot about the changes and data model. I was adding a new table, how hard can that be?

I got my LGTM, pressed merged, pushed my chair away from the desk, and leaned back. I let out a sigh and thought to myself, that was good work, I’m getting better as an engineer (watch out though, life always has a way of swatting you back in place). I had tested the changes locally, no problem. Relaxed, I went about my day.

An hour later, Grafaana started sending alerts. Rows from the freshly baked table couldn’t be deleted. That was odd, because there were no new rows created in that table. The code to do that hadn’t been written yet. But it did have a foreign key relationship with a crusty old table.

Here’s what the tables looked like.

CREATE TABLE crusty_table (
    id int PRIMARY KEY
    , crusty_title text
);

-- Part of the freshly baked batch of migrations
CREATE TABLE freshly_baked_table (
    crusty_id int REFERENCES crusty_table (id) ON DELETE CASCADE
    , fresh_title text
);

-- Boss, if you're reading this, I swear this doesn't violate our NDA agreement.

The only difference was that good old crusty_table existed a long time before freshly_baked_table came along. And now someone was deleting some crust and the deletes were failing. So, I tried to see if I could delete some crust on my local setup, I could (A good sign), then I tried it on QA, it worked fine (see? I didn’t make a mistake), but then, it failed in production. I couldn’t delete new crusty rows, and I couldn’t delete old crusty rows. This was serious, if this was the other way around, fails locally and in QA but works in production I could’ve swept this under the rug (we call backlog) and gone on with my life (Boss, if you’re reading this, I swear I don’t do this. It was a joke, you know, for humor purposes).

I dug up the logs, and what do you know, postgres had a message for me.

org.postgresql.util.PSQLException: ERROR: cannot delete from table "freshly_baked_table" 
because it does not have a replica identity and publishes deletes
  Hint: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.

(Yes, the logs did say “freshly_baked_table”)

I had never seen this error before. Turns out, we had logical replication turned on in production but not in any other environment, so we only got an error in production.

The ELI5 version (because that’s the level of understanding I have currently) is that postgres uses a pub-sub system to replicate data from one database to another.

This means that once data is written to the main database it is published to any database that is subscribing to it. You can have multiple databses subscribing to one databse.

A simple example

Grab your local postgres installation and create two databases. I am using docker to run postgres

-- will be the publisher
postgres=# CREATE DATABASE town_crier;
CREATE DATABASE

-- will be the subscriber
postgres=# CREATE DATABASE town_audience;
CREATE DATABASE

Let’s leave crusty_table and freshly_baked_table aside for a moment.

Here’s an ideal table

-- in town_crier
CREATE TABLE ideal_table (
    id int PRIMARY KEY
);

-- in town_audience
CREATE TABLE ideal_table (
    id int PRIMARY KEY
);

Now, in town_crier add some values to the table.

-- insert some data 
INSERT INTO ideal_table (id)
    VALUES (1),
    (2),
    (3);

town_crier=# SELECT * FROM ideal_table;
 id
----
  1
  2
  3
(3 rows)

-- but town_audience doesn't have any data yet
town_audience=# select * from ideal_table;
 id
----
(0 rows)

The town_audience DB doesn’t have any data yet because we haven’t set up logical replication. Let’s set it up.

Creating a publication

 town_crier=# CREATE PUBLICATION town_cry FOR ALL TABLES;

note you might run into a warning that says something about a wal_log like I did.

WARNING:  wal_level is insufficient to publish logical changes
HINT:  Set wal_level to logical before creating subscriptions.

For this you will have to set the wal_level to logical and restart your server. You can add postgres -c wal_level=logical to your docker-compose file and restart docker. This is what my docker-compose file looks like (YMMV).

version: '3.6'
services:
  postgres:
    restart: 'always'
    ports:
      - '5432:5432'
    environment:
      - "POSTGRES_PASSWORD=password"
    command: postgres -c wal_level=logical 
    build: './db'

Creating a subscription

If you are creating the subscription in the same database cluster, it’s a little tricky. The standard way to create a subscription would be to run a command like this

CREATE subscription listen_to_town_crier 
connection 'postgresql://postgres:password@localhost:5432/town_crier' 
publication town_cry;

But you might discover that this hangs forever (this resulted in a lot of head scratching for me). From the docs

Creating a subscription that connects to the same database cluster (for example, to replicate between databases in the same cluster or to replicate within the same database) will only succeed if the replication slot is not created as part of the same command. Otherwise, the CREATE SUBSCRIPTION call will hang. To make this work, create the replication slot separately (using the function pg_create_logical_replication_slot with the plugin name pgoutput) and create the subscription using the parameter create_slot = false. See Section 31.2.3 for examples. This is an implementation restriction that might be lifted in a future release.

Let’s modify the command

In the subscribing database

CREATE subscription listen_to_town_crier 
connection 'postgresql://postgres:password@localhost:5432/town_crier' 
publication town_cry 
with (connect=false); -- this is new!

-- output
WARNING:  tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
CREATE SUBSCRIPTION

In the publishing database

town_crier=# SELECT * 
town_crier-# FROM pg_create_logical_replication_slot('listen_to_town_crier' 
town_crier-#                                        , 'pgoutput');
      slot_name       |    lsn
----------------------+------------
 listen_to_town_crier | 3/3ED3F798
(1 row)

Let’s see if the data is replicated

town_audience=# select * from ideal_table;
 id
----
(0 rows)

Still no data. When you manually create a subscription like we did you need to enable it and refresh it.

town_audience=# ALTER SUBSCRIPTION listen_to_town_crier ENABLE;
ALTER SUBSCRIPTION
town_audience=# ALTER SUBSCRIPTION listen_to_town_crier REFRESH PUBLICATION;
ALTER SUBSCRIPTION

Now you should see the data in the town_audience database

town_audience=# SELECT * FROM ideal_table;
 id
----
  1
  2
  3
(3 rows)

(This felt like magic when I got it working!)

Testing out the production issue

Create the tables, you’ll have to create them in both the databases. Unfortunately the replication doesn’t replicate tables This is listed as a restriction

-- run this in town_crier and town_audience
CREATE TABLE crusty_table (
    id int PRIMARY KEY
    , crusty_title text
);

CREATE TABLE freshly_baked_table (
    crusty_id int REFERENCES crusty_table (id) ON DELETE CASCADE
    , fresh_title text
);

Once that is set up, let’s add some data to crusty_table

-- in town_crier
INSERT INTO crusty_table (id)
    VALUES (1),
    (2),
    (3);
INSERT 0 3

check if the data is replicated

town_audience=# SELECT * FROM crusty_table;
 id | crusty_title
----+--------------
(0 rows)

Ah, it hasn’t, you must alter the subscription to refresh the publication because you added new tables. (I don’t think we’d have to do this if we used the automatic command, but I’m not sure)

town_audience=# ALTER SUBSCRIPTION listen_to_town_crier REFRESH PUBLICATION;
ALTER SUBSCRIPTION

-- then try 
town_audience=# SELECT * FROM crusty_table;
 id | crusty_title
----+--------------
  1 |
  2 |
  3 |
(3 rows)

-- Celebrate!

Now for the moment of truth, let’s delete a crusty row.

town_crier=# DELETE FROM crusty_table WHERE ID=1;
ERROR:  cannot delete from table "freshly_baked_table" because it does not have a replica identity and publishes deletes
HINT:  To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.
CONTEXT:  SQL statement "DELETE FROM ONLY "public"."freshly_baked_table" WHERE $1 OPERATOR(pg_catalog.=) "crusty_id""

For postgres to figure out which rows need to be deleted from the replica, it needs a replica identity. I like to think of the replica identity as a sort of primary key between publishers and subscribers, since primary keys are used to uniquely identify database rows.

The easiest fix for this is to add a primary key to the freshly_baked_table.

-- remember to run this on both databases.
ALTER TABLE freshly_baked_table ADD PRIMARY KEY(crusty_id);

And now try deleting a crusty row.

town_crier=# DELETE FROM crusty_table WHERE ID=1;
DELETE 1

It succeeded!

Why can’t postgres run the same query on the replica?

One question I had in mind was why can’t postgres run the same query DELETE FROM crusty_table WHERE ID=1; on the replica, why does it need to have a replica identity?

I don’t have a satisfactory answer to this question yet, you’re welcome to tell me more about it. Maybe I will write a new TIL.

Closing thoughts

The takeaway for me from this whole incident was to always have primary keys on the database. I suppose there are exceptions to this but I don’t know enough to know when I don’t need to have a primary key. This is also a good example of the difference between designating an id column as a primary key versus just having a unique constraint on it. The unique constraint would require you to manually create a replica identity which uses that unique constraint.

A simple example#

Creating a publication#

Creating a subscription#

Testing out the production issue#

Why can’t postgres run the same query on the replica?#

Closing thoughts#