headers
Brian Fehrle | 15 May 2013 19:51
Favicon
Gravatar

Truncate trigger in slony 2.1.x+ requires superuser?

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 either A. granting these permissions to 
slony tables or B. making my drupal user a 'superuser' to get around it. 
Are those my only two options here to allow truncates to work?

Thanks,
- Brian F
(Continue reading)

Permalink | Reply | 3 comments |
headers
Granthana Biswas | 14 May 2013 19:22
Picon

Error while adding new set through altperl slonik script

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_slony_pkey', 'Table public.ddl_slony with primary key');  - ERROR:  Slony-I: setAddTable_int: table id 2 has already been assigned!
CONTEXT:  SQL statement "SELECT "_ms_prod_slony".setAddTable_int(p_set_id, p_tab_id, p_fqname,
                        p_tab_idxname, p_tab_comment)"
PL/pgSQL function _ms_prod_slony.setaddtable(integer,integer,text,name,text) line 28 at PERFORM

I tried the above by manually creating another table on both nodes and adding and creating set as set3. Still facing the same issue.

Can anybody point out where I am going wrong and how it should be fixed?

Thanks,
GB



_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
Permalink | Reply | 1 comment |
headers
kapil bhadke | 7 May 2013 12:54
Picon

pgsql replication with database schema changes using slonik execute script

I am using slony for replicating my pgsql database. I want to propagate the database schema changes(DDL) from master to the slave machine with the help of slonik execute script. I am curious to know what happens when I run slonik execute script(on master) and slave machine is not reachable. How will these schema changes get reflected on slave machine?
_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
Permalink | Reply | 2 comments |
headers
Neha Sharma | 1 May 2013 08:41
Picon

Slony multiple connections with database

Hi,

We are using slony to synchronise two postgres databases of two machines. 

Generally slony creates 6 connections with the database - 4 with local db and 2 with remote db.

But some problem is being observed on the machine having master db. Various slony remote connections are seen with the database.
The limit of postgres connections is 100 and slony is consuming maximum connections due to which the db fails to respond after sometime. It was observed that connection reached to 104 out of which slony connections (which remote has created with db) were 74.
We have to restart slony to clean all the connections.

Ideally there should only be 2 connections from the remote - one created by listener and another by worker thread.

There is no connectivity issue between the two machines.

Could anyone please suggest what could be the reason for this weired behaviour of slony?
Also, please tell if slony creates new connections without removing the older ones?

--
Neha Sharma 
_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
Permalink | Reply | 2 comments |
headers
Tory M Blue | 25 Apr 2013 20:29
Picon

Long slon replication times, 3 hours for 87, 873, 597 rows

Postgres 9.2.4, slony 2.1.2 (ya will be pushing to 2.1.3), but with my postgres upgrade had to keep slony version the same for the first go through.

But for  87,873,597 rows it took 3 hours to replicate over a gig link. There were no signs of system health issues, iostat showed hardly anything, no CPU/memory issues, so I'm wondering if there is some tuning that needs to happen?

These are my master/slave so do have fsync on, maybe during a rebuild a fsync off is in order, but not sure if that will truly help.

slon config is pretty basic

everything is default except for these values:

log_level=2
sync_interval=1000
remote_listen_timeout=10000

spotlightimpressions has  87,873,597 rows, took  12845.025 seconds
adimpressions has 1,542,169 rows, took  2098.679 seconds
listings has 4,080,091 rows . took 1491.740 seconds

2013-04-24 23:10:19 PDT CONFIG remoteWorkerThread_1: copy table "db"."listings"

2013-04-24 23:10:19 PDT CONFIG remoteWorkerThread_1: Begin COPY of table "db"."listings"

2013-04-24 23:35:10 PDT CONFIG remoteWorkerThread_1: 1491.740 seconds to copy table "db"."listings"

2013-04-24 23:35:23 PDT CONFIG remoteWorkerThread_1: copy table "db"."spotlightimpressions"

2013-04-24 23:35:23 PDT CONFIG remoteWorkerThread_1: Begin COPY of table "db"."spotlightimpressions"

2013-04-25 03:09:28 PDT CONFIG remoteWorkerThread_1: 12845.025 seconds to copy table "db"."spotlightimpressions" 

2013-04-25 03:09:28 PDT CONFIG remoteWorkerThread_1: copy table "db"."adimpressions"

2013-04-25 03:09:28 PDT CONFIG remoteWorkerThread_1: Begin COPY of table "db"."adimpressions"

2013-04-25 03:44:26 PDT CONFIG remoteWorkerThread_1: 2098.679 seconds to copy table "db"."adimpressions"   


