Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oxkeL-0001Mm-Sf for pgsql-admin@arkaria.postgresql.org; Wed, 23 Nov 2022 07:58:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oxkeK-0000tM-Np for pgsql-admin@arkaria.postgresql.org; Wed, 23 Nov 2022 07:58:28 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oxkeK-0000tB-4v for pgsql-admin@lists.postgresql.org; Wed, 23 Nov 2022 07:58:28 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oxkeH-0002G5-Dk for pgsql-admin@lists.postgresql.org; Wed, 23 Nov 2022 07:58:27 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id B845E380ED0; Wed, 23 Nov 2022 08:58:22 +0100 (CET) Received: from rs.plausibolo.de ([127.0.0.1]) by localhost (h2367442.stratoserver.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 9QDVuP0H9hNi; Wed, 23 Nov 2022 08:58:22 +0100 (CET) Received: from [192.168.178.28] (ip-037-201-152-231.um10.pools.vodafone-ip.de [37.201.152.231]) by rs.plausibolo.de (Postfix) with ESMTPSA id 4788538050F; Wed, 23 Nov 2022 08:58:22 +0100 (CET) Message-ID: Date: Wed, 23 Nov 2022 08:58:21 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.1 To: pgsql-admin@lists.postgresql.org References: <166892540450.1063.16335933262924996258@malur.postgresql.org> From: Holger Jakobs Subject: Re: Default Sort order Problem - Newbie ;-) In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------0cryw7iEWfFdJwgVm6PCBzEG" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --------------0cryw7iEWfFdJwgVm6PCBzEG Content-Type: multipart/mixed; boundary="------------bMqMuoIyLWywAjXjqjpzaNti"; protected-headers="v1" From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Message-ID: Subject: Re: Default Sort order Problem - Newbie ;-) References: <166892540450.1063.16335933262924996258@malur.postgresql.org> In-Reply-To: --------------bMqMuoIyLWywAjXjqjpzaNti Content-Type: multipart/alternative; boundary="------------0a8b7syNgZiwID6OAqwFj6Ki" --------------0a8b7syNgZiwID6OAqwFj6Ki Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: base64 QW0gMjMuMTEuMjIgdW0gMDM6MjMgc2NocmllYiBGcmFueiBYYXZlcjoNCj4NCj4gSGVsbG8s DQo+DQo+IGkgYW0gdXBkYXRpbmcgR1ZNIG9uIGFuIGV4aXN0aW5nIFN5c3RlbSAoS2FsaSBM aW51eCkuIFRoZXJl4oCZcyBhIA0KPiBzdHJhbmdlIFByb2JsZW0gaSBjYW4gbm90IHNvbHZl IG15c2VsZiBhbmQgZXZlbiBHb29nbGUgc2VhcmNoIGRvZXMgbm90IA0KPiBicmluZyBtZSBm dXJ0aGVyLg0KPg0KV2hhdCBpcyBHVk0/IE9idmlvdXNseSBub3RoaW5nIFBvc3RncmVzIHJl bGF0ZWQuDQoNCj4gVGhlcmXigJlzIHR3byBjbHVzdGVycyBhbmQgaSBoYWQgYmVlbiBhYmxl IHRvIHN3aXRjaCB0aGUgRGVmYXVsdCB0byAxNQ0KPg0KPiBDOlxob21lXGthbGk+IHBnX2xz Y2x1c3RlcnMNCj4NCj4gVmVyIENsdXN0ZXIgUG9ydCBTdGF0dXMgT3duZXLCoMKgwqAgRGF0 YSBkaXJlY3RvcnnCoMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoCBMb2cgZmlsZQ0KPg0KPiAx NMKgIG1haW7CoMKgwqAgNTQzMyBvbmxpbmUgcG9zdGdyZXMgL3Zhci9saWIvcG9zdGdyZXNx bC8xNC9tYWluIA0KPiAvdmFyL2xvZy9wb3N0Z3Jlc3FsL3Bvc3RncmVzcWwtMTQtbWFpbi5s b2cNCj4NCj4gMTXCoCBtYWluwqDCoMKgIDU0MzIgb25saW5lIHBvc3RncmVzIC92YXIvbGli L3Bvc3RncmVzcWwvMTUvbWFpbiANCj4gL3Zhci9sb2cvcG9zdGdyZXNxbC9wb3N0Z3Jlc3Fs LTE1LW1haW4ubG9nDQo+DQo+IEJ1dCB0aGUgZGF0YWJhc2UgY2FuIG5vdCBiZSBjcmVhdGVk IGR1ZSB0byBlcnJvcjoNCj4NCj4gV0FSTlVORzrCoCBWZXJzaW9uIHZvbiBTb3J0aWVyZm9s Z2UgZsO8ciBEYXRlbmJhbmsgwrtwb3N0Z3Jlc8KrIHN0aW1tdCANCj4gbmljaHQgw7xiZXJl aW4NCj4NCj4gREVUQUlMOsKgIERpZSBEYXRlbmJhbmsgd3VyZGUgbWl0IFNvcnRpZXJmb2xn ZW52ZXJzaW9uIDIuMzUgZXJ6ZXVndCwgDQo+IGFiZXIgZGFzIEJldHJpZWJzc3lzdGVtIGhh dCBWZXJzaW9uIDIuMzYuDQo+DQo+IFRJUDrCoCBCYXVlbiBTaWUgYWxsZSBPYmpla3RlIGlu IGRpZXNlciBEYXRlbmJhbmssIGRpZSBkaWUgDQo+IFN0YW5kYXJkc29ydGllcmZvbGdlIHZl cndlbmRlbiwgbmV1IHVuZCBmw7xocmVuIFNpZSBBTFRFUiBEQVRBQkFTRSANCj4gcG9zdGdy ZXMgUkVGUkVTSCBDT0xMQVRJT04gVkVSU0lPTiBhdXMsIG9kZXIgYmF1ZW4gU2llIFBvc3Rn cmVTUUwgbWl0IA0KPiBkZXIgcmljaHRpZ2VuIEJpYmxpb3RoZWtzdmVyc2lvbi4NCj4NCj4g cHNxbCAoMTUuMSAoRGViaWFuIDE1LjEtMSkpDQo+DQpZb3Ugd3JvdGUgIkJ1dCB0aGUgZGF0 YWJhc2UgY2FuIG5vdCBiZSBjcmVhdGVkIGR1ZSB0byBlcnJvciIuDQoNCkl0J3Mgd2Fybmlu Zywgbm90IGFuIGVycm9yLiBJdCBhcHBlYXJzIHdoZW4gU1RBUlRJTkcgdGhlIGRhdGFiYXNl IHNlcnZlciANCihub3QgY3JlYXRpbmcpLiBUaGUgbWVzc2FnZSBzYXlzIHRoYXQgdGhlIGRh dGFiYXNlIFdBUyBjcmVhdGVkIGVhcmxpZXIgDQp3aXRoIGEgZGlmZmVyZW50IHZlcnNpb24g b2YgdGhlIHNvcnRpbmcgbGlicmFyeS4NCg0KPiDigJ5Tb3J0aWVyZm9sZ2VudmVyc2lvbuKA nCBzZWVtcyB0byBiZSDigJ5zb3J0IG9yZGVy4oCcIGluIGVuZ2xpc2g/DQo+DQo+IEkgdHJp ZWQgdGhlIGdpdmVuIGNvbW1hbmQgd2l0aCBubyBzdWNjZXNzLiBUaGUgZGF0YWJhc2UgY2Fu IG5vdCBiZSANCj4gY3JlYXRlZDoNCj4NCj4gQzpcaG9tZVxrYWxpPiBzdWRvIHJ1bnVzZXIg LXUgcG9zdGdyZXMgLS0gDQo+IC91c3Ivc2hhcmUvZ3ZtL2NyZWF0ZS1wb3N0Z3Jlc3FsLWRh dGFiYXNlIDEg4qivDQo+DQo+IFdBUk5VTkc6wqAgVmVyc2lvbiB2b24gU29ydGllcmZvbGdl IGbDvHIgRGF0ZW5iYW5rIMK7cG9zdGdyZXPCqyBzdGltbXQgDQo+IG5pY2h0IMO8YmVyZWlu DQo+DQo+IERFVEFJTDrCoCBEaWUgRGF0ZW5iYW5rIHd1cmRlIG1pdCBTb3J0aWVyZm9sZ2Vu dmVyc2lvbiAyLjM1IGVyemV1Z3QsIA0KPiBhYmVyIGRhcyBCZXRyaWVic3N5c3RlbSBoYXQg VmVyc2lvbiAyLjM2Lg0KPg0KPiBUSVA6wqAgQmF1ZW4gU2llIGFsbGUgT2JqZWt0ZSBpbiBk aWVzZXIgRGF0ZW5iYW5rLCBkaWUgZGllIA0KPiBTdGFuZGFyZHNvcnRpZXJmb2xnZSB2ZXJ3 ZW5kZW4sIG5ldSB1bmQgZsO8aHJlbiBTaWUgQUxURVIgREFUQUJBU0UgDQo+IHBvc3RncmVz IFJFRlJFU0ggQ09MTEFUSU9OIFZFUlNJT04gYXVzLCBvZGVyIGJhdWVuIFNpZSBQb3N0Z3Jl U1FMIG1pdCANCj4gZGVyIHJpY2h0aWdlbiBCaWJsaW90aGVrc3ZlcnNpb24uDQo+DQo+IFtp XSBVc2VyIF9ndm0gYWxyZWFkeSBleGlzdHMgaW4gUG9zdGdyZVNRTA0KPg0KPiBXQVJOVU5H OsKgIFZlcnNpb24gdm9uIFNvcnRpZXJmb2xnZSBmw7xyIERhdGVuYmFuayDCu3Bvc3RncmVz wqsgc3RpbW10IA0KPiBuaWNodCDDvGJlcmVpbg0KPg0KPiBERVRBSUw6wqAgRGllIERhdGVu YmFuayB3dXJkZSBtaXQgU29ydGllcmZvbGdlbnZlcnNpb24gMi4zNSBlcnpldWd0LCANCj4g YWJlciBkYXMgQmV0cmllYnNzeXN0ZW0gaGF0IFZlcnNpb24gMi4zNi4NCj4NCj4gVElQOsKg IEJhdWVuIFNpZSBhbGxlIE9iamVrdGUgaW4gZGllc2VyIERhdGVuYmFuaywgZGllIGRpZSAN Cj4gU3RhbmRhcmRzb3J0aWVyZm9sZ2UgdmVyd2VuZGVuLCBuZXUgdW5kIGbDvGhyZW4gU2ll IEFMVEVSIERBVEFCQVNFIA0KPiBwb3N0Z3JlcyBSRUZSRVNIIENPTExBVElPTiBWRVJTSU9O IGF1cywgb2RlciBiYXVlbiBTaWUgUG9zdGdyZVNRTCBtaXQgDQo+IGRlciByaWNodGlnZW4g QmlibGlvdGhla3N2ZXJzaW9uLg0KPg0KPiBVbmZvcnR1bmF0ZWx5IGNoYW5naW5nIGxvY2Fs ZXMgdG8gZW4gZGlkIG5vdCBDaGFuZ2UgdGhlIGxhbmd1YWdlIG9mIA0KPiB0aGUgTWVzc2Fn ZXMgYnV0IGhvcGZ1bGx5IGl0IGlzIGVhc3kgZm9yIGFuIGV4cGVyIHRvIGZpbmQgdGhlIGNh dXNlIG9mIA0KPiBteSBQcm9ibGVtcy4NCj4NCkhhdmUgeW91IHRyaWVkIHRoZSBjb21tYW5k IEFMVEVSIERBVEFCQVNFIC4uLiBSRUZSRVNIIENPTExBVElPTiBWRVJTSU9OIA0KZm9yIGFs bCBkYXRhYmFzZXMgaW4geW91ciBjbHVzdGVyPyBUaGF0IHdhcyB0aGUgaGludCBpbiB0aGUg d2FybmluZy4NCj4NCj4gV2hhdCBOZWVkcyB0byBiZSBkb25lIHRvIGZpeCB0aGlzIGlzc3Vl Pw0KPg0KPiBTb3JyeSDigJMgeWVzIGkgYW0gYSBuZXdiaWUgd2l0aCBwb3N0Z3Jlc3MuDQo+ DQo+IFRoeCBQT1cNCj4NCklmIHlvdSBpZ25vcmUgdGhlIHdhcm5pbmcsIHRoZSBkYXRhYmFz ZSB3aWxsIHN0aWxsIHdvcmssIGJ1dCBpbmRleGVzIA0Kb3ZlciB0ZXh0IHZhbHVlcyB3b24n dCBmaW5kIGFsbCBlbnRyaWVzLiBOdW1iZXJzIGFuZCBkYXRlcyBhcmUgbm90IGFmZmVjdGVk Lg0KDQpSZS1JbmRleGluIGV2ZXJ5dGhpbmcgbWF5IGFsc28gaGVscC4NCg0KVmllbGUgR3LD vMOfZQ0KDQpIb2xnZXINCg0KLS0gDQpIb2xnZXIgSmFrb2JzLCBCZXJnaXNjaCBHbGFkYmFj aCwgVGVsLiArNDktMTc4LTk3NTkwMTINCg0K --------------0a8b7syNgZiwID6OAqwFj6Ki Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Am 23.11.22 um 03:23 schrieb Franz Xaver:

Hello,

=C2=A0

i am updating GVM on an existing System (Kali Linux). There=E2=80=99s a strange Problem i can not solve= myself and even Google search does not bring me further.

What is GVM? Obviously nothing Postgres related.

=C2=A0

There=E2=80=99s two clusters and i had bee= n able to switch the Default to 15

=C2=A0

C:\home\kali> pg_lsclusters<= /p>

Ver Cluster Port Status Owner=C2=A0=C2=A0=C2= =A0 Data directory=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 Log file

14=C2=A0 main=C2=A0=C2=A0=C2=A0 5433 onlin= e postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log

15=C2=A0 main=C2=A0=C2=A0=C2=A0 5432 onlin= e postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log

=C2=A0

But the database can not be created due to= error:

=C2=A0

WARNUNG:=C2=A0 Version von Sortierfolge f=C3= =BCr Datenbank =C2=BBpostgres=C2=AB stimmt nicht =C3=BCberein

DETAIL:=C2=A0 Die Datenbank wurde mit Sortierfolgenversion 2.35 erzeugt, aber das Betriebssystem hat Version 2.36.

TIP:=C2=A0 Bauen Sie alle Objekte in diese= r Datenbank, die die Standardsortierfolge verwenden, neu und f=C3=BChren Sie ALTER DATABASE postgres REFRESH COLLATION VERSI= ON aus, oder bauen Sie PostgreSQL mit der richtigen Bibliotheksversion.

