Query planner riddle (array-related?)

2018-05-04 Thread Markus
'50'::double precision) -- which in reality appears to be a good deal faster than the "bad" plan, though still much, much slower than the "good plan". Both tables are ANALYZE-d, and they should be reasonably VACUUMED. Is there anything I can do to make it easier for the planner to see the light? -- Markus

Re: Query planner riddle (array-related?)

2018-05-07 Thread Markus
Hi Tom, On Fri, May 04, 2018 at 09:32:08AM -0400, Tom Lane wrote: > Markus writes: > > I'm running Postgresql 9.6.7 on Debian stretch -- and I'm trying to > > understand a query plan, with any hint where to gain further insight > > welcome. > > Well,

How to make runtime partition pruning work?

2019-10-11 Thread Markus Heiden
"import_table", instead of pruning the "data_table" partitions by the import_ids at runtime. Static pruning (when using ... IN (1, 2, 3, 4)) works fine though. What am I doing wrong that runtime partition pruning with PostgreSQL 11.5 does not work in my case? Thanks, Markus

Re: How to make runtime partition pruning work?

2019-10-15 Thread Markus Heiden
Notice that only subqueries and parameterized nested loop joins are mentioned. The above text does not really go into the detail of which types of subqueries can be used, but I can confirm that they must be subqueries that can only return a scalar value. e.g WHERE x = (SELECT y FROM ...). The

Re: install postgres

2019-12-20 Thread Thomas Markus
Hi, there's a section "Direct RPM download" ;) Or https://download.postgresql.org/pub/repos/yum/12/redhat/ regards Thomas Am 20.12.19 um 10:24 schrieb Marc Millas: Hi, I may overlook something obvious.. I am just looking, on the download pages of postgresql.org for a

Re: Practical usage of large objects.

2020-05-14 Thread Thomas Markus
Am 14.05.20 um 15:36 schrieb Laurenz Albe: Interesting; only recently I played with that a little and found that that is not necessarily true: https://www.cybertec-postgresql.com/en/binary-data-performance-in-postgresql/ Yours, Laurenz Albe We used lo a lot in a project for large uploads (>4G

Re: Parallelism on Partitioning .

2021-01-18 Thread Thomas Markus
Hi Hi , We are trying to assign the parallel worker or execute the query in parallel manner on partitioned Tables and partitioned master table but not able to do that ,could u plz suggest . Current Postgresql version :- 9.6 Fyi, We have tried with all parameters which can allow the paralle

Binding Postgres to port 0 for testing

2023-03-25 Thread Markus Pilman
uot; (1 .. 65535) Is there a reason this is not allowed? What would be the recommended way of addressing my issue? Best Markus

Re: Binding Postgres to port 0 for testing

2023-03-27 Thread Markus Pilman
production environment this is almost never the thing you want to do, but I wouldn't consider this option very dangerous. Best Markus On Sat, Mar 25, 2023 at 12:10 PM Erik Wienhold wrote: > > On 25/03/2023 18:01 CET Markus Pilman wrote: > > > > I am building a simple integrat

Re: Binding Postgres to port 0 for testing

2023-03-27 Thread Markus Pilman
in fun learning than reducing work :) Generally I would agree that reusing existing and testing code to run this would be better unless there's a really good reason not to do that. On Sun, Mar 26, 2023 at 7:27 PM Michael Paquier wrote: > On Sat, Mar 25, 2023 at 11:01:33AM -0600, Markus

Re: sha512sum (program) gives different result than sha512 in PG11

2019-01-14 Thread Thomas Markus
Hi, echo contains a trailing carriage return. Try echo -n "A" | sha512sum regards Thomas Am 14.01.19 um 16:16 schrieb Andreas Joseph Krogh: Hi. Anyone can explain why these two don't give the same result? 1. $ echo "A" | sha512sum 7a296fab5364b34ce3e0476d55bf291bd41aa085e5ecf2a96883e593aa183

Re: Question: Multiple pg clusters on one server can be reached with the standard port.

2023-06-16 Thread Thomas Markus
Hi Am 16.06.23 um 11:40 schrieb Brainmue: Hello all, I am currently looking for a solution similar to Oracle Listener. Current situation: I have a system with multiple PostgreSQL clusters for different databases. Each cluster is running on the same physical machine and is accessed through its

Re: Question: Multiple pg clusters on one server can be reached with the standard port.

2023-06-18 Thread Thomas Markus
Hi, Am 16.06.23 um 13:53 schrieb Brainmue: possible solutions: * set up a firewall rule to forward connection * use a tcp proxy (nginx can do that) * check pg_bouncer best regards Thomas Hello Thomas, Thank you for your quick reply. With firewall you mean an additional software, right? Bec

