0

I use postgresql. I have many databases in a server. There is one database which I use the most say 'main'. This 'main' has many tables inside it. And also other databases have many tables inside them.

What I want to do is, whenever a new row is inserted into 'main.users' table I wish to insert the same data into 'users' table of other databases. How shall I do it in postgresql? Similarly I wish to do the same for all actions like UPDATE, DELETE etc.,

I had gone through the "logical replication" concept as suggested by you. In my case I know the source db name up front and I will come to know the target db name as part of the query. So it is going to be dynamic.

How to achieve this? is there any db concept available in postgresql? Or I welcome all other possible ways as well. Please share me some idea on this.

Improve this question
2
  • Does this answer your question? Postgres replication Commented May 13, 2020 at 4:19
  • @nicooga, that solution goes like a full db replication. But what I want is, if a single row is inserted, I want to add this single row data alone to tables of other dbs. Is it possible? also, I don't want the entire row to be replicated in tables of other db. Because they have different structure, I may need to deal with few columns of the entire row. Commented May 13, 2020 at 4:50

3 Answers 3

Reset to default
1

If this is all on the same Postgres instance (aka "cluster"), then I would recommend to use a foreign table to access the tables from the "main" database in the other databases.

Those foreign tables look like "local" tables inside each database, but access the original data in the source database directly, so there is no need to synchronize anything.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

but that foreign table concept will create a new table isn't it? in my case, I have to update the data into an existing table and already application is consuming those db tables. What shall I do?
Just "replace" your existing table with the foreign table.
0

Upgrade to a recent PostgreSQL release and use logical replication.

Improve this answer

Comments

0

Add a trigger on the table in the master database that uses dblink to access and write the other databases.

Be sure to consider what should be done if the row alreasdy exists remotely, or if the rome server is unreachable.

Also not that updates propogated usign dblink are not rolled back if the inboking transaction is rolled back

Improve this answer

3 Comments

I am dealing with a single server alone. Inside the same server I have different databases.
That eliminates my concerns over networking problems. dblink is still a good solution.
Triggers work on tables within a single database. Isn't it? My need is to work with multiple databases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.