Reuse an existing slot with a new initdb

On Wednesday, May 13, 2020, Michael Paquier <michael(at)paquier(dot)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.