headers
Raghav | 1 Jul 2012 14:32
Picon

Re: Swapping Providers

Sorry for late on this, its due to actual Production swap implementation. Though I tested as suggested, I didnt get time to post my reply. 
After doing vigorous testing by taking into consideration Steve & Jan points, we succeded it without any problem.

This analysis is flawed. The two event numbers are from different origins and therefore, don't compare to each other. The combination of ev_origin,ev_seqno can never be higher on any node, than it is on the origin itself.

Yes, my analysis has flaw. Its no where related to SEQ.No...which I analyzed and every node seq.no number is independed by itself. 

To simulate this problem, Steve and I were pointing out, do the following:
1. Create your setup as before.
2. Stop the streaming replication (simulating the network communication problem)
3. Update a row on the master and wait for the SYNC to replicate.
4. Stop the slon processes. DO NOT let the streaming replica catch up with the now DEAD master. Assume the master and all its data, including WAL, have become unavailable.
5. Promote DR-master and do the two store path commands.
6. Start slon processes.
7. Update another row on the new master.
8. Compare table content on master and slave.
You can detect the problem before step 5 by comparing the ev_seqno with ev_origin=old-master on the DR-master and slave. Whichever is higher should be promoted to master. In the unlikely case that it is the Slony slave, you will have to rebuild the DR-master from scratch, though.

Yep, I could able to detect before Step 5. 

