Slony1 PostgreSQL Replication - is there a way to delete a schema change sync

Author

is there a way to delete a schema change sync

Miguel

2006-04-07, 1:31 pm

Hi, im facing an error (stupid me !!!), im replicating between two
servers, the master server is gentoo and slave is freebsd, in gentoo the
owner of the database is postgres and in freebsd is pgsql, when i
created the schema in freebsd a modified every line

ALTER BLABLABLA OWNER TO postgres;

to

ALTER BLABLABLA OWNER TO pgsql;

and edited the conn line in slon_tools.conf according to every node's
database owner, the replication started without any problem, all was
working fine until today, i wanted to modify a function, so i created an
exec script, the problem was that i copied the original function from a
pgadmin window, edited it and pasted it in a vi screen, **BUT** i forgot
to edit the owner line, so now slon is dying in the slave node, refusing
to apply the schema change beacuse in the slave node there isnt a
postgres role

2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1
: queue event 1,20534
SYNC
2006-04-07 10:49:47 CST ERROR remoteWorkerThread_1
: "begin transaction;
set transaction isolation level serializable; lock table "_shiva".sl_
config_lock; select "_shiva".ddlScript_int(1, 'DROP FUNCTION
formatted_time(float
8);

CREATE OR REPLACE FUNCTION formatted_time(integ
er)
RETURNS "varchar" AS
$BODY$ select ($1 * ''1 second''::interval):
:varchar(15)$BODY$
LANGUAGE ''sql'' VOLATILE;
ALTER FUNCTION formatted_time(integ
er) OWNER TO postgres;

', -1); notify "_shiva_Event"; notify "_shiva_Confirm"; insert into
"_shiva".sl_event (ev_origin, ev_seqno, ev_timestamp, ev_minxid,
ev_maxxid, ev_xip, ev_type , ev_data1, ev_data2, ev_data3 ) values
('1', '17993', '2006-04-06 17:32:34.498772', '67820709', '67820710', '',
'DDL_SCRIPT', '1', 'DROP FUNCTION formatted_time(float
8);

CREATE OR REPLACE FUNCTION formatted_time(integ
er)
RETURNS "varchar" AS
$BODY$ select ($1 * ''1 second''::interval):
:varchar(15)$BODY$
LANGUAGE ''sql'' VOLATILE;
ALTER FUNCTION formatted_time(integ
er) OWNER TO postgres;

', '-1'); insert into "_shiva".sl_confirm (con_origin,
con_received, con_seqno, con_timestamp) values (1, 2, '17993',
now()); commit
transaction;" PGRES_FATAL_ERROR ERROR: role "postgres" does not exist
CONTEXT: SQL statement "DROP FUNCTION formatted_time(float
8);

CREATE OR REPLACE FUNCTION formatted_time(integ
er)
RETURNS "varchar" AS
$BODY$ select ($1 * '1 second'::interval)::
varchar(15)$BODY$
LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION formatted_time(integ
er) OWNER TO postgres;

"
PL/pgSQL function "ddlscript_int" line 53 at execute statement
2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1
: queue event 1,20535
SYNC
2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1
: queue event 1,20536
SYNC
2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1
: queue event 1,20537
SYNC
2006-04-07 10:49:47 CST DEBUG1 slon: shutdown requested
2006-04-07 10:49:47 CST DEBUG2 slon: notify worker process to shutdown
2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1
: queue event 1,20538
SYNC
2006-04-07 10:49:47 CST DEBUG2 slon: wait for worker process to shutdown
2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1
: queue event 1,20539
SYNC
2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1
: queue event 1,20540
SYNC
2006-04-07 10:49:47 CST DEBUG1 main: scheduler mainloop returned
2006-04-07 10:49:47 CST DEBUG2 remoteWorker_event: ignore new events due
to shutdown
2006-04-07 10:49:47 CST DEBUG2 main: wait for remote threads

how can i delete this replication event?, should this work if i create
a postgres role in slave node?
---
Miguel

Christopher Browne

2006-04-07, 1:31 pm

