Postgresql how to insert (or update) a local database (or table) with entries (or a query) from a remote database?

Question

I have a postgresql database called tables with 2 tables which looks like this

readingtable

date         reading1 reading2 reading3 source
2021-01-01   xx       xx       xx       x1
2021-01-01   xx       xx       xx       x2
2021-01-01   xx       xx       xx       x3
...
2021-01-01   xx       xx       xx       x150
2021-01-02   xx       xx       xx       x1
2021-01-02   xx       xx       xx       x2
....
2021-06-29   xx       xx       xx       x150

booltable

date         bool1    bool2    bool3    source
2021-01-01   xx       xx       xx       x1
2021-01-01   xx       xx       xx       x2
2021-01-01   xx       xx       xx       x3
...
2021-01-01   xx       xx       xx       x150
2021-01-02   xx       xx       xx       x1
2021-01-02   xx       xx       xx       x2
....
2021-06-29   xx       xx       xx       x150

I have 150 sources which save reading to my postgresql server which are saved to 2 tables.

So example, today I would get the following data appended to the readingtable table

date         reading1 reading2 reading3 source
2021-06-30   xx       xx       xx       x1
2021-06-30   xx       xx       xx       x2
2021-06-30   xx       xx       xx       x3
...
2021-06-30   xx       xx       xx       x150

I have another backup postgresql server in mylaptop where I save the readings everyday.

But to do that, what I do is first delete from booltable; and delete from readingtable;.

Then I do pg_dump -h $remoteip -p $remoteport -U $remoteuser -C tables | psql -h localhost -d tables -U $localuser

This copies every entry in the database.

Is there a way for my local database to just compare with the remote database and only update the entries which are missing (the latest date's 150 entries).

bfris · Accepted Answer · 2021-06-29T21:27:09.470

The trick here is to know about foreign data wrappers. This feature allows communication from one PostgreSQL database server to another database server. This answer links to postgres_fdw, which allows comms from PostgreSQL to PostgreSQL.

Install the extension:

CREATE EXTENSION postgres_fdw;

Create a server. This only works if your server is accepting connections:

CREATE SERVER foreign_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');

Create foreign table(s):

CREATE FOREIGN TABLE foreign_readingtable (
    date date,
    reading1 text,
    reading2 text,
    reading3 text,
    source   text)
    SERVER foreign_server
    OPTIONS (schema_name 'some_schema', table_name 'readingtable');

CREATE FOREIGN TABLE foreign_booltable (
    date   date,
    bool1  boolean,
    bool2  boolean,
    bool3  boolean,
    source text)
    SERVER foreign_server
    OPTIONS (schema_name 'some_schema', table_name 'booltable');

With the foreign tables all set up, now it becomes a question of how to getting the data in the right place with either SELECT INTO or INSERT statements

Postgresql how to insert (or update) a local database (or table) with entries (or a query) from a remote database?

1 Answers1