In short of our actual production swap, we stopped application(NO DML's) and Slony. Checked DR-master(which is warm standby) is complete sync with Master(primary). Promoted DR-master, did changes to store_path on both the ends. Started slon on both the nodes. Sync catch-up well. We have taken complete care of NOT happening any Step 2 or 3 though .. :)

Thanks once again.

--Raghav

_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
Permalink | Reply | 0 comments |
headers
Efraín Déctor | 2 Jul 2012 20:49

slonik_execute_script not working

Hello. I’m testin Slony, everything is running fine in terms of replication, however I’m trying to test adding a column to a table that is beaing replicated.
 
I’m using altperl, so using the documentation I see that using slonik_execute_script should help me to create the new column.
 
This is how I am using it:

slonik_execute_script 1 /root/script.sql
 
After I execute the script this message is shown:
 
cluster name = replication;
node 1 admin conninfo='host=192.168.20.144 dbname=nomina user=pgsql port=5432';
node 2 admin conninfo='host=192.168.20.210 dbname=nomina user=pgsql port=5432;
  execute script (
    set id = 1,
    filename = '/tmp/EpbvGiNpWb',
    event node = 1
  );
 
script.sql contains:  ALTER TABLE personas ADD COLUMN prueba integer;
 
However, nothing happens on the master or slave.

Could you please, tell me what Im doing wrong?.
 
Thank you in advance.
 
_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
Permalink | Reply | 6 comments |
headers
Wolf Schwurack | 2 Jul 2012 21:29
Favicon
Gravatar

Re: slonik_execute_script not working

You can not run DDL statement using slony, it states that in slony docs

      0___      Wolfgang Schwurack
     c/  /'_    SA/DBA - UEN
    (*)  \(*)   801-587-9444
                wolf-OzwJb8Xg5oY@public.gmane.org

From: Efraín Déctor <efraindector-nD6ai/4ffjJWk0Htik3J/w@public.gmane.org>
Organization: HESA Técnica
Date: Monday, July 2, 2012 12:49 PM
To: "slony1-general-8kkgcvHRObyz5F2/bZa4Fw@public.gmane.org" <slony1-general-8kkgcvHRObxiDHKYdvu0aQ@public.gmane.orginfo>
Subject: [Slony1-general] slonik_execute_script not working

Hello. I’m testin Slony, everything is running fine in terms of replication, however I’m trying to test adding a column to a table that is beaing replicated.
 
I’m using altperl, so using the documentation I see that using slonik_execute_script should help me to create the new column.
 
This is how I am using it:

slonik_execute_script 1 /root/script.sql
 
After I execute the script this message is shown:
 
cluster name = replication;
node 1 admin conninfo='host=192.168.20.144 dbname=nomina user=pgsql port=5432';
node 2 admin conninfo='host=192.168.20.210 dbname=nomina user=pgsql port=5432;
  execute script (
    set id = 1,
    filename = '/tmp/EpbvGiNpWb',
    event node = 1
  );
 
script.sql contains:  ALTER TABLE personas ADD COLUMN prueba integer;
 
However, nothing happens on the master or slave.

Could you please, tell me what Im doing wrong?.
 
Thank you in advance.
 
 
_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
Permalink | Reply | 3 comments |
headers
Guillaume Lelarge | 2 Jul 2012 22:37
Favicon
Gravatar

Re: slonik_execute_script not working

On Mon, 2012-07-02 at 13:49 -0500, Efraín Déctor wrote:
> Hello. I’m testin Slony, everything is running fine in terms of replication, however I’m trying to
test adding a column to a table that is beaing replicated.
> 
> I’m using altperl, so using the documentation I see that using slonik_execute_script should help me to
create the new column.
> 
> This is how I am using it: 
> 
> slonik_execute_script 1 /root/script.sql
> 
> After I execute the script this message is shown:
> 
> cluster name = replication;
> node 1 admin conninfo='host=192.168.20.144 dbname=nomina user=pgsql port=5432';
> node 2 admin conninfo='host=192.168.20.210 dbname=nomina user=pgsql port=5432;
>   execute script (
>     set id = 1,
>     filename = '/tmp/EpbvGiNpWb',
>     event node = 1
>   );
> 
> script.sql contains:  ALTER TABLE personas ADD COLUMN prueba integer;
> 
> However, nothing happens on the master or slave. 
> 
> Could you please, tell me what Im doing wrong?.
> 

You forgot to send the slonik script to slonik. IOW:

slonik_execute_script 1 /root/script.sql | slonik

--

-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

_______________________________________________
Slony1-general mailing list
Slony1-general <at> lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general
Permalink | Reply | 1 comment |
headers
Efraín Déctor | 3 Jul 2012 00:05

Re: slonik_execute_script not working

That was it. It worked.

Thank you really much.

-----Mensaje original----- 
From: Guillaume Lelarge
Sent: Monday, July 02, 2012 3:37 PM
To: Efraín Déctor
Cc: slony1-general <at> lists.slony.info
Subject: Re: [Slony1-general] slonik_execute_script not working

On Mon, 2012-07-02 at 13:49 -0500, Efraín Déctor wrote:
> Hello. I’m testin Slony, everything is running fine in terms of 
> replication, however I’m trying to test adding a column to a table that is 
> beaing replicated.
>
> I’m using altperl, so using the documentation I see that using 
> slonik_execute_script should help me to create the new column.
>
> This is how I am using it:
>
> slonik_execute_script 1 /root/script.sql
>
> After I execute the script this message is shown:
>
> cluster name = replication;
> node 1 admin conninfo='host=192.168.20.144 dbname=nomina user=pgsql 
> port=5432';
> node 2 admin conninfo='host=192.168.20.210 dbname=nomina user=pgsql 
> port=5432;
>   execute script (
>     set id = 1,
>     filename = '/tmp/EpbvGiNpWb',
>     event node = 1
>   );
>
> script.sql contains:  ALTER TABLE personas ADD COLUMN prueba integer;
>
> However, nothing happens on the master or slave.
>
> Could you please, tell me what Im doing wrong?.
>

You forgot to send the slonik script to slonik. IOW:

slonik_execute_script 1 /root/script.sql | slonik

--

-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

_______________________________________________
Slony1-general mailing list
Slony1-general <at> lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general
Permalink | Reply | 0 comments |
headers
Vick Khera | 4 Jul 2012 21:39

Re: slonik_execute_script not working

On Mon, Jul 2, 2012 at 3:29 PM, Wolf Schwurack <wolf <at> uen.org> wrote:
You can not run DDL statement using slony, it states that in slony docs

Where? That's the only safe way to perform DDL on a replicated table.

_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
Permalink | Reply | 2 comments |
headers
Jan Wieck | 6 Jul 2012 04:41
Picon
Favicon

Re: slonik_execute_script not working

On 7/4/2012 3:39 PM, Vick Khera wrote:
> On Mon, Jul 2, 2012 at 3:29 PM, Wolf Schwurack <wolf@...
> <mailto:wolf@...>> wrote:
>
>     You can not run DDL statement using slony, it states that in slony docs
>
>
> Where? That's the only safe way to perform DDL on a replicated table.

In 1.x yes. Since 2.0 DDL can be done directly with psql or by other 
means. If doing so, one needs to be careful which modifications are done 
to which nodes in which order so that inserts/updates aren't coming into 
a node with columns or things like that. But Slony by itself won't stand 
in the way any more.

Jan

--

-- 
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin
Permalink | Reply | 1 comment |
headers
Glyn Astill | 6 Jul 2012 10:26
Picon
Favicon

Re: slonik_execute_script not working

______________________

> From: Jan Wieck <JanWieck@...>
>To: Vick Khera <vivek@...> 
>Cc: "slony1-general@..."
<slony1-general@...> 
>Sent: Friday, 6 July 2012, 3:41
>Subject: Re: [Slony1-general] slonik_execute_script not working
> 
>On 7/4/2012 3:39 PM, Vick Khera wrote:
>> On Mon, Jul 2, 2012 at 3:29 PM, Wolf Schwurack <wolf@...
>> <mailto:wolf@...>> wrote:
>>
>>     You can not run DDL statement using slony, it states that in slony docs
>>
>>
>> Where? That's the only safe way to perform DDL on a replicated table.
>
>In 1.x yes. Since 2.0 DDL can be done directly with psql or by other 
>means. If doing so, one needs to be careful which modifications are done 
>to which nodes in which order so that inserts/updates aren't coming into 
>a node with columns or things like that. But Slony by itself won't stand 
>in the way any more.
>

Hi Jan,

I'm assuming most rules regarding replicated tables that applied in 1.x still apply in 2.0 though, i.e.
slony still needs to know about extra colums added outside of execure script, primary keys etc.

Disregarding things such as trigger activity etc, isn't Vick's statement is still correct if the DDL
changes the structure or primary keys of  the tables?  Slony needs to know about those changes.

We're on 2.0 and I've got a console that reads the slony configuration and allows our devs to execute a
restricted set of DDL under their own credentials via a 2 phase commit, the rest is done via execute script.
Permalink | Reply | 0 comments |
headers
Stuart Bishop | 9 Jul 2012 08:21
Gravatar

Initial replication failing due to collation error

Anyone seen something like this before?

2012-07-09 04:48:59 UTC[4137] ERROR  remoteWorkerThread_1: "select
"_sl".finishTableAfterCopy(20); analyze "public"."reviewsapp_review";
" PGRES_FATAL_ERROR ERROR:  could not determine which collation to use
for string comparison
HINT:  Use the COLLATE clause to set the collation explicitly.
CONTEXT:  SQL statement "reindex table "public"."reviewsapp_review""
PL/pgSQL function "finishtableaftercopy" line 27 at EXECUTE statement

I'm replicating an 8.4 master to a 9.1 slave as part of an upgrade.
The only thing unusual about this table I can see is the column using
the debversion type, but we have successfully done the 8.4 -> 9.1
replication with other databases using that type before.

                                                 Table
"public.reviewsapp_review"
          Column          |           Type           |
                Modifiers
--------------------------+--------------------------+----------------------------------------------------------------------------
 id                       | integer                  | not null
default nextval('reviewsapp_review_id_seq'::regclass)
 softwareitem_id          | integer                  | not null
 version                  | debversion               | not null
 architecture_id          | integer                  | not null
 repository_id            | integer                  | not null
 reviewer_id              | integer                  | not null
 date_created             | timestamp with time zone | not null
default '2010-12-09 20:47:18.910573+00'::timestamp with time zone
 rating                   | integer                  | not null
 summary                  | character varying(80)    | not null
 review_text              | character varying(5000)  | not null
 language                 | character varying(10)    | not null
 hide                     | boolean                  | not null default false
 usefulness_total         | integer                  | not null default 0
 usefulness_favorable     | integer                  | not null default 0
 usefulness_percentage    | integer                  | not null default 0
 app_name                 | character varying(100)   | not null
default ''::character varying
 date_deleted             | timestamp with time zone |
 usefulness_wilson_rating | double precision         | not null default 0.0
Indexes:
    "reviewsapp_review_pkey" PRIMARY KEY, btree (id)
    "reviewsapp_review_architecture_id" btree (architecture_id)
    "reviewsapp_review_hide" btree (hide)
    "reviewsapp_review_language" btree (language)
    "reviewsapp_review_repository_id" btree (repository_id)
    "reviewsapp_review_reviewer_id" btree (reviewer_id)
    "reviewsapp_review_softwareitem_id" btree (softwareitem_id)
    "reviewsapp_review_version" btree (version)
Foreign-key constraints:
    "architecture_id_refs_id_3635e112ecae648a" FOREIGN KEY
(architecture_id) REFERENCES reviewsapp_architecture(id) DEFERRABLE
INITIALLY DEFERRED
    "repository_id_refs_id_2f7062e6db353aa7" FOREIGN KEY
(repository_id) REFERENCES reviewsapp_repository(id) DEFERRABLE
INITIALLY DEFERRED
    "reviewer_id_refs_id_28b87f276a073e1e" FOREIGN KEY (reviewer_id)
REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "softwareitem_id_refs_id_677a12388e4ffc27" FOREIGN KEY
(softwareitem_id) REFERENCES reviewsapp_softwareitem(id) DEFERRABLE
INITIALLY DEFERRED
Referenced by:
    TABLE "reviewsapp_usefulness" CONSTRAINT
"review_id_refs_id_286d40f88d5acdc" FOREIGN KEY (review_id) REFERENCES
reviewsapp_review(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "reviewsapp_reviewmoderation" CONSTRAINT
"review_id_refs_id_6bfc7f3d50ef538b" FOREIGN KEY (review_id)
REFERENCES reviewsapp_review(id) DEFERRABLE INITIALLY DEFERRED

--

-- 
Stuart Bishop <stuart@...>
http://www.stuartbishop.net/
Permalink | Reply | 1 comment |
headers
Stuart Bishop | 9 Jul 2012 10:59
Gravatar

Re: Initial replication failing due to collation error

On Mon, Jul 9, 2012 at 1:21 PM, Stuart Bishop <stuart@...> wrote:
> Anyone seen something like this before?
>
> 2012-07-09 04:48:59 UTC[4137] ERROR  remoteWorkerThread_1: "select
> "_sl".finishTableAfterCopy(20); analyze "public"."reviewsapp_review";
> " PGRES_FATAL_ERROR ERROR:  could not determine which collation to use
> for string comparison
> HINT:  Use the COLLATE clause to set the collation explicitly.
> CONTEXT:  SQL statement "reindex table "public"."reviewsapp_review""
> PL/pgSQL function "finishtableaftercopy" line 27 at EXECUTE statement

I've sorted this.

For the record, I think the sequence of actions to cause this was:
  - Build the db schema on the slave database.
  - Install the debversion package on the slave server, installing its
.so and support files.
  - Attempt to set up slony replication

Those first two steps are obviously the wrong way around, yet I'm
pretty certain no errors were reported so I don't know what happened.
Dropping the new node and dropping the database, recreating it and
resubscribing worked fine.

--

-- 
Stuart Bishop <stuart@...>
http://www.stuartbishop.net/
Permalink | Reply | 0 comments |

Gmane