Copy result of a query to a new PostgreSQL database
Some ideas:


You can use COPY (SELECT ...) TO ... to write the relevant parts of the tables to a file on the database server and use COPY ... FROM on the destination database to load the data.

If the databases are not on the same machine, you could use NFS or similar for a shared directory, or you can use COPY ... TO PROGRAM ... to pipe the result into scp.
You can use a postgres_fdw foreign table and just use

INSERT INTO foreign_tab
   SELECT ... FROM localtab;


to transfer the data.