Re: Question: Multiple pg clusters on one server can be reached with the standard port.

2023-06-19 Thread Thomas Markus
Hi, Am 19.06.23 um 12:33 schrieb Peter J. Holzer: As Francisco already pointed out, this can't work with nginx either. The client resolves the alias and the TCP packets only contain the IP address, not the alias which was used to get that address. So nginx simply doesn't have that information an

Postgres > 12 with Windows Server 2012

2024-02-26 Thread Markus Oley
2012. Is this just due to the fact that Windows Server 2012 is already EndOfLive or are there really hard reasons (such as reliability degradation or expected technical problems) why this combination should be avoided? Would be nice if I could hear a well-founded opinion on this. Thanks in advance Regards Markus

Re: Match 2 words and more

2021-11-28 Thread Thomas Markus
Am 28.11.21 um 01:27 schrieb Shaozhong SHI: this is supposed to find those to have 2 words and more. select name FROM a_table where "STREET_NAME" ~ '^[[:alpha:]+ ]+[:alpha:]+$'; But, it finds only one word as well. It appears that regex is not robust. Can anyone shed light on this? Rega

SELECT DISTINCT scans the table?

2021-12-16 Thread Markus Demleitner
the information that there are constants in the select clauses? Thanks, Markus

Re: SELECT DISTINCT scans the table?

2021-12-17 Thread Markus Demleitner
Dear Tom, On Thu, Dec 16, 2021 at 03:47:57PM -0500, Tom Lane wrote: > Markus Demleitner writes: > > Maximally stripped down, my problem is that > > > select distinct 300 from > > > seqscans (at least in PostgreSQL 11.14). To me, it seems > > obvious t

Re: Getting json-value as varchar