psql (15.1 (Debian 15.1-1))

=

=C2=A0

You wrote "But the database can not be created due to error".

It's warning, not an error. It appears when STARTING the database server (not creating). The message says that the database WAS created earlier with a different version of the sorting library.

=E2=80=9ESortierfolgenversion=E2=80=9C see= ms to be =E2=80=9Esort order=E2=80=9C in english?

=C2=A0

I tried the given command with no success.= The database can not be created:

=C2=A0

C:\home\kali> sudo runuser -u postgres -- /usr/share/gvm/create-postgresql-database=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 1 =E2=A8= =AF

WARNUNG:=C2=A0 Version von Sortierfolge f=C3= =BCr Datenbank =C2=BBpostgres=C2=AB stimmt nicht =C3=BCberein

DETAIL:=C2=A0 Die Datenbank wurde mit Sortierfolgenversion 2.35 erzeugt, aber das Betriebssystem hat Version 2.36.

TIP:=C2=A0 Bauen Sie alle Objekte in diese= r Datenbank, die die Standardsortierfolge verwenden, neu und f=C3=BChren Sie ALTER DATABASE postgres REFRESH COLLATION VERSI= ON aus, oder bauen Sie PostgreSQL mit der richtigen Bibliotheksversion.

[i] User _gvm already exists in PostgreSQL=

