Обсуждение: Parameter Settings - Instance-Database-Specific
Hi,
I have PostgreSQL Instance that has several DBs.
I assume there are instance specific and database specific parameter settings?
How do I check this? Using the source column of pg_setting?
Is this true for both Aurora PostgreSQL / RDS and non-AWS PostgreSQL?
=> \d pg_settings
View "pg_catalog.pg_settings"
Column | Type | Collation | Nullable | Default
-----------------+---------+-----------+----------+---------
name | text | | |
setting | text | | |
unit | text | | |
category | text | | |
short_desc | text | | |
extra_desc | text | | |
context | text | | |
vartype | text | | |
source | text | | |
min_val | text | | |
max_val | text | | |
enumvals | text[] | | |
boot_val | text | | |
reset_val | text | | |
sourcefile | text | | |
sourceline | integer | | |
pending_restart | boolean | | |
Column | Type | Collation | Nullable | Default
-----------------+---------+-----------+----------+---------
name | text | | |
setting | text | | |
unit | text | | |
category | text | | |
short_desc | text | | |
extra_desc | text | | |
context | text | | |
vartype | text | | |
source | text | | |
min_val | text | | |
max_val | text | | |
enumvals | text[] | | |
boot_val | text | | |
reset_val | text | | |
sourcefile | text | | |
sourceline | integer | | |
pending_restart | boolean | | |
select distinct source from pg_settings ;
source
--------------------
client
configuration file
default
database
override
command line
(6 rows)
source
--------------------
client
configuration file
default
database
override
command line
(6 rows)
On Sun, Feb 2, 2025 at 7:53 AM Edwin UY <edwin.uy@gmail.com> wrote:
Hi,I have PostgreSQL Instance that has several DBs.I assume there are instance specific and database specific parameter settings?How do I check this? Using the source column of pg_setting?Is this true for both Aurora PostgreSQL / RDS and non-AWS PostgreSQL?=> \d pg_settingsView "pg_catalog.pg_settings"
Column | Type | Collation | Nullable | Default
-----------------+---------+-----------+----------+---------
name | text | | |
setting | text | | |
[snip]
select distinct source from pg_settings ;
source
--------------------
client
configuration file
default
database
override
command line
(6 rows)
What do you get when you run:
SELECT * FROM pg_setting WHERE source = ''database';
More important, though, is the fact that pg_settings does not have a "datname" column. Thus, it's values are all global to the instance.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On Sun, Feb 2, 2025 at 7:53 AM Edwin UY <edwin.uy@gmail.com> wrote:
>> I have PostgreSQL Instance that has several DBs.
>> I assume there are instance specific and database specific parameter
>> settings?
>> How do I check this? Using the source column of pg_setting?
> What do you get when you run:
> SELECT * FROM pg_setting WHERE source = ''database';
> More important, though, is the fact that pg_settings does not have a
> "datname" column. Thus, it's values are all global to the instance.
More precisely, it shows you the values that prevail at this moment
in your own session. You can tell where those values came from
via the "source" column, but it won't tell you anything about
values that might've been locally overridden.
I think psql's "\drds" command might be what Edwin is looking for.
>> Is this true for both Aurora PostgreSQL / RDS and non-AWS PostgreSQL?
[ shrug... ] Most people on this list only know about community
Postgres. You'd have to ask AWS whether Aurora differs in this
area.
regards, tom lane