Restore pg_basebackup on Linux which was created from a Windows hosted database cluster

Currently I am doing some Research on how to set up streaming replication for a HA postgresql architecture. But this issue is also relevant for us for backup/restore and maintenance in mixed environments in general.

Our "main" postgresql server is running on a Windows box. I am using pg_dump to create logical backups every day and pg_basebackup and WAL archiving to create a full backup which should provide PITR (I have not tested this yet).

Next step is to set up a slave machine which holds a replica via streaming replication. Due to the better Support of the postgresql ecosystem, this new host is running Ubuntu Server 16.04 LTS.

1. With pg_basebackup -h <main host> -D <datadirectory> --xlog-method=stream I initialized the slave datadirectory.
2. Then I had to fix some symbolic links onto tablespace files, as these were pointing on Windows paths instead of the Linux filesystem.
3. Next I created the recovery.conf file with standby_mode = on and the connectioninfo
4. I verified, that the Server is configured as hot_standby = on and started the daemon

Now I am running into issues as postgres tells me, that the "database locale (English_United_States.1252 which is obviously Windows specific) is incompatible with operating System"

Well, I am aware, that 1252 is a Windows specific locale and that I should use en_US.UTF8 on Linux, BUT in a mixed Environment like this, should I:

• Init our main DB on Windows also with an en_US.UTF8 locale?
• Is that even possible?
• Why is the pg_basebackup format Operating System specific, or is there a backup Format which is not?
• Is there any command line flag to set to translate this?
• I would also appreciate it to use only Linux (or even only Windows), but I am afraid our customers will rely on both Operating Systems quite a while.

You cannot restore file system backups like the one that pg_basebackup creates on a different architecture, like restore a backup from Windows on Linux.

It is also impossible to have streaming repication in such a scenario.

From PostgreSQL v10 on, you might consider logical replication.