CODEWITHSUNDEEP
mysqlpostgresqloptimizationimportexport
wsdookadr
wsdookadr

Reputation: 2662

Database dump tuned for fast imports

I'd like to be able to export a database in such a way that imports will be really fast. For example, if I just dump the database, and import it somewhere else, if there are any indexes, they have to be re-built. Would there be a way to export it so it's closer to the internal format the database server uses in order to speed up the import ?

What other ways would there be to create a data dump in such a way that imports would be fast ?

Are there any pros/cons to just moving data from /var/lib/postgresql/9.4/main or /var/lib/mysql/ around, perhaps any pitfalls ? Is this considered a good practice ?

Upvotes: 0

Views: 59

Answers (1)

Jasen
Jasen

Reputation: 12412

As you suggest for postgres you can shut down the server process and take a file copy of the base directory, I think the ssame works with mysql.

A downside is that this requires the destination server to be binary compatible with the origin server.

Another downside is that it requires down-time on the originating server, but I guess you could take an SQL snapshot and restore that and then dump the files of the restored snapshot.

Also file size will be larger, so if the data is coming off slow media the SQL snapshot restore may actually be faster.

Upvotes: 1

Related Questions