Can I use postgres_fdw without foreign tables defined? I have a production database "PRODdb1", with a read-only user account. I have a need to query(select statement) this database and insert the data into a secondary database named "RPTdb1". I originally planned to just create a temp table in PRODdb1 from my select, but permissions are the issue.

I've read abut dblink &amp; postgres_fdw, but are either of these a solution for my issue? I wouldn't be creating foreign tables because my SELECT is joining many tables from PRODdb1, so I'm unfamiliar if postgres_fdw would still be an option for my use case.

Another option would be any means of getting the results of the SELECT to a .CSV file or something. My main blocker here is that I only have a read-only user to work with, but no way around that issue.
postgres_fdw is definitely the better way. As you have read-only access to the production database, you can setup a FDW on your test (secondary) database provided you have superuser access to that (secondary) server. Or at least someone who can install the extension and setup the foreign server. Once those two things are there, you can proceed with a regular user. 

One thing to keep in mind: if you do use the FDW, any select on the foreign table (in your test server) will generate some load on the production server. Whether or not that is acceptable only you can know. 




  my SELECT is joining many tables from PRODdb1


If you join multiple foreign tables in a single statement those joins will be pushed down to the foreign server and evaluated there (at least with the Postgres 10 and I think 9.6 as well) only the result of the join will be sent back to the calling server. 



Another option is to use pg_dump to copy the data from your production server to your test server. 

pg_dump --host=prodserver --dbname=prod_db --table=public.some_table --file=some_table.sql --no-owner


That will export the data for some_table into a SQL script, including the necessary CREATE TABLE statement. 

You can then run that SQL script through psql to import the data into your test server:

psql --host=testserver --dbname=test_db --file=test_table.sql 


If you already have the table definition in your test server, then you might want to use --data-only for pg_dump to skip that part. 

That will be faster than trying to generate some CSV files.