Where do I start looking? These tables will only grow and since upgrades require full rebuilds etc, This is already painful, but will only get worse.

Thanks
Tory
_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
Permalink | Reply | 2 comments |
headers
andrew | 23 Apr 2013 00:45

Slony Set Sync Tracking and Archive Tracking - how to shift between these?

For some time now, we've been working with an older SuSE 11.3 server that had slony 1.1 (I think 1.1.2, possibly 1.1.5) on it.  Our remote servers were being replicated to by means of the log shipping setup.  As a result, the remote machines are all prepared for log files that have entries like the following:

 

select "_cpc_replic".setsyncTracking_offline(12, '476698', '476700', '2013-04-22 11:00:32.050019');

 

However, for the past week, I've been working on getting a new server (RHEL 6.4 virtual machine) in place as our primary replication box.  I couldn't get the old slony version to install on that correctly, so I ended up downloading and installing Slony 2.0.7 on it.  As a result, the logshipping files now no longer have the line above, they instead have a line much like the one below:

 

select "_cpc_replic".archiveTracking_offline('5945', '2013-04-22 11:07:38.617205');

 

How do I go about converting those?  My preference would be to modify the process itself so it goes back to generating the .setsyncTracking_offline statements, but if that cannot be done, where can I find a good set of instructions on converting our current database replication schemas to correctly handle the new statement types?

_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
Permalink | Reply | 0 comments |
headers
Mike James | 18 Apr 2013 16:19
Favicon

yum error uninstalling slony1

Hi, I’m running RHEL 5.9, 64-bit, fully patched. I’ve installed the pgdg92 repo and I’ve installed Postgresql-9.2.4 and slony1. Today, I was doing some troubleshooting and I found two versions of slony1-92 installed on the system. I tried to uninstall the older version but I get an error. Any ideas what caused this error, or how to resolve it? I rebuilt the rpm database, also ran “yum clean all”. The problem is not fixed.

 

[root <at> pg-server ~]# rpm -qa | grep slony1

slony1-92-2.1.2-1.rhel5

slony1-92-2.1.3-1.rhel5

 

[root <at> pg-server ~]# rpm -e slony1-92-2.1.2

error: %postun(slony1-92-2.1.2-1.rhel5.x86_64) scriptlet failed, exit status 5

 

And there are still 2 versions of slony in the rpm database:

 

[root <at> pg-server ~]# rpm -qa | grep slony1

slony1-92-2.1.2-1.rhel5

slony1-92-2.1.3-1.rhel5

 

thanks, Mike

_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
Permalink | Reply | 1 comment |
headers
Vick Khera | 16 Apr 2013 15:10

replication stalls with long-running vacuum

For years, I have run slony (currently version 2.1.3) with one origin and one replica. Every night at midnight, I run vacuum analyze on the whole DB.  I still run autovacuum with its default settings. The midnight vacuum takes approximately 4.5 hours to run.

All was well until I upgraded the DB from 8.4 to 9.2.x. (at the same time I upgraded slony from 2.0).  Now, every night, the replication basically stalls mid-way through the vacuum.

Here is what I observe:

midnight - vacuum starts on origin and replica
3:12am - replication delay reaches > 7 minutes
3:15am - replication delay = 624 seconds
3:30am - replication delay = 1524 seconds
3:45am - replication delay = 2423 seconds
... basically replication has stopped
4:30am - replication delay = 5124 seconds
4:40am - vacuum ends on replica
4:41am - vacuum ends on origind
4:45am - replication delay = 1018 seconds
4:49am - replication lag drops to under 5 minutes (I consider this recovered)


At no other time during the day, even when the DB is very very busy doing lots of writes and a fair number of reads, does the replication lag more than 5 or 10 seconds.

I have another DB on another pair of machines that is reasonably large as well, that does nightly vacuum similarly. It is running slony 2.1 also, but the DB version it replicates from is 8.3 to a 9.1. I never see any massive delay in replication on there.

So my instinct is that there is some change in 9.2 that slony is tripping over that is causing it to lock something for way too long. I would appreciate any guidance on figuring out what that is, so I can avoid having long delays in my replication while vacuum is running.
_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
Permalink | Reply | 4 comments |
headers
Pruteanu Dragos | 2 Apr 2013 10:37
Picon
Favicon

Slony on High Load

Hi Slony admins,

 

Here I have a problem for a Slony setup on a really loaded primary database. 

I try to build the slony and got from time to time an error.
Maybe is related to the high load we have. I hope you can help.


 

PGRES_FATAL_ERROR ERROR:  stack depth limit exceeded