WARNUNG:=C2=A0 Version von Sortierfolge f=C3= =BCr Datenbank =C2=BBpostgres=C2=AB stimmt nicht =C3=BCberein

DETAIL:=C2=A0 Die Datenbank wurde mit Sortierfolgenversion 2.35 erzeugt, aber das Betriebssystem hat Version 2.36.

TIP:=C2=A0 Bauen Sie alle Objekte in diese= r Datenbank, die die Standardsortierfolge verwenden, neu und f=C3=BChren Sie ALTER DATABASE postgres REFRESH COLLATION VERSI= ON aus, oder bauen Sie PostgreSQL mit der richtigen Bibliotheksversion.

=C2=A0

Unfortunately changing locales to en did not Change the language of the Messages but hopfully it is easy for an exper to find the cause of my Problems.<= /p>

=C2=A0

Have you tried the command ALTER DATABASE ... REFRESH COLLATION VERSION for all databases in your cluster? That was the hint in the warning.

What Needs to be done to fix this issue?

=C2=A0

Sorry =E2=80=93 yes i am a newbie with pos= tgress.

=C2=A0

Thx POW

If you ignore the warning, the database will still work, but indexes over text values won't find all entries. Numbers and dates are not affected.

Re-Indexin everything may also help.

Viele Gr=C3=BC=C3=9Fe

Holger

=C2=A0

--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--------------0a8b7syNgZiwID6OAqwFj6Ki-- --------------bMqMuoIyLWywAjXjqjpzaNti-- --------------0cryw7iEWfFdJwgVm6PCBzEG Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCY33SnQUDAAAAAAAKCRCKV5yTsxlhunr3 AKCnYsFDsZ0B018plKjUaJMIXiv5UgCgtoTtAab3X1OWm32xdiZa5UKlwkc= =JAwd -----END PGP SIGNATURE----- --------------0cryw7iEWfFdJwgVm6PCBzEG--