PostgreSQL seems doesn't flush dirty buffers related to unlogged tables in the database during alter database set tablespace ...;
Test case:
mboguk=# create database test tablespace tmp; CREATE DATABASE mboguk=# \c test You are now connected to database "test" as user "mboguk". test=# create unlogged table test (id integer); CREATE TABLE test=# insert into test select * from generate_series(1,10000000); INSERT 0 10000000 test=# \c postgres You are now connected to database "postgres" as user "mboguk". postgres=# alter database test set tablespace pg_default; ALTER DATABASE postgres=# checkpoint; ERROR: checkpoint request failed HINT: Consult recent messages in the server log for details.
In PostgreSQL logs:
2014-06-16 23:16:41 EDT ERROR: could not open file "pg_tblspc/16558/PG_9.3_201306121/16559/16560": No such file or directory 2014-06-16 23:16:41 EDT CONTEXT: writing block 27059 of relation pg_tblspc/16558/PG_9.3_201306121/16559/16560 2014-06-16 23:16:41 EDT WARNING: could not write block 27059 of pg_tblspc/16558/PG_9.3_201306121/16559/16560 2014-06-16 23:16:41 EDT DETAIL: Multiple failures --- write error might be permanent.
Thanks for the report. I can reproduce this on the current HEAD as well albeit not with the exact same steps. For me, it happens during the shutdown checkpoint.
LOG: shutting down
FATAL: could not open file "pg_tblspc/24576/PG_9.5_201406121/40971/40972": No such file or directory
CONTEXT: writing block 0 of relation pg_tblspc/24576/PG_9.5_201406121/40971/40972
LOG: checkpointer process (PID 2070) was terminated by signal 6: Aborted
It's clearly a bug. During a normal or a forced CHECKPOINT, we don't write buffers of UNLOGGED tables, even if they are dirty. ALTER DATABASE SET TABLESPACE relies on the checkpoint mechnism to ensure that all dirty buffers are written to the disk before proceeding with moving the files to the new tablespace. Leaving behind those dirty buffers with old tablespace and old relfilenode causes problems later when we try to sync those dirty buffers to the disk. AFAICS it can happen during the SHUTDOWN checkpoint or the End-of-Recovery checkpoint, at least in the HEAD.
ISTM that the right fix is to write *all* dirty pages during a FORCE checkpoint since system relies on FORCE checkpoints to handle such alterations. Attached patch fixes this for the HEAD. But this needs to be fixed all the way to 9.1 when unlogged tables were first introduced.
