Is it Possible to Rename Replication Slot + Can Publisher be Paused
Hi Experts, 1. Is it possible to rename replication slot? 2. Is it possible to pause publisher and resume if from the point it stopped after few minutes? It know it is possible to do it with subscriber, but since I have many subscribers, I would like to do the pause to the publisher. IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.
How to automatically delete idle client connections?
Dear community. I would like to ask a question regarding the below error occurred when using psql client. My environment is postgresql 12.3 docker container. https://hub.docker.com/layers/postgres/library/postgres/12.3/images/sha256-23839ca029051ca19072dc7f40b252ae8cbcd7ef632f7b8e2da09ba3abc60214?context=explore ### psql: error: could not connect to server: FATAL remaining connection slots are reserved for non-replication superuser connections ### It seems that this is caused by unused psql client sessions. Which was caused by closing the terminal without exiting the psql session correctly. ### postgres=# select usename,state,client_port from pg_stat_activity; usename | state | client_port --++- || postgres || catseye | idle | -1 catseye | idle | 36718 catseye | idle | -1 catseye | idle | 52960 catseye | idle | 40854 ... ### I have tried setting *idle_in_transaction_session_timeout* , however, this does not delete idle sessions. Would there be any expiry settings to delete client idle sessions automatically ? My apology if this question was asked before but I appreciate it if someone gives me advice. Best Regards, Yu Watanabe -- Yu Watanabe linkedin: www.linkedin.com/in/yuwatanabe1/ twitter: twitter.com/yuwtennis
Re: How to automatically delete idle client connections?
On Mon, 21 Jun 2021 at 21:59, Yu Watanabe wrote: > I have tried setting idle_in_transaction_session_timeout , however, this > does not delete idle sessions. > > Would there be any expiry settings to delete client idle sessions > automatically ? There is idle_session_timeout in PostgreSQL 14, but that's not much use to you, since a) it's not out yet, and b) you're using 12. I guess you could do something like: SELECT pg_terminate_backend(pid) FROM pg_stat_Activity where state = 'idle' and state_change < NOW() - INTERVAL '1 hour'; Change '1 hour' to whatever you like and maybe set up a cron job to run that. David
user privileges
Hi, My question can be silly but I need to ask that if a user is created without login privilege then what is the use of below command why don't postgres prompt error on it CREATE ROLE nolog_user WITH PASSWORD 'pass1'; as the user is created with login privileges then what is the use such user ? Where such users having no login privileges can be used ? Regards.
Re: user privileges
Hello On 2021-06-21 12:40, Atul Kumar wrote: Hi, My question can be silly but I need to ask that if a user is created without login privilege then what is the use of below command why don't postgres prompt error on it CREATE ROLE nolog_user WITH PASSWORD 'pass1'; A user without login privilege is usally called a group and is used for bundling privileges that you can grant granting the role to individual users. This helps you to keep more order and structure in your DB privileges. You may also have a user that can login and for whatever reason must be restricted access for a certain time. as the user is created with login privileges then what is the use such user ? See above. Where such users having no login privileges can be used ? Yes, you use it as a group. -- Charles Clavadetscher Spitzackerstrasse 9 CH - 8057 Zürich https://www.swisspug.org ++ | __ ___| | /)/ \/ \ | | ( / ___\) | | \(/ o) ( o) ) | | \_ (_ ) \ ) _/ | | \ /\_/\)/ | | \/ | | _| || | \|_/ | || |Swiss PostgreSQL| | Users Group | ++
replace inside regexp_replace
Hi, I have some text 'here is [[my text]] to replace and [[some more]]' which I want to transform to 'here is my_text to replace and some_more' i.e. wherever there are double square brackets, remove them and replace spaces in the contents with underscores. My attempt to do that is the regex select regexp_replace( 'here is [[my text]] to replace and [[some more]]', E'\\[\\[(.*?)\\]\\]', replace(E'\\1', ' ', '_'), 'g' ); which results in 'here is my text to replace and some more' It half works, i.e. it removes the brackets but doesn't seem to process the inner replace. It's as if the select were just select regexp_replace( 'here is [[my text]] to replace and [[some more]]', E'\\[\\[(.*?)\\]\\]', E'\\1', 'g' ); I've a feeling I'm missing something fundamental, any idea what? Thanks Oliver
How to hash a large amount of data within Postgres?
Hi all, I'm one of those people still storing user uploaded files within Postgres instead of some file system and over the years this increased to a point where individual uploads of multiple GiB are reached. Some years ago I implemented some SQL to read all files, build a table of SHA256 hashes and tell me how much data is redundant. The goal was to have a look at which files share the same hash with different LOIDs and optionally change that, so that all those files are only stored once on the end. While the approach was pretty naive, because it simply read all files into memory to calculate the hashes, I'm somewhat sure it worked in the past with Postgres 9.6. The executing server had enough free RAM available as well to process the at most ~4 GiB large files one after another. I tried that SQL today with Postgres 11 on UB 18.04 and it failed: > [Code: 0, SQL State: XX000] FEHLER: invalid memory alloc request size > 1898107949 > Wobei: PL/pgSQL-Funktion loid_hash_calc(oid,text)[...] > PostgreSQL > 11.12 (Ubuntu 11.12-1.pgdg18.04+1) > PostgreSQL JDBC Driver > 42.2.9 I searched regaridng that issue and only found two relevant results: Corrupted rows for some reason and simply size restrictions when allocating memory. The latter is more likely than the former in my case, as the restrictions seems to be 1 GiB and I do have larger files. I'm doing the following simply currently, because I didn't find any interfaces allowing to forward blocks of data, LOIDs, file descriptors or anything like that working with smaller buffers or alike. > fd := lo_open( loid, INV_READ); > size:= lo_lseek(fd, 0, SEEK_END); > PERFORMlo_lseek(fd, 0, SEEK_SET); > hashBin := digest(loread(fd, size), algorithm); > hashHex := encode(hashBin, 'hex'); So, is there any way to work around the problem I have currently? Can I increase the memory restriction somewhere in the config? Are there any functions available working with blocks of data I'm missing now? I didn't find any state maintainig HASH-calls. Thanks! Mit freundlichen Grüßen Thorsten Schöning -- AM-SoFT IT-Service - Bitstore Hameln GmbH Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister für IT und TK E-Mail: thorsten.schoen...@am-soft.de Web:http://www.AM-SoFT.de/ Tel: 05151- 9468- 0 Tel: 05151- 9468-55 Fax: 05151- 9468-88 Mobil: 0178-8 9468-04 AM-SoFT IT-Service - Bitstore Hameln GmbH, Brandenburger Str. 7c, 31789 Hameln AG Hannover HRB 221853 - Geschäftsführer: Janine Galonska Für Rückfragen stehe ich Ihnen sehr gerne zur Verfügung. Mit freundlichen Grüßen Thorsten Schöning Tel: 05151 9468 0 Fax: 05151 9468 88 Mobil: Webseite: https://www.am-soft.de AM-Soft IT-Service - Bitstore Hameln GmbH i.G. ist ein Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister für IT und TK AM-Soft IT-Service - Bitstore Hameln GmbH i.G. Brandenburger Str. 7c 31789 Hameln Tel: 05151 9468 0 Bitstore IT-Consulting GmbH Zentrale - Berlin Lichtenberg Frankfurter Allee 285 10317 Berlin Tel: 030 453 087 80 CBS IT-Service - Bitstore Kaulsdorf UG Tel: 030 453 087 880 1 Büro Dallgow-Döberitz Tel: 03322 507 020 Büro Kloster Lehnin Tel: 033207 566 530 PCE IT-Service - Bitstore Darmstadt UG Darmstadt Tel: 06151 392 973 0 Büro Neuruppin Tel: 033932 606 090 ACI EDV Systemhaus - Bitstore Dresden GmbH Dresden Tel: 0351 254 410 Das Systemhaus - Bitstore Magdeburg GmbH Magdeburg Tel: 0391 636 651 0 Allerdata.IT - Bitstore Wittenberg GmbH Wittenberg Tel: 03491 876 735 7 Büro Liebenwalde Tel: 033054 810 00 HSA - das Büro - Bitstore Altenburg UG Altenburg Tel: 0344 784 390 97 Bitstore IT – Consulting GmbH NL Piesteritz Piesteritz Tel: 03491 644 868 6 Solltec IT-Services - Bitstore Braunschweig UG Braunschweig Tel: 0531 206 068 0 MF Computer Service - Bitstore Gütersloh GmbH Gütersloh Tel: 05245 920 809 3 Firmensitz: AM-Soft IT-Service - Bitstore Hameln GmbH i.G. , Brandenburger Str. 7c , 31789 Hameln Geschäftsführer Janine Galonska
XX001ERROR: found xmin from before relfrozenxid for pg_authid and pg_database
Hi, Good Morning... We are getting below issue on postgresql 9.6.8 version XX001ERROR: found xmin 3355284520 from before relfrozenxid 1097492040 XX001CONTEXT: automatic vacuum of table "xyz.pg_catalog.pg_authid" XX001ERROR: found xmin 3355284522 from before relfrozenxid 1097492055 XX001CONTEXT: automatic vacuum of table "xyz.pg_catalog.pg_database" # Replacing the actual DB name with xyz. Please provide the reason and solution of this issue. Thanks and regards, Singh
Re: replace inside regexp_replace
Oliver:
On Mon, Jun 21, 2021 at 3:27 PM Oliver Kohll wrote:
...
> My attempt to do that is the regex
> select regexp_replace(
> 'here is [[my text]] to replace and [[some more]]',
> E'\\[\\[(.*?)\\]\\]',
> replace(E'\\1', ' ', '_'),
> 'g'
> );
> which results in
> 'here is my text to replace and some more'
> It half works, i.e. it removes the brackets but doesn't seem to process the
> inner replace. It's as if the select were just
> select regexp_replace(
> 'here is [[my text]] to replace and [[some more]]',
> E'\\[\\[(.*?)\\]\\]',
> E'\\1',
> 'g'
> );
> I've a feeling I'm missing something fundamental, any idea what?
You are assuming replace will magically work in a way it does not. The
inner replace is evaluated first:
> select replace(E'\\1', ' ', '_');
replace
-
\1
and it's result is passed as 3rd argument to the outer replace, so
both select are equivalent.
What you want to do can be done in some languages passing a closure,
or a function, to their replace function, or with special forms ( like
the e modifier in perl s/// ), but I'm not sure it can be done.
On languages with basic regex support, like I think SQL is, you
normally have to either split the string in match/no match or do a
multiple match ( match something like (.*?)\[\[(.*?)\]\] with two
captures ) and loop in the result aplying your second replacement (
which is what perl does behind the scenes, and other languages do )
In perl you can do it with something like:
$ perl -pe 's{\[\[(.*?)\]\]}{ $1=~s/ /_/gr}eg'
here is [[my text]] to replace and [[some more]]',
here is my_text to replace and some_more',
But note the magic e there.
In python you can use the function form:
re.sub(pattern, repl, string, count=0, flags=0)
Return the string obtained by replacing ..repl can be a string or
a function; if it is a string,
If repl is a function, it is called for every non-overlapping
occurrence of pattern. The function takes a single match object
argument, and returns the replacement string.
An so on on other languages, but in sql
regexp_replace ( string text, pattern text, replacement text [, flags
text ] ) → text
The replacement is a plain text ( and AFAIK you cannot use functions
as values in sql ).
You could probably define your function doing that if you have any PL
installed in your DB.
Francisco Olarte.
Re: user privileges
On Mon, 2021-06-21 at 16:10 +0530, Atul Kumar wrote: > My question can be silly but I need to ask that if a user is created > without login privilege then what is the use of below command why > don't postgres prompt error on it > > CREATE ROLE nolog_user WITH PASSWORD 'pass1'; > > as the user is created with login privileges then what is the use such user ? The role can be the owner of objects, or it can have members that inherit privileges. But setting a password is pointless on a role that cannot login. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: replace inside regexp_replace
On Mon, Jun 21, 2021 at 02:27:22PM +0100, Oliver Kohll wrote: > It half works, i.e. it removes the brackets but doesn't seem to process the > inner replace. It's as if the select were just > select regexp_replace( > 'here is [[my text]] to replace and [[some more]]', > E'\\[\\[(.*?)\\]\\]', > E'\\1', > 'g' > ); > I've a feeling I'm missing something fundamental, any idea what? \1 works only if it's argument to regexp_replace. And regexp_replace can't call any other functions. What you could do is: $ select string_agg(x[1] || replace(x[2], ' ', '_') || x[3], '') from regexp_matches( 'here is [[my text]] to replace and [[some more]] and maybe [[a bit longer]] too', '(.*?)\[\[(.*?)\]\](.*?)', 'g') x; string_agg ─ here is my_text to replace and some_more and maybe a_bit_longer (1 row) Or just use plperl, pl/python, or anything like this. Best regards, depesz
Re: replace inside regexp_replace
On Monday, June 21, 2021, Oliver Kohll wrote: > > select regexp_replace( > 'here is [[my text]] to replace and [[some more]]', > E'\\[\\[(.*?)\\]\\]', > replace(E'\\1', ' ', '_'), > 'g' > ); > Side note, you seldom want to use “E” (escape) string literals with regexes (or in general really) using just the simple literal syntax removes the need for double-backslashing. David J.
Re: How to automatically delete idle client connections?
On 6/21/21 5:17 AM, David Rowley wrote: On Mon, 21 Jun 2021 at 21:59, Yu Watanabe wrote: I have tried setting idle_in_transaction_session_timeout , however, this does not delete idle sessions. Would there be any expiry settings to delete client idle sessions automatically ? There is idle_session_timeout in PostgreSQL 14, but that's not much use to you, since a) it's not out yet, and b) you're using 12. I guess you could do something like: SELECT pg_terminate_backend(pid) FROM pg_stat_Activity where state = 'idle' and state_change < NOW() - INTERVAL '1 hour'; Change '1 hour' to whatever you like and maybe*set up a cron job* to run that. That's what we did. -- Angular momentum makes the world go 'round.
Psql wants to use IP6 when connecting to self using tcp...
Today I upgraded my Fedora system to Fedora 35 which included an upgrade to PostgreSQL 13.x.x. I did an initdb and restored my existing db from a dump. I modified pg_hba.conf to allow access machines on my local network hostall all 192.168.1.0/24 trust I modified postgresql.conf to listen to all interfaces. Localhost works... [jerry@bigbox ~]$ psql psql (13.3) Type "help" for help. levan=# \q The below fails... the 'fe80' is the ip6 address of big box. [jerry@bigbox ~]$ psql -h bigbox psql: error: FATAL: no pg_hba.conf entry for host "fe80::fe3f:dbff:fed1:f62e%enp7s0", user "lxxx", database "lxxx", SSL off Using the ip4 address of bigbox works [jerry@bigbox ~]$ psql -h 192.168.1.75 psql (13.3) Type "help" for help. lxxx=# I can connect to/from the other machines in my local network... Netstat shows that Postgres is listening on a ip6 interface. What did I have to do get the 'fatal' above case to work? Trapped in Steve Jobs Reality Distortion Field
Re: How to automatically delete idle client connections?
Ron , David Thank you for the advice. SQL statement had worked out in our environment and are also looking forward to the new release. Best Regards, Yu Watanabe On Tue, Jun 22, 2021 at 6:05 AM Ron wrote: > On 6/21/21 5:17 AM, David Rowley wrote: > > On Mon, 21 Jun 2021 at 21:59, Yu Watanabe > wrote: > > I have tried setting idle_in_transaction_session_timeout , however, this > does not delete idle sessions. > > Would there be any expiry settings to delete client idle sessions > automatically ? > > There is idle_session_timeout in PostgreSQL 14, but that's not much > use to you, since a) it's not out yet, and b) you're using 12. > > I guess you could do something like: > > SELECT pg_terminate_backend(pid) FROM pg_stat_Activity where state = > 'idle' and state_change < NOW() - INTERVAL '1 hour'; > > Change '1 hour' to whatever you like and maybe *set up a cron job* to run > that. > > > That's what we did. > > -- > Angular momentum makes the world go 'round. > -- Yu Watanabe linkedin: www.linkedin.com/in/yuwatanabe1/ twitter: twitter.com/yuwtennis
Re: Psql wants to use IP6 when connecting to self using tcp...
Jerry Levan writes: > The below fails... the 'fe80' is the ip6 address of big box. > [jerry@bigbox ~]$ psql -h bigbox > psql: error: FATAL: no pg_hba.conf entry for host > "fe80::fe3f:dbff:fed1:f62e%enp7s0", user "lxxx", database "lxxx", SSL off This is mostly a matter of how your local DNS is configured, not PG per se. What does "nslookup bigbox" say? > What did I have to do get the 'fatal' above case to work? Adjust your DNS setup to return the IPv4 address first. Or, perhaps, change PG's listen_addresses so that it's not listening on an IPv6 address at all. There's not going to be much point in that if you don't want to put IPv6 entries into pg_hba.conf. regards, tom lane
Re: Psql wants to use IP6 when connecting to self using tcp...
On 22.06.2021 02:44, Jerry Levan wrote: The below fails... the 'fe80' is the ip6 address of big box. [jerry@bigbox ~]$ psql -h bigbox psql: error: FATAL: no pg_hba.conf entry for host "fe80::fe3f:dbff:fed1:f62e%enp7s0", user "lxxx", database "lxxx", SSL off [...] Netstat shows that Postgres is listening on a ip6 interface. You stated earlier, that you instructed PostgreSQL to listen to all interfaces. This includes IPv6. Your configuration misses to configure trust for your IPv6 network. Alternatively you can change your network configuration to run on IPv4 and not use IPv6. You would remove the IPv6 interface from PostgreSQL and change resolver to return IPv4 (A record, not ) for your host. Stephan
