start logical replication from the beginning

user_2061886 0
Viewed 443

I'm trying to understand how I can start logical replication on postgres from the beginning. I'm trying to use this python library, psycopg (https://www.psycopg.org/docs/index.html) and have it all setup...but I'm only capturing new changes to the tables but not the current data thats in there. I'm assuming I need to specify the LSN to start at (doc: https://www.psycopg.org/docs/extras.html?highlight=loggingconnection#psycopg2.extras.ReplicationMessage.data_start)...but how does one get the start of LSN?

by basic python script looks like this:

import json
import random
import calendar
import time
from datetime import datetime
import psycopg2
from psycopg2.extras import LogicalReplicationConnection

my_connection  = psycopg2.connect(
                   "dbname='dbname' host='host.com' user='user' password='pass'" ,
                   connection_factory = LogicalReplicationConnection)
cur = my_connection.cursor()
cur.drop_replication_slot('wal2json_test_slot')
cur.create_replication_slot('wal2json_test_slot', output_plugin = 'wal2json')
cur.start_replication(slot_name = 'wal2json_test_slot', options = {'pretty-print' : 1}, decode= True)

def consume(msg):
    print (msg.payload)

cur.consume_stream(consume)
postgresql
1 Answers

You can't start streaming from a point before where the slot was created in the first place.

To get the pre-existing data, just open a second connection to the database, and read the existing data with SELECT or COPY.

user_17212390
Related