So I'm looking at the patch for ALTER DATABASE SET TABLESPACE, and
wondering about what happens if there's a system crash midway through.
The answer doesn't look too good: if the deletion pass has started,
your database is hosed.
I think we can fix this along the following lines:
1. Copy all files to new directory.2. Checkpoint (this is to prevent needing to replay XLOG_DBASE_CREATE, for the
samereasons mentioned in createdb()).3. Update the pg_database tuple.4. Commit transaction, start a new one.5. Remove
olddirectory and files.
If the system crashes before the commit in step 4, the ALTER SET didn't
happen, and the only bad consequence is some orphaned files copied to
the new directory. If the system crashes after the commit, the ALTER
SET is committed, and the only bad consequence is some orphaned files
left behind in the old directory.
That is, that's true as long as the filesystem copy in fact pushed
everything to disk. copydir() does an fsync() on each file it copies,
so I think we have done as much as we can to protect the data being
copied, but I wonder if anyone feels it's too dangerous?
Note that this is significantly more dangerous than CREATE DATABASE
(where presumably you have no great investment yet in the new DB)
or DROP DATABASE (where presumably you don't want the data anyway).
ALTER DATABASE SET TABLESPACE is pushing around what might be your
only copy of valuable data.
Comments?
regards, tom lane