Thread: record-based log shipping

On Thu, 2007-12-06 at 09:30 -0800, SHARMILA JOTHIRAJAH wrote:
> Hi,
> Have anyone implemented or tried record-based log shipping?
> If so is there any other materials in the web other than the
> documentation (it has very few details about this)
> Thanks
> sharmila
>

I don't know exactly what you mean by "record-based log shipping", but
perhaps you're looking for something like Slony-I, which can be found
here:

http://www.slony.info

Regards,
    Jeff Davis

On Thu, 6 Dec 2007, SHARMILA JOTHIRAJAH wrote:

> Have anyone implemented or tried record-based log shipping? If so is
> there any other materials in the web other than the documentation (it
> has very few details about this)

There is an implementation of that as part of the Skytools WalMgr code:
https://developer.skype.com/SkypeGarage/DbProjects/SkyTools

Some sample interesting parts are in the walmgr.py file, in the
master_sync function, where they run

select file_name, file_offset from pg_xlogfile_name_offset(pg_current_xlog_location())

and then copy that section of the current WAL file over.

It's kind of hard to reuse any portion of their solution without adopting
the whole thing, but it's a decent approach if you're comfortable with
Python code.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

How about writing a C function (invoked from a trigger) that will send
the serialized tuple using say UDP protocol (considering you're syncing
on a reliable LAN), and then a simple UDP-listening daemon that will
perform the insert into the slave one. If you have multiple slaves, can
use that with broadcasting....

Alex.


On Fri, 2007-12-07 at 21:38 -0500, Greg Smith wrote:
> On Thu, 6 Dec 2007, SHARMILA JOTHIRAJAH wrote:
>
> > Have anyone implemented or tried record-based log shipping? If so is
> > there any other materials in the web other than the documentation (it
> > has very few details about this)
>
> There is an implementation of that as part of the Skytools WalMgr code:
> https://developer.skype.com/SkypeGarage/DbProjects/SkyTools
>
> Some sample interesting parts are in the walmgr.py file, in the
> master_sync function, where they run
>
> select file_name, file_offset from pg_xlogfile_name_offset(pg_current_xlog_location())
>
> and then copy that section of the current WAL file over.
>
> It's kind of hard to reuse any portion of their solution without adopting
> the whole thing, but it's a decent approach if you're comfortable with
> Python code.
>
> --
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

On Fri, 7 Dec 2007, Alex Vinogradovs wrote:

> How about writing a C function (invoked from a trigger) that will send
> the serialized tuple using say UDP protocol (considering you're syncing
> on a reliable LAN), and then a simple UDP-listening daemon that will
> perform the insert into the slave one. If you have multiple slaves, can
> use that with broadcasting....

I think you are on your way to reinventing "Statement-Based Replication
Middleware" using triggers and UDP.  The great part is that you can look
into the future and predict the problems you'll run into by reading that
section of
http://www.postgresql.org/docs/current/interactive/high-availability.html
rather then waiting until you run into them yourself.

Seriously, though, not trying to knock you from taking a stab at the
solution you suggest.  Just pointing out that you'll be facing a bit more
than just a trigger and getting the data moved over the LAN before you're
done.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

The documents highlights possible problems with _SQL_ query intercepts.
I am talking about the actual tuples... i.e. row data rather than the
SQL requests. Please advise if you see any other problems with suggested
approach. Thanks!

Alex.



On Fri, 2007-12-07 at 22:44 -0500, Greg Smith wrote:
> On Fri, 7 Dec 2007, Alex Vinogradovs wrote:
>
> > How about writing a C function (invoked from a trigger) that will send
> > the serialized tuple using say UDP protocol (considering you're syncing
> > on a reliable LAN), and then a simple UDP-listening daemon that will
> > perform the insert into the slave one. If you have multiple slaves, can
> > use that with broadcasting....
>
> I think you are on your way to reinventing "Statement-Based Replication
> Middleware" using triggers and UDP.  The great part is that you can look
> into the future and predict the problems you'll run into by reading that
> section of
> http://www.postgresql.org/docs/current/interactive/high-availability.html
> rather then waiting until you run into them yourself.
>
> Seriously, though, not trying to knock you from taking a stab at the
> solution you suggest.  Just pointing out that you'll be facing a bit more
> than just a trigger and getting the data moved over the LAN before you're
> done.
>
> --
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

