Re: Create database from template very slow

On Wed, Dec 4, 2019 at 5:51 PM David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Tue, Dec 3, 2019 at 9:27 PM Mark Phillips <mark(dot)phillips(at)mophilly(dot)com> wrote:
>> This is likely not a bug, so please point me in the right direction if that is the case.
>
> The -general list is you best choice if you know its not a bug but don't know where else to send the email.
>
>> In our application, a copy of the local database is made at launch using...
>> CREATE DATABASE myCopy FROM TEMPLATE currentDB;
>>
>> On linux and MacOS this operation completed in 5 seconds or so. On Windows (10 at present) it takes over a minute or more.
>>
>> I would like to understand why this would be, and what might be done to speed it up on Windows.
>>
>
> Less capable hardware on the Windows box? Use better hardware? This isn't my area of expertise but I'm doubtful that any significant fault/difference exists within the PostgreSQL software. Is the data involved identical?

CREATE DATABASE (createdb()) does a checkpoint, copies all the files
with individual fsync, does another checkpoint. It'd be interesting
to narrow down what's taking time. How long does manual CHECKPOINT
take, how long does a recursive copy with the Windows command line
take (xcopy?), how long does CREATE DATABASE take if you run with
fsync = off (do this on a test database you don't care about)? If
that doesn't reveal where the time is spent, I'd probably start
investigating by using whatever Windows thing is like truss/strace
(NtTrace seems to be a thing?) or adding a bunch of elog(LOG, "about
to copy files ...") type calls into createdb(), if I had a compiler
handy.