Обсуждение: vacuumdb maintenance-db??
https://www.postgresql.org/docs/13/app-vacuumdb.html
--maintenance-db=dbnameSpecifies the name of the database to connect to to discover which databases should be vacuumed, when
-a/--allis used. If not specified, thepostgresdatabase will be used, or if that does not exist,template1will 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.
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=dbnameSpecifies the name of the database to connect to to discover which databases should be vacuumed, when
-a/--allis used. If not specified, thepostgresdatabase will be used, or if that does not exist,template1will 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
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=dbnameSpecifies the name of the database to connect to to discover which databases should be vacuumed, when
-a/--allis used. If not specified, thepostgresdatabase will be used, or if that does not exist,template1will 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.
David J.
> 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 11/9/22 14:39, David G. Johnston wrote:
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?
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=dbnameSpecifies the name of the database to connect to to discover which databases should be vacuumed, when
-a/--allis used. If not specified, thepostgresdatabase will be used, or if that does not exist,template1will 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.
Angular momentum makes the world go 'round.
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.
On 11/9/22 15:17, David G. Johnston wrote:
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).
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.
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 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.
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
wells.oliver@gmail.com
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.