On Fri, 7 Dec 2007, Alex Vinogradovs wrote:

> The documents highlights possible problems with _SQL_ query intercepts.
> I am talking about the actual tuples... i.e. row data rather than the
> SQL requests.

The first two issues that come to mind are how to deal with a) deletions,
and b) changes to DDL (table creation etc.).  Forwarding SQL handles those
but I'm not sure how your suggested scheme would.  You should certainly
look at what went into the design of both the existing WAL replication and
tools like Slony to get an idea the full scale of challenges here.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Now you're pointing out obvious problems. My company deals with data
warehouses, we don't really need to delete/update stuff, only
insert/select ;) But seriously, those issues can be handled if one
doesn't just send plain tuples, but also includes the information
about what kind of operations were performed. The receiving side
can then use primary keys to process deletes/updates. So the actual
solution might become way more flexible, it is only a question of
amount of development time put into its implementation...

P.S. DDL is never a subject for replication (in normal RDBMS'es).


Alex.


On Sat, 2007-12-08 at 01:10 -0500, Greg Smith wrote:
> On Fri, 7 Dec 2007, Alex Vinogradovs wrote:
>
> > The documents highlights possible problems with _SQL_ query intercepts.
> > I am talking about the actual tuples... i.e. row data rather than the
> > SQL requests.
>
> The first two issues that come to mind are how to deal with a) deletions,
> and b) changes to DDL (table creation etc.).  Forwarding SQL handles those
> but I'm not sure how your suggested scheme would.  You should certainly
> look at what went into the design of both the existing WAL replication and
> tools like Slony to get an idea the full scale of challenges here.
>
> --
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Fri, 7 Dec 2007, Alex Vinogradovs wrote:

> P.S. DDL is never a subject for replication (in normal RDBMS'es).

But it is passed along by PITR replication schemes like the record-based
log shipping that started this thread off; that's the main reason I
specifically pointed out that limitation of what you were suggesting.  I
personally work mostly on replication in terms of disaster recovery, so in
my world DDL is always a replication requirement rather than never.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Alex Vinogradovs wrote:
> Now you're pointing out obvious problems. My company deals with data
> warehouses, we don't really need to delete/update stuff, only
> insert/select ;) But seriously, those issues can be handled if one
> doesn't just send plain tuples, but also includes the information
> about what kind of operations were performed. The receiving side
> can then use primary keys to process deletes/updates. So the actual
> solution might become way more flexible, it is only a question of
> amount of development time put into its implementation...
>

If I understand what you are saying, this is how Replicator already does
replication (www.commandprompt.com). Inserts replication transactional
data, where updates and deletes replicate the low level command (what
was deleted/updated not the actual DML).

Joshua D. Drake



> P.S. DDL is never a subject for replication (in normal RDBMS'es).
>
>
> Alex.
>
>
> On Sat, 2007-12-08 at 01:10 -0500, Greg Smith wrote:
>> On Fri, 7 Dec 2007, Alex Vinogradovs wrote:
>>
>>> The documents highlights possible problems with _SQL_ query intercepts.
>>> I am talking about the actual tuples... i.e. row data rather than the
>>> SQL requests.
>> The first two issues that come to mind are how to deal with a) deletions,
>> and b) changes to DDL (table creation etc.).  Forwarding SQL handles those
>> but I'm not sure how your suggested scheme would.  You should certainly
>> look at what went into the design of both the existing WAL replication and
>> tools like Slony to get an idea the full scale of challenges here.
>>
>> --
>> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>