Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ostuy-0006rN-JL for pgsql-admin@arkaria.postgresql.org; Wed, 09 Nov 2022 22:51:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ostuw-0004fD-4y for pgsql-admin@arkaria.postgresql.org; Wed, 09 Nov 2022 22:51:34 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ostuv-0004f1-QY for pgsql-admin@lists.postgresql.org; Wed, 09 Nov 2022 22:51:33 +0000 Received: from impout002aa.msg.chrl.nc.charter.net ([47.43.20.26] helo=impout002.msg.chrl.nc.charter.net) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ostuq-0002cx-UM for pgsql-admin@lists.postgresql.org; Wed, 09 Nov 2022 22:51:32 +0000 Received: from miniblack.crazybean.net ([71.11.29.254]) by cmsmtp with ESMTP id stunomdz4iuOhstuooRZuI; Wed, 09 Nov 2022 22:51:26 +0000 Authentication-Results: crazybean.net; none X-Authority-Analysis: v=2.4 cv=CNY54DnD c=1 sm=1 tr=0 ts=636c2eee a=GkUsAQdcAzAk0MT19QAIRQ==:117 a=GkUsAQdcAzAk0MT19QAIRQ==:17 a=xqWC_Br6kY4A:10 a=9xFQ1JgjjksA:10 a=pGLkceISAAAA:8 a=oIfrFNJ-AAAA:8 a=NmirjbTtxiFFKdkmJuIA:9 a=QEXdDO2ut3YA:10 a=ZO35riTNRlpboHtMmrQA:9 a=2fjvL_D9TfozYfzo:21 a=_W_S_7VecoQA:10 a=lqcHg5cX4UMA:10 a=Cg2NcamS17xU0rR7el6q:22 Received: from smtpclient.apple ( [2001:470:e117:3:bc:f928:4492:2ca4]) by miniblack.crazybean.net (OpenSMTPD) with ESMTPSA id efffb078 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Wed, 9 Nov 2022 17:51:25 -0500 (EST) From: Rui DeSousa Message-Id: <62E3B896-85A3-45D3-8C46-9DA7683F66AE@crazybean.net> Content-Type: multipart/alternative; boundary="Apple-Mail=_194DEF66-7941-4BBA-AC62-E41187DB3FF0" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.1\)) Subject: Re: vacuumdb maintenance-db?? Date: Wed, 9 Nov 2022 17:51:24 -0500 In-Reply-To: Cc: Ron , pgsql-admin@lists.postgresql.org To: "David G. Johnston" References: <36bb2141-52e0-e75a-ea4a-5f2236b76c26@gmail.com> <92175f50-42f4-bb90-83cd-02b3407c0b56@gmail.com>

X-Mailer: Apple Mail (2.3696.120.41.1.1) X-CMAE-Envelope: MS4xfDqxAQ1pi3lrQVtjVRt2heDr94Z06yuObU8AhYNVqyHpul7d6Jts6sURV9ZthudrDlNsKzOkIPiJJ/p+TyAL1jptNS0pnLBGLMkZPttRkVoub0WUO0B9 rdM4mNRuAy6UVGd3vs6qAjfP6UH7j3fI4JW5YAx0ZB9HhmW5l6rj40H7q5Y50LEiaeXGOzf31YOjUDxLvdERfWlAuFP78p+jm+85smgQOmpEKprsV00OBcAy CgQDcNWBf4nzFBDAjRF1ur03+yU9eZR/yYhRzi5BfCFf6YoEpTT2KjoLbb/Mx30Q List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_194DEF66-7941-4BBA-AC62-E41187DB3FF0 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Nov 9, 2022, at 5:25 PM, David G. Johnston = wrote: >=20 > On Wed, Nov 9, 2022 at 3:12 PM Rui DeSousa > wrote: >=20 > I always drop the postgres database; no need for it; and I also drop = the public schema too. I usually use a database name that corresponds = to the given project. >=20 > I don't see the point of going to that effort - I'd rather just make = the defaults remain available and ensure they are only accessible to the = bootstrap superuser via the cluster owner. That way you don't have to = document your special environment for the next DBA that assumes "sudo -U = postgres psql" is just going to work (and have a schema sitting there = waiting for them to quickly do whatever semi-permanent stuff they are = doing). >=20 > I would not want my production databases used for "maintenance = purposes". And using template1 as the maintenance database seems like a = poor choice as well; too easy to make a mistake that will show up next = time you perform CREATE DATABASE. >=20 > David J. >=20 What maintenance would you do on an empty database?=20 Also, most environments that I=E2=80=99ve seen create a new database and = leave the default postgres database empty =E2=80=94 then complain when = it alerts due to wraparound thresholds. Why do I need a support ticket = only to login to an unused/empty database to vacuum it?=20 Setup the PGDATABASE environment variable; and =E2=80=9Csudo -U postgres = psql=E2=80=9D works as expected. =20= --Apple-Mail=_194DEF66-7941-4BBA-AC62-E41187DB3FF0 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On Nov 9, 2022, at 5:25 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Wed, Nov = 9, 2022 at 3:12 PM Rui DeSousa <rui@crazybean.net> wrote:

I always drop the = postgres database; no need for it; and I also drop the public schema = too. I usually use a database name that corresponds to the given = project.

I don't see the point = of going to that effort - I'd rather just make the defaults remain = available and ensure they are only accessible to the bootstrap superuser = via the cluster owner. That way you don't have to document your = special environment for the next DBA that assumes "sudo -U postgres = psql" is just going to work (and have a schema sitting there waiting for = them to quickly do whatever semi-permanent stuff they are = doing).

I would not want my = production databases used for "maintenance purposes". And using = template1 as the maintenance database seems like a poor choice as well; = too easy to make a mistake that will show up next time you perform = CREATE DATABASE.

David J.

What maintenance = would you do on an empty database?

Also, most environments that I=E2=80=99ve= seen create a new database and leave the default postgres database = empty =E2=80=94 then complain when it alerts due to wraparound = thresholds. Why do I need a support ticket only to login to an = unused/empty database to vacuum it?

Setup the PGDATABASE environment = variable; and =E2=80=9Csudo -U postgres psql=E2=80=9D works as expected. =

= --Apple-Mail=_194DEF66-7941-4BBA-AC62-E41187DB3FF0--