How to update selected tables in a Postgres database schema from a database dump on another server?

Question

I have a database and some tables are updated periodically on the dev server and need to be pushed to the database on the prod server. What's the appropriate way to migrate the updated tables from the dev database to the prod database with pg_dump and pg_restore? I could dump the whole database on the dev server and restore it on the prod server, but some tables contain dynamic data (created/updated by applications) that are different between the two databases, so these tables on the prod server must be preserved. Still some tables contain static data that won't be changed and need not be updated on the prod server though it's okay to update them.

So basically what I want to do is to update the data in some tables of the database on the prod server with the data of the corresponding tables from the database on the dev server, using pg_dump and pg_restore (version 15.4). Thanks.

P.S. I've read the manual pages of pg_dump and pg_restore, but still don't have a clear idea how to do what I want. I had tried a few tests and didn't work as I expected. I also searched online and didn't see posts addressing the same issue.

Answer 1

Adding --clean --if-exists offers the full table overwrite, while using --schema='specific_schema' and --table='specific_table' you can narrow down what's pg_dump supposed to target, skipping all else.

pg_dump -d your_dev_db -U your_dev_user -p 5432 \
   --table='your_schema.your_table' --clean --if-exists \
| psql -d your_prod_db -U your_prod_user -p 5432 --ON_ERROR_STOP

postgres_fdw mentioned by @Adrian Klaver can let you link objects on dev to be visible and accessible on prod as if they were present locally, which lets you either just use them directly, or reference them from DML to perform the necessary prod updates based on dev on the spot, from within prod. Demo:

CREATE EXTENSION postgres_fdw;
CREATE SCHEMA IF NOT EXISTS dev_public_schema;

CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (dbname 'dev');
CREATE USER MAPPING FOR current_user
        SERVER foreign_server
        OPTIONS (user 'postgres');
IMPORT FOREIGN SCHEMA public
   FROM SERVER foreign_server 
   INTO dev_public_schema;

INSERT INTO public.my_prod_table1 
SELECT * FROM dev_public_schema.my_dev_table1
WHERE created_at>='yesterday'::timestamptz;

The doc also documents multiple replication methods that let you set up an automatic process of moving the data from dev to prod. In particular, you can set up logical replication to "replay" on prod specific changes that took place on dev.