[PgSubscriber] Initial full load of data

[revolko]

How to deal with loading the initial data from Postgres database?
https://www.postgresql.org/docs/current/protocol-replication.html#PROTOCOL-REPLICATION-START-REPLICATION

https://www.postgresql.org/docs/current/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-COPYDATA

[revolko]

From what I understood the replication does not full load when started.

There are 3 options when creating the replication: export, use, and no snapshot. I tried to figure out how to utilize export and use but was not able to do so.

The approach I am going to try to implement is to create two connections to database at the beginning. The first connection will be basic replication connection. The second one will create temporary tables for each replicated table and insert all data from original tables to temporary tables. That way an insert statement will be triggered and caught by the replication connection. Disclaimer, I am not sure if it is feasible option in practice as potentially the source database must have enough disk space to store the copy of the biggest table (assuming that we will load/copy one table at a time).

[revolko]

Use pg_dump (creates .sql file that restores the database to a saved state). and pg_restore?

[revolko]

Temporary tables cannot be replicated :). However, pg_dump can generate literal INSERT statements based on the existing rows in tables. So my second idea is to execute pg_dump, send its output to Linux PIPE (ideally some TCP connection if possible), parse INSERT statements from the output and convert them to Core Insert messages.

[revolko]

For now I am happy with the full load implementation. Just start the process with path to pg_dump output that was created with --column-inserts flag.