On 5/13/2020 9:28 PM, David G. Johnston wrote:
On Wednesday, May 13, 2020, Michael Paquier <mich...@paquier.xyz
<mailto:mich...@paquier.xyz>> wrote:
On Wed, May 13, 2020 at 02:12:45PM -0700, live-school support wrote:
> I didn't recal that it was not possible to create a hot standby
with a fresh
> new install and pg_dumpall :(.
>
> only pg_basebackup or an exact copy of the data folder can do it
right? is
> the reason technical or else?
When using physical replication, both the primary and standby need to
have the same system ID, and both instances need to share the same
architectures to work properly as data is physically replayed from one
cluster to the other using WAL, which includes for example copies of
on disk relation 8kB pages (ever heard of full_page_writes?).
This basically hits the nail on the head. My reading is that the OP
has two abstractly identical restored databases, one created from a
physical copy and the other from a logical copy. The issue is why the
original server cannot use the same replication slot name to continue
synchronizing with the logically restored one but is able to continue
with the physically restored one. The above is why.
The OP asks whether the technical identifier error encountered can be
overcome. It cannot but even if it could the attempt would still end
up failed due to fundamental differences in the physical data layouts
between physical and logical restoration. If the OP needs to rebuild
a physical replication hot standby database they must use a physical
backup of the original database as a starting point. To use a
logically restored database target would require logical replication.
David J.
Thanks Michael and David for your answers
I think David caught it, the question is Why, as long as we have an
exact copy of the master (from pg_dumpall) we cannot start a new initdb
hot standby with an already existing physical replication slots without
the master complain about this "identifier doesn't match up"
knowing that everything seems to be synchronized?
Sorry Michael to not show you more logs, I made these tests weeks ago
and cannot restart them for now, too busy on other jobs.
David
