Re: The quickest way to migrate database within the same cluster

Your second idea, of copying the underlying disk files, probably won't work unless you're also copying all the relevant records from all of the system catalog tables (a non-trivial task, itself), and likely not even then. (For instance, if two tables in the different databases have the same 'relfilenode', you're going to overwrite that table in the second when you perform the copy, and because those tables probably have different structures, you'll then see "unexpected" behavior.)

You might instead try piping the output of a "COPY ($query) TO STDOUT" in a psql session against the source database into a "COPY table FROM STDIN" command in a psql session against the destination. That would filter on the source side, but would require you to have empty tables of the expected structure on the destination to receive the data.

rls