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.

First I recommend that you install the sshpass package, it will help you to send your password in the command. Using an admin account just use:

apt-get install sshpass

First we connect to the postgres account

sudo -i -u postgres

First, let’s try to connect with the ssh command to see if you can speak with the distant server.

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

In this case the distant server seems a bit old: the connection is impossible as the recent openssh version deprecated DSA keys by default. Hopefully we can force it to use ssh-dss. First let’s test it

ssh “Username with spaces”@sftp.myhost.io -P 51112 -oHostKeyAlgorithms=+ssh-dss

Next we’ll create a file in your .ssh folder if it doesn’t already exist. The aim is to preconfigure the ssh connection your sftp will use. It is specially useful if you have a username with a space in it, a non standard port, or a specific set of keys for example. Just copy, adapt and paste the following command:

cat << EOF > ~/.ssh/config  
 Host myhost  
 HostName sftp.myhost.io  
 User “Username with spaces”  
 Port 51112  
 HostKeyAlgorithms=+ssh-dss  
EOF

Here is the best part: we are creating a small bash file that will dump the information in your CSV file and upload it to the SFTP server.

Create a new file, my_export.sh

nano ~/my\_export.sh

Here is the content of this file:

$#!/bin/bash

export 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.csv

echo “put /tmp/StandardisedName\_$now.csv /” | sshpass -p “SFTP password” sftp -p myhost

find /tmp/StandardisedName\_\* -mtime +7 -exec rm {} \\;

What this file does?

First we define the postgres password as we do not want to enter it every time
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.

Now we just need to create a cron entry to make it run automatically. In this example, the script will execute itself every 60 minutes:

crontab -e

Here is what you need to put in the crontab file. Please adapt the path if needed.

\*/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

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

Comments

More from this blog

Strong Cross-Site Request Forgery Prevention (CSRF) for PHP and jQuery

Geoconcept Web - How to select an object on the map using its GCKEY, JSON and PHP

Creating in MySQL dynamic views with dynamic unions that adds automatically newly created tables by Ignition SCADA or others…

ETH — Cheat Sheet for MacOS Ethereum development

Be ready to CPU mine on Digibyte with RandomX and your Rock64 ARM with XMRig

From the source server

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

Command Palette

Comments

More from this blog

From the source server

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