Thread: vacuumdb maintenance-db??
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, thepostgres
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.
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, thepostgres
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.
https://www.postgresql.org/docs/13/app-vacuumdb.htmlPresumably this means that I can create a tiny database with one table that lists a set of databases. Or maybe not.
--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, thepostgres
database will be used, or if that does not exist,template1
will be used.
Where is this documented?
> 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 systemcatalogs can be read.
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 systemcatalogs can be read. But you connect to the database(s) you want to vacuum, right? -- Angular momentum makes the world go 'round.
On Wed, Nov 9, 2022 at 1:29 PM Ron <ronljohnsonjr@gmail.com> wrote:
https://www.postgresql.org/docs/13/app-vacuumdb.htmlPresumably this means that I can create a tiny database with one table that lists a set of databases. Or maybe not.
--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, thepostgres
database will be used, or if that does not exist,template1
will be used.
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.
But this raises a different question: when does the postgres database not exist?
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.
"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
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.
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.
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@gmail.com
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.