Como copiar um banco de dados SQLite entre computadores mais rapidamente

• Explicação de um método mais rápido para copiar bancos de dados SQLite entre computadores
• Os índices do banco de dados são a principal causa da lentidão na cópia
• O SQLite permite fazer um dump do banco de dados em um arquivo de texto usando o comando .dump
• O arquivo de texto é menor que o banco de dados original e, quando comprimido, fica ainda menor
• Esse método permite copiar bancos de dados grandes de forma mais rápida e confiável

---

Como copiar um banco de dados SQLite entre computadores mais rapidamente

• Explicação de como copiar um banco de dados SQLite armazenado em um servidor remoto para o computador local
• Em projetos iniciais, é possível copiar simplesmente usando o comando rsync
• À medida que o banco de dados cresce, a velocidade de cópia diminui e a confiabilidade piora

Criando um dump do banco de dados em um arquivo de texto

• O SQLite permite fazer um dump do banco de dados em um arquivo de texto usando o comando .dump
• Esse arquivo de texto é composto por instruções SQL e pode ser menor que o banco de dados original
• Os índices são reduzidos a uma única linha no arquivo de texto, o que pode economizar espaço de armazenamento

Economizando espaço com compressão

• O arquivo de texto fica ainda menor quando comprimido
• Por exemplo, se o banco de dados SQLite original tiver 3,4 GB, o arquivo de texto comprimido com gzip pode cair para 240 MB
• Baixar o arquivo de texto comprimido torna a cópia do banco de dados muito mais rápida

Novo comando ssh+rsync

• Gera um arquivo de texto comprimido com gzip no servidor, copia esse arquivo para o computador local e depois reconstrói o banco de dados
• Gerar o arquivo de texto comprimido no servidor: ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"
• Copiar o arquivo para o computador local: rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz
• Descompactar e reconstruir o banco de dados, depois apagar o arquivo local

O dump do banco de dados é uma fonte de cópia confiável

• Se houver atualizações durante a cópia do banco de dados, o rsync pode gerar um arquivo de banco de dados incorreto
• Criar um dump de texto resolve esse problema ao fornecer uma fonte estável para a cópia
• Esse método economiza tempo ao trabalhar com bancos de dados grandes e torna o download mais rápido e confiável

[GN⁺]

Comentários do Hacker News

• O SQLite oferece uma ferramenta oficial. Ela opera no nível de página, e a réplica envia o hash criptográfico de cada página para a origem, que então reenvia o conteúdo completo das páginas cujo hash não corresponde
• Copiar um arquivo de banco de dados em uso pode corrompê-lo. Para replicação segura, é possível usar o Litestream
• Uma forma de copiar um banco de dados entre computadores é enviar o círculo e ignorar o restante
  • O rsync incremental é mais rápido, mas não concordam com a afirmação de que enviar instruções SQL seja mais rápido do que enviar o banco de dados. É preciso executar as instruções SQL e realizar tarefas de otimização e VACUUM
  • Há cenários em que é necessário "reconstruir incrementalmente" um banco de dados a partir de arquivos CSV. Recriar o banco do zero é mais ideal, mas só executar inserções em lote em um banco de dados vazio em memória já leva 30 minutos
• O utilitário sqlite_rsync, lançado recentemente, usa uma versão do algoritmo do rsync otimizada para a estrutura interna dos bancos de dados SQLite. Ele compara com eficiência as páginas de dados internas e sincroniza apenas as páginas alteradas ou ausentes
• Salvar como arquivo de texto é ineficiente. Usa-se VACUUM INTO para salvar o banco de dados sqlite
  • O comando VACUUM é uma alternativa à API de backup, e o banco de dados de backup resultante fica com tamanho mínimo, reduzindo o I/O do sistema de arquivos
• Surpreende que não tenham usado o recurso de compressão oferecido pelo rsync. Comprimir com gzip antes da transferência pode ser mais rápido
• No DuckDB, é possível exportar para Parquet para reduzir o tamanho dos dados. A transferência e o carregamento são mais rápidos
• O SQLite oferece uma extensão de sessão para rastrear alterações em tabelas e gerar changesets/patchsets que permitem aplicar patches em versões anteriores de bancos de dados SQLite
• É possível otimizar usando a opção --rsyncable do gzip. Ela reduz um pouco a compressão, mas localiza as diferenças para que não afetem toda a saída comprimida
  • É possível pular a compressão da saída do dump e deixar o rsync calcular as diferenças entre o dump anterior não comprimido e o dump atual, e então fazer o rsync comprimir o changeset transmitido pela rede
• Há um relato de 2008 sobre a necessidade de enviar backups para várias máquinas usando Postgres. A rede ficou saturada, então foi usado o udpcast para enviar o backup a todos os destinos de uma só vez