Slony1 PostgreSQL Replication - Manually removing slony

Author

Manually removing slony

Glen Eustace

2006-04-04, 3:29 am

Slony seems to have packed a sad for us and I am trying to start over.
I can not uninstall from the master as slony tells me there is an error
with a 'set 9', which I don't recall ever creating. It certainly isn't
in the table of sets.

<stdin>:8: PGRES_FATAL_ERROR select "_gz_admin".uninstallNode(); -
ERROR: Slony-I: Table with id 9 not found
CONTEXT: SQL statement "SELECT "_gz_admin".alterTableRestore( $1 )"
PL/pgSQL function "uninstallnode" line 14 at perform

How can I clean up my database by hand so that I can re-install ?
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen and Rosanne Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 5301
Ph/Fax: +64 6 357 8168, Mob: +64 27 5 424 015, Web: www.godzone.net.nz

"A Ministry specialising in providing low-cost professional Internet
Services to NZ Christian Churches, Ministries and Organisations"

Victoria Parsons

2006-04-04, 3:29 am

Hi Glen

The problem is with table 9, not set 9. You can see which table that
refers to by looking in _gz_admin.sl_table.

The error is on alterTableRestore() function which is the part that
tries to restore the table to state it was in before slony was added.
This means putting back any rules and triggers that slony had "hidden".
When I see this problem its because I have put a new rule on the table
since slony was added, and the restore function doesn't know what to do
with it. Check if your table has any rules or triggers and delete them
(except the slony trigger) and make sure any rules it would be trying to
add back are still applicable. i.e. you haven't removed any of the
columns or tables they referred to. You can see which these are by
doing: "select * from pg_rules where tablename like '<TABLENAME>_*'".
The tablename you are looking for is actually
<tablename>_<primarykeyname>. If you have set up primary keys on your
tables by just creating one of the columns with PRIMARY KEY suffix the
tablename will be '<tablename>_pkey' E.g. if table 9 is called testdata
select where tablename='testdata_
pkey'.

Hope this helps you clear it up,
Vicki.

-----Original Message-----
From: slony1-general-bounces- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
[mailto:slony1-general-bounces- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org] On Behalf Of Glen
Eustace
Sent: 04 April 2006 06:19
To: slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
Subject: [Slony1-general] Manually removing slony

Slony seems to have packed a sad for us and I am trying to start over.
I can not uninstall from the master as slony tells me there is an error
with a 'set 9', which I don't recall ever creating. It certainly isn't
in the table of sets.

<stdin>:8: PGRES_FATAL_ERROR select "_gz_admin".uninstallNode(); -
ERROR: Slony-I: Table with id 9 not found
CONTEXT: SQL statement "SELECT "_gz_admin".alterTableRestore( $1 )"
PL/pgSQL function "uninstallnode" line 14 at perform

How can I clean up my database by hand so that I can re-install ?
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen and Rosanne Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 5301
Ph/Fax: +64 6 357 8168, Mob: +64 27 5 424 015, Web: www.godzone.net.nz

"A Ministry specialising in providing low-cost professional Internet
Services to NZ Christian Churches, Ministries and Organisations"

____________________
____________________
_______
Slony1-general mailing list
Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
http://gborg.postgresql.org/mailman.../slony1-general

This message should be regarded as confidential. If you have received this
email in error please notify the sender and destroy it immediately.
Statements of intent shall only become binding when confirmed in hard copy
by an authorized signatory.

Hannu Krosing

2006-04-04, 7:42 am

