How to Export Data from a Remote PostgreSQL Table to a CSV file on your Local Machine

You can export data from a PostgreSQL table to a CSV file on your Ubuntu server and then transfer that CSV file to your local laptop desktop using several methods.

Recently, I needed to export data from a PostgreSQL database of a Django App to a CSV file and save it on my local machine, and I was looking for a straightforward guide to do that but I couldn’t find any.

Anyways, I eventually figured it out, so I decided to write this short piece so that you wont need to try so many things before you figure it out.

How to Export Data from a Remote PostgreSQL Table to a CSV file on your Local Machine

There are various ways to can use to achieve this, but I will be sharing the easiest and also the most common approach using the `psql` command-line tool and `scp` for file transfer:

So, lets get started.

1. Login to your Ubuntu server via your SSH terminal. You will most likely use a command like below to access your Ubuntu server.

ssh <username>@<server_ip>

2. Connect to your server PostgreSQL database, this will involve a couple of commands, below is an example, please modify it to meet your needs.

sudo -i -u postgres

psql 

\c <database name>

3. Export the data to a CSV file by using the `psql` command-line tool. Replace `<username>` with your PostgreSQL username and `<database>` with your database name:

COPY <table_name> TO '/tmp/table_name.csv' WITH CSV HEADER;

COPY students TO '/tmp/students.csv' WITH CSV HEADER;

This command exports the data from the `students` table to a CSV file named `students.csv` with a header row.

4. Transfer the CSV file to your laptop: the next thing is copy this file to your laptop/local computer. we will use the `scp` (Secure Copy) command to transfer the CSV file from your server to your laptop.

Replace `<username>` with your server username and `<server_ip>` with your server’s IP address. Replace `<remote_file_path>` with the path to the CSV file on your server, and `<local_destination>` with the path to your local desktop folder:

scp <username>@<server_ip>:<remote_file_path> <local_destination>

For example, if your server’s IP address is 192.168.1.100, and the CSV file is in the `/tmp` directory on the server, and you want to save it on your laptop’s desktop, you can use a command like this:

scp username@192.168.1.100:/tmp/students.csv ~/Desktop/

Replace `username` with your server username.

You will be prompted to enter your server password. Once the transfer is complete, you should find the `students.csv` file on your laptop’s desktop.

Now, you have successfully exported the data from the PostgreSQL table to a CSV file and transferred it to your laptop’s desktop.

If you liked this article please share it with your network and don’t forget to follow me for more great articles and tutorials.

You can also Buy me a coffee.