[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] smartest way to exchange a sqlite3 database with another empty
From:       Luca Ferrari <fluca1978 () infinito ! it>
Date:       2016-10-04 10:15:01
Message-ID: CAKoxK+5dPwFz4KXTnKFPTRjLebmxLkvfeJ_xkRqm00Ly4AyW_Q () mail ! gmail ! com
[Download RAW message or body]

On Mon, Oct 3, 2016 at 11:51 AM, Richard Hipp <drh@sqlite.org> wrote:
> Safe way:  In a separate process, use the backup API
> (https://www.sqlite.org/backup.html) to copy the content of the main
> DB over to a separate DB, then "DELETE FROM log;" on the main DB.
> This will work without any cooperation on the part of the application.
> But it does involving a lot of I/O.

This sounds good to built a maintanance program to keep just in case,
I will go for this solution at the moment.


>
> Alternative:  Modify the application so that it automatically detects
> when the database is getting to large (perhaps using PRAGMA page_count
> - https://www.sqlite.org/pragma.html#pragma_page_count) and then (1)
> closes the database connection, (2) renames the database file to a
> backup, and (3) reopens the main DB and reinitializes the schema.

When working with PostgreSQL (not meant to flame) I would solve with
partitioning: create a table which refers to a time period and move
all records of that period to such table. In the cas eof sqlite I have
to move the period table to another database, but that be simpler than
dumping the whole database and archive it (I mean it could keep the
original database online).

By the way, my database is growing around 1 MB per day, so nothing
scaring (at least now). In other words, I've a chance to study the
problem and get a suitable solution.

Thanks all,
Luca
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[prev in list] [next in list] [prev in thread] [next in thread]