exporting postgres table to local file

Questions / Help
1

Hey guys, hopefully this would be a simple one for you.
I’m looking to export the contents of one of my tables saved on postgres cluster to a local file.
I’m no postgres expert, but I couldn’t find a way to run a psql command locally (withput first connecting to the server using ‘flyctl postgres connect’).
I’m sure there must be some way of doing this

2

Hey @Alonzo,

Guillaume from Fly.io here. Sure! All you have to do is to open a secure tunnel from your local machine to your postgres cluster by running fly proxy 5432 -a <postgres-app-name> as mentioned here in our docs. Then in another terminal window you can do whatever you want with psql or anything else to connect to your cluster: it’ll be as if it was running on your machine! (at postgres://postgres:<password>@localhost:5432)

Tell me if that helps

Cheers

3

Hi @guillaume ,

Thanks for the quick reply, I might need some further explanation though.
Once I opened the tunnel, what would be the the command I should run in the new terminal window in order to create a psql prompt? do I need to have psql installed locally?

4

do I need to have psql installed locally?

Well if you want an psql prompt yes. Then run:

psql postgres://postgres:<password>@localhost:5432

To get the psql prompt, and then to get a CSV export of your table just input:

\copy tablename to 'local/path/to/export-file.csv' csv;
1 Like
5

Thanks. That did the trick!

6

thanks, password get from DATABASE_URL

7

From How To to Questions / Help

8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.