1

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.
Jürgen Zornig
  • 1,174
  • 20
  • 48
  • Streaming replication from Pg on Windows to Linux or vice versa is not supported. – Craig Ringer Jun 28 '17 at 07:38
  • 2
    how you do the logical backup with `pg_basebackup`?.. you should use pg_dump to move from one OS to different – Vao Tsun Jun 28 '17 at 07:38
  • @VaoTsun as I have written I am doing both (pg_dump, pg_basebackup) for backup purposes. For the purpose of getting the database from one host to the other to initialize Streaming replication, I use pg_basebackup – Jürgen Zornig Jun 28 '17 at 07:40
  • @CraigRinger even when I move the database via pg_dump like VaoTsun is mentioning? – Jürgen Zornig Jun 28 '17 at 07:41
  • then @CraigRinger comment is the best answer – Vao Tsun Jun 28 '17 at 07:41
  • 1
    streaming replication requires binary safe copy of datafile - `pg_backup` or `rsync` - another os wont just take such files. pg_dump doesn't copy file - only data and thus cant be used for streaming replication – Vao Tsun Jun 28 '17 at 07:44
  • 1
    `pg_dump` isn't `pg_basebackup`. You should be able to use `pg_dump`, but you were talking about `pg_basebackup` above. – Craig Ringer Jun 28 '17 at 07:48
  • as I tagged my question with "backup-strategies" and "high-availability" and I asked questions pointing on these aspects, I thank you for your suggestions. A clear answer like Laurenz did seems to be possible according to my questions. – Jürgen Zornig Jun 28 '17 at 07:52

1 Answers1

3

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • thanks for clarification, I was afraid this is the case. Also thanks a lot for suggesting pglogical (https://www.2ndquadrant.com/en/resources/pglogical/) which I will further research on as an extension to postgres-9.6 – Jürgen Zornig Jun 28 '17 at 08:02
  • 1
    I won't say anything against pglogical, but it is different from logical replication as shipped with v10. If you can wait until then, I'd do that. – Laurenz Albe Jun 28 '17 at 10:17