Tignor, Tom | 28 Jun 16:15 2016

cannot safely MOVE SET, DROP NODE

 

                Hello slony community,

                I’m working now on some slony1 failover automation (slony1-2.2.4) and I’m having a lot of trouble getting slony1 to honor MOVE SET commands. Below are the commands I’m using, to my mind pretty simple instructions to move a set and confirm everyone gets the message (wait for event, confirmed by all) before moving on. Shortly after moving on, I’m dropping the former set origin node. I suspect that’s why one of subscribers enters the perpetual “MOVE_SET not received yet” loop, which I know isn’t a new slony1 problem. Is there any reason this shouldn’t work? The FAILOVER command never does this. In the scenario I’m working on, I would have liked to avoid the heavy hammer, but do I need to use FAILOVER to make this happen reliably?

                Thanks in advance,

 

 

--- slonik commands ----

lock set (id=1, origin=1);

move set (id=1, old origin=1, new origin=3);

wait for event (origin=3, confirmed=all,

                        wait on=3, timeout=60);

----

 

---- remote subscriber log ----

2016-06-28 12:23:26 UTC [19800] INFO   start processing ACCEPT_SET

2016-06-28 12:23:26 UTC [19800] INFO   ACCEPT: set=1

2016-06-28 12:23:26 UTC [19800] INFO   ACCEPT: old origin=1

2016-06-28 12:23:26 UTC [19800] INFO   ACCEPT: new origin=3

2016-06-28 12:23:26 UTC [19800] INFO   ACCEPT: move set seq=5000000008

2016-06-28 12:23:26 UTC [19800] INFO   got parms ACCEPT_SET

2016-06-28 12:23:26 UTC [19800] INFO   ACCEPT_SET - MOVE_SET not received yet - sleep

2016-06-28 12:23:36 UTC [19800] INFO   ACCEPT_SET - MOVE_SET not received yet - sleep

2016-06-28 12:23:46 UTC [19800] INFO   ACCEPT_SET - MOVE_SET not received yet - sleep

2016-06-28 12:23:56 UTC [19800] INFO   ACCEPT_SET - MOVE_SET not received yet - sleep

2016-06-28 12:24:06 UTC [19800] INFO   ACCEPT_SET - MOVE_SET not received yet - sleep

2016-06-28 12:24:16 UTC [19800] INFO   ACCEPT_SET - MOVE_SET not received yet – sleep

----

 

 

                Tom    J

 

 

_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
Mark Steben | 23 Jun 22:37 2016

Re: slonik_drop_table issues

Hello,

I thought I was out of the woods but apparently not.  I changed the index name and the slonik_drop_table apparently worked
However I am still getting the following error in the slony log and it has suspended replication:

  "lock table "_replication".sl_config_lock;select "_replication".setDropTable_int(140);insert into "_replication".sl_event     (ev_origin, ev_seqno, ev_timestamp,      ev_snapshot, ev_type , ev_data1    ) values ('1', '5021650374', '2016-06-23 12:25:10.772052-04', '3309223886:3309223886:', 'SET_DROP_TABLE', '140'); insert into "_replication".sl_confirm    (con_origin, con_received, con_seqno, con_timestamp)    values (1, 3, '5021650374', now()); commit transaction;" PGRES_FATAL_ERROR ERROR:  Slony-I: alterTableDropTriggers(): Table with id 140 not found

CONTEXT:  SQL statement "SELECT "_replication".alterTableDropTriggers(p_tab_id)"
PL/pgSQL function _replication.setdroptable_int(integer) line 52 at PERFORM
2016-06-23 16:27:02 EDT CONFIG slon: child terminated signal: 9; pid: 78119, current worker pid: 78119
2016-06-23 16:27:02 EDT CONFIG slon: restart of worker in 10 seconds

At this point all I want to do is for this process to stop. It apparently is trying to correct my previous screw-up and I already 'corrected' it.

Any ideas, comments welcome. Thank you


