Importing a remote database archive
Over at College Confidential, we have a lot of data that I'd like to analyze. I can use the Discourse Data Explorer to query the bulk of our data, but there's another data set that's currently sitting on an Amazon EC2 instance as a PostgreSQL data dump. So I'd like to populate the PostgreSQL database on my laptop with that data.
My first thought was to just copy the archive and restore it with psql. But then I counted the cost and noticed the dump would take nearly all of my disk space. Presumably I'd need some more space for PostgreSQL to store that data too. So it looked like I'd need to clear out some unused files first.
After some searching around, I discovered SSHFS which mounts a remote directory to behave like a local directory over SSH. The instructions for macOS are fairly straightforward:
- Download and install macFUSE.
- Download and install SSHFS.
- Restart your machine.
- Create an empty directory. (
- Mount the remote directory via SSHFS.
The last step is the only tricky bit because it depends on how you access the remote machine. If you use an SSH key (you should), you'll need to tell the command where it may be found.
sshfs firstname.lastname@example.org:/path/on/remote /path/to/remote \ -o IdentityFile=~/.ssh/key_for_server
If that works, you be able to see the remote files using
ls /path/to/mount. If you see the data archive, you can start importing
psql database_name < /path/to/remote/dump.sql
Setting up the database (prerequisite I should have mentioned before)
If you are like me, you probably didn't think to create a database first:
Or, horror of horrors, you might not have PostgreSQL running yet. If you have a Mac, you'll need to use these commands:
brew install postgresql@14
Next start the server with:
brew services start postgresql@14
When the restore fails
Unfortunately, this SSH file system setup is a bit fragile. If you lose your network connection, you'll get a message something like:
ERROR: COPY from stdin failed: aborted because of read failure CONTEXT: COPY user_profiles, line 479387 could not read from input file: Device not configured
CONTEXT line is the key. It tells you what table was being
copied and the line it was working on when the restore failed. For
better or worse, the COPY command is atomic. So when the command
failed, none of the data was committed to the database. It doesn't
really matter what line number it ended on. Only the table it was in
the middle of copying matters.
You can re-run the command, if you like, but there is no reason to
expect it will make progress. Fortunately, it's possible to trim the
archive to just data that hasn't been restored. One way to do that is
sed, if you know the line of the command that failed. I use
less to search for the
COPY command of the table that failed:
Most likely the first result won't be what you are looking for. You
can search forward with
n until you find the right table. Or you can
add the table name to the search command. Then I use
= to tell me
the line number. Finally, I copy from that line on into another file:
sed -n '1248450,$p' dump.sql > shorter-dump.sql
This is best done on the remote machine and not via SSHFS on the local
machine so that sed doesn't have to wait for the data to be pull over
SSH. Once the
sed command has finished, you can run the import again
from your local machine:
psql database_name < /path/to/remote/shorter-dump.sql
In any case, there's a good chance you'll need to remount the directory:
diskutil umount /path/to/remote
If that doesn't work, there's a good chance you'll need to restart your machine.
This is not the best way to move data around (see the problems caused by a dropped connection), but it helped me get the job done without using too much disk space.