Re: Delete values from JSON

2023-03-17 Thread Romain MAZIÈRE 

Hi,

If it is jsonb type, you can have a look at the documentation : 
https://www.postgresql.org/docs/14/functions-json.html


There are some examples :

|jsonb| |-| |text| → |jsonb|

Deletes a key (and its value) from a JSON object, or matching string 
value(s) from a JSON array.


|'{"a": "b", "c": "d"}'::jsonb - 'a'| → |{"c": "d"}|

|'["a", "b", "c", "b"]'::jsonb - 'b'| → |["a", "c"]|

|jsonb| |-| |text[]| → |jsonb|

Deletes all matching keys or array elements from the left operand.

|'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]| → |{}|

|jsonb| |-| |integer| → |jsonb|

Deletes the array element with specified index (negative integers count 
from the end). Throws an error if JSON value is not an array.


|'["a", "b"]'::jsonb - 1| → |["a"]|

|jsonb| |#-| |text[]| → |jsonb|

Deletes the field or array element at the specified path, where path 
elements can be either field keys or array indexes.


|'["a", {"b":1}]'::jsonb #- '{1,b}'| → |["a", {}]|

Regards

Romain MAZIÈRE
romain.mazi...@sigmaz-consilium.fr
+33.535.545.085
+33.781.46.36.96
https://sigmaz-consilium.fr

Le 17/03/2023 à 08:56, Andreas Joseph Krogh a écrit :


Hi, in PG-14, how do I delete the keys |"dunsNumber": "NaN"|:

|{ "sessionId": "ce6fc9d0-5923-4d71-9f7e-ae60f35c45d6", "details": [ { 
"keyInformation": { "dunsNumber": "NaN", "organizationType": 
"LIMITED_COMPANY" } }, { "keyInformation": { "dunsNumber": "123", 
"organizationType": "LIMITED_COMPANY" } } ], "nisse": 123 }|


So that the result becomes:

|{ "sessionId": "ce6fc9d0-5923-4d71-9f7e-ae60f35c45d6", "details": [ { 
"keyInformation": { "organizationType": "LIMITED_COMPANY" } }, { 
"keyInformation": { "dunsNumber": "123", "organizationType": 
"LIMITED_COMPANY" } } ], "nisse": 123 }|


Thanks.

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


OpenPGP_0x97C05C89DEC4129F.asc
Description: OpenPGP public key


OpenPGP_signature
Description: OpenPGP digital signature

Re: Query regarding managing Replication

2023-06-27 Thread Romain MAZIÈRE 

Hello,

You can have a look at the parameter : listen_addresses in the file 
postgresql.conf.

By default the value is localhost.

Regards

Romain MAZIÈRE
romain.mazi...@sigmaz-consilium.fr
+33.535.545.085
+33.781.46.36.96
https://sigmaz-consilium.fr

Le 27/06/2023 à 16:04, Ashok Patil a écrit :

Hello Sir/Madam,

I have to perform task of database replication. For that for testing 
purpose I have installed Postgress 14 on two different machine. One is 
primary (We can say it as server) and another one is secondary (stand by).


Below steps i have performed

*On Primary*

1. Update Postgres.conf with below settings
wal_level = hot_standby
full_page_writes = on
wal_log_hints = on
max_wal_senders = 6
max_replication_slots = 6
hot_standby = on
hot_standby_feedback = on

2. update pg_hba.conf
host replication 172.20.32.63/32 <http://172.20.32.63/32> scram-sha-256
host replication 172.20.32.43/32 <http://172.20.32.43/32> scram-sha-256

3. Create a repl_user
psql  -d postgres   -U postgres   -c "CREATE ROLE repl_user LOGIN 
REPLICATION ENCRYPTED PASSWORD 'xxx';"


4. create the replication slot using below command
psql -d postgres -U postgres  -c "SELECT * FROM 
pg_create_physical_replication_slot('standby1', true);"


*On Standby*
*
*
5.Perform a base backup of primary to standby
pg_ctl -D ..\data. stop -mi

6. After stopping the cluster delete the data directory
rmdir /s ..\data

7. run pg_basebackup on the standby to copy primary’s data directory 
to it.
pg_basebackup -D ..\data -Fp -R -Xs -c fast -l 'initial_clone' -P -v 
-h 172.20.32.63 y -U repl_user


but here i am getting error as
pg_basebackup: error: connection to server at "172.20.32.63", port 
5432 failed: Connection timed out (0x274C/10060)
        Is the server running on that host and accepting TCP/IP 
connections?


Will you please let me know which steps is wrong.

Also is there any proper steps given in any document, if yes, will you 
please share that.


Thanks in advance.

Regards,
Ashok

Re: Can't connect to server

2023-03-18 Thread Romain Mazière - SIGMAZ Consilium 
Hi,

As I can see in the error message :
"localhost" (::1), port 5432 failed: FATAL: password authentication failed for 
user "postgres"."
Pgadmin4 try to connect via the loopback in IPv6 (::1) and fail for postgres 
user.
Have a look to pg_hba.conf, the file which describes access to postgres cluster.

Le 18 mars 2023 07:10:47 GMT+01:00, "Wu, Abigaile"  a écrit :
>I am writing to seek your assistance with a problem I am facing while using 
>pgAdmin4. After downloading the PostgreSQL 15, I downloaded the latest version 
>of Pgadmin 4 and then I click the server and choose PostgreSQL 15. Whenever I 
>try to log in to the server after entering my password, I receive the 
>following error message:
>
>"Connection to server at "localhost"(::1), port 5432 failed: could not 
>initiate GSSAPI security context: The operation or option is not available: 
>credential handle connection to server at "localhost" (::1), port 5432 failed: 
>FATAL: password authentication failed for user "postgres"."
>
>Could you please advise me on how to resolve this issue? I would be grateful 
>if you could provide me with any instructions or guidance on what steps I can 
>take to fix this problem and gain access to the server.
>
>Thank you for your time and assistance. I look forward to hearing from you 
>soon.
>
>
>[cid:a456724f-1d8d-4cd9-ba3c-81dd950f3723]
>
>Abigaile Wu
>
>Pronouns: She/Her/Hers
>
>Full-Time MBA Candidate 2023
>
>Graduate Assistant - Admission Team
>
>Broad College of Business
>
>Michigan State University
>
>C: (517) 329 - 5793
>
>E: wuchi...@msu.edu
>
>
>
>WHO WILL MAKE BUSINESS HAPPEN?
>
>SPARTANS WILL.
>