Re: install postgres

[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 way to download rpm.(for  redhat 6 and  redhat 7 x64 machines)
NOT the noarch, but the full distro.
Reason is I have to install postgres on a network with NO internet access.

Thanks,

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com 

Re: Practical usage of large objects.

[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 (>4GB files). Really 
useful in a cloud environment.


I was interested in speed camparison myself and made a similar test with 
network connection and without pg specific code.

https://github.com/5UtJAjiRWj1q/psql-lob-performance

File access is really fast and lo access is much slower than bytea (as 
expected). But content size limitation and memory consumption for bytea 
is problematic.


regards
Thomas

Re: Parallelism on Partitioning .

[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 parallel 
query execution.


Thanks..


Your PG version is too old. 9.6 is not able to this (as I remember)

Thomas

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

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

2.
$ psql -A -t -c "select encode(sha512('A'), 'hex')"
21b4f4bd9e64ed355c3eb676a28ebedaf6d8f17bdc365995b319097153044080516bd083bfcce66121a3072646994c8430cc382b8dc543e84880183bf856cff5
Thanks!
--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 

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

[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 own DNS alias and corresponding port.
I only have one single IP address available, and there is no possibility of 
obtaining additional addresses.

Here's an example:

DNS ALIAS   Host
─> pgs1.server.net:5432 ─> PG_Cluster1@192.168.0.1:5432
─> pgs2.server.net:5433 ─> PG_Cluster2@192.168.0.1:5433
─> pgs3.server.net:5434 ─> PG_Cluster3@192.168.0.1:5434
─> pgs4.server.net:5435 ─> PG_Cluster4@192.168.0.1:5435
─> pgs5.server.net:5436 ─> PG_Cluster5@192.168.0.1:5436


Desired solution:
I still want to use the same system with different PostgreSQL clusters for the 
various databases.
These clusters are internally accessible through different ports.
However, I would like a service on the server to receive all external requests 
on port 5432 and forward them to the corresponding internal cluster based on 
the DNS alias.
It would also be desirable if this service could provide additional features 
like connection pooling or other functionalities.
Similar to a reverse proxy.

Here's an example:

DNS ALIAS HOST
─> pgs1.server.net:5432 ─┐   ┌──┬──> PG_Cluster1@localhost:5433
─> pgs2.server.net:5432 ─┤   │  ├──> PG_Cluster2@localhost:5434
─> pgs3.server.net:5432 ─┼─>─┤ 192.168.0.1:5432 ├──> PG_Cluster3@localhost:5435
─> pgs4.server.net:5432 ─┤   │  ├──> PG_Cluster4@localhost:5436
─> pgs5.server.net:5432 ─┘   └──┴──> PG_Cluster5@localhost:5437


Is there a solution for this, and what are the advantages or limitations that 
arise from it?

Thank you in advance for your suggestions and help.

Regards,
Michael




possible solutions:
* set up a firewall rule to forward connection
* use a tcp proxy (nginx can do that)
* check pg_bouncer

best regards
Thomas




OpenPGP_0x9794716335E9B5AF.asc
Description: OpenPGP public key


OpenPGP_signature
Description: OpenPGP digital signature

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

[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?
Because with iptables or netfilter I can't forward TCP packets based on the DNS 
alias name. Or is
that possible?

I have the same problem with nginx. I just looked in the documentation again 
but I can't find a way
to distinguish which cluster to forward to based on the DNS alias.
Do you have an example for me?

We have already looked at pgbouncer and it works with that but unfortunately 
you have to do the
authentication in pgbouncer. Which we don't like so much.

Regards,
Michael


You cant setup firewall rules basedon dns names. firewall rules are 
based on ip adresses and dns resolution happens on rule creation.
I dont have an example for nginx. As I remember nginx resolves dns names 
only for variables. So setup a variable with your hostname and use this 
variable in your server definition.


best regards
Thomas



OpenPGP_0x9794716335E9B5AF.asc
Description: OpenPGP public key


OpenPGP_signature
Description: OpenPGP digital signature

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

[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 and therefore can't act on it.

For HTTP this works because the HTTP protocol contains a Host field
which the client fills with the name it used. But the Postgres protocol
has no such information (and in any case nginx probably doesn't
understand that protocol anyway).

So (again, as Francisco already wrote) the best way is probably to write
a simple proxy which uses the database (not DNS) name for routing. I
seem to remember that nginx has a plugin architecture for protocols so
it might make sense to write that as an nginx plugin instead of a
standalone server, but that's really a judgement call the programmer has
to make. Another possibility would of course be to extend pgbouncer to
do what the OP needs.

 hp

yeah I know
I looked at his setup definition only and missed the point that he wants 
a single entry point for all.

The image doesnt match this. Looks like a simple forward proxy definition.

anyway, fw or nginx cant look into tcp streams nor does it makes sense 
to me. Maybe without tls




OpenPGP_0x9794716335E9B5AF.asc
Description: OpenPGP public key


OpenPGP_signature
Description: OpenPGP digital signature

Re: Match 2 words and more

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

Regards,

David

Hi,

It's robust, but syntax is sometimes weired

for words I would use something like (contains numbers too)
"STREET_NAME" ~ '(\w+\s+)+\w+';

or alpha only
"STREET_NAME" ~ '([[:alpha:]]+\s+)+[[:alpha:]]+'

regards
Thomas

Re: Getting json-value as varchar

[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":"value"}'::jsonb)->> 'key';
┌──┐
│ ?column? │
├──┤
│ value    │
└──┘
(1 row)

How to I use the subscript syntax and get the result as varchar 
instead of JSONB, assuming I /know/ the JSON-field is a String?

simply cast your value
SELECT (('{"key":"value"}'::jsonb)->> 'key')::text;

best regards
Thomas

--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com
www.visena.com 

Re: Getting json-value as varchar

[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":"value"}'::jsonb)['key'];
┌─┐
│  jsonb  │
├─┤
│ "value" │
└─┘
(1 row)

and this returns 'value' (without the quotes):
SELECT ('{"key":"value"}'::jsonb)->> 'key';
┌──┐
│ ?column? │
├──┤
│ value    │
└──┘
(1 row)

How to I use the subscript syntax and get the result as varchar
instead of JSONB, assuming I /know/ the JSON-field is a String?

simply cast your value
SELECT (('{"key":"value"}'::jsonb)->> 'key')::text;

best regards
Thoma

I think you misread my message. What I want is for the subscript-version:

('{"key":"value"}'::jsonb)['key']

to return:
┌──┐
│ ?column? │
├──┤
│ value    │
└──┘

instead of
┌─┐
│  jsonb  │
├─┤
│ "value" │
└─┘

yeah right :(

complicated but this should do:
SELECT  jsonb_build_array( ('{"key":"value"}'::jsonb)['key'] ) ->> 0;




--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com
www.visena.com <https://www.visena.com>
<https://www.visena.com>

Re: duplicate primary key

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

 id | integer | not null default 
nextval('player_id_seq'::regclass)

...
Indexes:
    "pk_id" PRIMARY KEY, btree (id)
...

# select * from pg_indexes where indexname='pk_id';
 schemaname | tablename | indexname | tablespace |  indexdef
+---+---++-- 

 public | player    | pk_id |    | CREATE UNIQUE INDEX 
pk_id ON player USING btree (id)


# select * from pg_constraint where conname='pk_id';
-[ RECORD 1 ]-+--
conname   | pk_id
connamespace  | 2200
contype   | p
condeferrable | f
condeferred   | f
convalidated  | t
conrelid  | 18319
contypid  | 0
conindid  | 18640
confrelid | 0
confupdtype   |
confdeltype   |
confmatchtype |
conislocal    | t
coninhcount   | 0
connoinherit  | t
conkey    | {1}
confkey   |
conpfeqop |
conppeqop |
conffeqop |
conexclop |
conbin    |
consrc    |

# select count(*) from player where id=14875;
-[ RECORD 1 ]
count | 2

The records are identical, besides ctid,xmin,xmax

# select tableoid,ctid,id,xmin,xmax from player where id=14875;
 tableoid | ctid |    id |    xmin    |    xmax
--+--+---++
    18319 | (9982129,2)  | 14875 | 3149449600 | 3152681810
    18319 | (9976870,49) | 14875 | 3149448769 | 3152328995



I don't understand how this could have happened


Hi Alex,

we got this with a broken index. Fix data and rebuild them. And check 
your system/storage


Thomas