On Thu, Jun 23, 2016 at 1:44 PM, Mark Steben <mark.steben-UjXgi8GuFLL8esGaZs7s5AC/G2K4zDHf@public.gmane.org> wrote:
In answer to my own question - yes the primary key index on the table itself 
has to match with the entry in sl_table because the function alterTableDropTriggers()
runs a join requiring a match for tables, indices and schema names.  I had changed the
pk name a couple months back which did not change the name in sl_table, hence the 
'NOT FOUND'

On Thu, Jun 23, 2016 at 12:00 PM, Mark Steben <mark.steben-UjXgi8GuFLL8esGaZs7s5AC/G2K4zDHf@public.gmane.org> wrote:
Yes it does - only one replica being used.
Question - do the index names on the master and replica have to match?

On Thu, Jun 23, 2016 at 11:47 AM, Vick Khera <vivek-5icKCuqbiQzYtjvyW6yDsg@public.gmane.org> wrote:

On Thu, Jun 23, 2016 at 9:54 AM, Mark Steben <mark.steben-UjXgi8GuFLL8esGaZs7s5AC/G2K4zDHf@public.gmane.org> wrote:
<stdin>:8: Could not drop table 140 for replication!


Does that exist on all replicas?



--
Mark Steben
 Database Administrator
<at> utoRevenue | Autobase 
  CRM division of Dominion Dealer Solutions 
95D Ashley Ave.
West Springfield, MA 01089

t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
 www.drivedominion.com








--
Mark Steben
 Database Administrator
<at> utoRevenue | Autobase 
  CRM division of Dominion Dealer Solutions 
95D Ashley Ave.
West Springfield, MA 01089

t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
 www.drivedominion.com








--
Mark Steben
 Database Administrator
<at> utoRevenue | Autobase 
  CRM division of Dominion Dealer Solutions 
95D Ashley Ave.
West Springfield, MA 01089

t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
 www.drivedominion.com





_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
Mark Steben | 23 Jun 15:54 2016

slonik_drop_table issues

Good morning,

We run slony 2.2.3 on a postgres 9.2 platform. I am attempting to drop a table from the slony registration:

 select * from _replication.sl_table where tab_relname = 'content_viewers';
 tab_id | tab_reloid |   tab_relname   | tab_nspname | tab_set |     tab_idxname      | tab_altered |                  tab_comment                  
--------+------------+-----------------+-------------+---------+----------------------+-------------+-----------------------------------------------
    140 |    1999743 | content_viewers | public      |       6 | content_viewers_pkey | f           | Table public.content_viewers with primary key

When I attempt a slonik_drop_table:


./slonik_drop_table -c /postgres_data/prime/PSQL_92/slon_tools.primeprod.conf  140 6 | ./bin/slonik &


I get this error:


[pguser <at> ardbc01 bin]$ <stdin>:6: PGRES_FATAL_ERROR lock table "_replication".sl_event_lock, "_replication".sl_config_lock;select "_replication".setDropTable(140);  - ERROR:  Slony-I: alterTableDropTriggers(): Table with id 140 not found
CONTEXT:  SQL statement "SELECT "_replication".alterTableDropTriggers(p_tab_id)"
PL/pgSQL function _replication.setdroptable_int(integer) line 52 at PERFORM
SQL statement "SELECT "_replication".setDropTable_int(p_tab_id)"
PL/pgSQL function _replication.setdroptable(integer) line 40 at PERFORM
<stdin>:8: Could not drop table 140 for replication!

Please advise.  thanks!
--
Mark Steben
 Database Administrator
<at> utoRevenue | Autobase 
  CRM division of Dominion Dealer Solutions 
95D Ashley Ave.
West Springfield, MA 01089

t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
 www.drivedominion.com





_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
William Widener | 3 Jun 21:35 2016
Gravatar

UPDATE syncs but not from Java app

Problem was a Java app maintaining connections. When restarted, all was well.
This email message is for the sole use of the intended recipient(s) and may contain information that is privileged, confidential, and exempt from disclosure under applicable law. Any unauthorized review, use, copying, disclosure or dissemination is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
Steve Singer | 3 Jun 03:37 2016

