One issue which has interested me for a while is moving databases from one server to another. My traditional approach involves taking down the website, dumping the database, copying the dump file, loading it up, and then changing the DNS to point to the new server.
With the goal of minimizing downtime, I’ve been considering this approach:
- dump the database
- scp the dump to the new server
- turn off updates on the old server
- dump again
- rsync to the new server
- load the dump onto the new server
- change the DNS
The goal is to reduce the time between turning off updates and changing the DNS. I have previous timings for dump and scp times. Let’s now look at rsync times. We will also trying dumping without compression.
[dan@nyi:~/transfer] $ time pg_dump freshports.org > freshports.sql real 26m12.041s user 0m17.144s sys 0m19.165s [dan@nyi:~/transfer] $
Now, when I do another rsync, after the dump, it takes just 10 minutes to rsync:
$ time /usr/local/bin/rsync -e "ssh -i /home/dan/.ssh/supernews-maildir-rsync-key" --recursive --delete -avz --stats --progress dan@nyi:transfer ~/ receiving file list ... 2 files to consider transfer/freshports.sql 2832187798 100% 4.50MB/s 0:10:00 (xfer#1, to-check=0/2) Number of files: 2 Number of files transferred: 1 Total file size: 2832187798 bytes Total transferred file size: 2832187798 bytes Literal data: 95236822 bytes Matched data: 2736950976 bytes File list size: 97 File list generation time: 0.011 seconds File list transfer time: 0.000 seconds Total bytes sent: 425578 Total bytes received: 21865883 sent 425578 bytes received 21865883 bytes 36099.53 bytes/sec total size is 2832187798 speedup is 127.05 real 10m16.993s user 0m25.544s sys 0m11.944s [dan@supernews:~/bin] $
Now let’s try a gzip approach.
$ time pg_dump freshports.org | gzip > freshports.sql.gz zreverse-i-search)`gzip': time pg_dump freshports.org | gzip > freshports.sql.gz real 23m45.288s user 5m42.822s sys 0m15.884s [dan@nyi:~/transfer] $
Initial reports indicate dumping to gzip is faster than no gzip.
Now trying an rsync of a gzip file… It’s not looking promising.
transfer/freshports.sql.gz 552743046 100% 222.41kB/s 0:40:26 (xfer#1, to-check=0/2) Number of files: 2 Number of files transferred: 1 Total file size: 552743046 bytes Total transferred file size: 552743046 bytes Literal data: 552014422 bytes Matched data: 728624 bytes File list size: 92 File list generation time: 0.001 seconds File list transfer time: 0.000 seconds Total bytes sent: 164646 Total bytes received: 552200103 sent 164646 bytes received 552200103 bytes 227076.98 bytes/sec total size is 552743046 speedup is 1.00 real 40m31.289s user 0m16.055s sys 0m7.961s
Well, that takes forty minute. it seems better to rsync raw text.
