Re: Re: could not accept ssl connection tlsv1 alert unknown ca

> -Ursprüngliche Nachricht- > Von: Zwettler Markus (OIZ) > Gesendet: Montag, 3. Februar 2025 09:37 > An: Adrian Klaver ; Tom Lane > ; pgsql-general@lists.postgresql.org > Betreff: Re: Re: could not accept ssl connection tlsv1 alert unknown ca > > > -

Re: Re: could not accept ssl connection tlsv1 alert unknown ca

> -Ursprüngliche Nachricht- > Von: Adrian Klaver > Gesendet: Freitag, 31. Januar 2025 18:07 > An: Zwettler Markus (OIZ) ; Tom Lane > ; pgsql-general@lists.postgresql.org > Betreff: [Extern] Re: could not accept ssl connection tlsv1 alert unknown ca > > On 1/31/2

Re: Re: could not accept ssl connection tlsv1 alert unknown ca

> -Ursprüngliche Nachricht- > Von: Adrian Klaver > Gesendet: Freitag, 31. Januar 2025 17:37 > An: Zwettler Markus (OIZ) ; Tom Lane > ; pgsql-general@lists.postgresql.org > Betreff: [Extern] Re: could not accept ssl connection tlsv1 alert unknown ca > > On 1/31/2

Re: Re: could not accept ssl connection tlsv1 alert unknown ca

> Von: Tom Lane > Gesendet: Donnerstag, 30. Januar 2025 18:51 > An: Zwettler Markus (OIZ) > Cc: pgsql-general@lists.postgresql.org > Betreff: [Extern] Re: could not accept ssl connection tlsv1 alert unknown ca > > "Zwettler Markus (OIZ)" writes: > > How

could not accept ssl connection tlsv1 alert unknown ca

We wanted to use pure ssl encryption without certificate validation. We created and configured self-signed certificates at the postgres server, turned "sslmode=on" and advised our clients to use "sslmode=prefer". This worked very well. However, one client also configured some client certificate

question on timezone with pgBackRest PITR

The Postgres instance is running on timezone CEST (parameter timezone = 'Europe/Zurich') which is UTC+2. The underlying Linux OS (K8s container) is running on timezone UTC. Backups are done with pgBackRest. With PITR we will have to use target timezone CEST (--target=2024-09-19 17:00:00+02") a

WG: question on plain pg_dump file usage

Von: Zwettler Markus (OIZ) Gesendet: Dienstag, 17. September 2024 14:22 An: PG-General Mailing List Betreff: question on plain pg_dump file usage I have to do an out-of-place Postgres migration from PG12 to PG16 using: pg_dump -F p -f dump.sql ... sed -i "s/old_name/new_name/g&quo

WG: [Extern] Re: question on plain pg_dump file usage

Von: Ron Johnson Gesendet: Dienstag, 17. September 2024 14:44 An: PG-General Mailing List Betreff: [Extern] Re: question on plain pg_dump file usage On Tue, Sep 17, 2024 at 8:22 AM Zwettler Markus (OIZ) mailto:markus.zwett...@zuerich.ch>> wrote: I have to do an out-of-place Postgres mig

question on plain pg_dump file usage

to a plain text file. Thanks, Markus

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

> -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: > >

PG16.1 security breach?

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: how to completely turn off statement error logging

> 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

how to completely turn off statement error logging

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

best migration solution

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

Postgres > 12 with Windows Server 2012

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

moving data with pg_dump/pg_restore between database with different locale

/locale.html#LOCALE-BEHAVIOR Thanks, Markus

AW: AW: Cast INTEGER to BIT confusion

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

AW: PostgreSQL and GUI management

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: Cast INTEGER to BIT confusion

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: Cast INTEGER to BIT confusion

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

Cast INTEGER to BIT confusion

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

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

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

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

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.

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: Binding Postgres to port 0 for testing

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: Binding Postgres to port 0 for testing

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

Binding Postgres to port 0 for testing

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

How to install vacuumlo on a client?

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?

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

> -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

Is a logical replication crash recovery safe?

Is a logical replication crash recovery safe? Thanks, Markus

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

> -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

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

> -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 +,

curious postgres (crash) recovery behavior

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

postgres restore & needed history files

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-

pg_isready mandatory parameters?

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

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

anyone know if this problem is still there in PG 12.11 ? Thanks, Markus

Patroni question

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

AW: [Extern] Re: consistent postgresql snapshot

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

consistent postgresql snapshot

g in 2 fs blocks per db block. Thanks, Markus

AW: [Extern] Re: postgres event trigger workaround

> > Hi, > > On Wed, Jan 12, 2022 at 11:57:45AM +0000, Zwettler Markus (OIZ) wrote: > > > > PG event triggers are not firing on CREATE ROLE, CREATE DATABASE, > > CREATE TABLESPACE by definition (would be nice if they do). > > > > Is there any workaround t

postgres event trigger workaround

Hi, PG event triggers are not firing on CREATE ROLE, CREATE DATABASE, CREATE TABLESPACE by definition (would be nice if they do). Is there any workaround to react with ddl_command_start behavior on such an event? Thanks, Markus

Re: Getting json-value as varchar

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: Getting json-value as varchar

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: SELECT DISTINCT scans the table?

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

SELECT DISTINCT scans the table?

the information that there are constants in the select clauses? Thanks, Markus

AW: error connecting to pgbouncer admin console

Simply a bug. https://github.com/pgbouncer/pgbouncer/issues/568 Cheers, Markus > -Ursprüngliche Nachricht- > Von: Zwettler Markus (OIZ) > Gesendet: Dienstag, 7. Dezember 2021 17:10 > An: pgsql-general@lists.postgresql.org > Betreff: error connecting to pgbouncer admin

error connecting to pgbouncer admin console

-07 16:57:00.969 CET [73046] FATAL @src/objects.c:312 in function put_in_order(): put_in_order: found existing elem $ pgbouncer --version PgBouncer 1.16.1 libevent 2.0.21-stable adns: libc-2.17 tls: OpenSSL 1.0.2k-fips 26 Jan 2017 Any idea? Thanks, Markus

Re: Match 2 words and more

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

postgres ssl client certificate authentification

TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. pcl_l222=> Any idea? Thanks, Markus

change ownership of schema public?

e.g. regarding extensions? Thanks, Markus

AW: AW: [Extern] Re: autovacuum on pg_catalog tables

); ERROR: AccessExclusiveLock required to add toast table. Time: 1.601 ms Any idea? Thanks, Markus Von: Zwettler Markus (OIZ) Gesendet: Freitag, 4. Juni 2021 17:44 An: Vijaykumar Jain Cc: pgsql-general@lists.postgresql.org Betreff: AW: [Extern] Re: autovacuum on pg_catalog tables Thanks

