How to export data from multiple tables to csv from remote postgres server (AWS RDS) to local machine?

Question

I need to export data in CSV format from many (undertimined amount) tables from a remote postgres (aws RDS PostgreSQL 10.6) server to a local machine.

When the server was in its own machine (AWS EC2) this was possible with a function that used a loop to iterate over the necesary tables then used execute and COPY commands to create the CSV files directly onto the same server from which they could by copied over where needed.

But since the server was migrated to an RDS instance, I can't use that approach anymore because I have no direct access to the filesystem on the postgres server. I can only connect to it remotely via a client.

Every solution I've seen until now requires either filesystem access to the server or works with only one set of data at time with a known table that produces only one csv file on the local machine.

Is there a way to achieve what I need ?

Answer 1

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 ''"''  ;';

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

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