w5xoZWwga2VuYWwgcMOk
ZXZhbCwgVCwgMjAwNi0w
NC0wNCBrZWxsIDE3OjE4
LCBraXJqdXRhcyBH
bGVuIEV1c3RhY2U6Cj4g
U2xvbnkgc2VlbXMgdG8g
aGF2ZSBwYWNrZWQgYSBz
YWQgZm9yIHVzIGFu
ZCBJIGFtIHRyeWluZyB0
byBzdGFydCBvdmVyLgo+
IEkgY2FuIG5vdCB1bmlu
c3RhbGwgZnJvbSB0
aGUgbWFzdGVyIGFzIHNs
b255IHRlbGxzIG1lIHRo
ZXJlIGlzIGFuIGVycm9y
Cj4gd2l0aCBhICdz
ZXQgOScsIHdoaWNoIEkg
ZG9uJ3QgcmVjYWxsIGV2
ZXIgY3JlYXRpbmcuICBJ
dCBjZXJ0YWlubHkg
aXNuJ3QKPiBpbiB0aGUg
dGFibGUgb2Ygc2V0cy4K
PiAKPiA8c3RkaW4+Ojg6
IFBHUkVTX0ZBVEFM
X0VSUk9SIHNlbGVjdCAi
X2d6X2FkbWluIi51bmlu
c3RhbGxOb2RlKCk7ICAt
Cj4gRVJST1I6ICBT
bG9ueS1JOiBUYWJsZSB3
aXRoIGlkIDkgbm90IGZv
dW5kCj4gQ09OVEVYVDog
IFNRTCBzdGF0ZW1l
bnQgIlNFTEVDVCAgIl9n
el9hZG1pbiIuYWx0ZXJU
YWJsZVJlc3RvcmUoICQx
ICkiCj4gUEwvcGdT
UUwgZnVuY3Rpb24gInVu
aW5zdGFsbG5vZGUiIGxp
bmUgMTQgYXQgcGVyZm9y
bQo+IAo+IEhvdyBj
YW4gSSBjbGVhbiB1cCBt
eSBkYXRhYmFzZSBieSBo
YW5kIHNvIHRoYXQgSSBj
YW4gcmUtaW5zdGFs
bCA/ CgpDcmVhdGUgYSBkdW1t
eSB0YWJsZSBmb3IgaWQg
OSAoY2hlY2sgZnJvbSBf
Z3pfYWRtaW4u
cGdfdGFibGVzKQoKLS0t
LS0tLQpIYW5udQoKX19f
X19fX19fX19fX19fX19f
X19fX19fX19fX19f
X19fX19fX19fX19fX19f
X18KU2xvbnkxLWdlbmVy
YWwgbWFpbGluZyBsaXN0
ClNsb255MS1nZW5l
cmFsQGdib3JnLnBvc3Rn
cmVzcWwub3JnCmh0dHA6
Ly9nYm9yZy5wb3N0Z3Jl
c3FsLm9yZy9tYWls
bWFuL2xpc3RpbmZvL3Ns
b255MS1nZW5lcmFsCg==

Jan Wieck

2006-04-04, 11:33 am

On 4/4/2006 1:18 AM, Glen Eustace wrote:

> Slony seems to have packed a sad for us and I am trying to start over.
> I can not uninstall from the master as slony tells me there is an error
> with a 'set 9', which I don't recall ever creating. It certainly isn't
> in the table of sets.
>
> <stdin>:8: PGRES_FATAL_ERROR select "_gz_admin".uninstallNode(); -
> ERROR: Slony-I: Table with id 9 not found
> CONTEXT: SQL statement "SELECT "_gz_admin".alterTableRestore( $1 )"
> PL/pgSQL function "uninstallnode" line 14 at perform
>
> How can I clean up my database by hand so that I can re-install ?

As others already pointed out, it is due to the missing table 9.
Apparently you dropped that table while the database was replicated.
Please note that it is not recommended to execute DDL directly against a
Slony-I replicated database. Please see the slonik command EXECUTE
SCRIPT for details.

Jan

--
#===================
====================
====================
===========#
# 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 #

Glen Eustace

2006-04-04, 8:29 pm

Table 9 still exists in _gz_admin.sl_table;

9 | 9527801 | domain_registry_hist
ory | public | 3 |
domain_registry_hist
ory_pkey | t | Domain Registry

and still exists for real in the database.