Miguel wrote:
> Hi, im facing an error (stupid me !!!), im replicating between two
> servers, the master server is gentoo and slave is freebsd, in gentoo the
> owner of the database is postgres and in freebsd is pgsql, when i
> created the schema in freebsd a modified every line
>
> ALTER BLABLABLA OWNER TO postgres;
>
> to
>
> ALTER BLABLABLA OWNER TO pgsql;
>
> and edited the conn line in slon_tools.conf according to every node's
> database owner, the replication started without any problem, all was
> working fine until today, i wanted to modify a function, so i created an
> exec script, the problem was that i copied the original function from a
> pgadmin window, edited it and pasted it in a vi screen, **BUT** i forgot
> to edit the owner line, so now slon is dying in the slave node, refusing
> to apply the schema change beacuse in the slave node there isnt a
> postgres role
>
> 2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1
: queue event 1,20534
> SYNC
> 2006-04-07 10:49:47 CST ERROR remoteWorkerThread_1
: "begin transaction;
> set transaction isolation level serializable; lock table "_shiva".sl_
> config_lock; select "_shiva".ddlScript_int(1, 'DROP FUNCTION
> formatted_time(float
8);
>
> CREATE OR REPLACE FUNCTION formatted_time(integ
er)
> RETURNS "varchar" AS
> $BODY$ select ($1 * ''1 second''::interval):
:varchar(15)$BODY$
> LANGUAGE ''sql'' VOLATILE;
> ALTER FUNCTION formatted_time(integ
er) OWNER TO postgres;
>
> ', -1); notify "_shiva_Event"; notify "_shiva_Confirm"; insert into
> "_shiva".sl_event (ev_origin, ev_seqno, ev_timestamp, ev_minxid,
> ev_maxxid, ev_xip, ev_type , ev_data1, ev_data2, ev_data3 ) values
> ('1', '17993', '2006-04-06 17:32:34.498772', '67820709', '67820710', '',
> 'DDL_SCRIPT', '1', 'DROP FUNCTION formatted_time(float
8);
>
> CREATE OR REPLACE FUNCTION formatted_time(integ
er)
> RETURNS "varchar" AS
> $BODY$ select ($1 * ''1 second''::interval):
:varchar(15)$BODY$
> LANGUAGE ''sql'' VOLATILE;
> ALTER FUNCTION formatted_time(integ
er) OWNER TO postgres;
>
> ', '-1'); insert into "_shiva".sl_confirm (con_origin,
> con_received, con_seqno, con_timestamp) values (1, 2, '17993',
> now()); commit
> transaction;" PGRES_FATAL_ERROR ERROR: role "postgres" does not exist
> CONTEXT: SQL statement "DROP FUNCTION formatted_time(float
8);
>
> CREATE OR REPLACE FUNCTION formatted_time(integ
er)
> RETURNS "varchar" AS
> $BODY$ select ($1 * '1 second'::interval)::
varchar(15)$BODY$
> LANGUAGE 'sql' VOLATILE;
> ALTER FUNCTION formatted_time(integ
er) OWNER TO postgres;
>
> "
> PL/pgSQL function "ddlscript_int" line 53 at execute statement
> 2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1
: queue event 1,20535
> SYNC
> 2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1
: queue event 1,20536
> SYNC
> 2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1
: queue event 1,20537
> SYNC
> 2006-04-07 10:49:47 CST DEBUG1 slon: shutdown requested
> 2006-04-07 10:49:47 CST DEBUG2 slon: notify worker process to shutdown
> 2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1
: queue event 1,20538
> SYNC
> 2006-04-07 10:49:47 CST DEBUG2 slon: wait for worker process to shutdown
> 2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1
: queue event 1,20539
> SYNC
> 2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1
: queue event 1,20540
> SYNC
> 2006-04-07 10:49:47 CST DEBUG1 main: scheduler mainloop returned
> 2006-04-07 10:49:47 CST DEBUG2 remoteWorker_event: ignore new events due
> to shutdown
> 2006-04-07 10:49:47 CST DEBUG2 main: wait for remote threads
>
> how can i delete this replication event?, should this work if i create
> a postgres role in slave node?
>
There are multiple possible answers to this, of varying levels of safety.

1. You could always create a 'postgres' user on the system that lacks it.

After doing that, the event will succeed, and all will be well.

You might very well want to have the 'postgres' user around anyways;
pg_dump from the Linux box will have things belonging to that user;
restoring dumps on FreeBSD would break, too.

I rather like this answer.

2. You could delete the event entry on the origin node.

That means the event won't have propagated; you might have to submit it
again, with a more suitable user role...

Definitely *not* as safe...

It's definitely not a good thing to define things that you want
replicated that belong to users that don't exist on all nodes...

Miguel

2006-04-07, 1:31 pm

Christopher Browne wrote:

>Miguel wrote:
>
>
>There are multiple possible answers to this, of varying levels of safety.
>
>1. You could always create a 'postgres' user on the system that lacks it.
>
>After doing that, the event will succeed, and all will be well.
>
>You might very well want to have the 'postgres' user around anyways;
>pg_dump from the Linux box will have things belonging to that user;
>restoring dumps on FreeBSD would break, too.
>
>I rather like this answer.
>
>
>

Me too :-), sounds easier.
Ok, the replication started again after i created the user in slave, im
guessing that the rule "dont make schema changes in a live cluster ,
use excecute scrips instead" dont apply to all objects...

thanks

Christopher Browne

2006-04-07, 1:31 pm

Miguel wrote:
> Me too :-), sounds easier.
> Ok, the replication started again after i created the user in slave,
> im guessing that the rule "dont make schema changes in a live
> cluster , use excecute scrips instead" dont apply to all objects...
If a change to, say, a stored procedure needs to be applied on all nodes
at the same point in the transaction stream, then EXECUTE SCRIPT is the
way to go.

But I don't imagine that's usually the case, and for changes like that,
it may actually be safer to apply such changes by running a script that
connects to each node, and not get Slony-I involved.

Basically, the things you *MUST* apply via EXECUTE SCRIPT are any
changes that directly affect replicated tables or sequences.

- You're altering a non-replicated table? No value to using EXECUTE SCRIPT.

- You're adding a new stored function? No value to using EXECUTE SCRIPT.

- You're replacing a view? No value to using EXECUTE SCRIPT.

Sponsored Links