In postgreSQL, I usually run this command to backup and compress (since my country have really low bandwidth) from server to local:

mkdir -p tmp/backup
ssh sshuser@dbserver -p 22 "cd /tmp; pg_dump -U dbuser -Fc -C dbname | xz - -c" \
 | pv -r -b > tmp/backup/db_backup_`date +%Y-%m-%d_%H%M%S`.sql.xz

and to restore:

fname=`ls -w 1 tmp/backup/*sql.xz | tail -n 1`
echo $fname

echo "select 'drop table \"' || tablename || '\" cascade;' from pg_tables WHERE schemaname = 'public';" |
psql -U dbuser |
 tail -n +3 |
 head -n 2 |
 psql -U dbuser

# sudo -u postgres dropdb dbname
# sudo -u postgres createdb --owner dbuser dbname
xzcat $fname | pg_restore  --clean --if-exists --no-acl --no-owner -U dbuser -d dbname

How to do similar thing in Clickhouse (backup, compress on the fly, compress to a file)?


look to https://github.com/AlexAkulov/clickhouse-backup it have SFTP support and compression_format your config should contains sftp: section in config.yaml

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.