Replication woes

[mguertin-jump]

Hi there ... having a really hard time replicating from a Linux postgres setup to Postgress.app. I've tried a few different approaches but none have worked so far, so looking for advice or possibly documentation ... here's the methods I've tried:

1. Copy over from current master using pg_basebackup to an appropriately located directory (~/Library/Application Support/Postgres/blah) -- PostgresApp does not pick up on this new directly.
2. tried then making a new DB at that location, still doesn't pick up, wants to initialize a new DB, doing so will make the new DB but it will not pickup the current data that's there.
3. Another attempt was to first initialize a new DB through app interface and then us pg_basebackup. This would not work because pb_basebackup wants an empty target (pg_basebackup: error: directory "/Users/mguertin/Library/Application Support/Postgres/v15_production" exists but is not empty)
4. Tried a combination of the two, made a new DB in the PostressApp UI, used pg_basebackup to copy to a different dir and then tried to cherry pick the things that PostgressApp was looking for and combining those two. PostgressApp still wants to initialize, so I'm assuming I'm missing on moving something that it requires.

Is there any known way to make these two play nicely together for replication? I'm looking to migrate my current production (linux) over to PostrgresApp on macos, and after that migration still have PostgressApp replicate back to the linux instance. If it matters I'm using log shipping replication right now between multiple linux hosts. If it's possible to do what I'm looking to do it would be awesome to have a bit of documentation on how this could all work, even if it's just a basic overview!

Thanks in advance and love this app, makes life a lot easier. Hoping I can use this and not have to go back to homebrew on macos.

[jakob]

I'll start with a big warning; Physical replication between different OS is very unsupported.

First of all, PostgreSQL makes no guarantees that the binary format on disk is compatible for different systems. It is architecture dependent. In practice it seems to work, but it is unsupported.

Second, when using physical replication, you need to ensure the systems behave the same way. A major issue are collations: If you have indices on text columns, you will run into issues. The indices will be corrupted. If you are just doing it for migration/backup, you can reindex the database to fix this, but when using physical replication this is not possible

That being said, I don't see why (2) shouldn't work. Postgres.app checks for the PG_VERSION file to see if a database directory is initialized or not. I haven't use pg_basebackup myself, but according to the docs it should recreate all files in the data directory. So it's odd that the new data directory would not contain this file. Furthermore, initdb should fail if the data dir isn't empty.

[mguertin-jump]

Ok thanks very much for the reply Jakob, it's very helpful, especially the info around the collations -- I have a lot of indices on text columns to deal with so I may have to rethink my approach here.

It did recreate the PG_VERSION file when I did the basebackup but I also just realized that my setup has a version mismatch right now, going from 14 on linux to 15 on macos. I'll just do a dump and restore to make the initial move and figure something out for my replication strategy moving forward.