Can we replicate postgres Data between two OS?

Question

I want to replicate postgresql data of windows server to linux server, I know how to replication between same operating systems but that method is not working with windows and linux. If yes what would be the better way to do this?

Answer 1

You cannot use streaming replication between different operating systems.

Look at the PostgreSQL Wiki for a list of replication solutions. Some of them should work for you.

From PostgreSQL v10 on, you could consider logical replication.

Answer 2

Done this using Postgresql 9.5.21 as master on Windows 2012 R2 and slave on Ubuntu 14.04.

You have to take care about a few things:

• most similar CPU (page size, architecture, registers). So, you can't mix 64/32bit, or using CPU with different endianess or page size;

  • same endianess also for the O.S.: both 32 or 64 bit;
  • same major version of PG: 9.5.x with same or other 9.5.x version (that's for streaming replication, that I'm using, Logical Replication works with different versions of PG);

  So, I find an already installed PG on Windows Server. Edit postgresql.conf to enable replica and PITR, and pg_hba.conf to allow connection.

  Then moved on Ubuntu and, with PG stopped, I fetched from the master with:

  pg_basebackup -D /tmp/db/ -X stream -R -U postgres -h ip-master

  Then modified configuration and replaced data directory with /tmp/db.

  Start slave, and is up and running, but look at this:

  2020-03-18 21:05:31.598 CET [44640] LOG: database system is ready to accept read only connections

  2020-03-18 21:05:31.631 CET [44645] LOG: started streaming WAL from primary at 36/C2000000 on timeline 1

  2020-03-18 21:05:31.905 CET [44646] [unknown]@[unknown] LOG: incomplete startup packet

  2020-03-18 21:05:32.416 CET [44649] postgres@postgres FATAL: database locale is incompatible with operating system

  2020-03-18 21:05:32.416 CET [44649] postgres@postgres DETAIL: The database was initialized with LC_COLLATE "Italian_Italy.1252", which is not recognized by setlocale().

  2020-03-18 21:05:32.416 CET [44649] postgres@postgres HINT: Recreate the database with another locale or install the missing locale.

  Here's the funny thing: replication works, but you can't connect to the databases.

  Anyway, if you raw copy the data dir on Windows, it works like a charm.

  Of course, if you re-create the cluster with UTF-8, there's no problem at all.

n.b.: thanks a lot to incognito and ilmari on official IRC PG channel for the hints.