# Backup & Restore: Postgres, Clickhouse, Kafka, & Redis ## Overview This is the generic solution for backup and restore. Depending on the backup strategy used, the tools might change. ## Backing up and restoring Postgres * The Ansible script will automatically configure pg\_basebackup, pgbackrest, wal-g and other recovery tools. For the sake of simplicity, we can use pg\_dump and pg\_restore. * The following command takes a backup. This will create a compressed tarball backup in the directory mentioned: {% hint style="info" %} pg\_dump -h 192.168.0.100 -U postgres -F c remote\_db1 > remote\_db1.tar {% endhint %} * This can be scheduled using a cron as shown below: {% hint style="info" %} 0 0 \* \* \* \ {% endhint %} * Pg\_basebackup is installed along with psql client {% hint style="info" %} sudo apt install postgresql-client {% endhint %} * You can restore from pg\_dump as follows: {% hint style="info" %} pg\_restore -h 192.168.0.100 -U postgres -F -C -d db1 < db1.tar {% endhint %} ## Backing up and restoring Clickhouse The plan is to use [clickhouse-backup](https://github.com/AlexAkulov/clickhouse-backup), which is open sourced under the liberal MIT license. This tool has the ability to create archived backups and upload them to NFS, S3, GCS, AZBlob, SFTP and other remote data repositories. * Download the latest release from * Untar the archive {% hint style="info" %} tar -zxvf clickhouse-backup.tar.gz {% endhint %} * Create a configuration file as follows, call it config.ini {% hint style="info" %} general:\ remote\_storage: none # REMOTE\_STORAGE, if \`none\` then \`upload\` and \`download\` command will fail\ max\_file\_size: 1073741824 # MAX\_FILE\_SIZE, 1G by default, useless when upload\_by\_part is true, use for split data parts files by archives \ disable\_progress\_bar: true # DISABLE\_PROGRESS\_BAR, show progress bar during upload and download, have sense only when \`upload\_concurrency\` and \`download\_concurrency\` equal 1\ backups\_to\_keep\_local: 0 # BACKUPS\_TO\_KEEP\_LOCAL, how much newest local backup should keep, 0 mean all created backups will keep on local disk\ \# you shall to run \`clickhouse-backup delete local \\` command to avoid useless disk space allocations\ backups\_to\_keep\_remote: 0 # BACKUPS\_TO\_KEEP\_REMOTE, how much newest backup should keep on remote storage, 0 mean all uploaded backups will keep on remote storage.\ \# if old backup is required for newer incremental backup, then it will don't delete. Be careful with long incremental backup sequences. log\_level: info # LOG\_LEVEL\ allow\_empty\_backups: false # ALLOW\_EMPTY\_BACKUPS\ download\_concurrency: 1 # DOWNLOAD\_CONCURRENCY, max 255\ upload\_concurrency: 1 # UPLOAD\_CONCURRENCY, max 255\ restore\_schema\_on\_cluster: "" # RESTORE\_SCHEMA\_ON\_CLUSTER, execute all schema related SQL queryes with \`ON CLUSTER\` clause as Distributed DDL, look to \`system.clusters\` table for proper cluster name\ upload\_by\_part: true # UPLOAD\_BY\_PART\ download\_by\_part: true # DOWNLOAD\_BY\_PART\ clickhouse:\ username: default # CLICKHOUSE\_USERNAME\ password: "" # CLICKHOUSE\_PASSWORD\ host: localhost # CLICKHOUSE\_HOST\ port: 9000 # CLICKHOUSE\_PORT, don't use 8123, clickhouse-backup doesn't support HTTP protocol\ disk\_mapping: {} # CLICKHOUSE\_DISK\_MAPPING, use it if your system.disks on restored servers not the same with system.disks on server where backup was created\ skip\_tables: # CLICKHOUSE\_SKIP\_TABLES\ \- system.\*\ \- INFORMATION\_SCHEMA.\*\ \- information\_schema.\*\ timeout: 5m # CLICKHOUSE\_TIMEOUT\ freeze\_by\_part: false # CLICKHOUSE\_FREEZE\_BY\_PART\ secure: false # CLICKHOUSE\_SECURE, use SSL encryption for connect skip\_verify: false # CLICKHOUSE\_SKIP\_VERIFY\ sync\_replicated\_tables: true # CLICKHOUSE\_SYNC\_REPLICATED\_TABLES\ log\_sql\_queries: true # CLICKHOUSE\_LOG\_SQL\_QUERIES, enable log clickhouse-backup SQL queries on \`system.query\_log\` table inside clickhouse-server\ debug: false # CLICKHOUSE\_DEBUG\ config\_dir: "/etc/clickhouse-server" # CLICKHOUSE\_CONFIG\_DIR\ restart\_command: "systemctl restart clickhouse-server" # CLICKHOUSE\_RESTART\_COMMAND, this command use when you try to restore with --rbac or --config options\ ignore\_not\_exists\_error\_during\_freeze: true # CLICKHOUSE\_IGNORE\_NOT\_EXISTS\_ERROR\_DURING\_FREEZE, allow avoiding backup failures when you often CREATE / DROP tables and databases during backup creation, clickhouse-backup will ignore \`code: 60\` and \`code: 81\` errors during execute \`ALTER TABLE ... FREEZE\`\ azblob:\ endpoint\_suffix: "core.windows.net" # AZBLOB\_ENDPOINT\_SUFFIX\ account\_name: "" # AZBLOB\_ACCOUNT\_NAME\ account\_key: "" # AZBLOB\_ACCOUNT\_KEY\ sas: "" # AZBLOB\_SAS\ use\_managed\_identity: false # AZBLOB\_USE\_MANAGED\_IDENTITY\ container: "" # AZBLOB\_CONTAINER\ path: "" # AZBLOB\_PATH\ compression\_level: 1 # AZBLOB\_COMPRESSION\_LEVEL\ compression\_format: tar # AZBLOB\_COMPRESSION\_FORMAT\ sse\_key: "" # AZBLOB\_SSE\_KEY\ buffer\_size: 0 # AZBLOB\_BUFFER\_SIZE, if less or eq 0 then calculated as max\_file\_size / 10000, between 2Mb and 4Mb\ max\_buffers: 3 # AZBLOB\_MAX\_BUFFERS\ s3:\ access\_key: "" # S3\_ACCESS\_KEY\ secret\_key: "" # S3\_SECRET\_KEY\ bucket: "" # S3\_BUCKET\ endpoint: "" # S3\_ENDPOINT\ region: us-east-1 # S3\_REGION\ acl: private # S3\_ACL\ assume\_role\_arn: "" # S3\_ASSUME\_ROLE\_ARN\ force\_path\_style: false # S3\_FORCE\_PATH\_STYLE\ path: "" # S3\_PATH\ disable\_ssl: false # S3\_DISABLE\_SSL\ compression\_level: 1 # S3\_COMPRESSION\_LEVEL\ compression\_format: tar # S3\_COMPRESSION\_FORMAT\ sse: "" # S3\_SSE, empty (default), AES256, or aws:kms\ disable\_cert\_verification: false . # S3\_DISABLE\_CERT\_VERIFICATION\ storage\_class: STANDARD # S3\_STORAGE\_CLASS\ concurrency: 1 # S3\_CONCURRENCY\ part\_size: 0 # S3\_PART\_SIZE, if less or eq 0 then calculated as max\_file\_size / 10000\ debug: false # S3\_DEBUG\ gcs:\ credentials\_file: "" # GCS\_CREDENTIALS\_FILE\ credentials\_json: "" # GCS\_CREDENTIALS\_JSON\ bucket: "" # GCS\_BUCKET\ path: "" # GCS\_PATH\ compression\_level: 1 # GCS\_COMPRESSION\_LEVEL\ compression\_format: tar # GCS\_COMPRESSION\_FORMAT\ debug: false # GCS\_DEBUG\ cos:\ url: "" # COS\_URL\ timeout: 2m # COS\_TIMEOUT\ secret\_id: "" # COS\_SECRET\_ID\ secret\_key: "" # COS\_SECRET\_KEY\ path: "" # COS\_PATH\ compression\_format: tar # COS\_COMPRESSION\_FORMAT\ compression\_level: 1 # COS\_COMPRESSION\_LEVEL\ ftp:\ address: "" # FTP\_ADDRESS\ timeout: 2m # FTP\_TIMEOUT\ username: "" # FTP\_USERNAME\ password: "" # FTP\_PASSWORD\ tls: false # FTP\_TLS\ path: "" # FTP\_PATH\ compression\_format: tar # FTP\_COMPRESSION\_FORMAT\ compression\_level: 1 # FTP\_COMPRESSION\_LEVEL\ debug: false # FTP\_DEBUG\ sftp:\ address: "" # SFTP\_ADDRESS\ username: "" # SFTP\_USERNAME\ password: "" # SFTP\_PASSWORD\ key: "" # SFTP\_KEY\ path: "" # SFTP\_PATH\ concurrency: 1 # SFTP\_CONCURRENCY \ compression\_format: tar # SFTP\_COMPRESSION\_FORMAT\ compression\_level: 1 # SFTP\_COMPRESSION\_LEVEL\ debug: false # SFTP\_DEBUG\ api:\ listen: "localhost:7171" # API\_LISTEN\ enable\_metrics: true # API\_ENABLE\_METRICS\ enable\_pprof: false # API\_ENABLE\_PPROF\ username: "" # API\_USERNAME, basic authorization for API endpoint\ password: "" # API\_PASSWORD\ secure: false # API\_SECURE, use TLS for listen API socket\ certificate\_file: "" # API\_CERTIFICATE\_FILE\ private\_key\_file: "" # API\_PRIVATE\_KEY\_FILE\ create\_integration\_tables: false # API\_CREATE\_INTEGRATION\_TABLES\ allow\_parallel: false # API\_ALLOW\_PARALLEL, could allocate much memory and spawn go-routines, don't enable it if you not sure {% endhint %} * Ensure configuration under clickhouse and general section of the configuration file. The rest are not mandatory. * If automated remote upload functionality is needed, the appropriate section needs to be filled in: sftp, ftp, s3, GCS, AZBlob, etc. * The following command can be run: {% hint style="info" %} sh \/bin/clickhouse-backup create -C \ {% endhint %} * The following is the list of possible commands which can be executed: {% hint style="info" %} COMMANDS:\ tables Print list of tables\ create Create new backup\ create\_remote Create and upload\ upload Upload backup to remote storage\ list Print list of backups\ download Download backup from remote storage\ restore Create schema and restore data from backup\ restore\_remote Download and restore\ delete Delete specific backup\ default-config Print default config\ print-config Print current config\ clean Remove data in 'shadow' folder from all \`path\` folders available from \`system.disks\`\ server Run API server\ help, h Shows a list of commands or help for one command {% endhint %} ## Backing up and restoring Kafka * Backup zookeeper state data \- Go to file {% hint style="info" %} kafka/config/zookeeper.properties {% endhint %} \- Copy location of dataDir property (typically, /tmp/zookeeper) \- Run the following command: {% hint style="info" %} tar -czf /home/kafka/zookeeper-backup.tar.gz /tmp/zookeeper/\* {% endhint %} * Backup Kafka topics and messages \- Go to the file kafka/config/server.properties \- Copy location of log.dirs (typically, /tmp/kafka-logs) \- Stop Kafka: {% hint style="info" %} sudo systemctl stop kafka {% endhint %} \- Login as kafka user: {% hint style="info" %} sudo -iu kafka {% endhint %} \- Run the following command: {% hint style="info" %} tar -czf /home/kafka/kafka-backup.tar.gz /tmp/kafka-logs/\* {% endhint %} * Restore zookeeper \- sudo systemctl stop kafka \- sudo systemctl stop zookeeper \- sudo -iu kafka \- rm -r /tmp/zookeeper/\* \- tar -C /tmp/zookeeper -xzf /home/kafka/zookeeper-backup.tar.gz \--strip-components 2 * Restore kafka \- rm -r /tmp/kafka-logs/\* \- tar -C /tmp/kafka-logs -xzf /home/kafka/kafka-backup.tar.gz --strip-components 2 \- sudo systemctl start kafka \- sudo systemctl start zookeeper * Verification of Restoration \- \~/kafka/bin/kafka-console-consumer.sh --bootstrap-server localhost:9092 --topic BackupTopic --from-beginning ## Backing up and restoring Redis Redis provides an in-built command to save a backup. * Install redis-cli using {% hint style="info" %} sudo apt install redis-cli {% endhint %} * The following command takes a backup of the redis-server: {% hint style="info" %} echo save | redis-cli -u redis\://\:\@\:\ >> /tmp/redis-backup.log {% endhint %} * This will save the backup as dump.rdb within: {% hint style="info" %} /var/lib/redis {% endhint %} Restoration can be done in the following way: * Locate the redis data directory, typically: {% hint style="info" %} /var/lib/redis {% endhint %} * Move the dump.rdb file into this folder * Start redis server * This will ensure that data is restored automatically [![Creative Commons License](https://i.creativecommons.org/l/by/4.0/80x15.png)](http://creativecommons.org/licenses/by/4.0/)*All content on this page by* [*eGov Foundation*](https://egov.org.in/) *is licensed under a* [*Creative Commons Attribution 4.0 International License*](http://creativecommons.org/licenses/by/4.0/)*.*