How to export PostgreSQL Data to CSV Files Using Shell Script

Hi Everyone,

I was using Ubuntu 20.04 LTS since its release, i.e. more than 4 years. So, I recently upgraded to the latest Ubuntu 24.04 LTS. During this process, I formatted my hard drive and performed a fresh installation. Before doing so, I made sure to back up all the PostgreSQL schemas on my laptop.

To automate the backup, I wrote a shell script that exports the PostgreSQL data into CSV files. While my primary motivation was to secure my data during the upgrade, this script can also be useful for other purposes such as data analysis, backups, or migrations.

Let’s see how to create that backup / export script. Or you can download the script from the GitHub.

Prerequisites

Create the .pgpass file in the home folder

touch ~/.pgpass

Add Your Database Credentials

Add your DB credentials in below format

your_host:your_port:your_database_name:your_username:your_password

Set the permissions on the .pgpass file

chmod 600 ~/.pgpass

The Script

Create a file export_schema_data.sh and put the below code in it.

#!/bin/bash

set -e  # Exit immediately if a command returns a non-zero status

# Database connection parameters
DB_HOST=""
DB_PORT=""
DB_NAME=""
SCHEMA_NAME=""

# Get a list of all tables in the schema
TABLES=$(psql -h $DB_HOST -p $DB_PORT -d $DB_NAME -t -c "SELECT table_name FROM information_schema.tables WHERE table_schema='$SCHEMA_NAME' AND table_type='BASE TABLE';")

# Export data from each table to a separate CSV file
for TABLE in $TABLES; do
    TABLE=$(echo $TABLE | xargs)  # Trim any leading/trailing whitespace
    echo "Exporting $TABLE"
    psql -h $DB_HOST -p $DB_PORT -d $DB_NAME -c "\COPY $SCHEMA_NAME.$TABLE TO STDOUT WITH CSV HEADER;" > "$TABLE.csv"
    echo "Exported $TABLE to $TABLE.csv"
done

echo "Export completed"

Usage

Make the Script Executable

chmod +x export_schema_data.sh

Run the script

./export_schema_data.sh

 

That’s it guys.

Hope you like it.

Critics / feedback are welcome.

Have a great day ahead!