Slony1 PostgreSQL Replication - NewBie:How to edit the replicate DB

Author

NewBie:How to edit the replicate DB

budi santoso

2005-06-28, 7:24 am

I try to edit the replicate DB. From slave (Replicate
DB) I dump that DB (from slave), and create new DB,
and got error when trying to edit.=20

the message : Slony-I: Table bsadjbs is replicated and
cannot be modified on a subscriber node.

1. how to make that DB (tables) are writeable? is it
posible?=20

Thank
Santoso

=09
____________________
______________=20
Yahoo! Mail=20
Stay connected, organized, and protected. Take the tour:=20
http://tour.mail.yahoo.com/mailtour.html=20

cbbrowne-swQf4SbcV9C7WVzo/KQ3Mw@public.gmane.org

2005-06-28, 7:24 am

> I try to edit the replicate DB. From slave (Replicate
> DB) I dump that DB (from slave), and create new DB,
> and got error when trying to edit.
>
> the message : Slony-I: Table bsadjbs is replicated and
> cannot be modified on a subscriber node.
>
> 1. how to make that DB (tables) are writeable? is it
> posible?

I take it that what you want to do is to turn some replicated copy into a
"plain unreplicated" database, right?

There is a bit of a problem with taking a pg_dump of a subscriber in that
Slony-I "corrupts" the rules/triggers on each subscriber, pointing them t=
o
the primary key. I'm not just sure what falls out when you do that...

The "ideally right" way to do this is to take the subscriber node (not
dumped), and basically switch it off. Supposing it's node #4...

include <preamble.slonik>;
DROP NODE (ID=3D4);
UNINSTALL NODE (ID=3D4);

In view of your having created a dumped copy in the database
'dumped_copy', well, you could define a little slonik script...

cluster name=3Dfoo;
node 1 admin conninfo=3D'dbname=3
Ddumped_copy';
uninstall node (id=3D1);

That should clean out the Slony-I-specific objects for cluster 'foo'; the
results for those fudged-with-triggers may be a tad incomplete...

Thomas Pundt

2005-06-28, 9:24 am

On Tuesday 28 June 2005 14:28, cbbrowne-swQf4SbcV9C7WVzo/KQ3Mw@public.gmane.org wrote:
| In view of your having created a dumped copy in the database
| 'dumped_copy', well, you could define a little slonik script...
|
| cluster name=foo;
| node 1 admin conninfo='dbname=dum
ped_copy';
| uninstall node (id=1);
|
| That should clean out the Slony-I-specific objects for cluster 'foo'; the
| results for those fudged-with-triggers may be a tad incomplete...

it might useful to empty the tables _foo.sl_table and _foo.sl_sequence
beforehand - but I don't have the details at hand right now...

Ciao,
Thomas

--
Dr. Thomas Pundt <thomas.pundt- TxOGGw7xYDWoYr4blSSd
5g@public.gmane.org> ---- http://rp-online.de/ ----

Christopher Browne

2005-06-28, 9:24 am

Thomas Pundt wrote:

>On Tuesday 28 June 2005 14:28, cbbrowne-swQf4SbcV9C7WVzo/KQ3Mw@public.gmane.org wrote:
>| In view of your having created a dumped copy in the database
>| 'dumped_copy', well, you could define a little slonik script...
>|
>| cluster name=foo;
>| node 1 admin conninfo='dbname=dum
ped_copy';
>| uninstall node (id=1);
>|
>| That should clean out the Slony-I-specific objects for cluster 'foo'; the
>| results for those fudged-with-triggers may be a tad incomplete...
>
>it might useful to empty the tables _foo.sl_table and _foo.sl_sequence
>beforehand - but I don't have the details at hand right now...
>
>
The OTHER alternative is to do...

drop schema _foo cascade;

That will drop out the Slony-I schema, and hence drop out the stored
functions, and, via CASCADE, drop out the triggers that use those stored
functions.

"drop schema" doesn't do several things that you might want:

1. If there are tables with a 'fabricated' Slony-I primary key on a
column created for that purpose, that column won't be dropped

2. "drop schema" won't do anything about recovering triggers to their
former states (although if the DB was a pg_dump | psql copy, the "lost
triggers" may be well and truly lost...)

I don't see there anything terribly useful about fiddling with
sl_table/sl_sequence in the process...

Jan Wieck

2005-06-28, 11:24 am

On 6/28/2005 10:39 AM, Christopher Browne wrote:
> Thomas Pundt wrote:
>
> The OTHER alternative is to do...
>
> drop schema _foo cascade;

The way to go is to use the script tools/ slony1_extract_schem
a.sh
against the ORIGIN and pgdump -a (data-only) against a SUBSCRIBER.
Combine that information back into an empty database and you have a
clean database.

Jan

>
> That will drop out the Slony-I schema, and hence drop out the stored
> functions, and, via CASCADE, drop out the triggers that use those stored
> functions.
>
> "drop schema" doesn't do several things that you might want:
>
> 1. If there are tables with a 'fabricated' Slony-I primary key on a
> column created for that purpose, that column won't be dropped
>
> 2. "drop schema" won't do anything about recovering triggers to their
> former states (although if the DB was a pg_dump | psql copy, the "lost
> triggers" may be well and truly lost...)
>
> I don't see there anything terribly useful about fiddling with
> sl_table/sl_sequence in the process...
> ____________________
____________________
_______
> Slony1-general mailing list
> Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> http://gborg.postgresql.org/mailman.../slony1-general

--
#===================
====================
====================
===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#===================
====================
=========== JanWieck- bwPqjjyvM7QAvxtiuMwx
3w@public.gmane.org #

Sponsored Links