That tutorial appears to be old, and also says "work in progress" at the top. I'll amend it if I have time. Please refer to the main documentation.
If you have continuous archiving set up you don't need to manually rsync the WAL, because your archive_command
will do that for you.
You may also wish to consider using pg_basebackup
instead of manually rsync'ing the datadir. It'll take care of everything for you over the PostgreSQL replication protocol. If you weren't using WAL archiving you'd use -X stream
mode to get it to copy the WAL for you too, but you don't need that.
You need to be aware of query results and query behavior with replication running. While there are a minimum of two threads for MySQL Replication, it is the SQL thread that can get in the way of SELECT queries. Why?
MyISAM
Each time an INSERT, an UPDATE, or a DELETE is executed, a full table locked is issued. That can block SELECTs. The only exception is when you have concurrent_insert set to 2 and the MyISAM table experiences no UPDATEs or DELETEs.
Please see my earlier posts on MyISAM behavior.
AFFECTS ON REPLICATION : You should be a little concerned here because the SQL thread only processes INSERTs, UPDATEs, DELETEs, and various DDL commands. Should any of these command get issue by the SQL thread during MySQL Replication on a MyISAM table you are reading, there will be some latency due to the momentary table lock.
InnoDB
InnoDB can be a little more forgiving in allowing you to read from an InnoDB table that is being written, but it comes at a price. What is the price ???
InnoDB allows for MVCC (Multiversion Concurrency Control) and Transaction Isolation. This means InnoDB will write gobs of housecleaning and point-in-time information so that you can read rows from an InnoDB table without blocking incoming writes, and vice versa.
Here is a pictorial representation of InnoDB's Architecture
All that housecleaning and point-in-time information sits inside the Undo Space and Rollback Segments inside the system tablespace.
AFFECTS ON REPLICATION : When it comes to MySQL Replication, INSERTs, UPDATEs and DELETEs against an InnoDB from an active SQL thread will not block SELECTs from that same InnoDB tables. Of course, any DDL against an InnoDB table will block SELECTs just the same as a MyISAM table would.
MySQL REPLICATION
MySQL Replication can also be affected for the same reasons I mentioned. The shoe would be on the other foot: the SQL thread would get locked if it needs to do an INSERT, UPDATE, or DELETE against a MyISAM table. For InnoDB, MVCC info would be written up to permit SELECTs on the Slave.
EPILOGUE
If the Slave has hundreds of DB Connections, one more DB Connection (the SQL thread) may not be a lot to worry about
If you do know that the Slave may experience an onslaught of INSERTs, UPDATEs, and DELETEs coming from the Slave's SQL Thread, you could run STOP SLAVE;
to disconnect the SQL thread and stop processing SQL commands and also stop downloading Master binlog events. As an alternative, you could run STOP SLAVE SQL_THREAD;
to disconnect the SQL thread but allow incoming master binlog event to continue to be downloaded to its realy logs and await processing when the START SLAVE;
is issued later. If the number of SQL commands coming from MySQL Replication are few and far between, then MySQL Replication can be left running.
Best Answer
There is no such feature at this time and the transaction logs lack some of the key information you would need to implement such a feature.
Work is ongoing for PostgreSQL 9.4 to implement logical and bi-directional replication between PostgreSQL instances. Part of this work involves enhancements to the xlogs to add some key information required for logical row replication, adding a new
wal_level = 'logical'
option. There's also a pluggable infrastructure for decoding the WAL and streaming it to remote hosts, writing SQL to a file, etc.That's where it becomes useful to you. Your best bet at this point is probably to use that infrastructure to decode PostgreSQL WAL into a change stream you can send to your remote Oracle or SQL Server systems. This will involve a considerable amount of development, as the logical replication code is somewhat immature and still has some limitations even with Postgresql-to-PostgreSQL limitations. DDL is totally unsupported at this time, for one thing.
If you do this you'll need to understand that the BDR work will probably not be committed into PostgreSQL 9.4 in a fully compatible form so you'd have to do re-work to make it compatible with main-line PostgreSQL later.
You'll want to start by adapting the existing text output plugin for the logical walsender to generate more database-agnostic SQL.
If this all sounds way too hard then your only other option is to implement a trigger-based replication solution or adapt an existing trigger based replication system to meet your needs. This will be much (much) higher overhead on the server, but a fair bit simpler.
I should disclose that I work for 2ndQuadrant, who're developing bidirectional replication for PostgreSQL, and I've recently started working on testing and documentation as part of the project.
Update as of 2014-07: Logical changeset extraction / logical decoding was committed to PostgreSQL 9.4, and is due to be released soon. Beta 2 came out a few days ago.
See:
Logical decoding makes it possible to implement what you want, but does not provide the feature you want directly. To stream changes into MS SQL, Oracle, etc, you would need an output plugin for logical decoding that can stream data to a streaming logical log receiver. The receiver application would maintain a connection to the target server and apply the changes as it receives them from PostgreSQL.
This is the basis of the PostgreSQL BDR/UDR architecture. BDR is not directly useful for your purposes, but the logical changeset decoding feature that was built to make BDR possible will be.
A good starting point would be to look at the
test_decoding
plugin