10

I have two Postgres 9.3 servers in synchronous replication.

I had needed to restart the slave in order to load a new archive_cleanup_command in the recovery.conf.

The server restarted correctly and it's now perfectly in sync with the master server.

But when I open "Server status" panel for the slave server in PgAdmin III (which executable is located on the master server), I get some errors like this:

invalid byte sequence for encoding “UTF8” plus some hex codes

enter image description here

It might be because I put a tilde ~ in the archive_cleanup_command, but it didn't worked, then I removed it and the command worked correctly.

Maybe that ~ has been written somewhere and it's not a valid char... but I also deleted logs...

Log of the slave server has a lot of lines like the followings:

2015-02-13 11:11:32 CET ERROR:  invalid byte sequence for encoding “UTF8”: 0xe8 0x20 0x73
2015-02-13 11:11:32 CET STATEMENT:  SELECT pg_file_read('pg_log/postgresql-2015-02-13_111038.log', 0, 50000)

Note that postgresql-2015-02-13_111038.log is the last log, the one from which I got these lines.

Teejay
  • 7,210
  • 10
  • 45
  • 76
  • What do you have configuration parameter `lc_messages` set to? Also see [this french forum post](http://forums.postgresql.fr/viewtopic.php?id=1258) and [this bug report](http://www.postgresql.org/message-id/201009161112.o8GBCYak052654@wwwmaster.postgresql.org) – harmic Aug 31 '15 at 08:26
  • Also relevant to know: what OS are you running these databases on? – harmic Aug 31 '15 at 08:29
  • @harmic: stupid me, I opened this bounty on friday but this week i'm on vacations, so I currently have no access to the system. I'll let you know and open a new bounty if necessary. – Teejay Aug 31 '15 at 09:28
  • @harmic: `lc_messages` is set to Italian_Italy.1252, which is probably wrong since databases have their default *collation* and *character type* set to English_United States.1252. Should I try to set `lc_messages` to English? – Teejay Sep 07 '15 at 08:29
  • @Teejay I know it's an old thread but I guess you should try `Italian_Italy.UTF-8` instead. By the way did you get rid of that popup? – Matthieu Feb 28 '18 at 17:03
  • Have you tried the answer to this probably relevant question? https://stackoverflow.com/questions/4867272/invalid-byte-sequence-for-encoding-utf8?rq=1 – cristianmtr Sep 02 '15 at 11:20

2 Answers2

4

The problem you have is that the locale setting lc_messages is set to an encoding that is different to the encoding of the database(s). As a result, some messages are being written into the log using Windows-1252 encoding, while when you try to use PgAdmin to view the log, it tries to interpret the file using UTF-8. Some of the byte sequences written in the log are not valid UTF-8, leading to the error.

In fact, the way in which different locales interact in postgresql can result in mixed encodings in the log file. There is a Bug Report on this, but it does not look like it has been resolved.

Probably the easiest way to resolve this is to set lc_messages to English_United States.UTF-8.

It would also be preferable to have lc_messages aligned across all of the databases on the server (or at least all using the same encoding).

Be sure to remove any existing log files as they will already contain the incorrect encoding.

harmic
  • 28,606
  • 5
  • 67
  • 91
  • Thanks for the answer. But, are you sure that the correct value is `English_United States.UTF-8` ? Searching Google for `"English_United States.1252"` leads me to roughly 15**k** results, searching `"English_United States.UTF-8"` leads me to 5 only. – Teejay Sep 08 '15 at 08:01
  • @Teejay '1252' specifies to use Windows-1252 encoding when writing the messages. Since reading the message via the database `pg_file_read` function expects UTF-8 encoding, that might not work. Although I suspect it may work because the subset of characters used in English (basically ASCII) are encoded the same in both encoding types. – harmic Sep 08 '15 at 12:35
  • That solved my exact same problem. It should be accepted. – Matthieu Mar 01 '18 at 10:04
0

It is due to your postgresql.log corrupted as stated in the statement 'select pf_file_read ....'.

If you do a "touch" (after a backup of your log perhaps) on you server log, and reconnect, you'll not see this unicode error anymore and thus, you'll be able to use pgadmin III furthermore.

Matthieu
  • 2,736
  • 4
  • 57
  • 87
Virgile
  • 9
  • 1
  • 1
    I don't see how a `touch` can ***un***corrupt a file... by the way I also tried to delete log entirely... – Teejay Mar 19 '15 at 10:25