2022-01-06 Thread Thomas Markus
Hi, Am 06.01.22 um 13:28 schrieb Andreas Joseph Krogh: Hi, in PG-14 this query returns "value" (with double-quotes): SELECT ('{"key":"value"}'::jsonb)['key']; ┌─┐ │  jsonb  │ ├─┤ │ "value" │ └─┘ (1 row) and this returns 'value' (without the quotes): SELECT ('{"key":"valu

Re: Getting json-value as varchar

2022-01-06 Thread Thomas Markus
Hi, Am 06.01.22 um 13:36 schrieb Andreas Joseph Krogh: På torsdag 06. januar 2022 kl. 13:31:19, skrev Thomas Markus : Hi, Am 06.01.22 um 13:28 schrieb Andreas Joseph Krogh: Hi, in PG-14 this query returns "value" (with double-quotes): SELECT ('{"key&quo

Re: duplicate primary key

2017-11-22 Thread Thomas Markus
Am 22.11.17 um 12:05 schrieb Alexander Pyhalov: Hello. I'm a bit shocked. During import/export of our database we've found a duplicate primary key. # \d player Table "public.player"    Column   |    Type |     Modifiers --

Index not used in certain nested views but not in others

2025-04-30 Thread Markus Demleitner
ur given I've switched off the genetic optimiser and postgres has hopefully exhaustively searched the space of plans in both cases? Thanks a lot! -- Markus

broken backup trail in case of quickly patroni switchback and forth

2019-11-07 Thread Zwettler Markus (OIZ)
kuped? any idea? - Markus

AW: broken backup trail in case of quickly patroni switchback and forth

2019-11-07 Thread Zwettler Markus (OIZ)
I already asked the Patroni folks. They told me this is not related to Patroni but Postgresql. ;-) - Markus On 11/7/19 5:52 AM, Zwettler Markus (OIZ) wrote: > we are using Patroni for management of our Postgres standby databases. > > we take our (wal) backups on the primary side

AW: AW: broken backup trail in case of quickly patroni switchback and forth

2019-11-07 Thread Zwettler Markus (OIZ)
filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_line_prefix = '%t : %h=>%u@%d : %p-%c-%v : %e ' log_statement = 'ddl' max_wal_senders = 5 port = 5436 shared_buffers = 512MB shared_preload_libraries = 'auto_explain, pg_stat_statements, pg_cron, pg_statsinfo'

AW: AW: AW: broken backup trail in case of quickly patroni switchback and forth

2019-11-07 Thread Zwettler Markus (OIZ)
osync: false -Ursprüngliche Nachricht- Von: Adrian Klaver Gesendet: Donnerstag, 7. November 2019 17:06 An: Zwettler Markus (OIZ) ; pgsql-general@lists.postgresql.org Betreff: Re: AW: AW: broken backup trail in case of quickly patroni switchback and forth On 11/7/19 7:47 AM, Zwettler Markus (OIZ)

AW: broken backup trail in case of quickly patroni switchback and forth

2019-11-08 Thread Zwettler Markus (OIZ)
, Markus On Thu, 2019-11-07 at 13:52 +, Zwettler Markus (OIZ) wrote: > we are using Patroni for management of our Postgres standby databases. > > we take our (wal) backups on the primary side based on intervals and > thresholds. > our archived wal's are written to a local

AW: AW: AW: AW: broken backup trail in case of quickly patroni switchback and forth

2019-11-08 Thread Zwettler Markus (OIZ)
ver. This might be the reason why the Patroni guys are always talking about failover only. It's not a Patroni issue but it's triggered by Patroni as it will do "some kind of switchover" on a regular shutdown. "Zwettler Markus (OIZ)" mailto:markus.zwett...@zuerich.c

AW: AW: AW: AW: AW: broken backup trail in case of quickly patroni switchback and forth

2019-11-08 Thread Zwettler Markus (OIZ)
Let me clarify: "But, it might start killing processes after a certain period if a _fast_ shutdown after SIGTERM didn't happen". I am talking about stopping the Patroni master process with a systemd scipt. Von: Brad Nicholson Gesendet: Freitag, 8. November 2019 15:58 An: Zwett

AW: AW: AW: AW: AW: AW: broken backup trail in case of quickly patroni switchback and forth

2019-11-08 Thread Zwettler Markus (OIZ)
How exactly? Please clarify. "Zwettler Markus (OIZ)" mailto:markus.zwett...@zuerich.ch>> wrote on 2019/11/08 11:02:49 AM: > From: "Zwettler Markus (OIZ)" > mailto:markus.zwett...@zuerich.ch>> > To: Brad Nicholson mailto:br...@ca.ibm.com&g

AW: AW: AW: AW: AW: AW: AW: broken backup trail in case of quickly patroni switchback and forth

2019-11-08 Thread Zwettler Markus (OIZ)
? "Zwettler Markus (OIZ)" mailto:markus.zwett...@zuerich.ch>> wrote on 2019/11/08 11:27:00 AM: >> From: "Zwettler Markus (OIZ)" >> mailto:markus.zwett...@zuerich.ch>> >> To: Brad Nicholson mailto:br...@ca.ibm.com>> >> Cc: Adrian

sql query for postgres replication check

2019-11-22 Thread Zwettler Markus (OIZ)
job or am I missing something here? Thanks, Markus

AW: sql query for postgres replication check

2019-11-25 Thread Zwettler Markus (OIZ)
> On Fri, Nov 22, 2019 at 01:20:59PM +, Zwettler Markus (OIZ) wrote: > > I came up with the following query which should return any apply lag in > > seconds. > > > > select coalesce(replay_delay, 0) replication_delay_in_sec from

secure deletion of archived logs

2019-12-04 Thread Zwettler Markus (OIZ)
with Oracle we use "backup archivelog all delete all input". this is a kind of atomic transaction. everything backuped for sure is deleted. with Postgres we archive to a local host directory we do a Networker backup of this directory afterwards and delete the archived logs but this is not an atom

archiving question

2019-12-04 Thread Zwettler Markus (OIZ)
When there is a Postgres archiver stuck because of filled pg_xlog and archive directories... ... and the pg_xlog directory had been filled with dozens of GBs of xlogs... ...it takes ages until the archive_command had moved all xlogs from the pg_xlog directory to the archive directory afterwards

AW: secure deletion of archived logs

2019-12-05 Thread Zwettler Markus (OIZ)
> Greetings, > > * Zwettler Markus (OIZ) (markus.zwett...@zuerich.ch) wrote: > > with Oracle we use "backup archivelog all delete all input". > > this is a kind of atomic transaction. > > everything backuped for sure is deleted. > > > > w

AW: archiving question

2019-12-05 Thread Zwettler Markus (OIZ)
> > Greetings, > > * Zwettler Markus (OIZ) (markus.zwett...@zuerich.ch) wrote: > > When there is a Postgres archiver stuck because of filled pg_xlog and > > archive > directories... > > > > ... and the pg_xlog directory had been filled with dozens of G

AW: archiving question

2019-12-06 Thread Zwettler Markus (OIZ)
> -Ursprüngliche Nachricht- > Von: Michael Paquier > Gesendet: Freitag, 6. Dezember 2019 02:43 > An: Zwettler Markus (OIZ) > Cc: Stephen Frost ; pgsql-general@lists.postgresql.org > Betreff: Re: archiving question > > On Thu, Dec 05, 2019 at 03:04:55PM +, Zwe

AW: archiving question

2019-12-06 Thread Zwettler Markus (OIZ)
> On Fri, Dec 6, 2019 at 10:50 AM Zwettler Markus (OIZ) > <mailto:markus.zwett...@zuerich.ch> wrote: >> -Ursprüngliche Nachricht- >> Von: Michael Paquier <mailto:mich...@paquier.xyz> >> Gesendet: Freitag, 6. Dezember 2019 02:43 >> An:

AW: PostgreSQL HA FO question

2019-12-09 Thread Zwettler Markus (OIZ)
In my opinion, the best open source product is Patroni. Von: Dor Ben Dov Gesendet: Montag, 9. Dezember 2019 11:24 An: pgsql-general@lists.postgresql.org Betreff: PostgreSQL HA FO question Hi everyone, What is the best / mostly common / production used open source solution for HA / FO/ backu

AW: PostgreSQL HA FO question

2019-12-11 Thread Zwettler Markus (OIZ)
production experience. Von: Dor Ben Dov Gesendet: Montag, 9. Dezember 2019 13:24 An: Zwettler Markus (OIZ) ; pgsql-general@lists.postgresql.org Betreff: RE: PostgreSQL HA FO question Hi Zwettler, Based on what comparison, production experience ? Regards, Dor From: Zwettler Markus (OIZ

AW: AW: secure deletion of archived logs

2019-12-11 Thread Zwettler Markus (OIZ)
> > Greetings, > > * Zwettler Markus (OIZ) (markus.zwett...@zuerich.ch) wrote: > > We use "rsync" on XFS with "wsync" mount mode. I think this should do the > > job? > > No, that just makes sure that namespace operations are executed synchronou

How to log pg_terminate_backend() calls

2020-01-14 Thread Zwettler Markus (OIZ)
We see some "FATAL: terminating connection due to administrator command" error messages in the Postgres logfile. We assume someone did pg_terminate_backend() calls. How can we log such calls, especially who did the call?

periodic refresh of pre-production stages

2020-02-07 Thread Zwettler Markus (OIZ)
hi, we have to refresh our DEV and UAT stages periodically with PRD data. we will to do this by cloning the whole cluster using pg_basebackup or restoring our filesystem backup (data + archive). I saw that the database system identifier stays the same. is there any complication on this? thx

gdal version for Postgis 2.4?

2020-03-09 Thread Zwettler Markus (OIZ)
I am in the same situation: I found that Postgis 24 depends on gdal30-libs.x86_64 0:3.0.4-2. But this installation creates an error : dbname=# create extension postgis; ERROR: could not load library "/usr/pgsql-11/lib/rtpostgis-2.4.so": /usr/gdal30/lib/libgdal.so.26: undefined symbol: GEOSMakeVal

vacuum full doubled database size

2020-03-13 Thread Zwettler Markus (OIZ)
We did a "vacuum full" on a database which had been interrupted by a network outage. We found the database size doubled afterwards. Autovacuum also found a lot of orphaned tables afterwards. The ophan temp objects went away after a cluster restart while the db size remained doubled. Any idea?

pg_upgrade 9.6 to 12 without 9.6 binaries

2020-03-13 Thread Zwettler Markus (OIZ)
ill a way to use pg_upgrade without the old (-b) binary set (I am aware of pg_dump/pg_restore)? Markus

AW: vacuum full doubled database size

2020-03-13 Thread Zwettler Markus (OIZ)
Thanks. How to get rid of it. New vacuum full? Von: Michael Loftis Gesendet: Freitag, 13. März 2020 14:48 An: Zwettler Markus (OIZ) Cc: pgsql-general Betreff: Re: vacuum full doubled database size A vacuum full rebuilds the tables, so yeah if it didn’t successfully complete I would expect

AW: Which commands are guaranteed to drop role

2020-04-14 Thread Zwettler Markus (OIZ)
ermissions to a role that you > plan to > drop. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > > Is there a query to get all missing privileges not revoked yet? Markus

Linux Update Experience

2020-05-28 Thread Zwettler Markus (OIZ)
nks, Markus

AW: Linux Update Experience

2020-05-28 Thread Zwettler Markus (OIZ)
Hi Marco, How do you handle these conflicts? No longer updating that regularly or not at all anymore? Thanks, Markus Von: Marco Lechner Gesendet: Donnerstag, 28. Mai 2020 11:01 An: Zwettler Markus (OIZ) ; PostgreSQL General Betreff: AW: Linux Update Experience Hi Markus, at the moment

AW: Linux Update Experience

2020-05-28 Thread Zwettler Markus (OIZ)
> -Ursprüngliche Nachricht- > Von: Adrian Klaver > Gesendet: Donnerstag, 28. Mai 2020 16:15 > An: Zwettler Markus (OIZ) ; PostgreSQL General > > Betreff: Re: Linux Update Experience > > On 5/28/20 12:59 AM, Zwettler Markus (OIZ) wrote: > > We are runni

pg_dump / pg_restore version confusion

2020-07-29 Thread Zwettler Markus (OIZ)
Hi, An external supplier had a postgres v9.5 database which he dumped with a pg_dump v12 client in custom format using PgAdmin4. Would a pg_restore with a v12 client into a postgres v9.6 database work and be officially supported? Thanks, Markus

AW: pg_dump / pg_restore version confusion

2020-07-29 Thread Zwettler Markus (OIZ)
And I can also do this restore: Would a pg_restore with a v12 client into a postgres v9.6 database work and be officially supported? -Markus > -Ursprüngliche Nachricht- > Von: Bruce Momjian > Gesendet: Mittwoch, 29. Juli 2020 13:49 > An: Zwettler Markus (OIZ) > Cc

AW: pg_dump / pg_restore version confusion

2020-07-29 Thread Zwettler Markus (OIZ)
=> pg_dump v12 => pg_restore v12 => PG v9.6. That's why my question has been: would the whole chain work and is it supported? > -Ursprüngliche Nachricht- > Von: Bruce Momjian > Gesendet: Mittwoch, 29. Juli 2020 14:55 > An: Zwettler Markus (OIZ) > Cc:

How to find out why user processes are using so much memory?

2020-07-30 Thread Zwettler Markus (OIZ)
something like that? pg_stat_statements is only showing shared_buffers per statement. -Markus last pid: 92572; load avg: 0.22, 0.32, 0.22; up 258+21:20:34 11:22:16 96 processes: 96 sleeping CPU states: 0.2% user

pgpool-II native replication

2020-10-29 Thread Zwettler Markus (OIZ)
tiple backends." Has anyone ever used pgpool-II native replication to build an HA environment? Thanks, Markus

Linux package upgrade without dependency conflicts

2020-11-19 Thread Zwettler Markus (OIZ)
PostGIS and Patroni. I was told that there will be no dependency conflicts anymore when we install Postgres from sourcecode and Patroni with pip. Is that correct? Because all Linux packages required by Postgres will continue to be updated. -Markus

AW: Linux package upgrade without dependency conflicts

2020-11-20 Thread Zwettler Markus (OIZ)
Any hints on this one? Thanks, Markus Von: Zwettler Markus (OIZ) Gesendet: Donnerstag, 19. November 2020 14:50 An: pgsql-general@lists.postgresql.org Betreff: Linux package upgrade without dependency conflicts We run Postgres 9.6 + 12 Community Edition on RHEL7 which we install directly out

AW: AW: Linux package upgrade without dependency conflicts

2020-11-20 Thread Zwettler Markus (OIZ)
Yes. Pretty sure. PGDG repos. The last problems I remember had been related to Patroni + python-psycopg and PostGIS + pgrouting_96. Regards, Markus > -Ursprüngliche Nachricht- > Von: Adrian Klaver > Gesendet: Freitag, 20. November 2020 16:26 > An: Zwettler Markus (

AW: Linux package upgrade without dependency conflicts

2020-11-20 Thread Zwettler Markus (OIZ)
> -Ursprüngliche Nachricht- > Von: Magnus Hagander > Gesendet: Freitag, 20. November 2020 16:29 > An: Zwettler Markus (OIZ) > Cc: pgsql-general@lists.postgresql.org > Betreff: Re: Linux package upgrade without dependency conflicts > > On Thu, Nov 19, 2020 at 2:50

AW: How to debug authentication issues in Postgres

2020-11-27 Thread Zwettler Markus (OIZ)
all users set new passwords, and change the authentication method specifications in pg_hba.conf to scram-sha-256. -Markus Von: Hemil Ruparel Gesendet: Freitag, 27. November 2020 09:38 An: Laurenz Albe Cc: pgsql-generallists.postgresql.org Betreff: Re: How to debug authentication issues i

FDW using remote ODBC driver

2020-12-01 Thread Zwettler Markus (OIZ)
Hi, Can I create a FDW connection using an ODBC driver which is installed on a remote host, e.g. Postgres@Linux ==> FDW ==> ODBC@Windows ==> SQL-Server In other words, must the ODBC driver be installed on the local Linux system? Thanks, Markus

AW: FDW using remote ODBC driver

2020-12-01 Thread Zwettler Markus (OIZ)
Client means Postgres Server / FDW in that case? Von: Hemil Ruparel Gesendet: Dienstag, 1. Dezember 2020 16:08 An: Zwettler Markus (OIZ) Cc: pgsql-general@lists.postgresql.org Betreff: Re: FDW using remote ODBC driver Yes. The driver needs to be on the client On Tue, Dec 1, 2020 at 8:34 PM

AW: FDW using remote ODBC driver

2020-12-01 Thread Zwettler Markus (OIZ)
Wrong direction. I want to do a Connection FROM Postgres@Linux using fdw + odbc TO SQL-Server@Windows. Question has been if the SQL-Server ODBC driver can also be installed on the remote Windows Server in that case. -Markus Von: Hemil Ruparel Gesendet: Dienstag, 1. Dezember 2020 16:17 An

AW: AW: FDW using remote ODBC driver

2020-12-01 Thread Zwettler Markus (OIZ)
Question has been for pure interest. I could use a remote ODBC driver with Oracle ;-) > -Ursprüngliche Nachricht- > Von: Laurenz Albe > Gesendet: Dienstag, 1. Dezember 2020 17:14 > An: Zwettler Markus (OIZ) ; Hemil Ruparel > > Cc: pgsql-general@lists.postgresql.org

ldap connection parameter lookup

2021-01-15 Thread Zwettler Markus (OIZ)
there also some kind of generic variant like this (meaning lookup connection parameters for the database name I tell you somehow): [${PGDATABASE}] ldap://ldap.mycompany.com/dc=mycompany,dc=com?description?one?(cn=${PGDATABASE}) Thanks, Markus

AW: ldap connection parameter lookup

2021-01-22 Thread Zwettler Markus (OIZ)
> -Ursprüngliche Nachricht- > Von: Laurenz Albe > Gesendet: Freitag, 15. Januar 2021 17:21 > An: Zwettler Markus (OIZ) ; pgsql- > gene...@postgresql.org > Betreff: Re: ldap connection parameter lookup > > On Fri, 2021-01-15 at 14:09 +, Zwettler Markus (OIZ)

open service broker api for local PGDG Postgres

2021-01-22 Thread Zwettler Markus (OIZ)
Does anyone know if there is an open service broker api for a local PGDG Postgres installation? I can only find an osb api for Crunchy Postgres pgo: https://github.com/CrunchyData/pgo-osb -Markus

running vacuumlo periodically?

2021-01-28 Thread Zwettler Markus (OIZ)
Short question. Is it recommended - or even best practice - to run vacuumlo periodically as a routine maintenance task? We don't do it. I think if this would be recommended it would have been implemented as an autotask like autovacuum. No? Thanks, Markus

AW: running vacuumlo periodically?

2021-01-28 Thread Zwettler Markus (OIZ)
> -Ursprüngliche Nachricht- > Von: Laurenz Albe > Gesendet: Donnerstag, 28. Januar 2021 17:39 > An: Zwettler Markus (OIZ) ; pgsql- > gene...@postgresql.org > Betreff: Re: running vacuumlo periodically? > > On Thu, 2021-01-28 at 13:18 +, Zwettler Markus (OIZ) w

AW: running vacuumlo periodically

2021-01-29 Thread Zwettler Markus (OIZ)
0G). Question: Will "vacuum full pg_catalog.pg_largeobjects" need less diskspace when "maintenance_work_mem" is increased? > -----Ursprüngliche Nachricht- > Von: Zwettler Markus (OIZ) > Gesendet: Donnerstag, 28. Januar 2021 18:04 > An: Laurenz Albe

curious vacuum full behavior

2021-02-04 Thread Zwettler Markus (OIZ)
y surprised. Is there any explanation on this behavior? Is vacuum full heavily using on-disk sort areas if maintenance_work_mem is too low? Postgres Version 9.6 Thanks, Markus

consistent postgresql snapshot

2022-05-11 Thread Zwettler Markus (OIZ)
g in 2 fs blocks per db block. Thanks, Markus

AW: [Extern] Re: consistent postgresql snapshot

2022-05-12 Thread Zwettler Markus (OIZ)
n] Re: consistent postgresql snapshot On 5/11/22 10:41, Zwettler Markus (OIZ) wrote: PostgreSQL12 running on CentOS7 + ext4. Would it be save to do a "fsfreeze –freeze" + disk snapshot + "fsfreeze –unfreeze" of the PGDATA filesystem to get a consistent snapshot? I am wonder

