Mark Steben | 26 Jul 23:01 2016

upgrading with pg_upgrade

Good afternoon,

I am attempting to run pg_upgrade with the link option to upgrade from postgres 9.2.12 to 9.4.8. Within the current 9.2.12 setup I have slony1-2.2.3 running.

This is what I have attempted so far:
 1. installed postgres 9.4.8 on the same file system as 9.2 from tarball source
 1. killed slony (on 9.2)
 2. stopped postgres
 3. ran 9.4.8 initdb 
 4. installed slony1-2.2.3 on the new 9.4.8 setup from tarball source (here is the command)
    ./configure --prefix=/usr/local/postgresql-9.4.8/bin --with-pgconfigdir=/usr/local/postgresql-9.4.8/bin --with-perltools=/usr/local/postgresql-9.4.8/bin --with-pglibdir=/usr/local/postgresql-9.4.8/lib --with-pgpkglibdir=/usr/local/postgresql-9.4.8/lib 
 make
 make install

 5. attempted pg_upgrade (here is the command)
   /usr/local/postgresql-9.4.8/bin/pg_upgrade  -b /usr/local/postgresql-9.2.11/bin -B /usr/local/postgresql-9.4.8/bin -d /data/PSQL_9.2 -D /data/PSQL_9.4 -k -v & 

I am now getting an error from this indicating a version mismatch:
   Could not load library "$libdir/slony1_funcs.2.2.3"
ERROR:  incompatible library "/usr/local/postgresql-9.4.8/lib/slony1_funcs.2.2.3.so": version mismatch
DETAIL:  Server is version 9.4, library is version 9.2.

I then installed slony1-2.2.5 with the above command, ran the same pg_upgrade command and got the exact same error. Then I deleted the slony1_funcs.2.2.3.so file from my library directory leaving the slony1_funcs.2.2.5.so and attempted the upgrade.  This time it errored out with a NOT FOUND condition on the slony1_funcs.2.2.3.so.  So I'm not sure what to do next.  I looked at slony.info to see if it shed any light on compatibilities between slony versions and postgres versions and didn't find anything.

A note: without slony installed in either instance the upgrade is successful.

Any help / insight appreciated.  Thank you

 

--
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
Tignor, Tom | 8 Jul 21:27 2016

drop node error

 

                Hello slony group,

                I’m testing now with slony1-2.2.4. I have just recently produced an error which effectively stops slon processing on some node A due to some node B being dropped. The event reproduces only infrequently. As some will know, a slon daemon for a given node which becomes aware its node has been dropped will respond by dropping its cluster schema. There appears to be a race condition between the node B schema drop and the (surviving) node A receipt of the disableNode (drop node) event. If the former occurs before the latter, all the remote worker threads on node A enter an error state. See the log samples below. I resolved this the first time by deleting all the recent non-SYNC events from the sl_event tables, and more recently with a simple node A slon restart.

                Please advise if there is any ticket I should provide this info to, or if I should create a new one. Thanks.

 

 

---- node 1 log ----

2016-07-08 18:06:31 UTC [30382] INFO   remoteWorkerThread_999999: SYNC 5000000008 done in 0.002 seconds

2016-07-08 18:06:33 UTC [30382] INFO   remoteWorkerThread_999999: SYNC 5000000009 done in 0.002 seconds

2016-07-08 18:06:33 UTC [30382] INFO   remoteWorkerThread_2: SYNC 5000017869 done in 0.002 seconds

2016-07-08 18:06:33 UTC [30382] INFO   remoteWorkerThread_3: SYNC 5000018148 done in 0.004 seconds

2016-07-08 18:06:45 UTC [30382] CONFIG remoteWorkerThread_2: update provider configuration

2016-07-08 18:06:45 UTC [30382] ERROR  remoteWorkerThread_3: "select last_value from "_ams_cluster".sl_log_status" PGRES_FATAL_ERROR ERROR:  schema "_ams_clu\

ster" does not exist

LINE 1: select last_value from "_ams_cluster".sl_log_status

                               ^

 

