Re: Missing query plan for auto_explain.
Yes, we do see some plans logged by the auto_explain. We couldn't find a `auto_explain.log_min_duration_statements` setting but `log_min_duration_statement` as in https://www.postgresql.org/docs/current/runtime-config-logging.html is set to 100 ms. Unfortunately, due to the amount of traffic we have, we cannot lower the threshold for the auto_explain. `log_lock_waits` is turned on and the logs do not indicate any locks related to the tables in the query. On Fri, 2 Sept 2022 at 05:49, Julien Rouhaud wrote: > Hi, > > On Thu, Sep 01, 2022 at 08:20:13PM +0100, Matheus Martin wrote: > > We tried running the prepared statement six times as suggested but wasn't > > still able to recreate the original problem. > > > > Perhaps more concerning/relevant is that we have not found any > explanation > > to why the explain plan is not being logged by `auto_explain`. Could this > > be a bug? Shall we report it? > > Just to be sure, do you get at least some plans logged by auto_explain when > queries are executed by the JDBC application? > > Can you try to temporarily lower auto_explain.log_min_duration_statements > to > less than 50ms and see what auto_explain sees for the execution time (and > planning time). > > Another possibility would be some conflicting locks held. If the conflict > happens during the planning auto_explain still won't be triggered as it's > outside the executor. Also, have you enabled log_lock_waits? >
Re: Missing query plan for auto_explain.
On 2022-09-02 10:58:58 +0100, Matheus Martin wrote: > Yes, we do see some plans logged by the auto_explain. We couldn't find a > `auto_explain.log_min_duration_statements` setting This is weird as the documentation says: | Note that the default behavior is to do nothing, so you must set at | least auto_explain.log_min_duration if you want any results. What does show auto_explain.log_min_duration; return? > but `log_min_duration_statement` as in > https://www.postgresql.org/docs/current/ runtime-config-logging.html > is set to 100 ms. I don't think this affects auto_explain. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: How to make PostreSQL utilities honor home directories?
On 2022-09-01 21:10:44 -0700, Adrian Klaver wrote: > On 9/1/22 6:31 PM, Jeffrey Walton wrote: > > On Thu, Sep 1, 2022 at 8:51 PM Tom Lane wrote: > > > I concur with the other person asking why you want to sudo to postgres > > > at all, though. It's generally safest if the client side isn't running > > > as the same user as the server. > > > > The use case is an install of DefectDojo [2]. I _think_ they are > > taking advantage of the fact that as root, you don't need to > > authenticate because of postgresql's use of domain sockets. (Hat tip > > for that, by the way). The installer code will install packages, setup > > the database, install the DefectDojo programs, etc. > > You are not running as root(Postgres won't allow you do this anyway) you are > running as postgres system user and the authentication is handled by > pg_hba.conf. I'm also betting that if you look at pg_hba.conf it is set up > to do peer authentication and hence the need to be postgres system user. You > can obtain a similar result in pg_hba.conf with: > > TYPE DATABASEUSERADDRESS METHOD > local allpostgrestrust > > Then you could eliminate the whole sudo dance. OTOH it allows any user on the machine to connect as postgres, which may or may not be a problem. I like to use ident for local connections: # TYPE DATABASEUSERADDRESS METHOD # "local" is for Unix domain socket connections only local all all ident map=localusers and in pg_ident.conf: # MAPNAME SYSTEM-USERNAME PG-USERNAME localusers rootpostgres Then root can invoke `psql -U postgres ...`, but other users can't. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: How to make PostreSQL utilities honor home directories?
On 2022-09-01 20:49:56 -0400, Jeffrey Walton wrote:
> On Thu, Sep 1, 2022 at 8:23 PM Peter J. Holzer wrote:
> >
> > On 2022-09-01 18:16:14 -0400, Tom Lane wrote:
> > > Jeffrey Walton writes:
> > > > We are having a heck of a time getting PostreSQL utilities to honor
> > > > home directories. For example, when I execute this script:
> > >
> > > > sudo -H -u postgres PGPASSWORD=${password} \
> > > > psql -h "${hostname}" -U "${username}" -d "${database}" \
> > > > --command="..."
> > >
> > > > It produces failures:
> > >
> > > > could not change directory to "/home/jwalton/godojo": Permission
> > > > denied
> > I get the same behaviour. So it seems that psql changes to its basedir
> > and then can't change back again.
>
> I guess there is no way to avoid the problem.
Changing to /tmp (or some other directory accessible by posgres) before
running the script would avoid it.
As would (temporarily) changing the permissions of the diretor(y/ies).
Or not using sudo at all (see other messages).
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
RE: Unable to archive logs in standby server
Hi,
I am still facing issue with executing pg_stop_backup in standby manually.
Copying from previous email -
Archival hangs. Is this expected?
postgres=# select pg_start_backup('test', true, false);
pg_start_backup
-
1/F960
(1 row)
postgres=# select pg_stop_backup('f');
NOTICE: base backup done, waiting for required WAL segments to be archived
WARNING: still waiting for all required WAL segments to be archived (60
seconds elapsed)
HINT: Check that your archive_command is executing properly. You can safely
cancel this backup, but the database backup will not be usable without all the
WAL segments.
WARNING: still waiting for all required WAL segments to be archived (120
seconds elapsed)
HINT: Check that your archive_command is executing properly. You can safely
cancel this backup, but the database backup will not be usable without all the
WAL segments.
……..
Regards,
Meera
From: subin
Sent: Friday, September 2, 2022 12:36 AM
To: Meera Nair
Subject: Re: Unable to archive logs in standby server
External email. Inspect before opening.
Hope you had a good time.
On Tue, Aug 30, 2022 at 6:00 PM Meera Nair
mailto:mn...@commvault.com>> wrote:
Hi Guillaume/team,
I set archive_mode = always in master and standby.
Archival to standby WAL directory completed when
· standby server was restarted
· pg_stop_backup was executed in master
But archival hangs when pg_stop_backup is executed in standby.
Could someone help to get this working?
postgres=# select pg_start_backup('test', true, false);
pg_start_backup
-
1/F960
(1 row)
postgres=# select pg_stop_backup('f');
NOTICE: base backup done, waiting for required WAL segments to be archived
WARNING: still waiting for all required WAL segments to be archived (60
seconds elapsed)
HINT: Check that your archive_command is executing properly. You can safely
cancel this backup, but the database backup will not be usable without all the
WAL segments.
WARNING: still waiting for all required WAL segments to be archived (120
seconds elapsed)
HINT: Check that your archive_command is executing properly. You can safely
cancel this backup, but the database backup will not be usable without all the
WAL segments.
……..
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
---
t
(1 row)
postgres=# show wal_level;
wal_level
---
replica
(1 row)
postgres=# show archive_mode;
archive_mode
--
always
(1 row)
postgres=# select version();
version
-
PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623
(Red Hat 4.8.5-39), 64-bit
(1 row)
Regards,
Meera
From: Guillaume Lelarge mailto:guilla...@lelarge.info>>
Sent: Thursday, July 21, 2022 6:01 PM
To: Meera Nair mailto:mn...@commvault.com>>
Cc:
pgsql-general@lists.postgresql.org
Subject: Re: Unable to archive logs in standby server
External email. Inspect before opening.
Hi,
Le jeu. 21 juil. 2022 à 13:58, Meera Nair
mailto:mn...@commvault.com>> a écrit :
Hi team,
With non-exclusive backup method, trying backup from standby node.
But pg_stop_backup function returns “WAL archiving is not enabled…” and the
logs are not archived to WAL directory configured.
Please check if I am missing anything in configuring this properly,
Server was restarted after setting the archiving params in postgresql.conf
Below is from version 14:
postgres=# select pg_start_backup('label', false, false);
pg_start_backup
-
0/6D8
(1 row)
postgres=# select pg_stop_backup('false');
NOTICE: WAL archiving is not enabled; you must ensure that all required WAL
segments are copied through other means to complete the backup
pg_stop_backup
---
(0/60001C0,"START WAL LOCATION: 0/6D8 (file 00010006)+
CHECKPOINT LOCATION: 0/6000110 +
BACKUP METHOD: streamed +
BACKUP FROM: standby +
START TIME: 2022-07-21 12:42:11 IST +
LABEL: label +
START TIMELINE: 1+
","")
(1 row)
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
---
t
(1 row)
postgres=# show wal_level;
wal_level
---
replica
(1 row)
postgres=# show archive_mode;
archive_mode
--
on
(1 row)
You're doing backups from the standby, and to allow archiving on the backups,
archive_mode should be set to always.
--
Guillaume.
Re: Unable to archive logs in standby server
> On Sep 1, 2022, at 21:41, Meera Nair wrote:
> Archival hangs. Is this expected?
> postgres=# select pg_start_backup('test', true, false);
> pg_start_backup
> -
> 1/F960
> (1 row)
>
> postgres=# select pg_stop_backup('f');
> NOTICE: base backup done, waiting for required WAL segments to be archived
> WARNING: still waiting for all required WAL segments to be archived (60
> seconds elapsed)
> HINT: Check that your archive_command is executing properly. You can safely
> cancel this backup, but the database backup will not be usable without all
> the WAL segments.
> WARNING: still waiting for all required WAL segments to be archived (120
> seconds elapsed)
> HINT: Check that your archive_command is executing properly. You can safely
> cancel this backup, but the database backup will not be usable without all
> the WAL segments.
This generally means the command being run by archive_command is failing.
Check the PostgreSQL logs (if you are using CSV logs, check the *.log file
rather than the *.csv file).
Re: How to make PostreSQL utilities honor home directories?
On Fri, Sep 2, 2022 at 7:34 AM Peter J. Holzer wrote:
>
> On 2022-09-01 20:49:56 -0400, Jeffrey Walton wrote:
> > On Thu, Sep 1, 2022 at 8:23 PM Peter J. Holzer wrote:
> > >
> > > On 2022-09-01 18:16:14 -0400, Tom Lane wrote:
> > > > Jeffrey Walton writes:
> > > > > We are having a heck of a time getting PostreSQL utilities to honor
> > > > > home directories. For example, when I execute this script:
> > > >
> > > > > sudo -H -u postgres PGPASSWORD=${password} \
> > > > > psql -h "${hostname}" -U "${username}" -d "${database}" \
> > > > > --command="..."
> > > >
> > > > > It produces failures:
> > > >
> > > > > could not change directory to "/home/jwalton/godojo": Permission
> > > > > denied
>
> > > I get the same behaviour. So it seems that psql changes to its basedir
> > > and then can't change back again.
> >
> > I guess there is no way to avoid the problem.
>
> Changing to /tmp (or some other directory accessible by posgres) before
> running the script would avoid it.
> As would (temporarily) changing the permissions of the diretor(y/ies).
> Or not using sudo at all (see other messages).
Ok, thanks everyone.
RE: Unable to archive logs in standby server
Hi Christophe,
If pg_stop_backup is executed in master OR if standby server is restarted, I
can see the archiving completing fine.
Only issue is while manually executing the pg_stop_backup in standby
Regards,
Meera
-Original Message-
From: Christophe Pettus
Sent: Friday, September 2, 2022 7:43 PM
To: Meera Nair
Cc: pgsql-general@lists.postgresql.org; Punit Pranesh Koujalgi
Subject: Re: Unable to archive logs in standby server
External email. Inspect before opening.
> On Sep 1, 2022, at 21:41, Meera Nair wrote:
> Archival hangs. Is this expected?
> postgres=# select pg_start_backup('test', true, false);
> pg_start_backup
> -
> 1/F960
> (1 row)
>
> postgres=# select pg_stop_backup('f');
> NOTICE: base backup done, waiting for required WAL segments to be
> archived
> WARNING: still waiting for all required WAL segments to be archived
> (60 seconds elapsed)
> HINT: Check that your archive_command is executing properly. You can safely
> cancel this backup, but the database backup will not be usable without all
> the WAL segments.
> WARNING: still waiting for all required WAL segments to be archived
> (120 seconds elapsed)
> HINT: Check that your archive_command is executing properly. You can safely
> cancel this backup, but the database backup will not be usable without all
> the WAL segments.
This generally means the command being run by archive_command is failing.
Check the PostgreSQL logs (if you are using CSV logs, check the *.log file
rather than the *.csv file).
Determine if a user and database are available
Hi Everyone,
I have another beginner question. I am trying to use pg_isready to
determine if a database and user are present. The program seems to
always succeed, even when I delete the user or the database.
This baffles me from the man page. I guess this explains the behavior
I am seeing.
NOTES
It is not necessary to supply correct user name, password, or database
name values to obtain the server status; however, if incorrect values
are provided, the server will log a failed connection attempt.
A typical usage is shown below, where variables are parsed from a config file.
password=$(grep 'DD_DB_Rpass' dojoConfig.yml | awk '{ print $2 }')
hostname=$(grep 'DD_DB_Host' dojoConfig.yml | awk '{ print $2 }')
database=$(grep 'DD_DB_Name' dojoConfig.yml | awk '{ print $2 }')
username=$(grep 'DD_DB_Ruser' dojoConfig.yml | awk '{ print $2 }')
PGPASSWORD=${password} pg_isready \
-h "${hostname}" -U "${username}" -d "${database}"
Given the NOTES in the man page, how do we determine if a user and
database are present using the shell? Is there another utility we
should be using?
Thanks in advance,
Jeff
Re: Determine if a user and database are available
> On Sep 2, 2022, at 14:22, Jeffrey Walton wrote: > Given the NOTES in the man page, how do we determine if a user and > database are present using the shell? Is there another utility we > should be using? pg_isready literally only checks that the server can be reached over the connection path (network or sockets), not that any login credentials work. You can use psql do that, though: psql
Re: Determine if a user and database are available
On Friday, September 2, 2022, Jeffrey Walton wrote: > > Given the NOTES in the man page, how do we determine if a user and > database are present using the shell? Is there another utility we > should be using? > Literally every other shell program that requires logging into the database will fail if invalid credentials are provided. Or, you can use good credentials and psql to connect to a known database and then query the system to learn, without an error, if other roles or databases exist in the same cluster. David J.
Re: Determine if a user and database are available
Jeffrey Walton writes: > I have another beginner question. I am trying to use pg_isready to > determine if a database and user are present. The program seems to > always succeed, even when I delete the user or the database. That's a feature actually. The intended use of pg_isready is to find out if the server is alive, not whether any particular user or database name is correct. So it treats responses like "no such database" as sufficient proof that the server is alive. As David says, you could try to log in with any other client software, or connect using known-good parameters and check the system catalogs. regards, tom lane
Re: Determine if a user and database are available
On 9/2/22 17:21, Tom Lane wrote: Jeffrey Walton writes: I have another beginner question. I am trying to use pg_isready to determine if a database and user are present. The program seems to always succeed, even when I delete the user or the database. That's a feature actually. The intended use of pg_isready is to find out if the server is alive, not whether any particular user or database name is correct. Then what's the point of the --username=USERNAME connection option? -- Angular momentum makes the world go 'round.
Re: Determine if a user and database are available
On 9/2/22 17:33, Ron wrote: On 9/2/22 17:21, Tom Lane wrote: Jeffrey Walton writes: I have another beginner question. I am trying to use pg_isready to determine if a database and user are present. The program seems to always succeed, even when I delete the user or the database. That's a feature actually. The intended use of pg_isready is to find out if the server is alive, not whether any particular user or database name is correct. Then what's the point of the --username=USERNAME connection option? My guess so you can specify a 'test' user that you can track in the logs. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Determine if a user and database are available
Ron writes: > On 9/2/22 17:21, Tom Lane wrote: >> That's a feature actually. The intended use of pg_isready is to >> find out if the server is alive, not whether any particular user >> or database name is correct. > Then what's the point of the --username=USERNAME connection option? That's explained in the documentation extract already quoted: if you do use a bad user/db/password, the server will log a message about that, since it just sees a failed connection attempt. If you'd rather not have such chatter in your log, then you want to give pg_isready valid connection data. But that's incidental to the purpose of the program. regards, tom lane
