[SOLVED] CREATE DATABASE WITH TEMPLATE Postgres 11 -> 15 Performance

CREATE DATABASE WITH TEMPLATE Postgres 11 -> 15 Performance

On our DEV workstations we have for test reasons to databases. Origin Database and Test Database, which is "destroyed" after testing.

In Postgres 11 we use the CREATE DATABASE with template command to reset the testing Database:

CREATE DATABASE test WITH OWNER = postgres TEMPLATE = origin ENCODING = 'UTF8' CONNECTION LIMIT = -1

This takes max. 2 minutes to restore the complete data. Restoring the schemas by backup takes 1 hour.

Since migration to Postgres 15 the CREATE DATABASE command takes 10 minutes. (same amount of data, same computer)

Is there a known reason for the difference duration? Somebody has a trick to speed up?

Expectation is that the command takes 2 minutes in PG 15 like in PG 11

Solution

Postgres 15 has introduced a new default copy strategy called WAL_LOG which may be faster for small databases, but is slower for large ones. You can use the old method called FILE_COPY like this:

create database newdatabase template originaldatabase strategy FILE_COPY;

See https://www.postgresql.org/docs/current/sql-createdatabase.html#CREATE-DATABASE-STRATEGY