2016-07-08 18:06:45 UTC [30382] ERROR  remoteWorkerThread_3: SYNC aborted

2016-07-08 18:06:45 UTC [30382] CONFIG version for "dbname=ams

      host=198.18.102.45

      user=ams_slony

      sslmode=verify-ca

      sslcert=/usr/local/akamai/.ams_certs/complete-ams_slony.crt

      sslkey=/usr/local/akamai/.ams_certs/ams_slony.private_key

      sslrootcert=/usr/local/akamai/etc/ssl_ca/canonical_ca_roots.pem" is 90119

2016-07-08 18:06:45 UTC [30382] ERROR  remoteWorkerThread_2: "select last_value from "_ams_cluster".sl_log_status" PGRES_FATAL_ERROR ERROR:  schema "_ams_clu\

ster" does not exist

LINE 1: select last_value from "_ams_cluster".sl_log_status

                               ^

 

2016-07-08 18:06:45 UTC [30382] ERROR  remoteWorkerThread_2: SYNC aborted

2016-07-08 18:06:45 UTC [30382] ERROR  remoteListenThread_999999: "select ev_origin, ev_seqno, ev_timestamp,        ev_snapshot,        "pg_catalog".txid_sna\

pshot_xmin(ev_snapshot),        "pg_catalog".txid_snapshot_xmax(ev_snapshot),        ev_type,        ev_data1, ev_data2,        ev_data3, ev_data4,        ev\

_data5, ev_data6,        ev_data7, ev_data8 from "_ams_cluster".sl_event e where (e.ev_origin = '999999' and e.ev_seqno > '5000000009') or (e.ev_origin = '2'\

and e.ev_seqno > '5000017870') or (e.ev_origin = '3' and e.ev_seqno > '5000018151') order by e.ev_origin, e.ev_seqno limit 40" - ERROR:  schema "_ams_cluste\

r" does not exist

LINE 1: ...v_data5, ev_data6,        ev_data7, ev_data8 from "_ams_clus...

                                                             ^

2016-07-08 18:06:55 UTC [30382] ERROR  remoteWorkerThread_3: "start transaction; set enable_seqscan = off; set enable_indexscan = on; " PGRES_FATAL_ERROR ERR\

OR:  current transaction is aborted, commands ignored until end of transaction block

2016-07-08 18:06:55 UTC [30382] ERROR  remoteWorkerThread_3: SYNC aborted

2016-07-08 18:06:55 UTC [30382] ERROR  remoteWorkerThread_2: "start transaction; set enable_seqscan = off; set enable_indexscan = on; " PGRES_FATAL_ERROR ERR\

OR:  current transaction is aborted, commands ignored until end of transaction block

2016-07-08 18:06:55 UTC [30382] ERROR  remoteWorkerThread_2: SYNC aborted

----

 

 

---- node 999999 log ----

2016-07-08 18:06:44 UTC [558] INFO   remoteWorkerThread_1: SYNC 5000081216 done in 0.004 seconds

2016-07-08 18:06:44 UTC [558] INFO   remoteWorkerThread_2: SYNC 5000017870 done in 0.004 seconds

2016-07-08 18:06:44 UTC [558] INFO   remoteWorkerThread_3: SYNC 5000018150 done in 0.004 seconds

2016-07-08 18:06:44 UTC [558] INFO   remoteWorkerThread_1: SYNC 5000081217 done in 0.003 seconds

2016-07-08 18:06:44 UTC [558] WARN   remoteWorkerThread_3: got DROP NODE for local node ID

NOTICE:  Slony-I: Please drop schema "_ams_cluster"

NOTICE:  drop cascades to 171 other objects

DETAIL:  drop cascades to table _ams_cluster.sl_node

drop cascades to table _ams_cluster.sl_nodelock

drop cascades to table _ams_cluster.sl_set

drop cascades to table _ams_cluster.sl_setsync

drop cascades to table _ams_cluster.sl_table

----

 

            Tom    J

 

 

 

_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
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

Gmane