It has the following rules and indices;
Indexes:
" domain_registry_hist
ory_pkey" PRIMARY KEY, btree ("domain", tstamp)
" domain_registry_hist
ory_idx1" btree (client, "domain", tstamp)
Triggers:
_gz_admin_logtrigger
_9 AFTER INSERT OR DELETE OR UPDATE ON
domain_registry_hist
ory FOR EACH ROW EXECUTE PROCEDURE
_gz_admin. logtrigger('_gz_admi
n', '9', 'vkkvvvvvvvvvvvvv')

It also has a view which includes update rules.

Where to next ?

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen and Rosanne Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 5301
Ph/Fax: +64 6 357 8168, Mob: +64 27 5 424 015, Web: www.godzone.net.nz

"A Ministry specialising in providing low-cost professional Internet
Services to NZ Christian Churches, Ministries and Organisations"

Marco Canderle

2006-04-04, 8:29 pm

On 4/4/06, Glen Eustace <geustace- YIUI9QZ1iGQ5w5cz1W7O
ew@public.gmane.org> wrote:
>
>
>
> How can I clean up my database by hand so that I can re-install ?
> -

Hi Glen,

I don't know if this is advisable for a working database, but for my test
databases I have started over again many times. What I do is simple: I drop
(cascade) the schema Slony creates when you first set up all the replication
system. For example if the schema created by slony is _slony_example1, I run
this script in the psql console: drop schema _slony_example1 cascade;

This drops the slony information and leaves the database clean of slony
"things". then you can start from the beginning with slony. As I said, I
CAN'T GUARANTEE THAT THIS WON'T HARM YOUR DATABASE, but I haven't had any
problem at all with my database after droping the slony schema manually.
Anyway, if you do this, be sure to try it first in a TEST server NOT IN THE
REAL ONE. I don't think you will have problems but..however:

Does anyone knows if there is a potential problem if you do this?

--
********************
************
Marco A. Canderle
marcocanderle- Re5JQEeQqe8AvxtiuMwx
3w@public.gmane.org
********************
************

Jan Wieck

2006-04-04, 8:29 pm

On 4/4/2006 2:58 PM, Marco Canderle wrote:

> On 4/4/06, Glen Eustace <geustace- YIUI9QZ1iGQ5w5cz1W7O
ew@public.gmane.org> wrote:
>
>
>
>
> Hi Glen,
>
> I don't know if this is advisable for a working database, but for my test
> databases I have started over again many times. What I do is simple: I drop
> (cascade) the schema Slony creates when you first set up all the replication
> system. For example if the schema created by slony is _slony_example1, I run
> this script in the psql console: drop schema _slony_example1 cascade;
>
> This drops the slony information and leaves the database clean of slony
> "things". then you can start from the beginning with slony. As I said, I
> CAN'T GUARANTEE THAT THIS WON'T HARM YOUR DATABASE, but I haven't had any
> problem at all with my database after droping the slony schema manually.
> Anyway, if you do this, be sure to try it first in a TEST server NOT IN THE
> REAL ONE. I don't think you will have problems but..however:
>
> Does anyone knows if there is a potential problem if you do this?

DON'T do this ever on a subscriber!!!

It will leave user triggers and rewrite rules pointing to the tables
index instead of the table. So if your database has any triggers,
foreign key constraints or rewrite rules, you will end up with a
corrupted system catalog.

As of now, it will work on the origin.

Jan

--
#===================
====================
====================
===========#
# 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 #

Jan Wieck

2006-04-04, 8:29 pm

On 4/4/2006 2:49 PM, Glen Eustace wrote:

> Table 9 still exists in _gz_admin.sl_table;
>
> 9 | 9527801 | domain_registry_hist
ory | public | 3 |
> domain_registry_hist
ory_pkey | t | Domain Registry
>
> and still exists for real in the database.

This is odd. Can it be that the table has been dropped and recreated,
and thereby it's OID changed? If the tables OID in pg_class is different
from the tab_reloid in sl_table (9527801), that would explain the error.

There is an (undocumented?) slonik command

REPAIR CONFIG
(set id = <int>[, event node = <int>] [, execute only on = <int>]);

Which should take care of this problem.

Why is it undocumented? Chris?

Jan

>
> It has the following rules and indices;
> Indexes:
> " domain_registry_hist
ory_pkey" PRIMARY KEY, btree ("domain", tstamp)
> " domain_registry_hist
ory_idx1" btree (client, "domain", tstamp)
> Triggers:
> _gz_admin_logtrigger
_9 AFTER INSERT OR DELETE OR UPDATE ON
> domain_registry_hist
ory FOR EACH ROW EXECUTE PROCEDURE
> _gz_admin. logtrigger('_gz_admi
n', '9', 'vkkvvvvvvvvvvvvv')
>
> It also has a view which includes update rules.
>
> Where to next ?
>

--
#===================
====================
====================
===========#
# 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 #

Darcy Buskermolen

2006-04-04, 8:29 pm

On Tuesday 04 April 2006 13:03, Jan Wieck wrote:
> On 4/4/2006 2:49 PM, Glen Eustace wrote:
>
> This is odd. Can it be that the table has been dropped and recreated,
> and thereby it's OID changed? If the tables OID in pg_class is different
> from the tab_reloid in sl_table (9527801), that would explain the error.
>
> There is an (undocumented?) slonik command
>
> REPAIR CONFIG
> (set id = <int>[, event node = <int>] [, execute only on = <int>]);
>
> Which should take care of this problem.
>
> Why is it undocumented? Chris?

I'm sure I submitted documentation on this. Let me check my local repo
[color=darkred]
>
>
> Jan
>

--
Darcy Buskermolen
Wavefire Technologies Corp.

http://www.wavefire.com
ph: 250.717.0200
fx: 250.763.1759

Darcy Buskermolen

2006-04-04, 8:29 pm

On Tuesday 04 April 2006 13:15, Darcy Buskermolen wrote:
> On Tuesday 04 April 2006 13:03, Jan Wieck wrote:
>
> I'm sure I submitted documentation on this. Let me check my local repo

Ok good I'm not loosing my mind I did infact commit it, see rev 1.14 of
doc/howto/slonik_commands.html By the looks of it this was happening at
about the same time as Chris was sgmlifying the docs.

[color=darkred]
>

--
Darcy Buskermolen
Wavefire Technologies Corp.

http://www.wavefire.com
ph: 250.717.0200
fx: 250.763.1759

Christopher Browne

2006-04-04, 8:29 pm

Darcy Buskermolen <darcy- QSklyHmfoqpWk0Htik3J
/w@public.gmane.org> writes:

> On Tuesday 04 April 2006 13:15, Darcy Buskermolen wrote:
>
> Ok good I'm not loosing my mind I did infact commit it, see rev 1.14 of
> doc/howto/slonik_commands.html By the looks of it this was happening at
> about the same time as Chris was sgmlifying the docs.

Then I'll see about adding this in...

Unfortunate that this wasn't caught sooner...
--
(format nil "~S@~S" "cbbrowne" "ca.afilias.info")
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Glen Eustace

2006-04-04, 8:29 pm

> This is odd. Can it be that the table has been dropped and recreated,
> and thereby it's OID changed? If the tables OID in pg_class is different
> from the tab_reloid in sl_table (9527801), that would explain the error.

Thank you, that is exactly what happened. About 4 months ago, I had to
make a varchar field longer and did so by using pg_dump. I had
forgotten all about it. It seemed such a trivial change at the time.

> There is an (undocumented?) slonik command
>
> REPAIR CONFIG
> (set id = <int>[, event node = <int>] [, execute only on = <int>]);
>
> Which should take care of this problem.

I'll give this a go.

Bingo.

Thanks heaps, worked like a charm !!

Glen.

Christopher Browne

2006-04-04, 8:29 pm

Jan Wieck <JanWieck- bwPqjjyvM7QAvxtiuMwx
3w@public.gmane.org> writes:
> There is an (undocumented?) slonik command
>
> REPAIR CONFIG
> (set id = <int>[, event node = <int>] [, execute only on = <int>]);
>
> Which should take care of this problem.
>
> Why is it undocumented? Chris?

It evidently got missed because Darcy added it at about the same time
I wrote up slonik_ref.sgml, the "SGML-ized" version of the docs.

I have drawn that out of CVS, and put in a version in the Slonik
reference in CVS HEAD. It'll be in 1.2, and presumably will get
published in some of the usual places perhaps even sooner...
--
output = reverse("ofni.sailifa.ac" "@" "enworbbc")
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Sponsored Links