0

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 ?

3
  • You can write a query to generate \copy commands to run in psql. Check the documentation about the difference between the copy statement (which exports to the server's filesystem) and the \copy command in psql (which exports to the client's filesystem). – Colin 't Hart Aug 26 '20 at 9:28
  • @colin-t-hart I know about \copy, but that is only recognized from the psql client so it can't be inside a query, so no dynamic/multiple table or file names. – Felipe Guajardo Aug 26 '20 at 17:04
  • I'm not saying run it directly, but to generate the statements which you will then run in psql. – Colin 't Hart Aug 26 '20 at 17:14
-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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.