---
# System prepended metadata

title: How-to backup and restore database
tags: [ThingsBoard]

---

:::warning
1. You can use our backup scrips following there: [GitHub - thingsboard/ThingsboardSupport](https://github.com/thingsboard/ThingsboardSupport)
2. make backup manually
:::

# Backup Manually

## Quick steps to backup and restore Cassandra and PostgreSQL databases

:::info
* Make sure you have enough space to place a backup of the database.
* Check database size: 
`sudo -u postgres psql -c "SELECT pg_size_pretty(pg_database_size('thingsboard'));"`
* Check free space: `df -h /`
* If there is enough free space - make a backup.
:::

### Backup PostgreSQL
```
sudo -u postgres pg_dump -d thingsboard > YOU_BACKUP_FILENAME-$(date +%Y-%m-%d).sql
```
### Backup PostgreSQL+TimescaleDB:
```
sudo pg_dump -h YOU_HOSTNAME -U postgres -Fc -f thingsboard.bak thingsboard
Check backup file being created.
```

### Restore PostgreSQL
1. Stop thingsboard: sudo systemctl stop thingsboard.service
* Replace YOUR_HOSTNAME and YOUR_BACKUP_FILE and execute the following commands
	* PostgreSQL:
	```
	psql -U postgres -d postgres -h 127.0.0.1 -W
	drop database thingsboard; 
	create database thingsboard;
	\q
	psql -U postgres -d thingsboard -h YOUR_HOSTNAME < YOUR_BACKUP_FILE.sql.bak
	```
	* PostgreSQL+TimescaleDB:
	```
	psql -U postgres -d postgres -h 127.0.0.1 -W
	drop database thingsboard; 
	create database thingsboard; 
	CREATE EXTENSION timescaledb; 
	SELECT timescaledb_pre_restore();
	\q
	pg_restore -h $HOSTNAME -U postgres -Fc -d thingsboard $YOUR_BACKUP_FILE.sql.bak
	psql -U postgres -d postgres -h 127.0.0.1 -W
	SELECT timescaledb_post_restore();
	```
:::success
For more information on backup/restore for TimescaleDB, refer to the [TimescaleDocs](https://docs.timescale.com/timescaledb/latest/how-to-guides/backup-and-restore/pg-dump-and-restore/#restore-your-entire-database-from-backup)
:::

:::spoiler 尚未整理
From bash execute:

Backup Cassandra:
Flash memtables to sstables: nodetool flush

Archive Cassandra's directory. Replace  YOUR_BACKUP_DIRECTORY and execute the following commands:


CASSANDRA_PATH= YOUR_BACKUP_DIRECTORY
cqlsh 127.0.0.1 -e "DESCRIBE KEYSPACE thingsboard;" > ${CASSANDRA_PATH}thingsboard-describe.txt

TARFILE=${CASSANDRA_PATH}/${CUR_DATE}.cassandra.tar
sudo tar -cvf "$TARFILE" -P /var/lib/cassandra/data/thingsboard ${CASSANDRA_PATH}thingsboard-describe.txt
rm -rf ${CASSANDRA_PATH}thingsboard-describe.txt

echo "Backup file size: $(du -m "$TARFILE" | awk '{print $1}') Mb"
Restore Cassandra: 
Replace  YOUR_BACKUP_FILE and execute the following commands:


#Untar your backup to tb-restore folder
sudo mkdir tb-restore && cd tb-restore
sudo tar -xvf $YOUR_BACKUP_FILE

#Restore keyspace
DESCRIBE=$(sudo find . -name "thingsboard-describe.txt")
cqlsh 127.0.0.1 < $DESCRIBE

#Clear table id
CF=$(sudo find . -name "ts_kv_cf*")
PARTITIONS_CF=$(sudo find . -name "ts_kv_partitions_cf*")
sudo mv ${CF} ./ts_kv_cf
sudo mv ${PARTITIONS_CF} ./ts_kv_partitions_cf

#Restore table
sudo sstableloader --verbose --nodes 127.0.0.1 ./thingsboard/ts_kv_partitions_cf
sudo sstableloader --verbose --nodes 127.0.0.1 ./thingsboard/ts_kv_cf
If you have LATEST data you need to execute these commands too:


LATEST=$(sudo find . -name "ts_kv_latest*")
sudo mv ${LATEST} ./thingsboard/ts_kv_latest
sudo sstableloader --verbose --nodes 127.0.0.1 ./thingsboard/ts_kv_latest
:::