Patroni question

2022-09-22 Thread Zwettler Markus (OIZ)
We had a failover. I would read the Patroni logs below as following. 2022-09-21 11:13:56,384 secondary did a HTTP GET request to primary. This failed with a read timeout. 2022-09-21 11:13:56,792 secondary promoted itself to primary 2022-09-21 11:13:57,279 primary did a HTTP GET request to seconda

is there still a memory leak with hash joins in PG 12.11 ?

2022-09-28 Thread Zwettler Markus (OIZ)
anyone know if this problem is still there in PG 12.11 ? Thanks, Markus

pg_isready mandatory parameters?

2022-11-11 Thread Zwettler Markus (OIZ)
4.4$ ./pg_isready -h localhost -p 5432 -d postgres localhost:5432 - no attempt bash-4.4$ ./pg_isready -h localhost -p 5432 -d postgres -U postgres localhost:5432 - accepting connections I always thought all parameters are optional? No? Thanks, Markus

postgres restore & needed history files

2023-01-03 Thread Zwettler Markus (OIZ)
We are using a DIY Postgres backup: --- psql -c "select pg_start_backup ('Full');" save -s "${NSR_SERVER}" -g "${NSR_POOL}" "${PGDATA}" psql -c "select pg_stop_backup();" --- The pg_wal directory is not saved with it because it is a linked directory. After some time, we had to perform a point-in-

