Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ram2b-00BOcU-M7 for pgsql-general@arkaria.postgresql.org; Fri, 16 Feb 2024 00:25:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ram2Z-00GGkC-MX for pgsql-general@arkaria.postgresql.org; Fri, 16 Feb 2024 00:25:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ram2Z-00GGk3-A6 for pgsql-general@lists.postgresql.org; Fri, 16 Feb 2024 00:25:19 +0000 Received: from smtprelay0248.b.hostedemail.com ([64.98.42.248] helo=relay.b.hostedemail.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ram2U-007Plz-SV for pgsql-general@lists.postgresql.org; Fri, 16 Feb 2024 00:25:18 +0000 Received: from omf06.b.hostedemail.com (omf06 [10.200.6.69]) by unirelay07.b.hostedemail.com (Postfix) with ESMTP id CFDB618045A for ; Fri, 16 Feb 2024 00:25:11 +0000 (UTC) Received: from [HIDDEN] (Authenticated sender: beansboy@cruzio.com) by omf06.b.hostedemail.com (Postfix) with ESMTPA id AB996140003 for ; Fri, 16 Feb 2024 00:25:10 +0000 (UTC) MIME-Version: 1.0 Date: Fri, 16 Feb 2024 00:25:10 +0000 From: Jay Stanley To: pgsql-general@lists.postgresql.org Subject: Re: PostgreSQL DB in prod, test, debug In-Reply-To: <20240215235152.va7wohn5ftcxi5a4@hjp.at> References: <7304F877-D043-4395-867E-014F48EB71ED@yesql.se> <20240215235152.va7wohn5ftcxi5a4@hjp.at> Message-ID: <29aada4e3b72a39db3f50bc299ce9880@cruzio.com> X-Sender: beansboy@cruzio.com Content-Type: multipart/alternative; boundary="=_fceb3b7c6e4c7cd17d248eeea7bd820f" X-Rspamd-Server: rspamout01 X-Rspamd-Queue-Id: AB996140003 X-Stat-Signature: twc1egb9bkpwnryr9uegxpfqcigfxoqs X-Spam-Status: No, score=-4.90 X-Session-Marker: 6265616E73626F79406372757A696F2E636F6D X-Session-ID: U2FsdGVkX19P+UGWTlS+nKG3vJxqovAYUlOy+ImC5/U= X-HE-Tag: 1708043110-821911 X-HE-Meta: U2FsdGVkX18Pzv+mAIgOQg1co/tq+hWWBeIviIHM8GUSpLjA9GJIZ1gl3NEHH+FxricWI0v/ney1gSJnc7EhC+TiWuqcjmltStK52MxoqtA77Ee+fXR+L8C6WRkW+ha/vrIsc+qM0GPwHdVwJl18XmXzqmOXhBhtRkG6kbgXHWH3Nrn9RHahiQaQ3gL73Ju83F6Dvs9D8Qp6qFYtV7yJii7ZlXzsV3v5rCiWVogN7CUX3gA+tkIe28wxCwc0Bv+gummbKS8N1Q6JjU29H0Jcnmq5LzT7yNWJ5a5z1WDwwOaIumnorDb6CWVnV0FkoZ1v0tfZSA5SZhp+HDOkklqzLsOQuTCJ4sotVykqEIWJOs9WloB7+GTKU1s7ZbMREorvgZCxm2QvWBBhNSOw3WFoXPvoXHtxXsw2jmK0IazFAJECqwBkrDdd0RsTR0O1EiafhaUrMY0WWiD6jbrQRNxwhYM7pp6p7T4MVOotlakrejlPEMeLT3ya+DPw1vejMq2q List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_fceb3b7c6e4c7cd17d248eeea7bd820f Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed On 2024-02-15 23:51, Peter J. Holzer wrote: > On 2024-02-14 11:25:48 +0100, Daniel Gustafsson wrote: On 14 Feb 2024, > at 10:59, Simon Connah wrote: > This is probably a stupid question so I apologies in advance. > There is no such thing. > > What I think is the best way to do this is to do a pg_dump of the > database (using the --schema-only flag) and then load it into a test > only database that gets created at the start of the unit tests and > destroyed at the end. The automated tests will insert, update, > delete and select data to test if it all still works. > If the source of truth for your schema is the database, then sure. If > the > source of truth is a .sql file in your source code repository then you > should > use that. I sort of do both for one of my projects: I originally created the SQL script by running pg_dump on a manually constructed test database (I do have code to create the empty schema, but I had to insert the test data manually). That script went into the git repo. The test files all contain a fixture which drops and recreates the test database using that sql file. When I add a new test case I try to make do with the existing test data. When I need additional data for a new test case, I create a new pristine test database using the sql file, add the new data, and then create a new sql file using sql_dump which is then committed with the test cases. Same for migrations: If I need to migrate the schema, I run the migration on the test database, then dump and commit it. This project is small enough (86 tests in 10 files) that all test cases can use the same test data. However, I could easily use different test data for different tests. hp I had a very similar issue a few years ago; our large-ish codebase needs a lot of suites tests (about 100) to run daily against specific database conditions -- each test involved specific data in around 50 tables. At first we had 'source of truth' sql scripts checked into git, and with jenkins would run each to create a database for the test, load it with the exact data needed for that suite of tests, run the tests and record them, then drop the database. This worked fine for a few tests but became unmanageable as more tests were added and the data volume increased. Instead, I created a 'master' script that creates one _database template_ for each suite of tests by creating a blank database and running those same build scripts in git, then disconnecting from that target database and switching it to become a template. This was re-done very infrequently -- only when we wanted to use a different testing database, or needed to refresh test content, drop tests or add more tests. That's right - we have about 100 templates; I've found copying a template is FAR faster than reloading it from SQL. When each test is run, it creates a new database from the template appropriate for that suite of tests. When the test suite completes, the new database is dropped. This sped up our tests by at least 2 orders of magnitude, and it was in a way more reliable because each test gets a binary copy of the test database including exactly how vacuumed each table is, exact state of the indexes, every block in the same place, etc. Results were definitely more consistent in how long they ran. Note that while a database is in template mode, it cannot be changed (unless a DBA switches it back to non-template-mode). This has been in production for a few years, in multiple projects, and hasn't really hit any issues; the one cavaet is that you can't switch a database to 'template mode' if anything's logged into the target. According to the doc, I don't see an upper limit for the number of template databases but there must be one - I haven't tested it with 1000's of templates. See doc for 'create database DBNAME template TEMPLATENAME' as well as 'alter database DBNAME set datistemplate = true|false'. - jay stanley - https://cyc.com; The Next Generation of Enterprise AI --=_fceb3b7c6e4c7cd17d248eeea7bd820f Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=UTF-8

On 2024-02-15 23:51, Peter J. Holzer wrote:

= On 2024-02-14 11:25:48 +0100, Daniel Gustafsson wrote:
On 14 Feb 2024, at 10:59, Simon Connah <simon.n.connah@protonmail.com>= wrote:

This is probably a stupid question so I apologies in a= dvance.

There is no such thing.

What I think is the best way to do this is to do a pg_= dump of the
database (using the --schema-only flag) and then load it i= nto a test
only database that gets created at the start of the unit te= sts and
destroyed at the end. The automated tests will insert, update,=
delete and select data to test if it all still works.

If the source of truth for your schema is the database, then sure. &n= bsp;If the
source of truth is a .sql file in your source code reposito= ry then you should
use that.

I sort of do both for one of my projects:

I originally cre= ated the SQL script by running pg_dump on a manually
constructed test = database (I do have code to create the empty schema,
but I had to inse= rt the test data manually). That script went into the
git repo.
<= br />The test files all contain a fixture which drops and recreates the tes= t
database using that sql file.

When I add a new test case = I try to make do with the existing test data.

When I need additi= onal data for a new test case, I create a new pristine
test database u= sing the sql file, add the new data, and then create a
new sql file us= ing sql_dump which is then committed with the test cases.

Same f= or migrations: If I need to migrate the schema, I run the
migration on= the test database, then dump and commit it.

This project is sma= ll enough (86 tests in 10 files) that all test cases
can use the same = test data. However, I could easily use different test
data for differe= nt tests.

        hp

I had a very similar issue a few years ago; our large-ish co= debase needs a lot of suites tests (about 100) to run daily against specifi= c database conditions -- each test involved specific data in around 50 tabl= es.  At first we had 'source of truth' sql scripts checked into git, a= nd with jenkins would run each to create a database for the test, load it w= ith the exact data needed for that suite of tests, run the tests and record= them, then drop the database.

This worked fine for a few tests but became unmanageable as = more tests were added and the data volume increased. Instead, I created a '= master' script that creates one database template for each suite o= f tests by creating a blank database and running those same build scripts i= n git, then disconnecting from that target database and switching it to bec= ome a template.  This was re-done very infrequently -- only when we wa= nted to use a different testing database, or needed to refresh test content= , drop tests or add more tests.  That's right - we have about 100 temp= lates; I've found copying a template is FAR faster than reloading it from S= QL.

When each test is run, it creates a new database from the te= mplate appropriate for that suite of tests.  When the test suite compl= etes, the new database is dropped.  This sped up our tests by at least= 2 orders of magnitude, and it was in a way more reliable because each test= gets a binary copy of the test database including exactly how vacuumed eac= h table is, exact state of the indexes, every block in the same place, etc.=    Results were definitely more consistent in how long they ran.&= nbsp; Note that while a database is in template mode, it cannot be changed = (unless a DBA switches it back to non-template-mode).

This has been in production for a few years, in multiple pro= jects, and hasn't really hit any issues; the one cavaet is that you can't s= witch a database to 'template mode' if anything's logged into the target.&n= bsp; According to the doc, I don't see an upper limit for the number of tem= plate databases but there must be one - I haven't tested it with 1000's of = templates.

See doc for 'create database DBNAME template TEMPLATENAME' a= s well as 'alter database DBNAME set datistemplate =3D true|false'.

- jay stanley
- https://cyc.com; The Next Generation of Enterp= rise AI

 

--=_fceb3b7c6e4c7cd17d248eeea7bd820f--