Who am I? Where am I connected?
LibPQ has various defaults for the host, user, and DB name. There's also the password file, the service file and service name. In the example below, I can connect with a "naked" psql invocation. Once connected, can I find out all aspects of the connection string? Or where they came from, like a pgpass.conf or service file? How to get the host, port, db name once connected? SHOW and pg_settings does not appear to be it, at first glance. Thanks, --DD c:\Users\ddevienne>psql psql (12.1, server 14.2) WARNING: psql major version 12, server major version 14. Some psql features might not work. WARNING: Console code page (437) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details. Type "help" for help. ddevienne=>
Re: Who am I? Where am I connected?
Hi Dominique, you can use \conninfo in psql to show the database, user, host (or socket in my example), and port: ewie@desktop ~ $ psql test Null display is "". psql (14.3) Type "help" for help. test=# \conninfo You are connected to database "test" as user "ewie" via socket in "/run/postgresql" at port "5432". - Erik > On 18/05/2022 12:07 Dominique Devienne wrote: > > > LibPQ has various defaults for the host, user, and DB name. > There's also the password file, the service file and service name. > In the example below, I can connect with a "naked" psql invocation. > > Once connected, can I find out all aspects of the connection string? > Or where they came from, like a pgpass.conf or service file? > > How to get the host, port, db name once connected? > SHOW and pg_settings does not appear to be it, at first glance. > > Thanks, --DD > > c:\Users\ddevienne>psql > psql (12.1, server 14.2) > WARNING: psql major version 12, server major version 14. > Some psql features might not work. > WARNING: Console code page (437) differs from Windows code page (1252) > 8-bit characters might not work correctly. See psql reference > page "Notes for Windows users" for details. > Type "help" for help. > > ddevienne=>
Re: Who am I? Where am I connected?
\conninfo will show you desired details Regards, Ganesh Korde. On Wed, 18 May 2022, 3:38 pm Dominique Devienne, wrote: > LibPQ has various defaults for the host, user, and DB name. > There's also the password file, the service file and service name. > In the example below, I can connect with a "naked" psql invocation. > > Once connected, can I find out all aspects of the connection string? > Or where they came from, like a pgpass.conf or service file? > > How to get the host, port, db name once connected? > SHOW and pg_settings does not appear to be it, at first glance. > > Thanks, --DD > > c:\Users\ddevienne>psql > psql (12.1, server 14.2) > WARNING: psql major version 12, server major version 14. > Some psql features might not work. > WARNING: Console code page (437) differs from Windows code page (1252) > 8-bit characters might not work correctly. See psql reference > page "Notes for Windows users" for details. > Type "help" for help. > > ddevienne=> > > >
Re: Who am I? Where am I connected?
## Dominique Devienne (ddevie...@gmail.com): > Once connected, can I find out all aspects of the connection string? \conninfo in psql (pro tip: \? actually helps), "Connection Status Functions" https://www.postgresql.org/docs/current/libpq-status.html in libpq; and in a pinch you could find your connection in pg_stat_activity (pid = pg_backend_pid()) plus some assorted queries for other details. You cannot get actual secrets like your private ssl key or your password (the server most likely doesn't have that, and the client assumes that you have it). Regards, Christoph -- Spare Space
show primary_conninfo unchanged after promotion?
Hi! i might be doing something wrong, but I cannot see what... Server is 14.3 (Debian 14.3-1.pgdg110+1) (at the time of writing, in Dockerfile FROM postgres:14) I have a 3 node cluster, starting with pg_red (172.26.0.2) as primary. I promote pg_green (172.26.0.4) as new primary, all other aligned. Now, on pg_green (172.26.0.4): =# select inet_server_addr(); inet_server_addr -- 172.26.0.4 =# select pg_is_in_recovery(); pg_is_in_recovery --- f =# select * from pg_stat_replication; (CUT) pid | usesysid | usename | application_name | client_addr -+--+-+--+- 486 | 16,384 | rep | walreceiver | 172.21.0.2 625 | 16,384 | rep | walreceiver | 172.21.0.4 But, if I execute show primary_conninfo, i get =# show primary_conninfo ; primary_conninfo user=rep passfile='/.pgpass' channel_binding=prefer host=pg_red port=5432 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 g. .ssencmode=prefer krbsrvname=postgres target_session_attrs=any which says primary is pg_red, which is the old primary. What is that I am doing wrong?!? Tnx!
Re: show primary_conninfo unchanged after promotion?
2022年5月18日(水) 23:07 Wiwwo Staff : > > Hi! > i might be doing something wrong, but I cannot see what... > > Server is 14.3 (Debian 14.3-1.pgdg110+1) > (at the time of writing, in Dockerfile FROM postgres:14) > > I have a 3 node cluster, starting with pg_red (172.26.0.2) as primary. > I promote pg_green (172.26.0.4) as new primary, all other aligned. > > Now, on pg_green (172.26.0.4): > > =# select inet_server_addr(); > inet_server_addr > -- > 172.26.0.4 > > =# select pg_is_in_recovery(); > pg_is_in_recovery > --- > f > > =# select * from pg_stat_replication; (CUT) > pid | usesysid | usename | application_name | client_addr > -+--+-+--+- > 486 | 16,384 | rep | walreceiver | 172.21.0.2 > 625 | 16,384 | rep | walreceiver | 172.21.0.4 > > But, if I execute show primary_conninfo, i get > =# show primary_conninfo ; > > primary_conninfo > > user=rep passfile='/.pgpass' channel_binding=prefer host=pg_red port=5432 > sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 g. > .ssencmode=prefer krbsrvname=postgres target_session_attrs=any > > which says primary is pg_red, which is the old primary. You're not doing anything wrong, it's just that the value of primary_conninfo is left untouched after promotion. It is however ignored if the server is a primary; see: https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-PRIMARY-CONNINFO which states: "This setting has no effect if the server is not in standby mode." Regards Ian Barwick -- EnterpriseDB: https://www.enterprisedb.com
Re: Who am I? Where am I connected?
On Wed, May 18, 2022 at 12:07 PM Dominique Devienne wrote: > LibPQ has various defaults for the host, user, and DB name. > There's also the password file, the service file and service name. > In the example below, I can connect with a "naked" psql invocation. > > Once connected, can I find out all aspects of the connection string? Thank you all for \conninfo. I was more thinking at the time about the SQL-way to get that info, not the psql way. But thanks to https://www.postgresql.org/docs/current/functions-info.html I managed to emulate it, modulo resolving the server's IP into a hostname. ddevienne=> \conninfo You are connected to database "ddevienne" as user "ddevienne" on host "localhost" (address "::1") at port "5432". ddevienne=> select current_database() || ' ' || session_user || ' ' || inet_server_addr() || ':' || inet_server_port(); ?column? -- ddevienne ddevienne ::1/128:5432 (1 row) > Or where they came from, like a pgpass.conf or service file? OTOH, no one replied to that part of the question. How to know if the user or database name was defaulted? Or came from a service-file, using a given service name? Is there no way, except by reverse-engineering the logic of the env-vars and built-in defaults?
Re: Who am I? Where am I connected?
On Wed, May 18, 2022 at 3:08 AM Dominique Devienne wrote: > Once connected, can I find out all aspects of the connection string? > Or where they came from, like a pgpass.conf or service file? > > How to get the host, port, db name once connected? > SHOW and pg_settings does not appear to be it, at first glance. > > The server has no clue how the values sent to it came into existence - nor should it. Whether and how any particular client might expose this kind of debugging information (or upgrade it to proper state info) is up to the client. I do not know what options psql offers. David J.
In case of network issues, how long before archive_command does retries
I've got a setup where archive_command will gzip the wal archive to a directory that is itself an NFS mount. When connection is gone or blocked, archive_command fails after the timeout specified by the NFS mount, as expected. (for a soft mount. hard mount hangs, as expected) However, on restoring connection, it's not clear to me how long it takes before the command is retried. Experience says "a few minutes", but I can't find documentation on an exact algorithm. To be clear, the question is: if archive_command fails, what are the specifics of retrying? Is there a timeout? How is that timeout defined? Is this detailed somewhere? Perhaps in the source code? I couldn't find it in the documentation. For detail, I'm using postgres 11, running on Ubuntu 20. Regards, Koen
Reasons for not overwriting processed wal archives?
The documentation here: https://www.postgresql.org/docs/11/continuous-archiving.html States: > It is advisable to test your proposed archive command to ensure that it indeed does not overwrite an existing file, *and that it returns nonzero status in this case*. Why exactly is this? Assuming a situation in which the files, as part of the archive_command, are gzipped to their final location. If the gzip fails, or stops halfway, perhaps due to a network issue... you're left with a file that isn't the full representation of the wal archive. So it needs to be done again. In cases where the file is simply moved, this can also occur. Wal size can be configured, and transfers might fail. So they'd have to be done again. If we test first for a file with that name being present, and refusing to go forward if there is... an admin is going to have to manually intervene and delete the half-finished file. So isn't it better to just overwrite? What scenario am I missing here? Regards, Koen
Re: Reasons for not overwriting processed wal archives?
On Wednesday, May 18, 2022, Koen De Groote wrote: > The documentation here: https://www.postgresql.org/ > docs/11/continuous-archiving.html > > States: > > > It is advisable to test your proposed archive command to ensure that it > indeed does not overwrite an existing file, *and that it returns nonzero > status in this case*. > > Why exactly is this? > Newer doc versions also say: This is an important safety feature to preserve the integrity of your archive in case of administrator error (such as sending the output of two different servers to the same archive directory). David J.
Re: Who am I? Where am I connected?
On Wed, May 18, 2022 at 5:43 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, May 18, 2022 at 3:08 AM Dominique Devienne > wrote: > >> Once connected, can I find out all aspects of the connection string? >> Or where they came from, like a pgpass.conf or service file? >> >> How to get the host, port, db name once connected? >> SHOW and pg_settings does not appear to be it, at first glance. >> >> > The server has no clue how the values sent to it came into existence - nor > should it. > > Whether and how any particular client might expose this kind of debugging > information (or upgrade it to proper state info) is up to the client. I do > not know what options psql offers. > AFAIK, it’s not psql that does this though, it’s libpq the official client api. And the fact libpq has no way to surface that info seems like an important oversight. >
Re: Who am I? Where am I connected?
On 5/18/22 14:59, Dominique Devienne wrote: AFAIK, it’s not psql that does this though, it’s libpq the official client api. And the fact libpq has no way to surface that info seems like an important oversight. Not all clients use libpq e.g. the Postgres JDBC driver. It just uses the Postgres Frontend/Backend protocol: https://www.postgresql.org/docs/current/protocol.html -- Adrian Klaver adrian.kla...@aklaver.com
switchover and switchback assistance ...
Hi, folks; I’m in the process of testing PG v12.7 hot streaming standby’s in async mode, and specifically in the area of reliable switchover and switchback operations. I have created test primary’s and standby’s successfully with pg_ctl initdb and pg_basebackup respectively. I can switch over/switch roles (standby -> primary, primary -> standby) successfully one time. When I switch roles a second time, the standby becomes primary just fine but the primary to standby fails to open properly. Logfile messages for both primary and standby are attached as well as pg_wal and archive directory lists. I shut down the primary first and then promote the standby. The only action I take on the standby side is to create the standby.signal file in $PGDATA. (Well almost. Initially, I do have to have an empty .history file in the archive locations or the standby’s fail to start.) My archive directories are separate from each other and the archive command is the given test … cp -p example in the docs. archive_mode is ‘on’ on both sides. Are there other undocumented manual steps I should be completing before starting the new standby? I would appreciate review of the attached logs to help pinpoint the issue. Inst1 is the primary/standby/primary, and inst2 is the standby/primary/standby. Many thanks! David2022-05-18 09:52:52.820 EDT [55785] LOG: starting PostgreSQL 12.7 on x86_64-pc-cygwin, compiled by gcc (GCC) 10.2.0, 64-bit 2022-05-18 09:52:52.833 EDT [55785] LOG: listening on IPv6 address "::1", port 5432 2022-05-18 09:52:52.833 EDT [55785] LOG: listening on IPv4 address "127.0.0.1", port 5432 2022-05-18 09:52:52.840 EDT [55785] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2022-05-18 09:52:53.088 EDT [55786] LOG: database system was shut down at 2022-05-18 09:52:47 EDT 2022-05-18 09:52:54.543 EDT [55785] LOG: database system is ready to accept connections 2022-05-18 09:56:52.618 EDT [55785] LOG: received fast shutdown request 2022-05-18 09:56:52.623 EDT [55785] LOG: aborting any active transactions 2022-05-18 09:56:52.639 EDT [55785] LOG: background worker "logical replication launcher" (PID 55793) exited with exit code 1 2022-05-18 09:56:52.656 EDT [55787] LOG: shutting down 2022-05-18 09:56:52.841 EDT [55785] LOG: database system is shut down 2022-05-18 09:57:35.741 EDT [55861] LOG: starting PostgreSQL 12.7 on x86_64-pc-cygwin, compiled by gcc (GCC) 10.2.0, 64-bit 2022-05-18 09:57:35.756 EDT [55861] LOG: listening on IPv6 address "::1", port 5432 2022-05-18 09:57:35.756 EDT [55861] LOG: listening on IPv4 address "127.0.0.1", port 5432 2022-05-18 09:57:35.764 EDT [55861] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2022-05-18 09:57:35.980 EDT [55862] LOG: database system was shut down at 2022-05-18 09:56:52 EDT cp: cannot stat '/home/itman/pgdata/archive1/0002.history': No such file or directory 2022-05-18 09:57:36.093 EDT [55862] LOG: entering standby mode cp: cannot stat '/home/itman/pgdata/archive1/00010004': No such file or directory 2022-05-18 09:57:36.232 EDT [55862] LOG: consistent recovery state reached at 0/4A0 2022-05-18 09:57:36.232 EDT [55862] LOG: invalid record length at 0/4A0: wanted 24, got 0 2022-05-18 09:57:36.859 EDT [55861] LOG: database system is ready to accept read only connections 2022-05-18 09:57:37.298 EDT [55868] LOG: started streaming WAL from primary at 0/400 on timeline 1 2022-05-18 09:57:37.301 EDT [55862] LOG: redo starts at 0/4A0 2022-05-18 09:59:14.673 EDT [55868] LOG: replication terminated by primary server 2022-05-18 09:59:14.673 EDT [55868] DETAIL: End of WAL reached on timeline 1 at 0/5A0. 2022-05-18 09:59:14.673 EDT [55868] FATAL: could not send end-of-streaming message to primary: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. no COPY in progress cp: cannot stat '/home/itman/pgdata/archive1/0002.history': No such file or directory cp: cannot stat '/home/itman/pgdata/archive1/00010005': No such file or directory 2022-05-18 09:59:14.902 EDT [55862] LOG: invalid record length at 0/5A0: wanted 24, got 0 2022-05-18 09:59:17.107 EDT [55881] FATAL: could not connect to the primary server: connection to server at "127.0.0.1", port 5433 failed: Connection refused Is the server running on that host and accepting TCP/IP connections? cp: cannot stat '/home/itman/pgdata/archive1/0002.history': No such file or directory cp: cannot stat '/home/itman/pgdata/archive1/00010005': No such file or directory 2022-05-18 09:59:17.396 EDT [55862] LOG: received promote request 2022-05-18 09:59:17.396 EDT [55862] LOG: redo done at 0/528 2022-05-18 09:59:17.396 EDT [55862] LOG: last completed transaction was at log time 2022-05-18 09:58:23.71503-04 cp: cannot stat '/home/itman/pgdata
Re: Who am I? Where am I connected?
Dominique Devienne writes: > AFAIK, it’s not psql that does this though, it’s libpq the official client > api. > And the fact libpq has no way to surface that info seems like an important > oversight. PQconninfo() will show you all the option values in use by a connection object. It's true that it's not very easy to tell where any given value came from, but I doubt it'd be worth an ABI break to add such info. (You could at least recognize values that match the hard-wired default or environment variable value.) regards, tom lane
About pg_basebackup
Hi,I executed the pg_basebackup command to set up a replication configuration in postgresql, but the following message appears and replication didn’t complete.2741/2742 tablespaces (/var/lib/pgsql/9.2/bac12609150596/12609150596 kB (100%), 2741/2742 tablespaces (/var/lib/pgsql/9.2/bac12609150596/12609150596 kB (100%), 2742/2742 tablespaces NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to be archived WARNING: pg_stop_backup still waiting for all required WAL segments to be archived (60 seconds elapsed) HINT: Check that your archive_command is executing properly. pg_stop_backup can be canceled safely, but the database backup will not be usable without all the WAL segments. WARNING: pg_stop_backup still waiting for all required WAL segments to be archived (120 seconds elapsed) ・ ・ ・Please let me know how to deal with this. The environment is as follows and I attached postgresql.conf file.Version : 9.2OS : Cent 6.6 -6124946774920295119postgresql.conf Description: Binary data
Re: About pg_basebackup
On Thu, 2022-05-19 at 13:18 +0900, 菊池祐 wrote: > I executed the pg_basebackup command to set up a replication configuration in > postgresql, > but the following message appears and replication didn’t complete. > > 2741/2742 tablespaces (/var/lib/pgsql/9.2/bac12609150596/12609150596 kB > (100%), 2741/2742 tablespaces (/var/lib/pgsql/9.2/bac12609150596/12609150596 > kB (100%), 2742/2742 tablespaces > NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to be > archived > WARNING: pg_stop_backup still waiting for all required WAL segments to be > archived (60 seconds elapsed) > HINT: Check that your archive_command is executing properly. pg_stop_backup > can be canceled safely, but the database backup will not be usable without > all the WAL segments. > WARNING: pg_stop_backup still waiting for all required WAL segments to be > archived (120 seconds elapsed) I guess that your "archive_command" is hanging or failing. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
