CREATE DATABASE ... TEMPLATE ... vs checksums

From: Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: CREATE DATABASE ... TEMPLATE ... vs checksums
Date: 2019-06-03 10:03:31
Message-ID: 20190603100331.GB6162@hermes.hilbert.loc
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

The application I am working on is doing database upgrades by
cloning the previous release into a new database by means of

CREATE DATABASE new_one TEMPLATE old_one ...;

(health care, extremely conservative, contingency: can switch
back to still existing old database with old client at any
time without delay).

For good measure, appropriate REINDEX / ANALYZE / VALIDATE
CONSTRAINTS are applied judiciously. Now I wondered how to
include a checksum verification step.

Before the advent of pg_(verify_)checksums the canonical way
to test checksums online (if enabled) was to do a dump of the
objects desired to be checked, if I am informed correctly.
The rationale being that for dumping an object it will need
to be read, and consequently CRCs are being checked.

So I was wondering whether creating a database from a
template also does that. I assume it does not because likely
that's a file level copy operation rather than a semantic
data read, correct ?

If so (that is: not), should there be a way of saying

CREATE DATABASE new_one TEMPLATE old_one ... VERIFY_CRC;

Likely, this proposal won't go very far since we can already
see an explicit (online) SQL command

select pg_verify_checksums(object);

on the horizon, right ?

So, for the time being, my only option would be to include a
pg_dump step of the template before cloning it ?

Thanks for insights shared,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

Browse pgsql-general by date

  From Date Subject
Next Message Morris de Oryx 2019-06-03 10:11:48 Re: Questions about btree_gin vs btree_gist for low cardinality columns
Previous Message Daulat Ram 2019-06-03 09:55:56 Cause: org.postgresql.util.PSQLException: ERROR: could not resize shared memory segment "/PostgreSQL.1946998112" to 8388608 bytes: No space left on device