headers
Glyn Astill | 2 Jul 2010 12:11
Picon
Favicon

Dropping table with lots of entries in sl_log

Hi Guys,

We have a replicated table that stores errors from an application that recently went mad and logged about
650000 records each about 75kb in size.

Our local replication is fine, but we have servers on the end of a vpn that are now lagging massively.

If I drop the table from replication will the entries in sl_log be cleared out and replication cease for that
table - or will the drop wait until the tables are in sync?

Thanks
Glyn
Permalink | Reply | 3 comments |
headers
Christopher Browne | 2 Jul 2010 19:55

Re: Dropping table with lots of entries in sl_log

Glyn Astill <glynastill@...> writes:
> We have a replicated table that stores errors from an application that recently went mad and logged about
650000 records each about 75kb in size.
>
> Our local replication is fine, but we have servers on the end of a vpn that are now lagging massively.
>
> If I drop the table from replication will the entries in sl_log be cleared out and replication cease for
that table - or will the drop wait until the tables are in sync?

SET DROP TABLE does not clear out the entries from sl_log_*, so you'd
find that the DROP would wait until the tables get into sync :-(.

I suppose that what you could do, if you plan to nuke the table, is to
delete those records from sl_log_* by hand.

That is...  If the table's ID is 17...
   delete from _my_schema.sl_log_1 where log_tableid = 17;
   delete from _my_schema.sl_log_2 where log_tableid = 17;

I think I'd want to order it thus...

0.  Back up the table some place where it contains the data that you think it ought to have

    e.g. - use "pg_dump -t my_log_table > my_log_table.sql" to
    preserve you from losing contents.

1.  Slonik script to drop table #17

2.  delete from sl_log_* against all the nodes
(Continue reading)

Permalink | Reply | 1 comment |
headers
Chirag Dave | 2 Jul 2010 20:02

Re: Dropping table with lots of entries in sl_log

Glyn Astill wrote:
> Hi Guys,
>
> We have a replicated table that stores errors from an application that recently went mad and logged about
650000 records each about 75kb in size.
>
> Our local replication is fine, but we have servers on the end of a vpn that are now lagging massively.
>
> If I drop the table from replication will the entries in sl_log be cleared out and replication cease for
that table - or will the drop wait until the tables are in sync?
>   

Correct, drop table event will be in the queue and will not get 
processed until all the events before that get processed. you have an 
option to drop the node and re-subscribe the node.

> Thanks
> Glyn
>
>
>       
>
> _______________________________________________
> Slony1-general mailing list
> Slony1-general@...
> http://lists.slony.info/mailman/listinfo/slony1-general
>
Permalink | Reply | 0 comments |
headers
Glyn Astill | 5 Jul 2010 10:32
Picon
Favicon

Re: Dropping table with lots of entries in sl_log

> From: Christopher Browne <cbbrowne@...>
> Subject: Re: [Slony1-general] Dropping table with lots of entries in sl_log
> To: "Glyn Astill" <glynastill@...>
> Cc: slony1-general@...
> Date: Friday, 2 July, 2010, 18:55
> Glyn Astill <glynastill@...>
> writes:
> > We have a replicated table that stores errors from an
> application that recently went mad and logged about 650000
> records each about 75kb in size.
> >
> > Our local replication is fine, but we have servers on
> the end of a vpn that are now lagging massively.
> >
> > If I drop the table from replication will the entries
> in sl_log be cleared out and replication cease for that
> table - or will the drop wait until the tables are in sync?
> 
> SET DROP TABLE does not clear out the entries from
> sl_log_*, so you'd
> find that the DROP would wait until the tables get into
> sync :-(.
> 
> I suppose that what you could do, if you plan to nuke the
> table, is to
> delete those records from sl_log_* by hand.
> 
> That is...  If the table's ID is 17...
>    delete from _my_schema.sl_log_1 where
> log_tableid = 17;
(Continue reading)

Permalink | Reply | 0 comments |
headers
Frank Jansen | 5 Jul 2010 17:45
Picon
Favicon

Upgrade Question

  Hi folks,

we want to upgrade our Slony 2.0.2 to 2.0.4. The documentation states it 
is very straightforward: just compile, install on all nodes and execute 
a slonik script with the command "update functions".
In the binary directory of slony i found a script called 
"slonik_update_nodes" which contains at the end:
run_slonik_script($slonik, 'UPDATE FUNCTIONS');

My 2 Questions are:
1. Can I use this slonik_update_nodes script to update the function on 
our nodes?
2. Do I have to invoke the script at the master with the id of the nodes 
as argument (e.g. slonik_update_node 1; slonik_update_node 2 and so on) 
or do i have it to run on each node itself? The docs and google dont say 
much about this (certainly dumb) question :(

Thanks and kind regards

Frank Jansen

--

--
Permalink | Reply | 1 comment |
headers
Jan Wieck | 5 Jul 2010 18:32
Picon
Favicon

rangerrick@...: slony patch for osx]

On 5/27/2010 12:14 PM, Christopher Browne wrote:
> Steve Singer wrote:
>> Steve Singer wrote:
>>> David Fetter wrote:
>>>
>>>
>>> I think I can replace our direct use of yyleng with calls to 
>>> yyget_len() to avoid this issue
>> 
>> 
>> David, does this patch (in addition to the autoconf changes) get things 
>> working on your version of OSX? I've replaced uses of yyleng with 
>> yyget_len() and am having flex generate a proper prototype for it.
>> 
>> 
>> 
>> http://github.com/ssinger/slony/commit/0febbf93046600e3fd7c59e37ca3b14cc137eb64 
> 
> FYI, result of some discussion that should get exposed on list...
> 
> yyget_len() is available on modern versions of flex (at least as far 
> back as 2.5.31, which is what PostgreSQL requires, of late).
> 
> So, I'm suggesting that configure be modified to check for 2.5.31 (or 
> later), for which a sample test may be gotten from config/programs.m4 in 
> the PostgreSQL tree.  Given that, it'll be very clear to people that 
> have any difficulties that they're using a too-old flex.

Has this been done? If not, I'm in favor of this solution.
(Continue reading)

Permalink | Reply | 0 comments |
headers
Steve Singer | 6 Jul 2010 14:56

Re: Upgrade Question

Frank Jansen wrote:
>   Hi folks,
> 
> we want to upgrade our Slony 2.0.2 to 2.0.4. The documentation states it 
> is very straightforward: just compile, install on all nodes and execute 
> a slonik script with the command "update functions".
> In the binary directory of slony i found a script called 
> "slonik_update_nodes" which contains at the end:
> run_slonik_script($slonik, 'UPDATE FUNCTIONS');
> 
> My 2 Questions are:
> 1. Can I use this slonik_update_nodes script to update the function on 
> our nodes?

Yes that is the purpose of the script. It should work.

> 2. Do I have to invoke the script at the master with the id of the nodes 
> as argument (e.g. slonik_update_node 1; slonik_update_node 2 and so on) 
> or do i have it to run on each node itself? The docs and google dont say 
> much about this (certainly dumb) question :(
> 

The slonik_update_node script will invoke slonik which will then connect 
to the node being updated.  It doesn't matter what machine you run the 
script on as long as it is able to connect through the network to the 
database your trying to update.   So if your master can connect to all 
of your nodes then you should be fine running the script on the master. 
  (The perl script should loop through each of the nodes in your .conf 
file and update them).
(Continue reading)

Permalink | Reply | 0 comments |
headers
ChronicDB Community Team | 6 Jul 2010 16:51
Favicon

Feedback on live schema changes and updates for PostgreSQL

Hello,

We have been developing a high-availability and version control solution for Postgres, called ChronicDB[1], that aims to combine live schema changes, replication, live connection migration, and scalability in a cohesive manner. We are requesting feedback[2] on the most desireable features Postgres users would like to have in such a solution.

ChronicDB is available for Postgres 7.4, 8.x, and 9, and can already apply live schema changes with zero downtime through live query-rewritting and live connection migration. The vision for ChronicDB is to eventually be able to apply:

Complex live schema changes:
- Partition operations such as vertical and horizontal splitting
- Field merge and splitting operations
- Field renaming
- Field relocation to a different table
- Table renaming
- All these operations applied together atomically, without downtime
- Allowing an old version and a new version of an application to run concurrently, indefinitely
- Reversing schema changes

Replication:
- Master/slave replication, with an unlimited number of slaves
- Not having to restart replication when 95% of a database is replicated over a WAN link and a timeout interrupts replication
- Synchronous replication between a master and a primary backup
- Hot swap of master with primary backup without downtime
- Simple configuration

Live connection migration:
- The client applications will not be interrupted, and won't need to reconnect; downtime will be eliminated

Scalability:
- Live query inspection and routing to read-only replicas. This assumes the application is capable of handling such a weaker consistency model.


We would like to invite you to learn more about ChronicDB, to download it and to request new features. We would like to ask:
(1) What are the major types of schema changes you need to be able to apply ?
(2) If it was possible for us to fix a single (or two) frustrating, database update problems, which problems would those be?
(3) What is the major cause of downtime in your database applications ?

References:
[1] http://chronicdb.com
[2] http://chronicdb.com/request_new_features

Sincerely,
ChronicDB Community Team
http://chronicdb.com/benefits_of_chronicdb 
_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://lists.slony.info/mailman/listinfo/slony1-general
Permalink | Reply | 0 comments |
headers
Scott Marlowe | 7 Jul 2010 05:25
Picon

Re: [PERFORM] WAL partition overloaded--by autovacuum?

On Tue, Jul 6, 2010 at 7:10 PM, Richard Yen <richyen@...> wrote:
> This leads me to believe that there was a sudden flurry of write activity that occurred, and the process
that would flush WAL files to /db/data/ couldn't keep up, thereby filling up the disk.  I'm wondering if
anyone else out there might be able to give me some insight or comments to my assessment--is it accurate?
 Any input would be helpful, and I'll try to make necessary architectural changes to keep this from
happening again.

I tend to agree.  What kind of disk setup is under your data store?
If it's RAID-5 or RAID-6 is it possibly degraded?

I'd run iostat -xd 10 while the write load it high to see how your
xlog partition %util compares to the main data store partition.

Do you have your main data store partition under a battery backed
caching RAID controller?  Tell us what you can about your hardware
setup.
Permalink | Reply | 12 comments |
headers
Scott Marlowe | 7 Jul 2010 05:27
Picon

Re: [PERFORM] WAL partition overloaded--by autovacuum?

On Tue, Jul 6, 2010 at 7:10 PM, Richard Yen <richyen@...> wrote:

One more thing, do you have long running transactions during these periods?
Permalink | Reply | 0 comments |

Gmane