How to backup clickhouse over SSH?
1
vote
1
answer
650
views
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)?
Asked by Kokizzu
(1403 rep)
Jul 29, 2021, 11:48 AM
Last activity: Aug 30, 2021, 01:58 PM
Last activity: Aug 30, 2021, 01:58 PM