Re: Inefficient query plan for SELECT ... EXCEPT ...
Thank you all for the answers, they covered me well. Is this worth a bug report? I can file one if the issue is not known. No. It's just a missing optimisation. We know about it. It's good I shot an email first then. FWIW my usual way in other projects would be to check the bugtracker, and just "follow" the relevant issue if it's minor like a missing optimisation. I didn't find a way to search for "known issues" in the Postgresql project. Dimitris
missing client_hostname
Hi, I have postgres 12 running in centos7. I recently have configured streaming replication from master to standby using below command: /usr/bin/pg_basebackup -h -p 5432 -U replication -D /path/of/data/directory/ -Fp -R -Xs -P -c fast It was successfully configured but when I query pg_stat_replication I don't get the hostname in output: postgres=# select * from pg_stat_Replication; -[ RECORD 1 ]+-- pid | 3692075 usesysid | 16384 usename | replication application_name | walreceiver client_addr | slave_ip *client_hostname* | client_port | 52500 backend_start| 2023-11-01 08:26:45.373297-07 backend_xmin | state| streaming sent_lsn | 2E5/41C0 write_lsn| 2E5/41C0 flush_lsn| 2E5/41C0 replay_lsn | 2E5/41C0 write_lag| flush_lag| replay_lag | sync_priority| 0 sync_state | async reply_time | 2023-11-01 08:41:47.60122-07 So please help me understand the reason for this and how I will fix this issue of having a respective hostname in this catalog. Regards, Atul
Re: missing client_hostname
## Atul Kumar (akumar14...@gmail.com): > It was successfully configured but when I query pg_stat_replication I don't > get the hostname in output: I Recommend The Fine Manual: https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW "... and only when log_hostname is enabled". Regards, Christoph -- Spare Space
Re: missing client_hostname
I have already enabled log_hostname, still *client_hostname is not showing up.* Do you think that just because I use ip instead of hostname while using pg_basebackup could be the reason for it ? Regards, Atul On Wed, Nov 1, 2023 at 11:23 PM Christoph Moench-Tegeder wrote: > ## Atul Kumar (akumar14...@gmail.com): > > > It was successfully configured but when I query pg_stat_replication I > don't > > get the hostname in output: > > I Recommend The Fine Manual: > > https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW > "... and only when log_hostname is enabled". > > Regards, > Christoph > > -- > Spare Space >
pg_dump/pg_restore --jobs practical limit?
I will "soon" migrate some 1+ TB database from Pg 9.6.24 on RHEL 6 VMs to Pg 14.latest on RHEL 8 VMs. The VMs have 10Gbps "NICs", SAN-based LUNs managed by LVM, and are all on ESX blades. nproc count on some is 16 and on others is 32. Does anyone have experience as to the point of diminishing returns? IOW, can I crank them processes up to --jobs=30, will I see no gain -- or even degradation -- after, for example, --jobs=24? This would be for both pg_dump and pg_restore (which would be run on the RHEL 8 VM). -- Born in Arizona, moved to Babylonia.
Re: missing client_hostname
Did you reconnect your replica after enabling log_hostname? If not, then do it and check again. śr., 1 lis 2023, 19:03 użytkownik Atul Kumar napisał: > I have already enabled log_hostname, still *client_hostname is not > showing up.* > > Do you think that just because I use ip instead of hostname while using > pg_basebackup could be the reason for it ? > > > > > Regards, > Atul > > On Wed, Nov 1, 2023 at 11:23 PM Christoph Moench-Tegeder < > c...@burggraben.net> wrote: > >> ## Atul Kumar (akumar14...@gmail.com): >> >> > It was successfully configured but when I query pg_stat_replication I >> don't >> > get the hostname in output: >> >> I Recommend The Fine Manual: >> >> https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW >> "... and only when log_hostname is enabled". >> >> Regards, >> Christoph >> >> -- >> Spare Space >> >
Re: missing client_hostname
Yes, I have already tried that as log_hostname was already enabled by me. By the way, just to inform you that the log_hostname is used for logging the hostname instead of ip addresses in error log files only, Please correct me if I am wrong. Could you help me in telling my query that Iasked in my trial mail: "Do you think that just because I use ip_address instead of hostname while using pg_basebackup on standby side could be the reason for not showing client_hostname in pg_stat_replication ?" I used below command in standby node: /usr/bin/pg_basebackup -h -p 5432 -U replication -D /path/of/data/directory/ -Fp -R -Xs -P -c fast Regards. On Wed, Nov 1, 2023 at 11:46 PM Mateusz Henicz wrote: > Did you reconnect your replica after enabling log_hostname? If not, then > do it and check again. > > śr., 1 lis 2023, 19:03 użytkownik Atul Kumar > napisał: > >> I have already enabled log_hostname, still *client_hostname is not >> showing up.* >> >> Do you think that just because I use ip instead of hostname while using >> pg_basebackup could be the reason for it ? >> >> >> >> >> Regards, >> Atul >> >> On Wed, Nov 1, 2023 at 11:23 PM Christoph Moench-Tegeder < >> c...@burggraben.net> wrote: >> >>> ## Atul Kumar (akumar14...@gmail.com): >>> >>> > It was successfully configured but when I query pg_stat_replication I >>> don't >>> > get the hostname in output: >>> >>> I Recommend The Fine Manual: >>> >>> https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW >>> "... and only when log_hostname is enabled". >>> >>> Regards, >>> Christoph >>> >>> -- >>> Spare Space >>> >>
Re: missing client_hostname
Hi, please don't top-post. ## Atul Kumar (akumar14...@gmail.com): > I have already enabled log_hostname, still *client_hostname is not showing > up.* It's always helpful to mention relevant non-default settings along with the question. Was log_hostname really active at backup start time? (Check pg_settings before starting the backup). Can the server actually resolve the IP address to a host name? > Do you think that just because I use ip instead of hostname while using > pg_basebackup could be the reason for it ? No. Regards, Christoph -- Spare Space
Re: pg_dump/pg_restore --jobs practical limit?
On Wed, 2023-11-01 at 13:09 -0500, Ron wrote: > I will "soon" migrate some 1+ TB database from Pg 9.6.24 on RHEL 6 VMs to Pg > 14.latest on RHEL 8 VMs. The VMs have 10Gbps "NICs", SAN-based LUNs managed > by LVM, and are all on ESX blades. nproc count on some is 16 and on others > is 32. > > Does anyone have experience as to the point of diminishing returns? > > IOW, can I crank them processes up to --jobs=30, will I see no gain -- or > even degradation -- after, for example, --jobs=24? > > This would be for both pg_dump and pg_restore (which would be run on the > RHEL 8 VM). Test, test, test. Theoretical considerations are pretty worthless, and it is easy to measure that. Yours, Laurenz Albe
Re: pg_dump/pg_restore --jobs practical limit?
On 11/1/23 15:42, Laurenz Albe wrote: On Wed, 2023-11-01 at 13:09 -0500, Ron wrote: I will "soon" migrate some 1+ TB database from Pg 9.6.24 on RHEL 6 VMs to Pg 14.latest on RHEL 8 VMs. The VMs have 10Gbps "NICs", SAN-based LUNs managed by LVM, and are all on ESX blades. nproc count on some is 16 and on others is 32. Does anyone have experience as to the point of diminishing returns? IOW, can I crank them processes up to --jobs=30, will I see no gain -- or even degradation -- after, for example, --jobs=24? This would be for both pg_dump and pg_restore (which would be run on the RHEL 8 VM). Test, test, test. Theoretical considerations are pretty worthless, Which is why I asked if anyone has experience. and it is easy to measure that. Not necessarily. Our test systems are way too small (only good enough to validate that the script works correctly), and there's always something (sometimes a lot, sometime just "some") going on in production, whether it's my customer's work, or the SAN (like snapshotting /every/ VM and then copying the snapshots to the virtual tape device) or something else. -- Born in Arizona, moved to Babylonia.
Re: pg_dump/pg_restore --jobs practical limit?
From: Ron Sent: Thursday, November 2, 2023 3:01:47 AM To: pgsql-general@lists.postgresql.org Subject: Re: pg_dump/pg_restore --jobs practical limit? On 11/1/23 15:42, Laurenz Albe wrote: On Wed, 2023-11-01 at 13:09 -0500, Ron wrote: I will "soon" migrate some 1+ TB database from Pg 9.6.24 on RHEL 6 VMs to Pg 14.latest on RHEL 8 VMs. The VMs have 10Gbps "NICs", SAN-based LUNs managed by LVM, and are all on ESX blades. nproc count on some is 16 and on others is 32. Does anyone have experience as to the point of diminishing returns? IOW, can I crank them processes up to --jobs=30, will I see no gain -- or even degradation -- after, for example, --jobs=24? This would be for both pg_dump and pg_restore (which would be run on the RHEL 8 VM). Test, test, test. Theoretical considerations are pretty worthless, Which is why I asked if anyone has experience. and it is easy to measure that. Not necessarily. Our test systems are way too small (only good enough to validate that the script works correctly), and there's always something (sometimes a lot, sometime just "some") going on in production, whether it's my customer's work, or the SAN (like snapshotting every VM and then copying the snapshots to the virtual tape device) or something else. -- Sure, but are the new systems busy already? Ideally you could run tests on them before they are put into production.
Re: pg_dump/pg_restore --jobs practical limit?
On 11/1/23 20:05, Brad White wrote: *From:* Ron *Sent:* Thursday, November 2, 2023 3:01:47 AM *To:* pgsql-general@lists.postgresql.org *Subject:* Re: pg_dump/pg_restore --jobs practical limit? On 11/1/23 15:42, Laurenz Albe wrote: On Wed, 2023-11-01 at 13:09 -0500, Ron wrote: I will "soon" migrate some 1+ TB database from Pg 9.6.24 on RHEL 6 VMs to Pg 14.latest on RHEL 8 VMs. The VMs have 10Gbps "NICs", SAN-based LUNs managed by LVM, and are all on ESX blades. nproc count on some is 16 and on others is 32. Does anyone have experience as to the point of diminishing returns? IOW, can I crank them processes up to --jobs=30, will I see no gain -- or even degradation -- after, for example, --jobs=24? This would be for both pg_dump and pg_restore (which would be run on the RHEL 8 VM). Test, test, test. Theoretical considerations are pretty worthless, Which is why I asked if anyone has experience. and it is easy to measure that. Not necessarily. Our test systems are way too small (only good enough to validate that the script works correctly), and there's always something (sometimes a lot, sometime just "some") going on in production, whether it's my customer's work, or the SAN (like snapshotting /every/ VM and then copying the snapshots to the virtual tape device) or something else. Sure, but are the new systems busy already? Ideally you could run tests on them before they are put into production. Testing pg_restore with different --jobs= values will be easier. pg_dump is what's going to be reading from a constantly varying system. -- Born in Arizona, moved to Babylonia.
Re: pg_checksums?
On Mon, Oct 30, 2023 at 6:57 AM Alexander Kukushkin wrote: ... > As Michael already said, the following workflow works just fine (I did it > dozens of times): > 1. enable checksums on the standby node > 2. start the standby and let it catch up with the primary > 3. switchover to a standby node > 4. enable checksums on the former primary (now replica). There is also a good trick described in https://www.crunchydata.com/blog/fun-with-pg_checksums to avoid accidental start of Postgres: after pg_ctl stop and before pg_checksums --enable, do: mv data/pg_twophase data/pg_twophase.DO_NOT_START_THIS_DATABASE and once pg_checksums --enable is done, move it back. Additionally, I compiled some thoughts about running pg_checksums without downtime (Patroni-friendly, of course) here: https://twitter.com/samokhvalov/status/1719961485160689993.