curious postgres (crash) recovery behavior

2023-01-04 Thread Zwettler Markus (OIZ)
We are using Crunchy PGO which uses "pgbackrest". Over Christmas we had a disk full error on the "pgbackrest" repo followed by a disk full error on the PostgreSQL instance pod. Unfortunately, a colleague then deleted the current "pg_wal" directory on the instance pod. So we had to do a point-in-t

AW: [Extern] Re: postgres restore & needed history files

2023-01-12 Thread Zwettler Markus (OIZ)
> -Ursprüngliche Nachricht- > Von: Laurenz Albe > Gesendet: Freitag, 6. Januar 2023 06:28 > An: Zwettler Markus (OIZ) ; pgsql- > gene...@lists.postgresql.org > Betreff: [Extern] Re: postgres restore & needed history files > > On Tue, 2023-01-03 at 16:03 +,

AW: AW: [Extern] Re: postgres restore & needed history files

2023-01-13 Thread Zwettler Markus (OIZ)
> -Ursprüngliche Nachricht- > Von: Laurenz Albe > Gesendet: Freitag, 13. Januar 2023 11:25 > An: Zwettler Markus (OIZ) ; pgsql- > gene...@lists.postgresql.org > Betreff: Re: AW: [Extern] Re: postgres restore & needed history files > > On Thu, 2023-01-12 at

