Update a table (Database1) from the same table (Database2) in Postgresql

I have two databases with similar data but what I want to do is to update the changes of sample_table from Database2 to sample_table of Database1.

Example:

Database1 [sample_table]

ID     Reference      Name
0001   Ref0001        Test1
0002   Ref0002        Test2

Database2 [sample_table] - with changes

ID     Reference      Name
0001   Ref0001        Name1
0002   Ref0002        Name2

In the sample_table of Database2, I changed the data in the Name field and I want to apply the changes in the sample_table in Database1. What SQL query to execute to do this? I'm using pgAdminIII and pgAdmin4. (both databases are on the same server)

If you have any questions, comment below.

Solution

There is no single answer for that question, you could get it solved in so many ways. Let's enumerate a few:

Create a plain sql dump for the Database2, edit the table names to be different from the Database1 tables. Execute the modified sql dump into Database1 to have all the data into the same Database. Execute an update from the imported tables into the original ones joining/matching the tuples by id.
Like suggested: Create a foreign data wrapper of type postgres_fdw as described to use it to update the original tables from the external datasource directly in postgres.
Create a simple external script that connect to boths databases to read from one to update into another one.
Use a kind of (ETL, or Replication) software system to be able to keep databases updated. Something like Bucardo or Kettle(Pentaho Data Integration) could do the trick.

All depends on how complex or simple solution do you need, one or few usages or long term usage