Slony 2.2.5 released

The Slony team is pleased to announce Slony 2.2.5 the next minor release 
of the Slony 2.2.x series

Slony 2.2.5 includes the following changes

   - PG 9.5 makefile fix for win32
   - PG 9.6 header file fix
   - Bug 359 :: Additional parameter to GetConfigOptionByName() in HEAD
   - Remove unsupported warning for PG 9.5

Slony 2.2.5 can be downloaded from the following URL

http://www.slony.info/downloads/2.2/source/slony1-2.2.5.tar.bz2
Christopher Browne | 2 Jun 00:01 2016

A BRIN experiment with Slony

I have set up a branch where I attempt adding in some BRIN indexes (new in 9.5), in the interests of seeing if this (supposedly cheaper) index provides some improvement in performance.

https://github.com/cbbrowne/slony1-engine/tree/brinexperiment

In running it against a Slony instance on Postgres 9.5, I do find that *some* queries certainly make use of the BRIN indexes.  It's too early to conclude it makes things better/stronger/faster, but I would be hopeful.
_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
Dave Cramer | 1 Jun 14:28 2016
Picon

is there an easy way to resyncronize a table


Dave Cramer
_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
William Widener | 25 May 23:57 2016
Gravatar

UPDATE syncs but not from Java app

Hello there,

Our company has used an older version of slony for years successfully.
We have embarked on an exercise to update to slony1-2.2.4-3.

As a test, we successfully replicated our legacy DB from a primary server to a secondary.
However, a legacy Java app performs periodic table UPDATEs which are not replicated.

Performing the equivalent postgres UPDATE command manually on the master replicates with no problem.
Examination of verbose Slony and postgres logs are not helpful to-date.

* Do any of you listeners have suggestions?

Thanks in advance,
W. Widener
Application Infrastructure Engineer
Ecovate
This email message is for the sole use of the intended recipient(s) and may contain information that is privileged, confidential, and exempt from disclosure under applicable law. Any unauthorized review, use, copying, disclosure or dissemination is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
Tory M Blue | 21 May 23:56 2016
Picon

Slony with postgresql replication?

This maybe an odd request and let me preface this by saying, we have
used Slony for over 10 years, it works great, we have had no real
major issues.

However my data sets, index creation times are starting to mean that a
drop/add of my master or a full replicated slave, takes about 15 hours
to come back on line (only takes about 2 hours to copy the data, but
some indexes take 7 hours on their own).. So any maintenance that
requires a drop/add (upgrades), requires a herculean effort, even with
dropping indexes and adding them at the end manually and concurrently
(as much as possible)

So I was thinking with a master-master setup that Postgres provides,
could I run Postgres replication between the master  servers
(master/slave), and run Slony to create our read only db's (these take
a bit less than 30 minutes to create)?

I've been unsuccessful in determining why the index creation takes so
long (and right, it's not Slony, it's Postgresql!!)

I would love to keep Slony for the read only query DB's and like I
said considering moving to Postrgresql replication for the
master/master. My query DB's only need about 5% of the data that is in
the insert master/slave, so using Postgresql replication and forcing
the same huge size db on each smaller query node, doesn't sound very
appetizing.

The other issue is any heavy process causes Slony to backup, major
deletes back up, and it's just getting worse.

Is this silly, is it either/or and I should now this?

Thanks
Tory
Mike James | 17 May 20:59 2016

removing old slony triggers

Hi, all. Due to some changes in our slony schema over the years, in our database I now see three slony schemas. Our current replication set only uses the most recent one. (slony 2.2.1 and PG 9.3)

In the database when I "select * from pg_trigger; " I see some triggers from the older, unused schemas, particularly _logtrigger, _truncatetrigger have tgenabled = O. 

_denyaccess and _truncatedeny are disabled (tgenabled = D)

How do I safely remove these triggers and delete the unused slony schemas? This maybe a simple question - but I'd rather not take the chance with the production database. Thanks,

