How to export data from multiple tables to csv from remote postgres server (AWS RDS) to local machine?
if it was on premises, only thing you need to is to mount it first and then and run this as a sql query :
DO $$
DECLARE ssql varchar := 'COPY ( SELECT  * from schema.table1   ) TO ''/projeler2/Raporlar/SatisRaporlari/test' 
|| TO_CHAR(NOW(),'DD_MM_YYYY') 
|| '.csv'' WITH CSV DELIMITER '','' HEADER QUOTE ''&quot;''  ;';

BEGIN
    EXECUTE ssql;
    --raise notice 'Value: %', ssql;
END; $$;

but remote server may change the game. give it a try