PostgreSQL 8.4 replication of more than 100 tables

Question

I am facing a problem with PostgreSQL version 8.4.

I need to create an slave machine but when I look to all tutorials of slony or londsite its saying I will need to specify the tables that I want to replicate with some details and sequences separately..... Now imagine a database with more than 100 tables, that is my case, what should I do?

If I upgrade PostgreSQL to version 9X the database links with Oracle stop working, so I need to stay with 8.4 but I can't find a tutorial to replicate the entire database like I do in the mysql instances.

I will be more specific in the end of this tutorial learning how to use slony suggests:

set add table (id=1, set id=1, origin = @PRIMARY, fully qualified name = 'schema.tablename', comment = 'table');
set add sequence (id=1, set id=1, origin = @PRIMARY, fully qualified name = 'schema.sequencename', comment = 'seq');

(Link for the full tutorial: https://www.howtoforge.com/configuring-slony-i-cascading-replication-on-postgresql-8.3)

For each table i want, and don't say nothing about replicating the entire database, another tutorials says the same.

Please somebody know a good way to solve that? Maybe I will need some Shell Script?

• Craig Ringer: Well, you're going to have to upgrade at some point. In what way do the "database links" with Oracle "stop working" when you upgrade? To which version of PostgreSQL?
• Craig Ringer: It sounds like you probably want a streaming replica with hot standby. I don't remember if that's supported back in 8.4, but you could use hot standby with an archive-based standby if not. See postgresql.org/docs/current/static/continuous-archiving.html and postgresql.org/docs/current/static/high-availability.html . If you were on 9.4 or newer you could also consider bdr-project.org but that'll do you no good on 8.4

Answer 1

The solution was to use Postgres version 9.1 and Oracle Version 11.2.0.1.0.

I am running oracle in Debian Wheezy all do i need is to do some upgrades in certain libraries and now it works good:

Version of ODBC:

ii odbc-postgresql:amd64 1:09.01.0100-1+deb7u1 amd64 ODBC driver for PostgreSQL

Version of Libssl required by ODBC:

ii libssl1.0.0:amd64 1.0.1e-2+deb7u13 amd64 SSL shared libraries 

Postgres version in the another server:

ii postgresql-9.1 9.1.15-0+deb7u1 amd64 object-relational SQL database, version 9.1 server

This is how do i install Oracle with PostgreSQL 9.1 datalink and its working very fine now, also with Mysql 5.5.

Think now i will be able with this new version to do easy replication just like i do in the Mysql instances!