HINT:  Increase the configuration parameter "max_stack_depth", after ensuring the platform's stack depth limit is adequate.

 

The line before this error message in the slony logs has ~11MB worth of text consisting mainly in a long concatenation of:

 

... and  log_actionseq <> '...'

 

This data is also present in the sl_setsync table.

 

The problem happens immediately after the slave finishes syncing the set, enables the subscription and tries to do the first sync.

 

I found a thread about it here:

 

http://old.nabble.com/Slave-can%27t-catch-up,-postgres-error-%

27stack-depth-limit-exceeded%27-td33182661.html

 

We're running on postgres 9.0.10 and slony1 version 2.0.7, and upgrading is not an option in the near future (eventually we will upgrade both postgres and slony).

 

The problem is that we hit this issue now more and more regularly - and it is a killer for the slony replication, as it is not possible to reliably set it up...

 

What I already tried and didn't help:

 

 * set max_stack_depth up to ridiculous amounts (10s of GB) - not sure if I got the OS side of it right, but I did my best;

 

 * decrease the slon deamon's SYNC_CHECK_INTERVAL to 1 second;

 

With both those I still get the error regularly...

 

I wonder if this is fixed in newer slony releases, or if there's any chance I can get some help/directions on how to fix/patch it in the version we use to avoid this problem ?

 

Jan Wieck mentions in the thread cited above that the a solution would

be:

 

<quote>

The improvement for a future release would be to have the remote worker get the log_actionseq list at the beginning of copy_set. If that list is longer than a configurable maximum, it would abort the subscribe and retry in a few seconds. It may take a couple of retries, but it should eventually hit a moment where a SYNC event was created recently enough so that there are only a few hundred log rows to ignore.

</quote>

 

Was this already implemented in a newer release ?

 

If not I would like to work on it, including back-patch for the 2.0.7 version we use...

 

I would appreciate any help/hints on how to approach this !

 

Cheers,

_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
Permalink | Reply | 1 comment |
headers
Csaba Nagy | 27 Mar 2013 18:07

PGRES_FATAL_ERROR ERROR: stack depth limit exceeded

Hi all,

We have here a slony setup where this error is consistently showing up:

PGRES_FATAL_ERROR ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth", after
ensuring the platform's stack depth limit is adequate.

The line before this error message in the slony logs has ~11MB worth of
text consisting mainly in a long concatenation of:

... and  log_actionseq <> '...'

This data is also present in the sl_setsync table.

The problem happens immediately after the slave finishes syncing the
set, enables the subscription and tries to do the first sync.

I found a thread about it here:

http://old.nabble.com/Slave-can%27t-catch-up,-postgres-error-%
27stack-depth-limit-exceeded%27-td33182661.html

We're running on postgres 9.0.10 and slony1 version 2.0.7, and upgrading
is not an option in the near future (eventually we will upgrade both
postgres and slony).

The problem is that we hit this issue now more and more regularly - and
it is a killer for the slony replication, as it is not possible to
reliably set it up...

What I already tried and didn't help:

 * set max_stack_depth up to ridiculous amounts (10s of GB) - not sure
if I got the OS side of it right, but I did my best;

 * decrease the slon deamon's SYNC_CHECK_INTERVAL to 1 second;

With both those I still get the error regularly...

I wonder if this is fixed in newer slony releases, or if there's any
chance I can get some help/directions on how to fix/patch it in the
version we use to avoid this problem ?

Jan Wieck mentions in the thread cited above that the a solution would
be:

<quote>
The improvement for a future release would be to have the remote worker 
get the log_actionseq list at the beginning of copy_set. If that list is
longer than a configurable maximum, it would abort the subscribe and 
retry in a few seconds. It may take a couple of retries, but it should 
eventually hit a moment where a SYNC event was created recently enough 
so that there are only a few hundred log rows to ignore.
</quote>

Was this already implemented in a newer release ?

If not I would like to work on it, including back-patch for the 2.0.7
version we use...

I would appreciate any help/hints on how to approach this !

Cheers,
Csaba
Permalink | Reply | 1 comment |
headers
Suvv | 14 Mar 2013 07:47
Favicon

Slony replication monitoring


Dear experts,

We have implemented a table level replication between two sites.What are the
best methods to monitor slony replication.Thank you in advance.

Reagrds
Suvv 
--

-- 
View this message in context: http://old.nabble.com/Slony-replication-monitoring-tp35172851p35172851.html
Sent from the Slony-I -- General mailing list archive at Nabble.com.
Permalink | Reply | 3 comments |

Gmane