Re: Create database from template very slow

Thank you both.

I will look into joining the general list.

We have observed the long execution time on several windows machines, including those of end users. Also, due to the nature of our deployment, the postgres version is the same for all users. All user databases are small, typically being less than 10MB.

The suggestion for timing the stages of CREATE DATABASE is good. I am not familiar with tracing postgres functionality, so I will dig into this and see how far I get.

Cheers!

> On Dec 3, 2019, at 9:16 PM, Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:
>
> On Wed, Dec 4, 2019 at 5:51 PM David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com <mailto: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.