AW: [Extern] Re: autovacuum on pg_catalog tables

Thanks for the info. I have a lot of LO manipulation and want a more aggressive autovacuum on some pg_catalog tables therefore. I do not see any reason why this should not work or be at risk? Markus Von: Vijaykumar Jain Gesendet: Freitag, 4. Juni 2021 17:37 An: Zwettler Markus (OIZ) Cc

autovacuum on pg_catalog tables

: "pg_largeobject_metadata" is a system catalog (How) Is it possible to change such table attributes on pg_catalog tables? Thanks, Markus

AW: [Extern] Re: Advice on binary installation

> -Ursprüngliche Nachricht- > Von: Paul Förster > Gesendet: Dienstag, 13. April 2021 15:02 > An: Zwettler Markus (OIZ) > Cc: pgsql-gene...@postgresql.org > Betreff: [Extern] Re: Advice on binary installation > > Hi Markus, > > On 13. Apr, 2021, at 14:43,

Advice on binary installation

install all global needed extensions or software with every project to get a homogenous binary installation. Software installation is done with yum using PGDG downstream channels. Any recommendations? Thanks, Markus

AW: Re: PostgreSQL container crash trouble.

Hi Laurenz, You said use your backup. Wouldn't it be enough to start instance crash recovery just before the corrupted wal in that case? recovery_target_lsn = <> recovery_target_inclusive = off No need for a backup. Correct? Cheers, Markus > -Ursprüngliche Nachri

curious vacuum full behavior

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

AW: running vacuumlo periodically

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

AW: running vacuumlo periodically?

> -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

running vacuumlo periodically?

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

open service broker api for local PGDG Postgres

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

AW: ldap connection parameter lookup

> -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)

Re: Parallelism on Partitioning .

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

ldap connection parameter lookup

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: AW: FDW using remote ODBC driver

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

AW: FDW using remote ODBC driver

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: FDW using remote ODBC driver

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

FDW using remote ODBC driver

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: How to debug authentication issues in Postgres

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

AW: Linux package upgrade without dependency conflicts

> -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: AW: Linux package upgrade without dependency conflicts

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

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

Linux package upgrade without dependency conflicts

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

pgpool-II native replication

tiple backends." Has anyone ever used pgpool-II native replication to build an HA environment? Thanks, Markus

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

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

AW: pg_dump / pg_restore version confusion

=> 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:

AW: pg_dump / pg_restore version confusion

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

pg_dump / pg_restore version confusion

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: Linux Update Experience

> -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

AW: Linux Update Experience

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

Linux Update Experience

nks, Markus

Re: Practical usage of large objects.

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

AW: Which commands are guaranteed to drop role

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

AW: vacuum full doubled database size

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

pg_upgrade 9.6 to 12 without 9.6 binaries

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

vacuum full doubled database size

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?

gdal version for Postgis 2.4?

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

periodic refresh of pre-production stages

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

How to log pg_terminate_backend() calls

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?

Re: install postgres

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

AW: AW: secure deletion of archived logs

> > 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

AW: PostgreSQL HA FO question

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: PostgreSQL HA FO question

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: archiving question

> 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: archiving question

> -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

> > 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: secure deletion of archived logs

> 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

archiving question

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

secure deletion of archived logs

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

AW: sql query for postgres replication check

> 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

  1   2   >