How to migrate data from SQLite to PostgreSQL

sqlite is an excellent embedded database, which is often used in scenarios such as mobile terminals, internal or temporary databases, embedded devices, and the Internet of Things. It occupies very low resources, and may only need a few hundred k of memory. It is a truly open source unlimited database, cross-platform, supports Linux, Mac, Android, iOS and Windows, etc., and is mainly used in embedded development .

At present, SQLite is used by many users. Most mobile terminals are equipped with SQLite databases, including popular applications such as WeChat and Meituan that we are familiar with. SQLite is used. An application can also use SQlite to quickly build a prototype in the initial version stage. As the amount of continuous running data gradually increases, the business becomes more and more complex. Some of the shortcomings of SQLite are also exposed, including not suitable for large data storage. To meet the high concurrency and sharing of multiple threads, lack of user management and security functions, it is necessary to migrate the application to a C/S structured database management system (MySQL, PostgreSQL, Oracle, SQL Server), and also need to transfer historical data Synchronize.

SqliteToPostgres is a powerful database conversion software that supports the conversion of Sqlite database to Postgres type. The software provides a wizard-style configuration process, supports importing, exporting, or custom fields for database conversion operations, which is very convenient Data migration tool.

Ready to work

talk is cheap, show me the code , and then we will experience it personally. Our preparations are as follows:

  • A sqlite database file, we use the golang program to generate
package main
import (
 "database/sql"
 "fmt"
 _ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", "./foo.db")
checkErr(err)
    sql_table := `
CREATE TABLE IF NOT EXISTS "userinfo" (
 "uid" INTEGER PRIMARY KEY AUTOINCREMENT,
 "username" VARCHAR(64) NULL,
 "departname" VARCHAR(64) NULL,
 "created" TIMESTAMP default (datetime('now', 'localtime'))
);`
 _, err = db.Exec(sql_table)//执行数据表
checkErr(err)
//插入数据
stmt, err := db.Prepare("INSERT INTO userinfo(username, departname, created) values(?,?,?)")
checkErr(err)
res, err := stmt.Exec("astaxie", "研发部门", "2012-12-09")
checkErr(err)
id, err := res.LastInsertId()
checkErr(err)
fmt.Println(id)
//更新数据
stmt, err = db.Prepare("update userinfo set username=? where uid=?")
checkErr(err)

res, err = stmt.Exec("astaxieupdate", id)
checkErr(err)

affect, err := res.RowsAffected()
checkErr(err)

fmt.Println(affect)

//查询数据
rows, err := db.Query("SELECT * FROM userinfo")
checkErr(err)

for rows.Next() {
    var uid int
    var username string
    var department string
    var created string
    err = rows.Scan(&uid, &username, &department, &created)
    checkErr(err)
    fmt.Println(uid)
    fmt.Println(username)
    fmt.Println(department)
    fmt.Println(created)
}
db.Close()
}

func checkErr(err error) {
if err != nil {
panic(err)
}
}
go mod init tttt
go mod tidy
rm -rf ~/.cache/go-build/* 
yum install binutils
go build main.go
  • A postgresql database service, I am using a free postgresql database service here: MemFireDB , which runs on the public cloud, does not need to be deployed by itself, and can be accessed by one-click registration.
  • SqliteToPostgres program, download address https://www.crsky.com/soft/217259.html

Started

The operation of SqliteToPostgres is very simple. After the installation is completed and started, you only need to follow the wizard step by step.

The first step is to select the sqlite file to be migrated, and then click "ok", or in order to verify whether the file is damaged, you can also click "Connect" to test, showing that Connected means the file is intact

image.png

The second step is to log in to MemFireDB to obtain the service address, user name, and database name, and click "Connect" to test. If "Connected" is displayed, the service connection is normal, and we click "ok" to proceed to the next step.

image.png

The third step is to select the data table to be imported. Here we select "Import All Tables" and "Select All" to import all the tables, and click "Next" for the rest of the steps

image.png

Finally , after the import is complete, log in to MemFireDB to view the data we imported

image.png

Thanks

SqliteToPostgres is a powerful and easy-to-use software. The fool-like operation allows us to quickly import the data in SQLite into PostgreSQL, which has a multiplier effect in data backup and application migration.