[rsyslog] PostgreSQL: Problems with character encoding [Solved]

[Kenneth Marshall]

Would it be possible to send the poorly behaving loggers to
a different port to allow it to be cleaned up properly? Using
SQL_ASCII does allow truly anything into the database, which
means that all the output pieces need to process it appropriately
too.

Regards,
Ken

On Wed, Jan 06, 2010 at 04:48:02PM +0100, Marc Schiffbauer wrote:
> Hi all again,
> 
> replying to myself because I think I found the solution:
> 
> With an db encoding of SQL_ASCII the postgres server will not do any character 
> conversion which seems to be the right thing for syslog messages where the 
> encoding cannot be determined reliably.
> 
> Maybe this is an important piece for the rsyslog documentation as well.
> 
> Now everthing is working again.
> 
> To convert my existing database I switch to user postgres and used "pg_dump -C 
> syslog > syslog.sql" to dump the database. Then added a "DROP DATABASE syslog" 
> before the "CREATE DATABASE", changed any encodings from "UTF-8" to 
> "SQL_ASCII" (client_encoding and in the CREATE DATABASE statement) and then 
> loaded the data again with "psql < syslog.sql".
> 
> -Marc
> 
> 
> 
> Am Mittwoch, 6. Januar 2010 16:14:59 schrieb Marc Schiffbauer:
> > Hi all,
> > 
> > which encoding should be chosen for the database when using postgres?
> > 
> > My rsyslog version is 4.4.3.
> > 
> > Which client_encoding does rsyslog use in ompgsql?
> > 
> > 
> > I currently have set UTF-8 on the database. It worked for a while until
> >  some special message arrived at the server where postgres denies the
> >  INSERT:
> > 
> > 2010-01-06 16:13:11 CET syslog syslog ERROR:  invalid byte sequence for
> > encoding "UTF8": 0xd220
> > 2010-01-06 16:13:11 CET syslog syslog HINT:  This error can also happen if
> >  the byte sequence does not match the encoding expected by the server,
> >  which is controlled by "client_encoding".
> > 
> > Now rsyslog is not able to log anything... it is currently spooling to disk
> > because it "hangs" at this message not being accepted by postgres.
> > 
> > Any hints?
> > TIA
> > -Marc
> > 
> > _______________________________________________
> > rsyslog mailing list
> > http://lists.adiscon.net/mailman/listinfo/rsyslog
> > http://www.rsyslog.com
> > 
> _______________________________________________
> rsyslog mailing list
> http://lists.adiscon.net/mailman/listinfo/rsyslog
> http://www.rsyslog.com
>