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 ?
\copy
commands to run inpsql
. Check the documentation about the difference between thecopy
statement (which exports to the server's filesystem) and the\copy
command inpsql
(which exports to the client's filesystem).\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.