Is a logical replication crash recovery safe?

2023-01-24 Thread Zwettler Markus (OIZ)
Is a logical replication crash recovery safe? Thanks, Markus

AW: [Extern] Re: Is a logical replication crash recovery safe?

2023-01-24 Thread Zwettler Markus (OIZ)
> -Ursprüngliche Nachricht- > Von: Christophe Pettus > Gesendet: Dienstag, 24. Januar 2023 16:48 > An: Zwettler Markus (OIZ) > Cc: pgsql-general@lists.postgresql.org > Betreff: [Extern] Re: Is a logical replication crash recovery safe? > > > > > On

How to install vacuumlo on a client?

2023-03-23 Thread Zwettler Markus (OIZ)
I want to install vacuumlo on a client. I would install client + contrib + libs package: yum install postgresql15 postgresql15-contrib postgresql15-libs Is this enough or are there some other rpm / libs needed?

Postgres Enhancement Request

2019-03-20 Thread Zwettler Markus (OIZ)
CREATEROLE allows users to create new roles also having the CREATEDB privilege (at least in version 9.6). We want special users to be able to CREATEROLE without being able to CREATEDB (eg. when usermanagement is done by the application itself). Please prevent users with CREATEROLE to create rol

AW: Postgres Enhancement Request

2019-03-20 Thread Zwettler Markus (OIZ)
We already did and use this at the moment. Unfortunately. Some out-of-the-box applications can't use functions for user management. Some users don't want "special" functions for user management. ... Markus -Ursprüngliche Nachricht- Von: Thomas Kellerer Gesendet

