Hi
I'm trying to get Slony-I up and running; master-db is PostgreSQL 8.3.1, slave is PostgreSQL 8.2.4, Slony-I is 1.2.14 on both machines.
This is the DDL for the table I wish to replicate:
CREATE TABLE stats.article_impressions_day
(
site_id integer NOT NULL,
article_id integer NOT NULL,
date_day date NOT NULL,
impressions_p1 integer,
impressions_total integer NOT NULL,
impressions_pages integer,
CONSTRAINT "pk_article_impressions_day" PRIMARY KEY (site_id, article_id, date_day)
)WITHOUT OIDS;
-- Indexes
CREATE INDEX idx_article_impressions_day_total ON stats.article_impressions_day USING btree (impressions_total);
CREATE INDEX idx_article_impressions_day_site ON stats.article_impressions_day USING btree (site_id);
CREATE INDEX idx_article_impressions_day_p1 ON stats.article_impressions_day USING btree (impressions_p1);
CREATE INDEX idx_article_impressions_day_date_day ON stats.article_impressions_day USING btree (date_day);
CREATE INDEX idx_article_impressions_day_aid ON stats.article_impressions_day USING btree (article_id);
This is my slonik-script:
--------
#!/bin/sh
CLUSTER=stats
DBNAME1=community
DBNAME2=cbox
HOST1=ciadb2
HOST2=ciadb1
SLONY_USER=postgres
PGBENCH_USER=postgres
/opt/pgsql/bin/slonik <<_EOF_
cluster name = $CLUSTER;
node 1 admin conninfo = 'dbname=$DBNAME1 host=$HOST1 user=$SLONY_USER';
node 2 admin conninfo = 'dbname=$DBNAME2 host=$HOST2 user=$SLONY_USER';
init cluster ( id = 1, comment = 'Node 1' );
create set ( id = 1, origin = 1, comment = 'All stats tables' );
set add table ( set id = 1, origin = 1,
id = 1, full qualified name = 'stats.article_impressions_day',
comment = 'daily article stats' );
set add table ( set id = 1, origin = 1,
id = 2, full qualified name = 'stats.entity_impressions_day',
comment = 'daily entity stats' );
store node ( id = 2, comment = 'Node 2' );
store path ( server = 1, client = 2,
conninfo = 'dbname=$DBNAME1 host=$HOST1 user=$SLONY_USER');
store path ( server = 2, client = 1,
conninfo = 'dbname=$DBNAME2 host=$HOST2 user=$SLONY_USER');
store listen ( origin = 1, provider = 1, receiver = 2 );
store listen ( origin = 2, provider = 2, receiver = 1 );
--------
When I run this script, I get some error messages:
<stdin>:41: PGRES_FATAL_ERROR select "_stats".setAddTable(1, 1, 'stats.article_impressions_day', 'pk_article_impressions_day', 'daily article stats'); - ERROR: operator is not unique: unknown || integer
LINE 1: SELECT 'create trigger "_stats_logtrigger_' || $1 || '" a...
^
HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
QUERY: SELECT 'create trigger "_stats_logtrigger_' || $1 || '" after insert or update or delete on ' || $2 || ' for each row execute procedure
"_stats".logTrigger (''_stats'', ''' || $1 || ''', ''' || $3 || ''');'
CONTEXT: PL/pgSQL function "altertableforreplication" line 62 at EXECUTE statement
SQL statement "SELECT "_stats".alterTableForReplication( $1 )"
PL/pgSQL function "setaddtable_int" line 109 at PERFORM
SQL statement "SELECT "_stats".setAddTable_int( $1 , $2 , $3 , $4 , $5 )"
PL/pgSQL function "setaddtable" line 37 at PERFORM
I strongly suspect that there is some conflict with the implicit casts I added in the master-db - I used http://people.planetpostgresql.org/peter/uploads/pg83-implicit-casts.sqlx in order to restore pre-8.3 cast behaviour. As our application still depends on this behaviour I cannot simply drop the casts. Now what could I do to get replication with these casts in place on the master-db?
Kind regards
Markus
Computec Media AG
Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jürg Marquard
Umsatzsteuer-Identifikationsnummer: DE 812 575 276
