On 2013-09-02 13:48, Michelle Sullivan wrote:
Christian Ritter wrote:
On 2013-09-02 11:49, Michelle Sullivan wrote:
Christian Ritter wrote:
I have switched the query log on, and it looks like bucardo has
problems with reading the data from the source database.
my insert into database testa:
2013-09-02 11:08:28 CEST LOG: statement: INSERT INTO testtable
VALUES
(1, '192.168.1.1', 'foobar');
what bucardo tries to insert into database testb:
2013-09-02 11:08:29 CEST LOG: execute dbdpg_p14740_6: INSERT INTO
public.testtable (id, host_ip,"prüfsumme") VALUES ($1,$2,$3)
2013-09-02 11:08:29 CEST DETAIL: parameters: $1 = '1', $2 =
'192.168.1.1', $3 = NULL
2013-09-02 11:08:29 CEST ERROR: null value in column "prüfsumme"
violates not-null constraint
2013-09-02 11:08:29 CEST STATEMENT: INSERT INTO public.testtable
(id,
host_ip,"prüfsumme") VALUES ($1,$2,$3)
prüfsumme is quoted with "" which looks ok.
client encoding is set to utf8:
testa=# SHOW client_encoding;
client_encoding
-----------------
UTF8
(1 row)
Now that's more like it.. I'm betting the issue therefore is in
the
bucardo_delta function(s) that insert the data into the table.
On the source machine try the following (assuming 4.5.x - I don't
know
on 4.99.7 except this won't work):
SELECT * FROM bucardo.bucardo_delta WHERE rowid = '1'::text AND
rowid2 =
'192.168.1.1'::text;
this will give you the rows in the bucardo_delta table and you
should be
able to see if the source data is actually NULL.
If it is and there are no other rows the problem will likely be in
the
delta() functions. If not the the read of the data is incorrect.
If
there are multiple rows and one is null (earliest txntime) then it
may
be because you changed the column/table definition after a delta
was
created and not replicated - in which case you will need to remove
the
rows with something like:
DELETE FROM bucardo.bucardo_delta WHERE rowid = '1'::text AND
rowid2 =
'192.168.1.1'::text AND rowid3 IS NULL; (if there are other tables
you
will need to specify the table OID in the DELETE as well. Table
OIDs
can be obtained on the source with something like:
=# select relname, oid from pg_class where relname = 'hosts2evid';
relname | oid
------------+-------
hosts2evid | 32596
(1 row)
Regards,
Michelle
bucardo delta looks good to me, so it looks like bucardo is unable
to
read the data if there are umlauts in the column name.
testa=# SELECT * from bucardo_delta;
tablename | rowid | txntime
-----------+-------+-------------------------------
28039 | 1 | 2013-09-02 13:23:12.344017+02
(1 row)
testa=# select relname, oid from pg_class where relname =
'testtable';
relname | oid
-----------+-------
testtable | 28039
testa=# SELECT * from testtable;
id | host_ip | prüfsumme
----+-------------+-----------
1 | 192.168.1.1 | foobar
(1 row)
Ok is your version 4.5.x?
My example select was for rows with a combined key, obviously by your
data your primary key is just 'id' (I must have been asleep to miss
that
:/ )
If you are not using 4.5.x then what I'm talking about here probably
does not apply or make sense.
Now assuming 4.5...
bucardo uses COPY to take the source rows and pass them to the
target... So your logging doesn't make sense for 4.5..
bucardo version is 4.5.0 <-got that from bucardo_ctl
-- Schema for the main Bucardo database
-- Version 4.5.0
The logs are from postgres, and there is no copy in the logs, only 2
inserts
The full log is here:
http://pastebin.com/8CxrSFmU
Cheers
Christian
_______________________________________________
Bucardo-general mailing list
[email protected]
https://mail.endcrypt.com/mailman/listinfo/bucardo-general