AW: Postgres Enhancement Request

2019-03-20 Thread Zwettler Markus (OIZ)
e never been granted. Markus -Ursprüngliche Nachricht- Von: Tom Lane Gesendet: Mittwoch, 20. März 2019 15:30 An: Thomas Kellerer Cc: pgsql-general@lists.postgresql.org Betreff: Re: Postgres Enhancement Request Thomas Kellerer writes: > Tom Lane schrieb am 20.03.2019 um 14:59: &

Cast INTEGER to BIT confusion

2023-08-15 Thread [Quipsy] Markus Karg
er AS bit) WITH INOUT AS IMPLICIT; Unfortunately that tells me: cast from type integer to type bit already exists This is confusing! Apparently PostgreSQL 15.3 comes with the needed cast out-of-the-box but it does not apply it? This is confusing! What is my fault? Thanks! -Markus

AW: Cast INTEGER to BIT confusion

2023-08-15 Thread [Quipsy] Markus Karg
the database? Thanks a lot! -Markus -Ursprüngliche Nachricht- Von: Erik Wienhold Gesendet: Dienstag, 15. August 2023 13:48 An: [Quipsy] Markus Karg ; pgsql-general@lists.postgresql.org Betreff: Re: Cast INTEGER to BIT confusion > On 15/08/2023 10:49 CEST [Quipsy] Markus Karg wr

