LINUX — How to export a CSV from PostgreSQL and upload it to a SFTP server

Fancy to know how to export your newly exported CSV file from PostgreSQL and upload it to a SFTP server? Here is a small step by step guide.

From the source server

Connect with through an SSH connection to the source server with a user that is able to connect to the PostgreSQL server.

apt-get install sshpass
sudo -i -u postgres
ssh “Username with spaces”@sftp.myhost.io -P 51112
Impossible to connect to the distant server as the proposed key differs from what is allowed in openssh
ssh “Username with spaces”@sftp.myhost.io -P 51112 -oHostKeyAlgorithms=+ssh-dss
cat << EOF > ~/.ssh/config
Host myhost
HostName sftp.myhost.io
User “Username with spaces”
Port 51112
HostKeyAlgorithms=+ssh-dss
EOF
nano ~/my_export.sh
$#!/bin/bashexport PGPASSWORD=”Your postgres password comes here”database=”Your database name comes here”now=$(date +”%Y%m%d%H%M%S”)psql -d $database -c “COPY (SELECT * FROM my_view) to ‘/tmp/export_temp.csv’ WITH WITH (FORMAT CSV, DELIMITER ‘;’, HEADER)”mv /tmp/export_temp.csv /tmp/StandardisedName_$now.csvecho “put /tmp/StandardisedName_$now.csv /” | sshpass -p “SFTP password” sftp -p myhostfind /tmp/StandardisedName_* -mtime +7 -exec rm {} \;
  • then the database name
  • the psql function lets you send a query against the database and return the result directly in a CSV file. In this example, I chose to export the file in the CSV format, with ; as a delimiters, plus the headers are added in the first row.
  • next, we just rename the file with a standard name
  • the next command helps you to upload the file directly to the distant server using SFTP. The previously sshpass package we installed before lets you send the SFTP password directly in the command. The -p option in the sftp command will preserve files original creation date and time
  • The find command let you delete the files that are older than 7 days. This is an example if you choose to keep few version of the uploaded files for backup purpose.
crontab -e
*/60 * * * * sh /var/lib/postgresql/my_export.sh

Next story: Running a DigiByte Full Node on a Pine64 Rock64 — v2/v3

https://link.medium.com/Dnwyk2QEI1

IT Guru • Defender against the Digital Dark Arts🛡 • Blockchain Advocate 🔑️. https://xn--l9hygo898lbsb.y.at

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store