gmane.comp.db.postgresql.slony1.general

Re: Slony cleanupEvent erroring out with "server closed the connection unexpectedly" - Soln version: 2.1.2

Jan Wieck — 2013-06-15T13:24:27

You're welcome.


Jan

Re: Slony cleanupEvent erroring out with "server closed the connection unexpectedly" - Soln version: 2.1.2

Sridevi R — 2013-06-15T08:46:03

Jan,

You are right. Tweaking the tcp keep alive parameters helped.

Now slon.conf contains:

tcp_keepalive=true
tcp_keepalive_interval=45
tcp_keepalive_count=20
tcp_keepalive_idle=30
cleanup_interval=30

Thanks a lot for the timely response.

- Sridevi



On Thu, Jun 13, 2013 at 11:46 PM, Jan Wieck <JanWieck-/E1597aS9LQAvxtiuMwx3w< at >public.gmane.org> wrote:

_______________________________________________
Slony1-general mailing list
Slony1-general-8kkgcvHRObyz5F2/bZa4Fw< at >public.gmane.org
http://lists.slony.info/mailman/listinfo/slony1-general

Re: Slony cleanupEvent erroring out with "server closed the connection unexpectedly" - Soln version: 2.1.2

Jan Wieck — 2013-06-13T18:16:40

This all can very well be a slightly too eager firewall dropping idle
connections. Have you tried to enable TCP keep alive options that kick
in after something like 30 seconds? If not, enable them on both, the PG
server and the Slony side. That usually prevents those firewall issues.


Jan

Re: Slony cleanupEvent erroring out with "server closed the connection unexpectedly" - Soln version: 2.1.2

Sridevi R — 2013-06-13T10:25:48

Hello Jan,

The Master and Slave DBs talk through a firewall.
VIP IPs and SNAT IPs are used in pg_hba.conf.

The corresponding messages in the postgres server log:

2013-06-13 09:46:21.224
GMT,,,6630,"10.4.2.2:42031",51b994ed.19e6,1,"",2013-06-13
09:46:21 GMT,,0,LOG,08P01,"incomplete startup packet",,,,,,,,,""
2013-06-13 09:57:38.596 GMT,"postgres","db01",6634,"<ip address printed
here>:53924",51b994f7.19ea,1,"idle",2013-06-13 09:46:31
GMT,28/0,0,LOG,08006,"could not receive data from client: Connection reset
by peer",,,,,,,,,"slon.node_1_listen"
2013-06-13 09:57:38.596 GMT,"postgres","db01",6634,"<ip address printed
here>:53924",51b994f7.19ea,2,"idle",2013-06-13 09:46:31
GMT,28/0,0,LOG,08P01,"unexpected EOF on client
connection",,,,,,,,,"slon.node_1_listen"
2013-06-13 09:57:38.607 GMT,"postgres","db01",6637,"<ip address printed
here>:53926",51b994f9.19ed,1,"idle",2013-06-13 09:46:33
GMT,32/0,0,LOG,08006,"could not receive data from client: Connection reset
by peer",,,,,,,,,"slon.subscriber_1_provider_1"
201

Re: Slony cleanupEvent erroring out with "server closed the connection unexpectedly" - Soln version: 2.1.2

Jan Wieck — 2013-06-12T18:32:24

"server closed the connection unexpectedly" ...

Is this connection by any chance through some firewall or NAT gateway
that will drop idle connections?

What are the corresponding postmaster server log entries? Since slony
reports an unexpected connection drop from the server, the server must
have some message in its log too, because the client never sent the 'X'
libpq protocol message.


Jan

Re: Slony cleanupEvent erroring out with "server closed the connection unexpectedly" - Soln version: 2.1.2

Sridevi R — 2013-06-12T14:17:23

Jan,

Thanks for the reply.

The only errors in the slon log are failure of cleanupThread.
child process is restarting right after the cleanupThread Failure.
This occurs approximately every 10 minutes since cleanup_interval is set to
10 minutes.

Here is a sample from the log again:

2013-06-06 14:23:27 GMT FATAL  cleanupThread: "begin;lock table
"_xx_cluster".sl_config_lock;select "_xx_cluster".cleanupEvent('10
minutes'::interval);commit;" - server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
2013-06-06 14:23:27 GMT CONFIG slon: child terminated signal: 9; pid:
16135, current worker pid: 16135
2013-06-06 14:23:27 GMT CONFIG slon: restart of worker in 10 seconds

Thanks ,
Sridevi


On Wed, Jun 12, 2013 at 7:33 PM, Jan Wieck <JanWieck-/E1597aS9LQAvxtiuMwx3w< at >public.gmane.org> wrote:

_______________________________________________
Slony1-general mailing list
Slony1-general-8kkgcvHRObyz5F2/bZa4Fw< at >public.gmane.org
http://l

Re: Slony cleanupEvent erroring out with "server closed the connection unexpectedly" - Soln version: 2.1.2

Jan Wieck — 2013-06-12T14:03:29

Slon kills its worker(s) with signal 9 (SIGKILL) when it needs to
restart, like when there are errors in event processing or if it
receives certain signals. Are there any other errors in the slon log or
is something on the machine sending signals to slon?


Jan

Slony cleanupEvent erroring out with "server closed the connection unexpectedly" - Soln version: 2.1.2

Sridevi R — 2013-06-12T11:14:59

Hello,

The slony logs are consistently posting this error:

2013-06-12 10:01:05 GMT FATAL  cleanupThread: "begin;lock table
"_xx_cluster".sl_config_lock;select "_xx_cluster".cleanupEvent('10
minutes'::interval);commit;" - server closed the connection unexpectedly
2013-06-12 10:12:24 GMT FATAL  cleanupThread: "begin;lock table
"_xx_cluster".sl_config_lock;select "_xx_cluster".cleanupEvent('10
minutes'::interval);commit;" - server closed the connection unexpectedly

checked and found that sl_confirm table is not cleaned up. cleanup event
never succeeds.
Additionally, the child processes terminates and restarts after each such
cleanup failure.

2013-06-11 11:20:04 GMT CONFIG slon: child terminated signal: 9; pid:
20172, current worker pid: 20172
2013-06-11 11:20:04 GMT CONFIG slon: restart of worker in 10 seconds

When cleanup is run manually, on the psql prompt it runs to completion
without any issues and cleans up sl_event and sl_confirm tables
"begin;lock table "_xx_cluster".sl_config_lock;select
"_xx_clust

Re: Slony Versions and Postgres Versions and the compatibility.

Jan Wieck — 2013-06-07T20:18:27

That is correct. Slony requires the same Slony version on all nodes, but
supports running on top of different PostgreSQL version.


Jan

Slony 2.2.0 beta 4 released

Steve Singer — 2013-06-07T14:51:21

The Slony team is pleased to announce the release of the fourth beta for 
Slony 2.2.0.

Slony 2.2.0 will be the next major release of the Slony-I replication 
system for PostgreSQL.

Key features of the 2.2.0 release include:

* The storage and transport and application of the slony log 
(sl_log_1/sl_log_2) has changed providing performance improvements. Data 
is now stored in a different format and the postgresql COPY protocol and 
triggers are used to replicate and apply changes to replicas.

* DDL handling with the EXECUTE SCRIPT command has changed.  The DDL is 
no longer stored as a special event in sl_event but is instead stored in 
sl_log_script and is processed as part of a SYNC event inline with data 
changes. DDL can also be specified inline

* FAILOVER has been reworked to be more reliable but not all nodes can 
be used as failover targets.

* A RESUBSCRIBE NODE command was added because the provider of a 
subscribed set can no longer be changed with the SUBSCRIBE SET command 
in some cases.  All

Re: Slony Versions and Postgres Versions and thecompatibility.

Raghav — 2013-06-06T08:16:56

AFAIK, Slony-I need exact version on both the node's.
Because upgrading a node to newer version will do changes to slony_func.so
file where it should match the version with the other replicating nodes
else it will break. Below link has a comment after the upgrade steps as " If
there is any mismatch between component versions, the
slon<http://main.slony.info/documentation/2.1/slon.html> will
refuse to start up, which provides protection against corruption."

http://main.slony.info/documentation/2.1/slonyupgrade.html

For your other questions, hopefully you will see some answer's here.

Slony Versions and Postgres Versions and thecompatibility.

Vitale, Anthony, Sony Music — 2013-06-05T20:07:48

Hello Guys

I am new to the Slony World as well as Postgres World, And I have a question which I have tried to get info from the Web but without success. (At least maybe I did get the answer and do not like what I think I understand).

Here is my question.

At a point in Time,  I have a Master Database running Slony V 2.1.0 and a Slave Node running Slony V 2.1.0

I now need to update Slony to v2.1.2

Can I upgrade the Slave Node To V2.1.2 ahead of the Master being upgraded to V2.1.0 or vice versa

Or Does Slony require 100% version equality within the nodes at all times.
I Got this From the  Documentation Regarding Minor Slony-I version Upgrades.


*         Stop the slon<http://slony.info/documentation/2.1/slon.html> processes on all nodes. (e.g. - old version of slon<http://slony.info/documentation/2.1/slon.html>)



*         Install the new version of slon<http://slony.info/documentation/2.1/slon.html> software on all nodes.



*         Execute a slonik<http://slony.info/documentation/2.1/slonik.html> s

Re: Truncate trigger in slony 2.1.x+ requiressuperuser?

Brian Fehrle — 2013-05-16T21:12:37

Thanks Jan, I'll be testing this soon.

- Brian F

On 05/16/2013 11:47 AM, Jan Wieck wrote:

Re: Truncate trigger in slony 2.1.x+ requiressuperuser?

Jan Wieck — 2013-05-16T17:47:14

I have committed the 2-line fix for this to master and REL_2_1_STABLE.

The diff for 2.1 is attached.


Jan

Re: Truncate trigger in slony 2.1.x+ requiressuperuser?

Jan Wieck — 2013-05-15T18:50:53

I would call it a bug. The truncate trigger should be "security 
definer", like the log trigger and several other functions are.


Jan

Truncate trigger in slony 2.1.x+ requiressuperuser?

Brian Fehrle — 2013-05-15T17:51:32

Hi All,

I have a slony cluster running on slony 2.1.0 (Have tested this 
situation on 2.1.3 also). This cluster is replicating a drupal database, 
which preforms truncates on some of the cache tables periodically. When 
the truncates execute, they fire the log_truncate trigger in slony, 
which immediatly errors due to no select permissions on the sl_table 
relation in the slony cluster.

In normal events (inserts, updates, deletes), the user doesn't need any 
permissions granted in the slony schema in order for the event trigger 
to log the event into sl_log_1, etc.

So is this a design decision, making truncates on slony tables only be 
able to be executed by superusers in the database?

I've got it working in a testing environment by granting the following:
grant select on _slony.sl_table to user;
grant insert on _slony.sl_log_status to user;
grant insert on _slony.sl_log_1 to user;
grant insert on _slony.sl_log_2 to user;
grant usage on _slony.sl_action_seq to user;

However I don't like the idea of eith

Error while adding new set through altperl slonikscript

GB — 2013-05-15T15:30:04

Granthana Biswas <granthana.biswas< at >...> writes:

table to a running replication using following
steps:1. ./slonik_execute_script --config slon_tools.conf 1
/usr/pgsql-9.2/bin/test.sql | ./slonik             (sql file has only create
table command)
"ddl_slony_pkey" for table "ddl_slony"2.
{       "set_id"       => 2,       "table_id"     => 2,       "sequence_id" 
=> 2,       "pkeyedtables" => ["public.ddl_slony"],
executing create set, I am getting the following error: ./slonik_create_set
-c slon_tools.conf 2 | ./slonik<stdin>:11: Subscription set 2
created<stdin>:12: Adding tables to the subscription set<stdin>:13:
PGRES_FATAL_ERROR lock table "_ms_prod_slony".sl_config_lock;select
"_ms_prod_slony".setAddTable(2, 2, 'public.ddl_slony', 'ddl_slony_pkey',
'Table public.ddl_slony with primary key');  - ERROR:  Slony-I:
setAddTable_int: table id 2 has already been assigned!
p_tab_id, p_fqname,                        p_tab_idxname,
p_tab_

Error while adding new set through altperl slonikscript

Granthana Biswas — 2013-05-14T17:22:31

Hi,

I have configured Slony-II using altperl tools. I am trying to add a new
table to a running replication using following steps:

1.
 ./slonik_execute_script --config slon_tools.conf 1
/usr/pgsql-9.2/bin/test.sql | ./slonik             (sql file has only
create table command)
<stdin>:4: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"ddl_slony_pkey" for table "ddl_slony"

2.
Adding the table in the slon_tools.conf file as set2 below set1:

 "set2" => {
       "set_id"       => 2,
       "table_id"     => 2,
       "sequence_id"  => 2,
       "pkeyedtables" => ["public.ddl_slony"],
       "keyedtables"  => {},
       "sequences"    => [],
    },

3. While executing create set, I am getting the following error:

 ./slonik_create_set -c slon_tools.conf 2 | ./slonik

<stdin>:11: Subscription set 2 created
<stdin>:12: Adding tables to the subscription set
<stdin>:13: PGRES_FATAL_ERROR lock table
"_ms_prod_slony".sl_config_lock;select "_ms_prod_slony".setAddTable(2, 2,
'public.ddl_slony', 'ddl_s

Re: Slony multiple connections with database

Jan Wieck — 2013-05-10T02:48:18

This sounds very much like lost TCP connections that never get the FIN
or RST packts. Have you tried to enable keepalive with settings, that
let connections time out within 5 minutes or faster in both, the
PostgreSQL and the Slony configurations?


Jan

Re: pgsql replication with database schema changes using slonik execute script

Jan Wieck — 2013-05-10T02:14:59

Unless there is an implicit or explicit WAIT FOR EVENT done my slonik,
nothing should block. EXECUTE SCRIPT in all versions prior to 2.2
creates an event, containing the SQL. In 2.2 the SQL is injected into
the data stream processed during SYNC application to make it work
reliable with concurrent table access.


Jan

Re: pgsql replication with database schema changes using slonik execute script

Vick Khera — 2013-05-10T00:31:24

I'm pretty sure slonik will just block until it is reachable. Try not to do
that. This may be different in slony 2.0+, but I haven't tested.



On Tue, May 7, 2013 at 6:54 AM, kapil bhadke <kapilbhadke-Re5JQEeQqe8AvxtiuMwx3w< at >public.gmane.org> wrote:

_______________________________________________
Slony1-general mailing list
Slony1-general-8kkgcvHRObyz5F2/bZa4Fw< at >public.gmane.org
http://lists.slony.info/mailman/listinfo/slony1-general