AW: Cast INTEGER to BIT confusion

2023-08-15 Thread [Quipsy] Markus Karg
the search path is ignored?! -Markus -Ursprüngliche Nachricht- Von: Erik Wienhold Gesendet: Dienstag, 15. August 2023 13:48 An: [Quipsy] Markus Karg ; pgsql-general@lists.postgresql.org Betreff: Re: Cast INTEGER to BIT confusion > On 15/08/2023 10:49 CEST [Quipsy] Markus Karg wro

AW: PostgreSQL and GUI management

2023-08-15 Thread [Quipsy] Markus Karg
I am using pg_admin in the browser, and it works rather fine for me. -Markus Von: Jason Long Gesendet: Dienstag, 15. August 2023 17:09 An: pgsql-general@lists.postgresql.org Betreff: PostgreSQL and GUI management Hello, Does PostgreSQL have a graphical environment for management or is it only

AW: AW: Cast INTEGER to BIT confusion

2023-08-17 Thread [Quipsy] Markus Karg
seems it is simply impossible to run this application on PostgreSQL, and we have to stick with a different RDBMS. Very sad. -Markus -Ursprüngliche Nachricht- Von: Erik Wienhold Gesendet: Dienstag, 15. August 2023 16:28 An: [Quipsy] Markus Karg ; pgsql-general@lists.postgresql.org

moving data with pg_dump/pg_restore between database with different locale

2023-10-11 Thread Zwettler Markus (OIZ)
/locale.html#LOCALE-BEHAVIOR Thanks, Markus

best migration solution

2024-04-25 Thread Zwettler Markus (OIZ)
we have to migrate from hosted PG12 to containerized PG16 on private cloud. some of the installed PG12 extensions are not offered on the containerized PG16, eg. PostGIS related extensions like pg_routing and ogr_fdw. some of these extensions are not needed anymore. some of these extensions were

how to completely turn off statement error logging

2024-05-13 Thread Zwettler Markus (OIZ)
I don't want to log statement errors in the server logfile - whether the statement string nor the error message. I set "log_min_error_statement = panic" according to the docs: To effectively turn off logging of failing statements, set this parameter to PANIC. But error messages are still l

AW: [Extern] Re: how to completely turn off statement error logging

2024-05-13 Thread Zwettler Markus (OIZ)
> Von: Tom Lane > Gesendet: Montag, 13. Mai 2024 16:26 > An: Zwettler Markus (OIZ) > Cc: pgsql-general@lists.postgresql.org > Betreff: [Extern] Re: how to completely turn off statement error logging > > "Zwettler Markus (OIZ)" writes: > > I don't

PG16.1 security breach?

2024-06-07 Thread Zwettler Markus (OIZ)
gres | UTF8 | libc| de_CH.utf-8 | de_CH.utf-8 | | | =Tc/postgres + | | | | | | | | postgres=CTc/postgres (1 row) What I am missing? Is there something new with PG 16? Is it a bug? Cheers, Markus

AW: [Extern] Re: PG16.1 security breach?

2024-06-07 Thread Zwettler Markus (OIZ)
> -Ursprüngliche Nachricht- > Von: Joe Conway > Gesendet: Freitag, 7. Juni 2024 15:22 > An: Zwettler Markus (OIZ) ; pgsql- > gene...@lists.postgresql.org > Betreff: [Extern] Re: PG16.1 security breach? > > On 6/7/24 07:04, Zwettler Markus (OIZ) wrote: > >

question on plain pg_dump file usage

2024-09-17 Thread Zwettler Markus (OIZ)
to a plain text file. Thanks, Markus

  1   2   >