1

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)?

Kokizzu
  • 1,403
  • 6
  • 18
  • 35

1 Answers1

1

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

Slach
  • 146
  • 3