Mike
 
 
  Mike James | Manager of Infrastructure
267.419.6400, ext 204 | mike.james-8f6iZ/AGwKbQT0dZR+AlfA@public.gmane.org
201 S Maple St. | Suite 250 | Ambler, PA 19002 
Clutch.com | Twitter | LinkedIn | YouTube | Clutch Support Center
 
The only end to end consumer management platform that empowers consumer-focused businesses to identify, target, message, and engage their best customers.
_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
Rob Brucks | 26 Feb 21:51 2016

Replication Lag?

I have a fairly simple test cluster, three PG instances running on different ports:  a master and two slave
DBs both subscribed to the master.  Everything is running on the same server (it's a playground), so I have
three PG instances (ports 5432, 5433, 5434; connecting via sockets in /tmp) and a slony daemon for each instance.

I'm running Postgres 9.3.9 and slony1 2.2.4 on Centos 6.7 x86_64.  Both postgres and slony were installed
via yum using the PGDG repo.

I used the config below to initialize a very simple replication setup of only one table and one sequence.

My problem is that if I shut down just one slave postgres instance then sl_status on the master instance
shows replication stalling on both slave DBs, instead of just one.

But, if I insert some test data into the master DB, I see the data show up on the remaining active slave.  So
replication to the remaining slave DB is obviously working.

We use sl_status to monitor replication so we need it to accurately report lag if there's an issue.  The Slony
1.2 version we used before did not behave this way, it accurately reported which slave was not replicating.

Why does sl_status report lag on the active slave even though replication appears to be working fine?

Do I have a misconfiguration somewhere?

Thanks,
Rob

Here's my slony config:

      CLUSTER NAME = slony;
      NODE 1 ADMIN CONNINFO = 'dbname=test_db host=/tmp port=5432 user=slony';
      NODE 2 ADMIN CONNINFO = 'dbname=test_db host=/tmp port=5433 user=slony';
      NODE 3 ADMIN CONNINFO = 'dbname=test_db host=/tmp port=5434 user=slony';

############ CLUSTERS

      INIT CLUSTER (ID = 1, COMMENT = 'Master');

############ NODES

      STORE NODE (ID = 2, COMMENT = 'Slave1', EVENT NODE = 1);
      STORE NODE (ID = 3, COMMENT = 'Slave2', EVENT NODE = 1);

############ PATHS

      STORE PATH (SERVER = 1, CLIENT = 2, CONNINFO = 'dbname=test_db host=/tmp port=5432 user=slony');
      STORE PATH (SERVER = 1, CLIENT = 3, CONNINFO = 'dbname=test_db host=/tmp port=5432 user=slony');
      STORE PATH (SERVER = 2, CLIENT = 1, CONNINFO = 'dbname=test_db host=/tmp port=5433 user=slony');
      STORE PATH (SERVER = 2, CLIENT = 3, CONNINFO = 'dbname=test_db host=/tmp port=5433 user=slony');
      STORE PATH (SERVER = 3, CLIENT = 1, CONNINFO = 'dbname=test_db host=/tmp port=5434 user=slony');
      STORE PATH (SERVER = 3, CLIENT = 2, CONNINFO = 'dbname=test_db host=/tmp port=5434 user=slony');

############ SETS

      CREATE SET (ID = 1, ORIGIN = 1, COMMENT = 'TEST Set 1');

############ SEQUENCES

      SET ADD SEQUENCE (SET ID = 1, ORIGIN = 1, ID = 1, FULLY QUALIFIED NAME = '"public"."test_seq"');

############ TABLES

      SET ADD TABLE (SET ID = 1, ORIGIN = 1, ID = 2, FULLY QUALIFIED NAME = '"public"."test"');

############ SUBSCRIPTIONS

      SUBSCRIBE SET (ID = 1, PROVIDER = 1, RECEIVER = 2, FORWARD = YES);
      SUBSCRIBE SET (ID = 1, PROVIDER = 1, RECEIVER = 3, FORWARD = YES);

Gmane