pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuumdb maintenance-db??
14+ messages / 7 participants
[nested] [flat]
* vacuumdb maintenance-db??
@ 2022-11-09 20:29 Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: Ron @ 2022-11-09 20:29 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
https://www.postgresql.org/docs/13/app-vacuumdb.html
|
--maintenance-db=/|dbname|/|
Specifies the name of the database to connect to *to discover which
databases should be vacuumed*, when|-a|/|--all|is used. If not
specified, the|postgres|database will be used, or if that does not
exist,|template1|will be used.
/*Presumably*/ this means that I can create a tiny database with one table
that lists a set of databases. Or maybe not.
Where is this documented?
Thanks
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: vacuumdb maintenance-db??
@ 2022-11-09 20:38 Paul Smith <paul@pscs.co.uk>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Paul Smith @ 2022-11-09 20:38 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
I think it's just because a connection has to be to a database, any
database. Then, it'll do a "select * from pg_database" (or similar)
I expect this option is if the "postgres" database isn't accessible for
some reason
Paul
On 9 November 2022 20:30:14 Ron <ronljohnsonjr@gmail.com> wrote:
>
> https://www.postgresql.org/docs/13/app-vacuumdb.html
>
>
> --maintenance-db=dbname Specifies the name of the database to connect to to
> discover which databases should be vacuumed, when -a/--all is used. If not
> specified, the postgres database will be used, or if that does not exist,
> template1 will be used.
> Presumably this means that I can create a tiny database with one table that
> lists a set of databases. Or maybe not.
>
> Where is this documented?
>
> Thanks
>
>
> --
> Angular momentum makes the world go 'round.
--
Paul Smith Computer Services
Tel: 01484 855800
Vat No: GB 685 6987 53
Sign up for news & updates at http://www.pscs.co.uk/go/subscribe
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: vacuumdb maintenance-db??
@ 2022-11-09 20:39 David G. Johnston <david.g.johnston@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 2 replies; 14+ messages in thread
From: David G. Johnston @ 2022-11-09 20:39 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Wed, Nov 9, 2022 at 1:29 PM Ron <ronljohnsonjr@gmail.com> wrote:
>
> https://www.postgresql.org/docs/13/app-vacuumdb.html
>
>
> --maintenance-db=*dbname*
>
> Specifies the name of the database to connect to *to discover which
> databases should be vacuumed*, when -a/--all is used. If not specified,
> the postgres database will be used, or if that does not exist, template1 will
> be used.
> *Presumably* this means that I can create a tiny database with one table
> that lists a set of databases. Or maybe not.
>
> Where is this documented?
>
>
It means that in order to query any data from a cluster you must already be
connected to a database. Any database. Though to actually vacuum a
database you must be connected specifically to it.
I have no clue what you are getting at with your presumption and what
specifically you are looking for documentation on. The few places that
deal with "whole cluster" type situations, such as here, document the need,
like you've quoted here, to connect to a database first before getting said
cluster-level information.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: vacuumdb maintenance-db??
@ 2022-11-09 20:43 Scott Ribe <scott_ribe@elevated-dev.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Scott Ribe @ 2022-11-09 20:43 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Nov 9, 2022, at 1:39 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> Presumably this means that I can create a tiny database with one table that lists a set of databases. Or maybe not.
No need to create such a database. It just means that the connection has to be made to some database, at which point system catalogs can be read.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: vacuumdb maintenance-db??
@ 2022-11-09 20:51 Ron <ronljohnsonjr@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 14+ messages in thread
From: Ron @ 2022-11-09 20:51 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
On 11/9/22 14:43, Scott Ribe wrote:
>> On Nov 9, 2022, at 1:39 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>>
>> Presumably this means that I can create a tiny database with one table that lists a set of databases. Or maybe not.
> No need to create such a database. It just means that the connection has to be made to some database, at which point system catalogs can be read.
But you connect to the database(s) you want to vacuum, right?
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: vacuumdb maintenance-db??
@ 2022-11-09 20:54 Ron <ronljohnsonjr@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 2 replies; 14+ messages in thread
From: Ron @ 2022-11-09 20:54 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 11/9/22 14:39, David G. Johnston wrote:
> On Wed, Nov 9, 2022 at 1:29 PM Ron <ronljohnsonjr@gmail.com> wrote:
>
>
> https://www.postgresql.org/docs/13/app-vacuumdb.html
>
> |
> --maintenance-db=/|dbname|/|
>
> Specifies the name of the database to connect to *to discover
> which databases should be vacuumed*, when|-a|/|--all|is used. If
> not specified, the|postgres|database will be used, or if that does
> not exist,|template1|will be used.
>
> /*Presumably*/ this means that I can create a tiny database with one
> table that lists a set of databases. Or maybe not.
>
> Where is this documented?
>
>
> It means that in order to query any data from a cluster you must already
> be connected to a database. Any database. Though to actually vacuum a
> database you must be connected specifically to it.
>
> I have no clue what you are getting at with your presumption and what
> specifically you are looking for documentation on. The few places that
> deal with "whole cluster" type situations, such as here, document the
> need, like you've quoted here, to connect to a database first before
> getting said cluster-level information.
I think I misread the docs, mentally adding the word "not" to "when -a/--all
is used".
But this raises a different question: when does the postgres database not exist?
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: vacuumdb maintenance-db??
@ 2022-11-09 21:17 David G. Johnston <david.g.johnston@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 2 replies; 14+ messages in thread
From: David G. Johnston @ 2022-11-09 21:17 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Wed, Nov 9, 2022 at 1:54 PM Ron <ronljohnsonjr@gmail.com> wrote:
>
> But this raises a different question: when does the postgres database not
> exist?
>
>
You are giving it too much specialness. Both ALTER DATABASE ... RENAME and
DROP DATABASE can be used on it.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: vacuumdb maintenance-db??
@ 2022-11-09 21:36 Ron <ronljohnsonjr@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Ron @ 2022-11-09 21:36 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 11/9/22 15:17, David G. Johnston wrote:
> On Wed, Nov 9, 2022 at 1:54 PM Ron <ronljohnsonjr@gmail.com> wrote:
>
>
> But this raises a different question: when does the postgres database
> not exist?
>
>
> You are giving it too much specialness. Both ALTER DATABASE ... RENAME
> and DROP DATABASE can be used on it.
Ah. It would have never occurred to me to do that (though I'm sure there
are things I do which others scratch their heads over).
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: vacuumdb maintenance-db??
@ 2022-11-09 21:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Tom Lane @ 2022-11-09 21:42 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, Nov 9, 2022 at 1:54 PM Ron <ronljohnsonjr@gmail.com> wrote:
>> But this raises a different question: when does the postgres database not
>> exist?
> You are giving it too much specialness. Both ALTER DATABASE ... RENAME and
> DROP DATABASE can be used on it.
The template databases aren't all that special either. There is a defense
against dropping them accidentally:
regression=# drop database template1;
ERROR: cannot drop a template database
but you can override that if you really mean it:
regression=# alter database template1 with is_template false;
ALTER DATABASE
regression=# drop database template1;
DROP DATABASE
This might seem pretty scary, but it's intentional, per the comments
in IsPinnedObject:
* Databases are never pinned. It might seem that it'd be prudent to pin
* at least template0; but we do this intentionally so that template0 and
* template1 can be rebuilt from each other, thus letting them serve as
* mutual backups (as long as you've not modified template1, anyway).
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: vacuumdb maintenance-db??
@ 2022-11-09 22:12 Rui DeSousa <rui@crazybean.net>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Rui DeSousa @ 2022-11-09 22:12 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> On Nov 9, 2022, at 3:54 PM, Ron <ronljohnsonjr@gmail.com> wrote:
>
> But this raises a different question: when does the postgres database not exist?
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.=
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: vacuumdb maintenance-db??
@ 2022-11-09 22:25 David G. Johnston <david.g.johnston@gmail.com>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 1 reply; 14+ messages in thread
From: David G. Johnston @ 2022-11-09 22:25 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: vacuumdb maintenance-db??
@ 2022-11-09 22:51 Rui DeSousa <rui@crazybean.net>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: Rui DeSousa @ 2022-11-09 22:51 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
> 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 <mailto: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’ve seen create a new database and leave the default postgres database empty — 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 “sudo -U postgres psql” works as expected. =
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: vacuumdb maintenance-db??
@ 2022-11-09 22:59 Wells Oliver <wells.oliver@gmail.com>
parent: Rui DeSousa <rui@crazybean.net>
1 sibling, 0 replies; 14+ messages in thread
From: Wells Oliver @ 2022-11-09 22:59 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
you guys dropping the postgres database just like that are giving me the
heebie jeebies
On Wed, Nov 9, 2022 at 2:51 PM Rui DeSousa <rui@crazybean.net> wrote:
>
>
> 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’ve seen create a new database and leave the
> default postgres database empty — 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 “sudo -U postgres psql”
> works as expected.
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: vacuumdb maintenance-db??
@ 2022-11-09 23:11 David G. Johnston <david.g.johnston@gmail.com>
parent: Rui DeSousa <rui@crazybean.net>
1 sibling, 0 replies; 14+ messages in thread
From: David G. Johnston @ 2022-11-09 23:11 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
On Wed, Nov 9, 2022 at 3:51 PM Rui DeSousa <rui@crazybean.net> wrote:
>
>
> 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?
>
Vacuum...
Create Database
Changing user passwords
>
> Also, most environments that I’ve seen create a new database and leave the
> default postgres database empty — 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?
>
But a fair point that I wasn't considering.
> Setup the PGDATABASE environment variable; and “sudo -U postgres psql”
> works as expected.
>
Arguably a form of documentation but I get the point and mostly agree with
it.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2022-11-09 23:11 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-09 20:29 vacuumdb maintenance-db?? Ron <ronljohnsonjr@gmail.com>
2022-11-09 20:38 ` Paul Smith <paul@pscs.co.uk>
2022-11-09 20:39 ` David G. Johnston <david.g.johnston@gmail.com>
2022-11-09 20:43 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-11-09 20:51 ` Ron <ronljohnsonjr@gmail.com>
2022-11-09 20:54 ` Ron <ronljohnsonjr@gmail.com>
2022-11-09 21:17 ` David G. Johnston <david.g.johnston@gmail.com>
2022-11-09 21:36 ` Ron <ronljohnsonjr@gmail.com>
2022-11-09 21:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-09 22:12 ` Rui DeSousa <rui@crazybean.net>
2022-11-09 22:25 ` David G. Johnston <david.g.johnston@gmail.com>
2022-11-09 22:51 ` Rui DeSousa <rui@crazybean.net>
2022-11-09 22:59 ` Wells Oliver <wells.oliver@gmail.com>
2022-11-09 23:11 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox