Logical replication and restore from pg_basebackup

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Logical replication and restore from pg_basebackup

Dmitry Vasiliev
What is the scope of logical replication if I cannot make recovery from pg_basebackup?

in this example: https://gist.github.com/vadv/e55fca418d6a14da71f01a95da493fae I get logically unsynchronized data at the subscriber and publisher, but I'm not told anything about it in the log. 

Do I understand correctly that logical replication and recovery from pg_basebackup are incompatible things?
Reply | Threaded
Open this post in threaded view
|

Re: Logical replication and restore from pg_basebackup

Dmitry Vasiliev
At the start of the logical subscriber is not informed that it is connected to the logical replication slot with a non-consistent state.
Whether I understood correctly that, postgresql deceives the user and data in logical replication cannot be trusted.

2019-02-11 17:22:20.103 MSK [71156] LOG:  database system was shut down at 2019-02-11 17:22:19 MSK
2019-02-11 17:22:20.108 MSK [71154] LOG:  database system is ready to accept connections
2019-02-11 17:22:20.243 MSK [71171] LOG:  logical replication apply worker for subscription "sub_pgbench" has started
2019-02-11 17:22:20.260 MSK [71174] LOG:  logical replication table synchronization worker for subscription "sub_pgbench", table "pgbench_accounts" has started
2019-02-11 17:22:23.422 MSK [71174] LOG:  logical replication table synchronization worker for subscription "sub_pgbench", table "pgbench_accounts" has finished
2019-02-11 17:22:27.807 MSK [71269] LOG:  database system was interrupted; last known up at 2019-02-11 17:22:24 MSK
2019-02-11 17:22:27.824 MSK [71269] LOG:  recovered replication state of node 1 to 0/0
2019-02-11 17:22:27.825 MSK [71269] LOG:  redo starts at 0/C000028
2019-02-11 17:22:27.825 MSK [71269] LOG:  consistent recovery state reached at 0/C0000F8
2019-02-11 17:22:27.826 MSK [71269] LOG:  redo done at 0/C0000F8
2019-02-11 17:22:27.883 MSK [71267] LOG:  database system is ready to accept connections
2019-02-11 17:22:27.893 MSK [71276] LOG:  logical replication apply worker for subscription "sub_pgbench" has started

On Mon, Feb 11, 2019 at 5:39 PM Dmitry Vasiliev <[hidden email]> wrote:
What is the scope of logical replication if I cannot make recovery from pg_basebackup?

in this example: https://gist.github.com/vadv/e55fca418d6a14da71f01a95da493fae I get logically unsynchronized data at the subscriber and publisher, but I'm not told anything about it in the log. 

Do I understand correctly that logical replication and recovery from pg_basebackup are incompatible things?
Reply | Threaded
Open this post in threaded view
|

Re: Logical replication and restore from pg_basebackup

Michael Paquier-2
In reply to this post by Dmitry Vasiliev
On Mon, Feb 11, 2019 at 05:39:49PM +0300, Dmitry Vasiliev wrote:
> Do I understand correctly that logical replication and recovery from
> pg_basebackup are incompatible things?

When using physical streaming replication, it is mandatory to have
nodes with a system ID matching, meaning that all nodes have been
created from the same source instance.  With logical replication,
nodes are separate entities from this point of view, hence you may be
able to make a logical copy from a base backup, however this is not
really necessary as changes are exchanged in a logical shape.
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Logical replication and restore from pg_basebackup

Alexey Kondratov-2
In reply to this post by Dmitry Vasiliev
Hi Dmitry,

On 11.02.2019 17:39, Dmitry Vasiliev wrote:
> What is the scope of logical replication if I cannot make recovery
> from pg_basebackup?


No, you can, but there are some things to keep in mind:

1) I could be wrong, but usage of pgbench in such a test seems to be a
bad idea, since it drops and creates tables from the scratch, when -i is
passed. However, if I recall it correctly, pub/sub slots use OIDs of
relations, so I expect that you should get only initial sync data on
replica and last pgbench results on master.

2) Next, 'srsubstate' check works only for initial sync. After that you
should poll master's replication slot lsn for 'pg_current_wal_lsn() <=
replay_lsn'.

Please, find attached a slightly modified version of your test (and gist
[1]), which works just fine. You should replace %username% with your
current username, since I did not run it as postgres user.

[1] https://gist.github.com/ololobus/a8a11f11eb67dfa1b6a95bff5e8f0096


Regards

--
Alexey Kondratov

Postgres Professional https://www.postgrespro.com
Russian Postgres Company


logical-replication-test.sh (3K) Download Attachment