Postgresql – Copying Tables (partly replicating) databases remotely

copypostgresql-9.4

I am having hard time figuring out a possible solution copying data from a tables to another.

I have 2 databases
(DB1 and DB2).

DB1 and DB2 has the following tables (eg):

Product
Contacts
Sales

Copying Part,

When a data is inserted, updated to DB1 product tables, it has to be copied to DB2 also.

However,
if DB2 product table is inserted / updated, it should not reflect to DB1 (i assume this might not be a issue).

Further Complication:

If in DB2 Sales table, if new data is inserted/updated, it has to be copied to DB1 and DB1 can make changes and update the table which should reflect to DB2.

I have very less experience with database, although i am using it for quite frequently but have not attempted to do this sort of work, i am finding this a bit complicated. I attempted to look few of the Tutorials, videos. There are techniques using replications, hot standby, which is good but not suitable for my case.

Is it possible to do this thing, i am sure there are tools but i am not sure which tools is very useful for my case? Any suggestions or help is much appreciated.

AFAIK, if we can sync between 2 different database we need to use GUID / UUID (i have used this for one way replication). For this case i use a python server calculate and do the transaction. to retrieve about 10000 records it takes around 5-10 minutes which is tediously slow and useless for my new case. My new case might need to retrieve more than 100 thousan records at a time.

Any suggestions i much appreciated. I am just looking forward for some sign of hope as i could not google any solution.

Best Answer

Create 2 DBLinks between the 2 databases(DB1 to DB2 and vice versa), and use these links to insert/update data by creating triggers for insert/update for the tables.

Related Solutions

Postgresql – Optimize PostgreSQL server setting for extremely wide tables

How many of those columns to you use for grouping? If it's relatively few, then I would recommend restructuring the data to be in a long format, where each grouping (category) column is maintained, and is each grouped-by (metric) column is instead jammed into two columns variable and value, similar to how R's reshape2::melt function works. For instance, a table:

id|cat1|cat2|metric1|metric2|

Would become:

id|cat1|cat2|variable|value
id|cat1|cat2|metric1|value of metric 1 column
...
id|cat1|cat2|metric2|value of metric 2 column

The table would become K times longer, with K being the number of metrics you'd like to melt. This can actually improve query performance if you add indexes on your category columns.

If that doesn't speed up performance, then I'd recommend using a different tool than Postgres, such as Apache Spark.

Postgresql – To partition or not to partition

There are many open ends in your question, but partitioning by customer could to be the way to go - especially if:

you expect many customers,
each of them could have tons of data ("tons" means much more than RAM cache size),
most of their datasets will be mutually exclusive (each customer sees different subset of data).

RULEs or triggers are a performance overhead, and can be avoided.

Consider something along these lines:

BEGIN;

CREATE USER tenant1;
CREATE USER tenant2;

CREATE SCHEMA app;
CREATE SCHEMA tenant1;
CREATE SCHEMA tenant2;

CREATE TABLE app.objects_nonphysical(id int);
CREATE TABLE app.objects_physical(id int);
CREATE TABLE app.objects_mapping(id int);    
CREATE TABLE tenant1.objects_nonphysical() INHERITS(app.objects_nonphysical);
CREATE TABLE tenant1.objects_physical() INHERITS(app.objects_physical);
CREATE TABLE tenant1.objects_mapping() INHERITS(app.objects_mapping);
CREATE TABLE tenant2.objects_nonphysical() INHERITS(app.objects_nonphysical);
CREATE TABLE tenant2.objects_physical() INHERITS(app.objects_physical);
CREATE TABLE tenant2.objects_mapping() INHERITS(app.objects_mapping);

GRANT USAGE ON SCHEMA tenant1 TO tenant1;
GRANT USAGE ON SCHEMA tenant2 TO tenant2;
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA tenant1 TO tenant1;
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA tenant2 TO tenant2;

/* TEST: simulate login as customer */
SET SESSION AUTHORIZATION tenant2;
/* No schema needed - default search_path works */
SELECT count(*) FROM objects_nonphysical; 

ROLLBACK;

You do not need any triggers / rules to maintain it.

There are open ends here - that's just a draft... Some issues:

PK, FK and indexes are not "inherited".
even if you create them, the PK is not enforced on master table
you can overcome this by using same sequence for all tenants
obviously, application must be adjusted for this model

Related Question