Re: glibc updarte 2.31 to 2.38
Hi Ron, > On 22. Sep, 2024, at 16:11, Ron Johnson wrote: > > The real question is why nobody notices it in other RDBMSs like Oracle, SQL > Server and MySQL. The answer is simple for Oracle: It includes a whole zoo of locale mappings and uses each one as it is needed. This is one of the many things with Oracle that only grows over time but does never get smaller again. I suspect it's similar with MariaDB, MySQL, SQL Server and others. Only PostgreSQL has no such thing as a local inventory and relies on either glibc or ICU. Cheers, Paul
Re: glibc updarte 2.31 to 2.38
Hi Joe > On 22. Sep, 2024, at 15:47, Joe Conway wrote: > > Note that moving to ICU might improve things, but there are similar potential > issues with ICU as well. The trick there would be to get your OS distro > provider to maintain the same ICU version across major versions of the > distro, which is not the case currently. Nor does the PGDG repo do that. Then I strongly suggest that the PostgreSQL developers develop a fail safe sorting mechanism that holds for generations of locale changes. Cheers, Paul
Re: pg_checksums?
Hi Nikolay, > On Nov 2, 2023, at 07:36, Nikolay Samokhvalov wrote: > There is also a good trick described in > https://www.crunchydata.com/blog/fun-with-pg_checksums to avoid > accidental start of Postgres: [...] > Additionally, I compiled some thoughts about running pg_checksums > without downtime (Patroni-friendly, of course) here: > https://twitter.com/samokhvalov/status/1719961485160689993. These two links are very interesting. Thanks very much. Cheers Paul
Re: pg_checksums?
Hi Alexander, > On Oct 30, 2023, at 19:49, Alexander Kukushkin wrote: > That's not what I said. That's why I asked. Because you used the word orthogonal. 🤣 > Patroni only manages Postgres. It is exactly the same Postgres as you would > run it without Patroni. > Everything will work. Now that is, what I suspected because of what I have learned how Patroni (and PostgreSQL replication) works so far. Thanks very much. Cheers Paul
Re: pg_checksums?
Hi Alexander, > On Oct 30, 2023, at 14:56, Alexander Kukushkin wrote: ... > But anyway, Patroni is orthogonal to pg_checksums. ... Just to be sure I understand you correctly: This does not work with Patroni? Cheers Paul
Re: pg_checksums?
Hi Peter, > On Oct 30, 2023, at 11:03, Peter J. Holzer wrote: > On 2023-10-29 13:26:27 -0500, Ron wrote: >> Best to ask Percona. > > Why Percona? Probably a typo. Patroni is used. Cheers Paul
Re: pg_checksums?
Hi Michael, > On Oct 30, 2023, at 01:56, Michael Paquier wrote: > > Hm? Page checksums are written when a page is flushed to disk, we > don't set them for dirty buffers or full-page writes included in WAL, > so it should be OK to do something like the following: > - Stop cleanly a standby. > - Run pg_checksums on the standby to enable them. > - Restart the standby. > - Catchup with the latest changes > - Stop cleanly the primary, letting the shutdown checkpoint be > replicated to the standby. > - Promote the standby. > - Enable checksums on the previous primary. > - Start the previous primary to be a standby of the node you failed > over to. That's exactly the reasoning behind my initial idea and question. Patroni does the switchover job for me including catching up on the latest changes, etc. Seems that opinions vary. Are there any hard facts? It turns out that enabling checksums can take quite some time to complete, i.e. downtime for the application which is hard to do in a 24x7 environment. Cheers Paul
Re: pg_checksums?
Hi Ron, > On Oct 29, 2023, at 16:38, Peter J. Holzer wrote: > And this is where it would break down. The modifications can't be > applied to the replica any more because the replica now contains > checksums and the modifications don't. In the best case the replica > would catch the discrepancy and refuse to apply the modifications which > would lead to the loss of these modifications. In the worst case it > would apply them anyway causing severe data corruption. ... > Not just the file headers. Every single data block. > > (Ok, it looks like the space for the checksum is reserved even if > checksums aren't enabled[1]. So at least pg_checksums doesn't have to > move data around to enable them. But overwriting a page with a checksum > with one without one would still be bad.) Those are the kind of answers and insights I was looking for. Thank you very much. Ok, I will do a reinit then. Cheers Paul
Re: pg_checksums?
Hi Ron, > On Oct 29, 2023, at 16:37, Ron wrote: > > As for safety, what do you mean by "safe"? Safe in the sense that, if I enable checksums on a replica, switch over and the enable checksums on the other side, if this is ok, or whether future mutations on the primary will corrupt the replica. That's why I asked if I need to perform a patronictl reinit. Cheers Paul
Re: pg_checksums?
Hi Peter, > On Oct 29, 2023, at 11:49, Peter J. Holzer wrote: > > It *might* work if there are zero writes on the primary during the > downtime of the replica (because those writes couldn't be replicated), > but that seems hard to ensure. Even if you could get away with making > the primary read-only (is this even possible?) I wouldn't have much > confidence in the result and reinit the (new) replica anyway. As soon as I stop the replica to enable checksums, even writes can't get replicated anymore. So during enabling checksums, a replica is definitely protected against modifications by its primary, simply because it's down. The modifications of the primary are applied to the replica when it comes back online. So, I don't see a problem at this particular stage. My fear is merely that enabling checksums does something to the physical state of the data files which are not compatible with the other side. Like for example manipulate the file headers in some way. Maybe this question is better suited for the admin list than this general list? Cheers Paul
Re: pg_checksums?
Hi Peter > On Oct 29, 2023, at 02:43, Peter J. Holzer wrote: > I don't think so. AFAIK Replication keeps the data files in sync on a > bit-for-bit level and turning on checksums changes the data layout. > Running a cluster where one node has checksums and the other doesn't > would result in a complete mess. I agree with the last sentence. This is why I asked if it is safe to enable checksums on a replica, switch over and then do it again on the ex primary, i.e. now new replica without doing a reinit. Cheers Paul
Re: pg_checksums?
Hi Bruce, hi Daniel, > On Oct 27, 2023, at 23:21, Bruce Momjian wrote: > > On Fri, Oct 27, 2023 at 10:45:16PM +0200, Daniel Gustafsson wrote: >>> On 27 Oct 2023, at 20:34, Paul Förster wrote: >> >>> a) why isn't it possible to enable checksumming while a database cluster is >>> up? >> >> It is surprisingly complicated to enable checksums on a live cluster, a patch >> was submitted a while back but ultimately never made it into postgres. The >> below threads may shine some light on the problem: >> >> https://www.postgresql.org/message-id/flat/CABUevEx8KWhZE_XkZQpzEkZypZmBp3GbM9W90JLp%3D-7OJWBbcg%40mail.gmail.com >> https://www.postgresql.org/message-id/flat/560A2239-5DE2-4B9C-92BC-878C6822F47C%40yesql.se#10b665b2193445a3d7f24be7917a952c > > Yeah, it was a big surprise that this feature was so hard to implement > because we have _no_ infrastructure for having multiple data layouts > active in a live system. The discussion eventually made that clear. > > If we have more features that need this kind of dynamic ability, we > might revisit this feature too. Ok, I see. But unfortunately still, my questions c) and d) are unanswered. I'd especially be interested in an answer to c), i.e. is it *safe* to "pg_checksum -e" the replica instance in a patroni cluster, switch over, and then do the other one? Cheers Paul
Re: pg_checksums?
Hi Ron, > On Oct 27, 2023, at 21:02, Ron wrote: >> b) why isn't it possible to check whether checksums are enabled or not? > > (This is my tiny test instance.) > > $ pg_controldata | grep checksum > Data page checksum version: 0 > > postgres=# show data_checksums; > data_checksums > > off > (1 row) this helps a lot. Thanks very much. Now, there are only two other questions. Cheers Paul
pg_checksums?
Hi, I have a few questions about pg_checksums. Long story short, checksums were disabled in our environment but are getting enabled now (again!) because of block corruptions which destroyed a few databases in a database cluster. And before you say "told you so", the decision to disable checksums was not mine. Some people just have to learn the hard way. Anyway, re-enabling data checksums creates a few questions: a) why isn't it possible to enable checksumming while a database cluster is up? b) why isn't it possible to check whether checksums are enabled or not? c) in a Patroni cluster consisting of a primary and a sync standby, is it safe to enable checksumming in the replica, then switch over and enable it in the ex-primary, i.e. now new replica, without any penalty? Or do I have to perform a reinit to really get them in sync again, though paronictl happily reports them to be in sync? d) how long does it take to enable checksums in general? Minimizing down-time is crucial. Does it depend on the database cluster size, or the number of files it uses, or what can be taken as a criterion to estimate then necessary down-time. Thanks in advance for your insights. Cheers Paul
Re: Oracle vs. PostgreSQL - a comment
Hi Ludovico, > Sorry for this reply, but I feel it is necessary to make it clear what is > reality and what is FUD against Oracle from Paul's e-mails in this thread... nothing of it was a FUD. It was a comparison done on a single machine. Then, I drew my conclusions from that and added my personal view. You don't necessarily havet to agree to my opinion nor did I ask you to agree. But it's definitely not FUD! > (Note: I work for Oracle now, but I've had 20 years experience as > multi-platform database consultant) I work *with* Oracle databases too and have been for 20+ years. But I do not work *for* Oracle and I don't feel inclined to spread their advertising. > That is... not a problem. Is it, for real? technically no. Still, a) it makes no sense at all to advertise a 64 bit product that still needs 32 bit support (one could even call that an advertising lie!) and b) it may (or may not?) cost performance. > Although I completely agree that the Oracle installation process is much > longer and more complex than PostgreSQL, I disagree with the rest. to create a CDB, you still have to provide paths which are then hard-coded into the control-file! Oracle software takes tons of space and the installation takes longer. > The CREATE PLUGGABLE DATABASE is also a single line SQL command... The > scripts to create a PDB or a PostgreSQL database depend a lot on what do you > want to achieve (empty database? specific users or permissions? sanity > checks? pre-emptive backup? add to cmdb?) yes, create pluggable database. Takes 30+ secs to run, while on PostgreSQL, it takes a few milliseconds. But we require a certain structure in the filesystem which makes the thing much more complex. > For a new PostgreSQL architecture in the past I have written 230 lines of > code to automate the database creation in an existing PostgreSQL cluster. > That included setting up application users, hardening the default permissions > on the public schema, registering in the CMDB, etc. It is not much code in my > opinion and it is done once for all. again, a simple initdb, or create database would do. For all to be done in PostgreSQL, my script is some 30 lines and includes default user creation, revoking some stuff, etc., nothing compared to what I need for Oracle. > This is bashing FUD against Oracle or lack of basic Oracle knowledge. Oracle > online move, reorganization and patching capabilities are far ahead from > PostgreSQL. nonsense! > Online Datafile Movement has existed since 12cR1. 8 years! > https://oracle-base.com/articles/12c/online-move-datafile-12cr1 yes, I know. But did you try to move SYSTEM, UNDO or TEMP tablespace or online redo log files? Did you try to move the *whole* database? You can move all data/index tablespace files with that (one by one which is tiresome with many files), but you can't move the essential tablespace files! Well, you can move the online reado log files by creating new ones and dropping the old ones but that's about it. You still can't move the essential tablespace files. I admit that I didn't try that with 19.x but it wasn't possible up to now. > Prior to that, for many years, it was possible to offline, move, rename and > online datafiles, either grouped or singularly, without stopping the > instance. Online logs can be rotated to a new location online. The only > exception are the controlfiles that require an ALTER SYSTEM, shutdown, move, > startup. I know all that but it still requires far to much work! And it still doesn't move the while database! > PostgreSQL must be stopped in order to move the database to a new path, and > if it is to a new filesystem, you need the time for a full copy of the data, > unless you do it via backup and recovery to reduce the downtime. that's not true. pg_basebackup it while running to a new destination. Set up primary_conn_info and replication and start up the copy. Once it's in sync and you have a physical copy, change the port in postgresql.conf of the copy, stop both and then only launch the copy. Promote it then. The switch takes 2-3 secs of downtime. If downtime doesn't matter but space does, stop the database cluster, move the whole PGDATA to a new location and start it there. It only requires as much downtime as the copy process takes plus a few seconds for shutdown and startup. > Again no, you don't need to recreate the controlfile for moving the datafiles > , and no: altering binary controlfiles with `sed` is nothing a production DBA > would ever do... again no, you can't move SYSTEM, UNDO and TEMP! Also, what I mentioned was a quick and dirty hack and is not recommended. > The laziness or lack of knowledge of your developers is not a problem with > Oracle technology. Still, you can get a "query which is generated and, if > printed out in a 11pt. sized font, can fill a billboard on a street", give it > to Oracle and get the optimal execution plan 99.9% of the times. And if the
Re: Advice on binary installation
Hi Markus, On 13. Apr, 2021, at 14:43, Zwettler Markus (OIZ) wrote: > We assume to get more than 100 Postgres clusters in the future. > > We will get a very heterogeneous binary installation basis if we install > needed extensions (e.g. ip4r) or software (e.g. patroni) on a per project > basis. > > There could be even more incompatibility problems otherwise if we 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? I don't know how you plan to do it, but I can only say how we do it: - Compile from source including all extensions needed (make install-world). Create a binary only directory this way, i.e. compile source to /data/postgres/xx.x - Create a tar file of said directory. - Distribute that via Ansible untaring it on the destination servers. - Have a standard postgresql.conf ready which includes a shared_preload_libraries = 'pg_stat_statements' (or whatever you need) for use with initdb. This way, we make sure that all servers get the same new software directory as needed in a separate directory including all necessary extensions, which means we can then delete the old directory if it is not longer needed (i.e. after all databases have been upgraded). Also, this makes sure, everything is loaded properly. With individual and only some few cases, we then use "create extension", but only extensions which we deliver with the tar via Ansible. If there is doing to be a new extension (which we avaoid if we can), then we put it into the tar Archive and nowhere else. So it's on all servers, but only a few databases use it then. Hope this helps. Paul
Re: PostgreSQL URI
Hi Tom, > On 26. Feb, 2021, at 21:29, Tom Lane wrote: > > Actually we need a patch against the SGML sources, not the generated > files. I didn't know this, sorry. I'm not a developer. :-) > I took this and marked it up into SGML, and (as usual when > looking at this text, it seems) failed to resist the temptation to > do some nearby copy-editing too. Pushed at > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=4e90052c46c7751779ed83627676ed5e74ebe6d4 thanks very much. > BTW, I ended up leaving out the extra brackets in the hostspec. > I do not buy the argument that those are needed to clarify what > you can repeat; I think they add confusion not clarity. Besides, > the adjacent text and examples make this quite clear. ok, you have the final word. I just made a suggestion. Cheers, Paul
Re: PostgreSQL URI
Hi Tom, > On 26. Feb, 2021, at 19:02, Paul Förster wrote: > > as I said, I don't know how to write a patch. But I played around with diff & > patch. > > However, does this do when applied to > https://www.postgresql.org/docs/current/libpq-connect.html? Would this be > what is needed? sorry, I just realized I used a redundant @ character. So here's the corrected version. libpq-connect.patch Description: Binary data Cheers, Paul
Re: PostgreSQL URI
Hi Tom, > On 26. Feb, 2021, at 17:13, Tom Lane wrote: > > WFM. Who's going to write the patch? (I can, but if one of you > wants to, be my guest.) as I said, I don't know how to write a patch. But I played around with diff & patch. However, does this do when applied to https://www.postgresql.org/docs/current/libpq-connect.html? Would this be what is needed? libpq-connect.patch Description: Binary data Cheers, Paul
Re: PostgreSQL URI
Hi Tom, > On 26. Feb, 2021, at 17:13, Tom Lane wrote: > > WFM. Who's going to write the patch? (I can, but if one of you > wants to, be my guest.) I don't know how to write a patch. Is there any documentation about that? Cheers, Paul
Re: PostgreSQL URI
Hi Tom, > On 26. Feb, 2021, at 15:51, Tom Lane wrote: > > +1. I think you could lose the outer brackets in hostspec in > this formulation, ie given that hostspec is already bracketed > above, it should be enough to write > >hostspec is [host][:port][,...] > > Also, the paramspec is under-bracketed today. Should be > more like > >paramspec is param=value[&...] if you remove the outer brackets of host spec, then that means that only the port may be repeated. The repeat is always meant to refer to its immediate preceding argument. The outer brackets make sure that it refers to either of both host *and* port. This is exactly what I was initially confused about. So I consider the outer brackets essential for that. Cheers, Paul
Re: PostgreSQL URI
Hi Alvaro, > On 26. Feb, 2021, at 15:30, Alvaro Herrera wrote: > > We could say something like > > postgresql://[userspec@][hostspec][/dbname][?paramspec] > > where > userspec is user[:password] > hostspec is [[host][:port]][,...] > paramspec is param1=value1&... > > which makes it easier to focus on each part separately, and we can > provide more verbose explanations or examples where needed. (Now that I > broke it up, the original line became very clear to me, but when I saw > it in isolation it was not. You need to count brackets carefully to be > able to read it.) +1 Cheers, Paul
Re: PostgreSQL URI
Hi Tom,
> On 25. Feb, 2021, at 16:43, Tom Lane wrote:
>
> Experimenting, it does let you omit the host and specify a port:
>
> $ psql -d postgresql://:5433
> psql: error: could not connect to server: No such file or directory
>Is the server running locally and accepting
>connections on Unix domain socket "/tmp/.s.PGSQL.5433"?
>
> So the original syntax diagram is not wrong. We could add brackets
> to clarify the repeatable part:
>
> postgresql://[user[:password]@][[host][:port][,...]][/dbname][?param1=value1&...]
>
> but I'm less sure that that's an improvement.
hmm, the following indeed connects me to the primary, leaving out the host part
completely:
$ psql -d postgresql://:5432,:5433/postgres?target_session_attrs=read-write
psql (13.2, server 12.6)
Type "help" for help.
postgres=# select user, current_setting('data_directory');
user | current_setting
--+--
postgres | /data/pg01/cdb01b/db
(1 row)
remark: cdb01a currently is replica and cdb01b is currently primary of a local
Patroni test cluster, replicating between /data/pg01/cdb01a/db and
/data/pg01/cdb01b/db.
So, my suggestion is:
postgresql://[user[:password]@][[host][:port]][,...][/dbname][?param1=value1&...]
Still, I think that it's an improvement, because it makes clear that not only
the port, but also the host may be repeated.
Cheers,
Paul
Re: PostgreSQL URI
Hi Tom, > On 25. Feb, 2021, at 16:22, Tom Lane wrote: > > Hmm. Maybe > > postgresql://[user[:password]@][host[:port][,...]][/dbname][?param1=value1&...] > > ? Seems like that would clarify how much you can repeat. yes, that looks better, thanks. Cheers, Paul
Re: PostgreSQL URI
Hi Tom, > On 25. Feb, 2021, at 16:09, Tom Lane wrote: > > =?utf-8?Q?Paul_F=C3=B6rster?= writes: >> in >> https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING >> it says that the syntax for a PostgreSQL URI is: > >> postgresql://[user[:password]@][host][:port][,...][/dbname][?param1=value1&...] > >> What I don't understand is the [,...] part, i.e. optionally repeating >> argument. > > You can repeat the host[:port] part, no more. I suspected this already. Still the position of the closing angle bracket behind the "host" part in the syntax is IMHO wrong in the doc. It currently says: postgresql://[user[:password]@][host][:port][,...][/dbname][?param1=value1&...] But shouldn't it say instead: postgresql://[user[:password]@][host[:port]][,...][/dbname][?param1=value1&...] Thanks very much. Cheers, Paul
PostgreSQL URI
Hi, in https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING it says that the syntax for a PostgreSQL URI is: postgresql://[user[:password]@][host][:port][,...][/dbname][?param1=value1&...] What I don't understand is the [,...] part, i.e. optionally repeating argument. I know that this is a valid URI: postgresql://user1:pass1@host1:port1,host2:port2/dbname?target_session_attrs=read-write But is the following valid? postgresql://user1:pass1@host1:port1,user2:pass2@host2:port2/dbname?target_session_attrs=read-write i.e. can (or should) the user[:pass] part be repeated, and possibly can even be different, if I provide more than one host:port information? Or is the user[:pass] part definitely a one-time only argument and must not appear a second time? I don't know how to read this repetition [,...] syntax. A repeat syntax usually means providing more parameters like the one immediately before that, which in this case, is the port. I think, it should be more correct this way (note the angle bracket): postgresql://[user[:password]@][host[:port]][,...][/dbname][?param1=value1&...] I tried this with psql but failed miserably, though both user1:pass1 and user2:pass2 exist on both databases. The documentation also claims that any of the parts is optional except the postgresql:// part. This means, specifying a port without a host would be perfectly fine, which IMHO makes no sense. Can someone enlighten me? Is this just a misleading line to me in the documentation? Cheers, Paul
Re: psql behavior change on upgrade from version 12.x to 13.1
Hi Bryn, > On 09. Feb, 2021, at 19:55, Bryn Llewellyn wrote: > > Using a MacBook Pro with the current Big Sur—Version 11.2 (20D64). > > I just upgraded to PostgreSQL 13.1. (Earlier, I was on 12.x.) Using psql, the > behavior of ordinary copy-and-paste has change dramatically, and for the > worse, w.r.t. Version 12. > > HAS ANYBODY ELSE SEEN WHAT I REPORT BELOW? > > First observation > > Now, when I copy a single line SQL command, terminated with semicolon and > newline from the Text Edit app (with Command-C or the menu item) and then > paste it into psql (with Command-V or the menu item), the newline isn't > respected. I have to hit the return key by hand to see the effect. Moreover, > the pasted line has a highlighted background. > > Second observation > > When I copy _several_ lines of SQL commands from the Text Edit app and then > paste them into psql, none of the newlines are respected. (I still get the > strange highlight.) Now when I hit the return key, I get errors like this: > > \i: extra argument "" ignored > > for every single line. > > This makes a standard working practice that my fingers have learned over > decades suddenly completely unusable. > > NOTE: the \i metacommand still works as it always did. well, I guess this is a macOS Big Sur issue and not a PostgreSQL problem. Or rather, it's a user (your) issue because it works as designed. ;-) I'm on Big Sur 11.2 (20D64) too. From what I can tell, copy/paste hasn't changed. But then, I don't use Text Edit. I use both vi in a Terminal and TextMate. I don't like Text Edit because this is just as comfortable and capable as is Windows' Notepad... Anyway, copying a line including the newline will require you to mark the whole line. Triple click on a line and you see the marking go beyond the last character in the line. If you click (and hold!) while moving the mouse you will see the marking move accordingly. Note how it jumps to show the complete line up to the full right including the part beyond the last character if you move the mouse beyond that. If you want to include the new line in your copying to the clipboard, you'll need to include that in your marking. Cheers, Paul
Re: Postgres 9.4 Needed
Hi Taranum, > On 09. Feb, 2021, at 11:09, Paul Förster wrote: >> I am new to postgres, can you please guide me from which folder I can get >> the downloadable and repository for 9.4? you may also try this if you need a binary release: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads Yes, it even has Windows, 32 and 64 bit. :-P Cheers, Paul
Re: Postgres 9.4 Needed
Hi Taranum, > On 09. Feb, 2021, at 10:21, Taranum Fatima wrote: > > I am new to postgres, can you please guide me from which folder I can get the > downloadable and repository for 9.4? depends on what platform you're on. Windows? Linux? Solaris?... Seems like all the directories in the binary tree only contain Solaris files. So you will probably have to go to the source directory, download the source and build it yourself for your platform: https://www.postgresql.org/ftp/source/v9.4.26/ 9.4.26 ist the last 9.4 release. > Get Outlook for Android Also, please don't top-post. This is a very annoying thing especially from the Outlook generation. Cheers, Paul
Re: Postgres 9.4 Needed
Hi Taranum, > On 09. Feb, 2021, at 01:59, Taranum Fatima wrote: > > Do you have any place from where I can download this 9.4 repository and > 94server? old versions of PostgreSQL can still be downloaded from: https://www.postgresql.org/ftp/ Cheers, Paul
Re: identifier will be truncated
Hi Joao, > On 08. Feb, 2021, at 17:39, Joao Miguel Ferreira > wrote: > > NOTICE: identifier > "y1972_1004_vat_periodic_declaration_suppliers_lines_dupl_c24_liq" will be > truncated to "y1972_1004_vat_periodic_declaration_suppliers_lines_dupl_c24_li" > > Please advise on what I should do about it or if this might be a potential > problem that I should address. this is an indication that your object name is longer than 63 characters, which is a hard coded limit. You have two options: a) compile the PostgreSQL software from source and increase that limit (which I wouldn't recommend), or b) use shorter object names (table-, view-, sequence-, column-, whatever names) with a maximum length of 63 characters. Cheers, Paul
Re: Does pg_ctl promote wait for pending WAL?
Hi Tatsuo, > On 29. Jan, 2021, at 03:51, Tatsuo Ishii wrote: > > Does anybody know whether a standby server waits for pending WAL > records/files while promotion is requested? I assume that no data > update is performed on the primary server while promotion. > > I imagine that a standby server stops to replay WAL and promotes as > soon as SIGUSR1 signal is received. > > The motivation of this question behind is I want to have a complete > copy of the primary server using promote command. do I understand you correctly that you wait until there is no activity on the primary and want to promote the replica only then? If this is the case, you should be able to safely shutdown the primary anyway at any time to be assured that the replica doesn't receive any more WAL files and is complete. Then promote the replica and restart the primary. From then on, the replica is no replica anymore but has a life on its own with its own timeline. Cheers, Paul
Re: How to keep format of views source code as entered?
Hi Ingolf, > On 26. Jan, 2021, at 14:41, Markhof, Ingolf > wrote: > > You may not be able to delete tables / views that are referenced by other > users objects, e.g. views. Unless you add the CASCADE option which will cause > all depending views to be deleted as well. And the CASCASE will work and > delete the other users view even when you don't have the permission to drop > that other users view! > > Looks like the Oracle concept of marking views as invalid makes some sense... yes, PostgreSQL's and Oracle's approaches IMHO both make perfect sense in their own way, depending on your design philosophy. I never said otherwise. I just said that I hate to debug invalid objects in Oracle because Oracle does not clearly show dependencies and reading source code can be hard, especially if the author was one of those genius generators which produce tons of code. Cheers, Paul
Re: solved (was plain stupidity) Re: ubuntu 18: PostgreSQL does not start. how can I totally remove and reinstall it
Hi Robert, > On 24. Jan, 2021, at 08:13, robert rottermann wrote: > > thanks a lot. > why dos such stupidity not hurt. ? the human brain does not contain pain generating or transmitting nerve cells, that's why. :-P Cheers, Paul
Re: localhost ssl
Hi Rob, > On 23. Jan, 2021, at 00:02, Rob Sargent wrote: > > I only wish I could set a default database as well, but IMHO that's a failing > of JDBC as much as postgres. you can't for a role or a user. You'd have to specify the database in the jdbc connection string. It's all here: https://jdbc.postgresql.org/documentation/head/connect.html You must include a database name, and optionally a role to connect to, in the jdbc connection string. Cheers, Paul
Re: localhost ssl
Hi Rob, > On 22. Jan, 2021, at 23:48, Rob Sargent wrote: > > Yes, I'm confused. As I said in reply to Jeff, I would rather not need to > remember to set the search_path, which I can avoid if I login as "role". I didn't follow the whole discussion, so sorry, to just jump in here. You don't need to remember the search_path once your role is set up correctly. The following example demonstrates that you can set a default search_path for a role. But keep in mind that it is a *default* search_path for that role, which means a) it's for that role only and b) since it's a default, it is in effect only after the next login to that role. db01=# \conninfo You are connected to database "db01" as user "paul" on host ... db01=# show search_path; search_path - "$user", public (1 row) db01=# \dn List of schemas Name | Owner +-- public | postgres (1 row) db01=# create schema s1; CREATE SCHEMA db01=# create schema s2; CREATE SCHEMA db01=# create schema s3; CREATE SCHEMA db01=# \dn List of schemas Name | Owner +-- public | postgres s1 | paul s2 | paul s3 | paul (4 rows) Now comes the crucial part. Notice that the search path still shows the old value until I reconnect: db01=# alter role paul set search_path to s2, s3; ALTER ROLE db01=# show search_path; search_path - "$user", public (1 row) db01=# \c db01 psql (13.1, server 12.5) You are now connected to database "db01" as user "paul". db01=# show search_path; search_path - s2, s3 (1 row) See? No $user, public or s1 after connecting to the database, only s2 and s3 as specified by me. The same applies to resetting it to its default values "$user", public: db01=# alter role paul reset search_path; ALTER ROLE db01=# show search_path; search_path - s2, s3 (1 row) db01=# \c db01 psql (13.1, server 12.5) You are now connected to database "db01" as user "paul". db01=# show search_path; search_path - "$user", public (1 row) There's a lot of descriptions on how this works. Just google for something like "postgres set default search path". Also, see chapter 5.9.3 of the docs: The Schema Search Path https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH Hope this helps. Cheers, Paul
Re: upgrade using logical replication
Hi Michael, > On 20. Jan, 2021, at 20:37, Michael Lewis wrote: > > Using pg_upgrade takes minutes for an in place upgrade. If you can allow 1+ > hour of downtime, it seems overly complicated to use logical replication. my all time best score was 18 seconds for migrating from 11 to 12. :-) Cheers, Paul
Re: Do we need a way to moderate mailing lists?
Hi Peter, > On 18. Jan, 2021, at 17:34, Peter J. Holzer wrote: > > In 1990, MIME didn't exist. E-Mail was by definition US-ASCII text > (except for uuencode or by private arrangement). People often used text > terminals and may not have any Internet access. yes, those were the days! :-) As for people using computers these days, I often wish I could go back in time. :-P Cheers, Paul
Re: Do we need a way to moderate mailing lists?
Hi Thiemo, > On 17. Jan, 2021, at 11:23, Thiemo Kellner > wrote: > > I would not do that. It is the work on the wrong end with doubtful result. > Wouldn't it be better to reject non-plain-text postings? coming to think of it: +1 > While at it, is there a rule of thumb for the length of inline code - in > comparison to attaching code files in comparison to using something like > pastebin.com? I only found very coarse instructions on what to do on the > lists. Have I been missing a link to a netiquette page? no, there's no formal statement that I know of. But as you can see, I for example only quote the sentences or paragraphs I refer to, which is what the old Netiquette said and which I find makes perfect sense. I also rarely quote more than one quote level, and only when absolutely necessary. Cheers, Paul
Re: Do we need a way to moderate mailing lists?
Hi Tim, > On 17. Jan, 2021, at 10:04, Tim Cross wrote: > > There is nothing stopping you from using a text mail program, like mutt, > on macOS. right. And what I said was not meant to be a complaint. Otherwise I would have complained long ago. It was just a wish. :-) Cheers, Paul
Re: Do we need a way to moderate mailing lists?
Hi Tim, > On 17. Jan, 2021, at 09:43, Tim Cross wrote: > > Highly recommend a mutt and imap combination. Your not locked into any > particular mail folder format, can still access things via mobile > devices and can process messages fast and efficiently. also, there's the good old elm. ;-) > He was amazed when I showed him all he needed to do was highlight the > error message, copy it and paste it into the message. This guy was one > of the senior developers on the team. > > I switched employers a few weeks later. one just can't know it all, I can understand that. But I expect a senior developer to develop good software. Knowing at least basic functionality of the tools s/he uses is one aspect of it. Copy/paste text is a basic thing which I expect a developer to know how to use, be it a terminal window or the IDE in use. The buzz-title "senior" developer/dba/whatever is very relative to what the company sees in you. I found that what is called "junior" in one company is "senior" or even "seasoned" in the next. These are only buzz words without a proper norm and classification and I don't give them much credit. With about 21 years of experience, my company calls me "Senior Oracle DBA". Still, I don't know RAC because we never had it, and know only little of Data Guard, which is way too complicated and bloated anyway. Oracle is so bug-ridden that I spend all of my day searching for fixes for databases and the OEM. Thank god, my day shifts more and more to PostgreSQL. :-) Cheers, Paul
Re: Do we need a way to moderate mailing lists?
Hi raf, > On 17. Jan, 2021, at 02:59, raf wrote: > > I once wrote a program to do that very thing: > > http://raf.org/textmail/ > https://github.com/raforg/textmail/ thanks very much for the nice offer but I mostly read my Mails on a Mac, sometimes Windows, but never Linux. I have no mail access on Linux. At home I use Macs and at work I (have to :-() use Windows as desktops. So textmail is not an option for me. Cheers, Paul
Re: Do we need a way to moderate mailing lists?
Hi Adrian, > On 16. Jan, 2021, at 23:46, Adrian Klaver wrote: > > That is trying to put the toothpaste back in the tube. yes, but sometimes, just sometimes, things in the olden days were just better. :-) > Too many GUI email interfaces these days that use 'advanced` formatting. I > use Thunderbird and it allows me to convert incoming to plain text on the > fly. Not a perfect system, but it cuts down on a lot of the garish content. I'm on a Mac w/ Big Sur (macOS 11.1) and use Apple Mail. I've been on Macs since 2003. Apple Mail is simple to use and I love it for exactly that. But Apple Mail has everything I expect a mail client to have, it does not allow a couple of things which other mail clients might have. Some people hate it for exactly that. I set Mail to always compose in plain text but there is no way of manipulating incoming mails other than that automatic displaying remote content (HTML links, etc.) can (and should) be turned off. Asking our ex don't-be-evil friend revealed a setting for Apple Mail: defaults write com.apple.mail PreferPlainText -bool TRUE But that doesn't work (anymore?), at least not with Big Sur. So I sometimes resort to either hit cmd-opt-u to see the mail text raw source, or better yet, just hit reply and then drop the reply after reading. As I set composing to plain text, it will convert any quoted parts. Sometimes, I just copy/paste the whole mail for reading over to TextMate, which is also sub-optimal but obviously also gives me non-proportional font reading. Still, this is somewhat cumbersome as I have to do that for each mail individually. Thank god, this doesn't happen too often. Yet, it's still annoying enough. > That is a learning curve thing. Many people don't know that copy and paste > exists for terminals/GUI's/etc. Most people, once they are pointed in the > right direction, will change that habit. That is why I would not advocate > dropping non plain text attachments. Take this as a teaching moment and > explain the reason why text is a benefit. I guess, they only change their behavior because copying/pasting some text is easier to do than creating a windowshot with aligning the frame manually, etc. But whatever the reason, thank god, some people are willing to learn that if being told. Cheers, Paul
Re: Do we need a way to moderate mailing lists?
Hi Bruce, > On 16. Jan, 2021, at 19:36, Bruce Momjian wrote: > > That "quoting entire messages on reply" is something I see far too often > here. I have been meaning to mention this problem. Thousands of people > are reading postings here, so it pays to take time to trim down what > others have to view. +1. Also, could it be possible to make messages plain text? I see a lot of varying fancy fonts and I hate that. I even hate it more when people post messages not properly trimmed or messages that need formatting preserved such as select output, i.e. table data, explain plans, etc. Proportional fonts (Outlook with its darn Arial) is one of the worst... And then there's people posting screen shots instead of copy/paste... :-( What a world. Actually, the old Netiquette from the 1990's had it right. But then, who knows about that anymore. I think, an automatic conversion of incoming posts to plain text and dropping all non plain text attachments would help a lot already. Cheers, Paul
Re: Do we need a way to moderate mailing lists?
Hi Mike, > On 16. Jan, 2021, at 18:29, Michael Nolan wrote: > > There's so much garbage in a Google search any more that they're becoming > nearly useless. Between 'sponsored' hits and ones that have little or no > relevance but throw in words to get included, I find as often as not that IF > Google finds what I'm looking for, it'll be several pages in. > > At some point there may be a Next Great Search Engine, at least I hope so. I always put "postgres" or "postgresql" in front of my searches. That almost always yields results one of which contains useful information. Or use duckduckgo or some other search engine. Cheers, Paul
Re: How to keep format of views source code as entered?
Hi Laurenz, > On 14. Jan, 2021, at 04:59, Laurenz Albe wrote: > > If PostgreSQL were to store the original text, either that text would become > wrong, or you would have to forbid renaming of anything that is referenced > by a view. this is why views, procedures, functions and packages can become invalid in Oracle, which I really hate because as a DBA, it's almost impossible to quickly see (or in fact see at all) why this happens, or having to debug applications that you don't know and/or can't fix anyway. Oracle's invalid object concept doesn't make sense. So, I'm not at all in favor of saving the original statement text. Cheers, Paul
Re: Using more than one LDAP?
Hi Stephen, > On 08. Jan, 2021, at 22:59, Stephen Frost wrote: > > Done correctly, the developers will hopefully be going from "this stupid > thing prompts me to provide a username/password in order to log in" to > "no more prompt for logging in, it just *works*". Further, as Magnus > explained, you could actually have the mapping to allow user X to log in > by providing GSSAPI credentials Y, if they are actually still going to > be including some username in their connection request to PG (even > though they shouldn't need to, since it'll be the same between their > local Windows/AD login and the GSSAPI user that PG will see). You > should be able to make both work concurrently thanks to pg_ident.conf. I agree. But the company policy is to have users being asked each time they want to login somewhere, no matter where. We need to use an RSA tamagotchi at least twice to even get somewhere close to being able to launch a tool like DbVisualizer or SQL Developer. If we want a shell on a server, we need to use the tamagotchi even one more time. And then, for such tools, or in fact anything, "no more prompt" unfortunately is just no option. Some call that security, I call that paranoia. This is why I don't care whether GSSAPI is more secure than LDAPS. The whole environment is stuffed inside some network zone which is stuffed into another network zone, then divided into again some other network zones inside, etc. LDAP and AD are in separate zones than the databases, developer's and admin's machines are again in some other network zone. Even some databases have their own network zones. You get the picture... The best thing is: they still call this single sign on because you get to use the same username everywhere. rotfl From the network perspective, Magnus is right. We have a hacky environment. But architecture is not something I have an influence on. Cheers, Paul
Re: Using more than one LDAP?
Hi Magnus, > On 07. Jan, 2021, at 12:43, Magnus Hagander wrote: > > The docs say "When using an external authentication system such as > Ident or GSSAPI, the name of the operating system user that initiated > the connection might not be the same as the database user (role) that > is to be used." > > I think that's a bit of a left-over to when it was really just ident. > First of all it should probably say peer rather than ident, and it's > not actually operating systems that are relevant here. > > So I can understand you getting ab it confused by that. but the > property that matter is where the username comes from. In GSSAPI, or > peer, or certificate, etc, the username is provided by the external > system, and the mapping is applied *after* that. > > With LDAP authentication, the username is provided by the client, and > is then passed to the external system. > > Mapping applies *after* the authentication, which inthe case of LDAP > would be too late to make any difference. > > The references to "unix user" and "operating system users" are > probably a leftover from the old days and actually contribute to some > of the confusion I think. that explains it. The use case in our company is: Developers connect with tools like DbVisualizer or SQL Developer (Oracle using the nasty PostgreSQL Hack :-( ) providing their username via JDBC to the database. Developers work on Windows, the databases run on Linux (SLES) and the AD obviously runs on Windows. Ok, since LDAP doesn't work that way, I either need to build GSSAPI packages and have the AD admins to provide me with the keytab file or make the transition a "hard" one, i.e. no transition phase. Though I'd rather have liked to see a transition phase where either account could have been used I personally can live with that. It's the developers who will have to change quickly, not me. ;-) Cheers, Paul
Re: LDAP(s) doc misleading
Hi Magnus, > On 07. Jan, 2021, at 11:04, Magnus Hagander wrote: > > No, I think this is correct. > > "Set to ldaps to use ldaps" means you set it to the value "ldaps" in > order to use ldaps. > > I think you missed the "to" in the sentence -- without that one, your > reading of it would make more sense. See also the following parameter, > ldaptls, which uses similar language. argh! Sorry, my bad. Yes, I missed the "to". :-( Thanks. Cheers, Paul
Re: LDAP(s) doc misleading
Hi Stephen, > On 06. Jan, 2021, at 18:14, Stephen Frost wrote: > > When in an Active Directory environment, it's far more secure to use > Kerberos/GSSAPI and not LDAP (or LDAPS). Using the ldap authentication > method with PostgreSQL will result in the credentials of users being > sent to the database server, such that if the database server is > compromised so will all of those user accounts. I understand. But users can't login on the database server, just on the database. Database servers and client machines are located in different network zones with firewalls between them. Also, my point was not about using LDAP(S) versus Kerberos or GSSAPI. My point was, that I find the description of the ldapscheme entry misleading. Cheers, Paul
Re: Using more than one LDAP?
Hi Magnus, > On 06. Jan, 2021, at 16:57, Magnus Hagander wrote: > > Yes. But you have a really hacky environment :P actually not. We have an old LDAP which we want to retire this year. And we also have Windows AD, which offers LDAP. So the idea is to switch the LDAP environments in PostgreSQL. The old LDAP uses aaa-u1, aaa-u2, etc. which are also accounts in the database. But our Windows AD has bbb-u1, bbb-u2, etc. So just switching LDAPs doesn't work. I'd also have to rename all users. Though it's just a one-liner, it would mean that users have to use their new names from one second to the next. But we want a transition phase if that's possible. > You could have a third LDAP instance that federates the other two. > > Another option could be to proxy it through something like FreeRADIUS. > I'm fairly certain it can also move on to a secondary server if the > first one reports login failure. I can't. I'm no sysadmin and have no rights on systems to install anything except the PostgreSQL software. Also, the network guys wouldn't be too happy. And then, there is a problem introducing new software, which is possible, but can take months for us to get the necessary permissions. > I assume you're not using any of the standard packagings then, as I > believe they all come with support for GSSAPI. Yet another reason why > it's a good idea to use that :) no, we always compile from source and only what we need. I can build packages with GSSAPI compiled into it but it does require me do have a small service interruption if I install packages with the same PostgreSQL version number, a situation, which I'd like to avoid, if possible. > And no, gssapi does not use certificates. that's good news as I'm not really happy about all that certificate stuff. ;-) > pg_ident only works for authentication methods where the username > comes from the other system, such as with Kerberos. It does not work > for LDAP, where the username is specified in PostgreSQL. I don' understand that. The doc says it should work for all external authentication services. Maybe I misread something?... Cheers, Paul
Re: Using more than one LDAP?
Hi Magnus, > On 06. Jan, 2021, at 15:48, Magnus Hagander wrote: > > Only if you can create rules in your pg_hba.conf file that knows where > the users are. You can specify multiple servers on one line, but that > only balances across servers that don't work. If a server replies "no" > to a response, PostgreSQL will not move on to the next one. So you > have to make it initially pick the correct rule. that unfortunately is not an option, partly because LDAP and AD use different options and also, as you already mentioned it, if one server says no, it's no. > And what would you do if user5 exists in both the two ldap servers? that wouldn't matter as long as user5 exists on the database and can be authenticated by either LDAP. > One hacky way you could do it is create a group role for each server, > maintained by some cron job, that indicates with LDAP server the user > is on. You can then use group matching to pick the correct rule in > pg_hba. It's kind of an ugly hack though.. that sounds really hacky. ;-) > You'd probably be better off to have a federated ldap server that has > a view of both servers, and use that. can't do that either. I have no control over both LDAP services. PostgreSQL is just a consumer and I can't make any of the two LDAPs to sync onto each other. > Or even better, since one of your nodes is AD, it speaks Kerberos. > Setting up a Kerberos trust between the two environments would make it > possible to do things like regexp matching on the realm in > pg_ident.conf, and as a bonus you get Kerberos which is a lot more > secure than ldap for auth.. It might have a slightly higher barrier > of entry, but could probably pay off well in a case like this. that'd require me to recompile and redistribute the PostgreSQL software. I only have openLDAP compiled into it but no GSSAPI. While this could be possible, it would also mean service interruption, almost not possible in a 24x7 environment. Also, and I'm no expert on this, it would require me to get certificates and configure them, and so on, right? I thought of a pg_ident.conf configuration. In fact, it's more of a prefix change. The complete situation is like this: ldap1 knows aaa-u1, aaa-u2, and so on ldap2 knows bbb-u1, bbb-u2, and so on So, I thought, I could create a pg_ident.conf like this: mymap /^aaa-(.*)$ bbb-\1 Then pg_ctl reload of course. But that doesn't seem to work. Maybe I'm trying something wrong here. Cheers, Paul
Using more than one LDAP?
Hi, can I use more than one LDAP server entry in pg_hba.conf? My tests show that only the first one is used. Example: ldap1.domain contains user1, user2 ldap2.another.domain contains user3, user4 All 4 users have an account in a PostgreSQL cluster. ldap1 is openLDAP and ldap2 is Windows AD. Both naturally have different parameters/options and as such require two different lines in pg_hba.conf. If I connect as user1 or user2, it works. If I try to connect as user3 or user4, it fails because ldap1 reports the user as non-existent, which is correct for ldap1. But in this case, ldap2 is never asked. How can I solve this dilemma? Cheers, Paul
LDAP(s) doc misleading
Hi, I found what I believe to be misleading in the LDAP documentation: https://www.postgresql.org/docs/current/auth-ldap.html It says: "ldapscheme Set to ldaps to use LDAPS."... IMHO, it should say: "ldapscheme Set to ldapscheme to use LDAPS (ldapscheme=ldaps)."... I found this because I'm in the process of making our Linux LDAP servers obsolete by reconfiguring PostgreSQL to use our company Windows Active Directory LDAPS service. Cheers, Paul
Re: pg_upgrade question
Hi Dan, > I am trying to find out if there is any step by step instruction to reconcile > old data dir and upgraded data dir after using “—link” option to do an > upgrade. > > I ran this to do an upgrade from 11.5 to 12.1: pg_upgrade -d > /hostname/pg/dev115/data -D /hostname/pg/dev121upg/data --link -b > /pgdbadevbal800/pg/PostgreSQL-11.5/bin -B > /pgdbadevbal800/pg/PostgreSQL-12.1/bin -p 1432 -P 2432 –v > > postgresdbad:dev115:pgdbadevbal800:> pwd > /hostname/pg > > postgresdbad:dev115:pgdbadevbal800:> du -sh dev121upg > 2.3Gdev121upg > > postgresdbad:dev115:pgdbadevbal800:> du -sh dev115 > 22G dev115 > > My goal is to be able to do an in place upgrade from 11.5 to 12.1 using the > same data dir “/hostname/pg/dev115/data”. Without the “—link” option I need > to double up the space usage for the instance. What is the easiest way to > accomplish this task? > > Thanks so much for your help. after a successful upgrade, you may delete the dev115 directory and move the dev121upg directory in its place. That's how I usually do it. Something like this example: (DB = cluster name) /data/pg/DB/db <= PGDATA old /data/pg/DB/dbnew <= PGDATA new, do the initdb here! initdb -k -D /data/pg/DB/dbnew ... pg_upgrade -d /data/pg/DB/db -D /data/pg/DB/dbnew ... pg_ctl -D /data/pg/DB/dbnew stop rm -rf /data/pg/DB/db mv /data/pg/DB/dbnew /data/pg/DB/db pg_ctl -D /data/pg/DB/db start Your milage may vary. Use at your own risk. ;-) If you shut down a PostgreSQL cluster properly, you can then easily move PGDATA to virtually any place you want and start it there because PostgreSQL doesn't keep references to absolute paths anywhere. Cheers, Paul
Re: pg_ctl.exe deleted on abrupt shutdown of Windows
Hi, > On 15. Dec, 2020, at 18:37, Ron wrote: > > On 12/15/20 11:26 AM, Laurenz Albe wrote: >> On Tue, 2020-12-15 at 10:00 -0600, Ron wrote: After abrupt shutdown of Windows, we are seeing the pg_ctl.exe file getting deleted automatically. >>> Only pg_ctl.exe gets deleted? Anyway, there's nothing in Postgres that >>> says "delete pg_ctl.exe on startup". >>> This smells strongly of filesystem corruption which requires a Windows guru. >> Not that I am one, but this smacks of anti-virus software that mistakenly >> thinks "pg_ctl.exe" is malware and removes or "isolates" it. > > OP mentioned that they checked that AV didn't "delete" the file. Nothing > about quarantine, though. I'm no Windows guru either, but AFAIK, some AV software moves "infected" files some place else, i.e. out of the bin directory. That means, could it be possible that it's "deleted" from the bin directory, but be put somewhere else in a quarantine directory? A "dir /s" or something like that may help. And what does "shutdown" mean? Stop of the service, or proper shutdown of Windows, or sudden power-off? Or losing a network drive which the PostgreSQL software resides on and hence killing the running database, or what does "shutdown" mean in this context? What is an "*abrupt* shutdown"? Just a thought. There's probably not much to speculate on unless there are more details. Cheers, Paul
Re: User input to queries
Hi Rich, > On 09. Dec, 2020, at 19:22, Rich Shepard wrote: > > Okay. I use mupdf to view the document and my search string were 'prompt', > and 'prompt command'. I didn't use '\prompt', \prompt is a psql special command, hence the backslash. Only psql knows that, the database does not, as with all backslash commands. Cheers, Paul
Re: User input to queries
Hi Rich, > On 09. Dec, 2020, at 19:10, Rich Shepard wrote: > > Looking at the postgres-12 doc I cannot find a command 'PROMPT' anywhere in > it. but 12 has it: postgres=# \prompt 'input: ' input input: this is test input postgres=# select version(), :'input'; version | ?column? + PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 7.5.0, 64-bit | this is test input (1 row) postgres=# \? ... Variables \prompt [TEXT] NAMEprompt user to set internal variable \set [NAME [VALUE]]set internal variable, or list all if no parameters \unset NAMEunset (delete) internal variable ... Cheers, Paul
Re: User input to queries
Hi Rich, > On 09. Dec, 2020, at 18:53, Rich Shepard wrote: > > My business tracking tool. Yes, the GUI will have text entry widgets for > user input but I want to apply these queries using psql on the command line > until I build the GUI. maybe you're looking for this? https://stackoverflow.com/a/7389606 Cheers, Paul
Re: Accessing Postgres Server and database from other Machine
Hi Adrian, > On 05. Dec, 2020, at 15:58, Adrian Klaver wrote: > > Given that the OP changed pg_hba.conf.sample, probably not:). sorry, I overlook the *.sample part. :D Cheers, Paul
Re: Accessing Postgres Server and database from other Machine
Hi Hemil, > On 05. Dec, 2020, at 07:50, Hemil Ruparel wrote: > > Did you restart postgres after changing pg_hba.conf? that shouldn't be necessary for changes in pg_hba.conf. Just do either on the command line: $ pg_ctl reload or from psql: postgres=# select pg_reload_conf(); You can then see the effective result immediately in pg_hab_file_rules: postgres=# table pg_hba_file_rules; Hope this helps. Cheers, Paul
Re: Accessing Postgres Server and database from other Machine
Hi Adrian,
> On 04. Dec, 2020, at 16:13, Adrian Klaver wrote:
> That is the wrong file, the *.sample is the giveaway.
hmmm, I'd rather call it essential reference documentation or template for
automation. It's perfectly well suited to automatically strip all comments and
then diff the result to ones real world postgresql.conf or some other version
postgresql.conf file to find parameters that have been removed or changed with
a new PostgreSQL version. This is highly useful for planning migrations and
have a quick reference what to check for before actually migrating. So for me
this is much more than just a giveaway.
A simple, but effective (Linux, bash) example:
#!/bin/bash
oldHome=/data/postgres/12.5
newHome=/data/postgres/13.1
# confOld=${oldHome}/share/postgresql.conf.sample
confOld=/data/pg01/cdb01a/db/postgresql.base.conf # postgresql."base".conf
because of Patroni
confNew=${newHome}/share/postgresql.conf.sample
sed -e "s/^#//; s/[[:space:]]*#.*$//; /^--*/d; /^ /d; /^$/d" ${confOld} | sort
>/tmp/f1
sed -e "s/^#//; s/[[:space:]]*#.*$//; /^--*/d; /^ /d; /^$/d" ${confNew} | sort
>/tmp/f2
diff -y /tmp/f1 /tmp/f2
rm /tmp/f1 /tmp/f2
Cheers,
Paul
Here's some sample output of my (still) 12.5 Patroni/etcd
test-and-play-around-cluster run against the new PostgreSQL 13.1 sample file:
archive_command = 'cp %p /data/arch/cdb01a/%f'|
archive_cleanup_command = ''
archive_mode = on | archive_command
= ''
archive_timeout = 1800| archive_mode =
off
> archive_timeout
= 0
> array_nulls = on
>
authentication_timeout = 1min
autovacuum_analyze_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.1
autovacuum_analyze_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_freeze_max_age = 2
autovacuum_freeze_max_age = 2
autovacuum_max_workers = 10 |
autovacuum_max_workers = 3
autovacuum_multixact_freeze_max_age = 4
autovacuum_multixact_freeze_max_age = 4
autovacuum_naptime = 60s |
autovacuum_naptime = 1min
autovacuum = on autovacuum = on
>
autovacuum_vacuum_cost_delay = 2ms
>
autovacuum_vacuum_cost_limit = -1
>
autovacuum_vacuum_insert_scale_factor = 0.2
>
autovacuum_vacuum_insert_threshold = 1000
autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_threshold = 50
checkpoint_timeout = 30s |
autovacuum_work_mem = -1
...
and so on
Re: Determine if postgresql cluster running is primary or not
Hi Raul, > On 20. Nov, 2020, at 11:45, Raul Kaubi wrote: > > Hmm, ok. > > But how is this possible..? > > when b.sender>0 and c.receiver>0 then > 'Primary+Replica' > > Raul this happens for example if you have a primary a and replica b running as a normal cluster (we use Patroni for automatic failover) and then add another replica c to the existing replica b, effectively replicating: a => b => c In this case, b would be the replica of a, but also be the primary for c. It's called cascading replication. Cheers, Paul
Re: Determine if postgresql cluster running is primary or not
Hi Raul, > On 20. Nov, 2020, at 10:41, Raul Kaubi wrote: > > Hi > > Thanks. > Seems like 9.5 does not work. > > ERROR: relation "pg_stat_wal_receiver" does not exist > LINE 20: from pg_stat_wal_receiver > > Any ide how to achieve this in 9.5 ? > > Raul this query is tested to work on 10.x and newer, not 9.x. I don't have 9.x, so I can't say, sorry. Cheers, Paul
Re: Determine if postgresql cluster running is primary or not
Hi David, > On 20. Nov, 2020, at 10:34, David G. Johnston > wrote: > > > On Friday, November 20, 2020, Paul Förster wrote: > > > On 20. Nov, 2020, at 10:03, Thomas Kellerer wrote: > > > > > select pg_is_in_recovery(); > > I usually don't recommend using pg_is_in_recovery() only because a database > cluster can be in recovery for other reasons. This is why I always do the > following: > > Do any of those other reasons allow connections that could execute that > function to exist? that always depends on what your application does. An application could still select a lot of things, maybe even wrongly so, even if the cluster is in recovery mode. That was my idea when writing this query and it's been working fine for years now. Cheers, Paul
Re: Determine if postgresql cluster running is primary or not
Hi Thomas, > On 20. Nov, 2020, at 10:03, Thomas Kellerer wrote: > > Raul Kaubi schrieb am 20.11.2020 um 09:53: >> CentOS 7 >> Postgres 9 to 12 >> >> For monitoring purpose, I would like that certain scripts are only run in >> primary server. >> So I am looking ways to universally discover if postgresql cluster that is >> running is primary or not. > > As the standby will be in constant recovery, you can use > > select pg_is_in_recovery(); I usually don't recommend using pg_is_in_recovery() only because a database cluster can be in recovery for other reasons. This is why I always do the following: select distinct case when b.sender=0 and c.receiver=0 then 'Standalone' when b.sender>0 and c.receiver=0 then 'Primary' when b.sender=0 and c.receiver>0 then 'Replica' when b.sender>0 and c.receiver>0 then 'Primary+Replica' end as pgrole from pg_database a, ( select count(*) as sender from pg_stat_replication ) b, ( select count(*) as receiver from pg_stat_wal_receiver ) c where not a.datistemplate; Cheers, Paul
Re: \COPY command and indexes in tables
Hi Jayadevan, > On 19. Nov, 2020, at 11:07, Jayadevan M wrote: > > May be you could also make sure that loading actually stopped, by checking > the size of the data directory. In another session, you could try > watch du -h -s that might be misleading if you have the pg_wal directory inside PGDATA. Cheers, Paul
Re: create type with %type or %rowtype
Hi,
> On 18. Nov, 2020, at 22:08, Post Gresql wrote:
>
> I might be stupid, but where in the document for create function does it say
> that the return type can be a table?
>
> From the doc for version 13
> https://www.postgresql.org/docs/13/sql-createfunction.html
>
> "rettype
> The return data type (optionally schema-qualified). The return type can be a
> base, composite, or domain type, or can reference the type of a table column."
right in the syntax:
CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr
] [, ...] ] )
[ RETURNS rettype
| RETURNS TABLE ( column_name column_type [, ...] ) ]
"RETURNS TABLE(...)" is probably what you're looking for?
Cheers,
Paul
Re: vacuum vs vacuum full
Hi Ravi, > On 18. Nov, 2020, at 15:30, Ravi Krishna wrote: > > ALTER TABLE TABLE_NAME DROP PARTITION PARTITION_NAME UPDATE INDEXES; IIRC the statement is alter table drop partition update *GLOBAL* indexes; But we experienced big problems in the past which is why we changed all to local indexes. The situation may have improved in the last few years but we will not change back again. :-) Why should we? Cheers, Paul
Re: vacuum vs vacuum full
Hi Laurenz, > On 18. Nov, 2020, at 13:02, Laurenz Albe wrote: > > I personally hope that we will never have global indexes. > I am not looking forward to helping customers with the problems that > they create (long duration of ATTACH/DETACH PARTITION, index fragmentation). +1. Experience shows that global index in Oracle lead to problems when dropping a partition. rebuilding an index, or other such nice administrative stuff, often leading to unnecessarily long downtimes. Cheers, Paul
Re: Discovering postgres binary directory location
Hi Mark,
> On 12. Nov, 2020, at 16:37, Paul Förster wrote:
>
> how about searching for pg_ctl only inside a bin directory:
>
> $ find / -type f -name "pg_ctl" -exec grep "/bin/" {} \; 2>/dev/null
> Binary file /data/postgres/12.4/bin/pg_ctl matches
> Binary file /data/postgres/13.0/bin/pg_ctl matches
>
> That should also solve your source tree and root mail problems.
btw., you can also do it without calling grep:
$ find / -type f -executable -regex "*/bin/pg_ctl" 2>/dev/null
At least on openSUSE. But I guess, it should be pretty much the same on CentOS.
Cheers,
Paul
Re: Discovering postgres binary directory location
Hi Mark,
> On 12. Nov, 2020, at 16:19, Mark Johnson wrote:
>
> # find / -name pg_ctl
> /usr/pgsql-13/bin/pg_ctl
> /usr/local/pgsql/bin/pg_ctl
> /usr/pgsql-12/bin/pg_ctl
> /usr/pgsql-9.6/bin/pg_ctl
> /root/Downloads/postgresql-12.1/src/bin/pg_ctl
> /root/Downloads/postgresql-12.1/src/bin/pg_ctl/pg_ctl
> You have mail in /var/spool/mail/root
how about searching for pg_ctl only inside a bin directory:
$ find / -type f -name "pg_ctl" -exec grep "/bin/" {} \; 2>/dev/null
Binary file /data/postgres/12.4/bin/pg_ctl matches
Binary file /data/postgres/13.0/bin/pg_ctl matches
That should also solve your source tree and root mail problems.
Cheers,
Paul
Re: Discovering postgres binary directory location
Hi Raul, hi Adrian, > On 11. Nov, 2020, at 23:26, Adrian Klaver wrote: > > On 11/11/20 2:22 PM, Raul Kaubi wrote: >> Hi >> CentOS 7 >> Postgres 9 to 12 >> I am looking ways to universally discover postgresql binary directory for >> monitoring purpose. >> For example postgres 12, it is: */usr/pgsql-12* > > pg_config --bindir > /usr/local/pgsql12/bin or by query: postgres=# select setting from pg_config where name = 'BINDIR'; setting - /data/postgres/12.4/bin (1 row) Cheers, Paul
Re: initdb --data-checksums
Hi Matt, > On 09. Nov, 2020, at 18:00, Matt Zagrabelny wrote: > > Hello, > > I see the --data-checksums option for initdb. Is it possible to use > --data-checksums after the cluster has been initialized? I'm guessing "not", > but thought I'd ask. > > I'm running v12 on Debian. > > Thanks for any help! indeed, it is. Have a look at: https://www.postgresql.org/docs/12/app-pgchecksums.html Make sure the database is cleanly shut down before doing it. Cheers, Paul
Re: Multi-row insert: error at terminal row.
Hi David, > On 29. Oct, 2020, at 17:21, David G. Johnston > wrote: > > On Thu, Oct 29, 2020 at 9:16 AM Paul Förster wrote: > But I guess that Emacs shows the matching closing bracket at the beginning of > the line, which matches that single tuple. But you also need a closing > bracket for the set of tuples like this: > > insert ... > ( > (v1, v2, v3), > (v4, v5, v6), > (v7, v8, v9)<= this is the bracket pair that Emacs shows as matching. > ); <= this is the missing bracket. > > > Except that isn't valid INSERT statement syntax. You are missing "values" > and there is no enclosing parens: > > INSERT INTO tbl (i) VALUES (1), (2), (3); --this is perfectly valid > > That said seeing the first few rows, in addition to the last few, would help. sorry, I didn't check. My point was that there is a mismatch between a closing parent and an initial opening parent somewhere even if Emacs shows the last parent as matching the last opening parent. So, you are right of course. postgres=# create table t1(v1 int, v2 int, v3 int); CREATE TABLE postgres=# insert into t1(v1, v2, v3) values (1, 2, 3), (4, 5, 6), (7, 8, 9); INSERT 0 3 Cheers, Paul
Re: Multi-row insert: error at terminal row.
Hi Rich, > On 29. Oct, 2020, at 17:08, Rich Shepard wrote: > > On Thu, 29 Oct 2020, Paul Förster wrote: > >>> (2698,'Port of >>> Portland','http://www.portofportland.com',null,'Port','Opportunity',null); >> the last line has a closing parenthesis missing. > > Paul, > > I see a closing parenthesis immediately in front of the semi-colon and emacs > shows it matches the opening parenthesis. I don't know Emacs, I'm a vi guy. ;-) But I guess that Emacs shows the matching closing bracket at the beginning of the line, which matches that single tuple. But you also need a closing bracket for the set of tuples like this: insert ... ( (v1, v2, v3), (v4, v5, v6), (v7, v8, v9)<= this is the bracket pair that Emacs shows as matching. ); <= this is the missing bracket. Cheers, Paul
Re: Multi-row insert: error at terminal row.
Hi Rich, > On 29. Oct, 2020, at 16:58, Rich Shepard wrote: > > On Thu, 29 Oct 2020, Rob Sargent wrote: > >> Can we see the last two line of the file (1924, 1925)? > > Rob, > > (2697,'Port of > Newport','http://www.portofnewport.com',null,'Port','Opportunity',null), > (2698,'Port of > Portland','http://www.portofportland.com',null,'Port','Opportunity',null); > > Each line is enclosed in parentheses and is terminated with a comma. the last line has a closing parenthesis missing. Cheers, Paul
Re: Hot backup in PostgreSQL
Hi Laurent, > On 22. Oct, 2020, at 08:42, W.P. wrote: > > Hi there, > > how to do "hot backup" (copying files) while database running? > > Not using pg_dump. > > Is there some equivalent of Oracle (I knew v8) "ALTER DATABASE BEGIN BACKUP", > which froze writes to database files, pushing everything to redo files? yes, there is. Please read the chapter: 25.3.3.1. Making A Non-Exclusive Low-Level Backup https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP Cheers, Paul
Re: What's your experience with using Postgres in IoT-contexts?
Hi Peter, > On 14. Oct, 2020, at 14:49, Peter J. Holzer wrote: > > "IOT" means "Internet of things". IOT also means "index organized table"... So much for using abbreviations. :-) Cheers, Paul
Re: How to execute the sql file in PSQL
Hi Mark, > On 06. Oct, 2020, at 10:23, Mark wrote: > > Actually, a single quotation will work. > \ir 'C:\\Program Files\\PostgreSQL\\12\\demo-big-en-20170815.sql' > But I don't know why a single quotation will work. > It would be very helpful if you guys can explain to me. because Microsoft had the glorious idea of putting a blank character into "Program Files". Quoting this will make the whole C:\...sql line one single word which is then passed to \i. Cheers, Paul
Re: UUID generation problem
Hi James, > On 05. Oct, 2020, at 19:16, James B. Byrne wrote: > > As this is an application package it is not within my purview to alter the > code. To do so would rik a return of the problem with every update. > > Adding public to the search path is fine by me. However, I still need to find > out how this situation arose. Is it something I did or something that the > installer does by default? The project notes 'suggest' 'adempiere' as the > username for access. However they do not use wording to imply that one must > use it. However, if there is something in the installer that uses 'adempiere' > regardless of the properties settings then I need to discover this. well, actually, you can just set the search_path for the role the application logs in with: alter role set search_path = ', pg_catalog, public'; The next time logs in, it should see the freshly set search_path. When we create an app schema and role set in our databases, we always do this to make sure that the application role always finds its schema. We never had any problems with this. https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH https://www.postgresql.org/docs/current/sql-alterrole.html Hope this helps, Paul
Re: UUID generation problem
Hi Adrian,
> On 05. Oct, 2020, at 19:20, Adrian Klaver wrote:
> Actually it does:
>
> From the prompt I'm guessing you are logging in as 'postgres' user. In that
> case "$user" will become postgres and you will get:
>
> postgres=# select current_schemas(false);
> current_schemas
> ---
> {postgres,public}
>
> The current_schemas(true) case will include implicit schemas that are
> 'always'(as I'm sure that someone will come up with the exception) there.
yes, I am. And I know about the false case. It did it just out of curiosity,
not to cause confusion here, sorry.
Cheers,
Paul
Re: UUID generation problem
Hi James, > idempiere=# select uuid_generate_v4(); > ERROR: function uuid_generate_v4() does not exist > LINE 1: select uuid_generate_v4(); > ^ > HINT: No function matches the given name and argument types. You might need > to > add explicit type casts. > idempiere=# select public.uuid_generate_v4(); > uuid_generate_v4 > -- > 5ba19b69-ec8e-4d8e-8968-7c84eccc4351 > (1 row) > On 05. Oct, 2020, at 18:51, James B. Byrne wrote: > idempiere=# show search_path; > search_path > --- > adempiere, pg_catalog > (1 row) I guess that's why you don't see the uuid_generate_v4() function. I suggest you either fully qualify it, i.e. public.uuid_generate_v4() or add public to your search path. Cheers, Paul
Re: UUID generation problem
Hi James,
> On 05. Oct, 2020, at 17:57, James B. Byrne wrote:
>
> [root@accounting-2 ~ (master)]# psql --dbname=idempiere
> --username=idempiere_dbadmin
> Password for user idempiere_dbadmin:
> psql (11.8)
> Type "help" for help.
>
> idempiere=# select current_schemas(true);
>current_schemas
>
> {adempiere,pg_catalog}
> (1 row)
>
> idempiere=# select uuid_generate_v4();
> ERROR: function uuid_generate_v4() does not exist
> LINE 1: select uuid_generate_v4();
> ^
> HINT: No function matches the given name and argument types. You might need
> to
> add explicit type casts.
> idempiere=# select public.uuid_generate_v4();
> uuid_generate_v4
> --
> 066e3298-3c91-4079-98ee-2b279bfc4025
> (1 row)
just out of curiosity, what does the search_path contain? It needs not
necessarily reflect the contents of current_schemas, see the following example:
postgres=# select current_schemas(true);
current_schemas
--
{pg_catalog,postgres,public}
(1 row)
postgres=# select current_schemas(false);
current_schemas
---
{postgres,public}
(1 row)
postgres=# show search_path;
search_path
-
"$user", public
(1 row)
Cheers,
Paul
Re: UUID generation problem
Hi James, > On 03. Oct, 2020, at 04:17, James B. Byrne wrote: > > On Fri, October 2, 2020 21:13, Tom Lane wrote: >> "James B. Byrne" writes: >>> On Fri, October 2, 2020 18:46, Tom Lane wrote: >> >>> idempiere=# \dn >>>List of schemas >>> Name| Owner >>> ---+--- >>> adempiere | adempiere >>> public| postgres > I will resolve the conflict either by granting 'idempiere_dbadmin' the > necessary privileges or by changing the connection to use the 'adempiere' user > instead. you can also rename roles/users: alter role adempiere_dbadmin rename to idempiere_dbadmin; https://www.postgresql.org/docs/13/sql-alterrole.html or schema: alter schema adempiere rename to idempiere; https://www.postgresql.org/docs/13/sql-alterschema.html Cheers, Paul
Re: pg_upgrade Python version issue on openSUSE
Hi Adrian, > On 28. Sep, 2020, at 16:30, Adrian Klaver wrote: > Not necessarily, if it is installing plpythonu functions. I'll have to check that anyway. I'm already logged out of work, so I won't do that now. 😇 Cheers, Paul
Re: pg_upgrade Python version issue on openSUSE
Hi Adrian, > On 28. Sep, 2020, at 16:03, Adrian Klaver wrote: > > So pgwatch2 installs functions that use plpythonu? > How does that work if there is no plpython language installed? at work, the extension is installed everywhere. But it seems we have an old version. The current version seems to use plpython3u. https://github.com/cybertec-postgresql/pgwatch2 Or more specifically: https://github.com/cybertec-postgresql/pgwatch2#integration-of-os-level-metrics At least, at a very quick first look, this one is updated and hence off the list to check. Ok, but this is going to be off-topic. Cheers, Paul
Re: pg_upgrade Python version issue on openSUSE
Hi Adrian, > On 28. Sep, 2020, at 15:34, Adrian Klaver wrote: > > Well I'm out of ideas. That means circling back to having Python 2 installed, > should the powers that be agree. they don't. But fortunately, it seems that the number of applications which use Python code inside a database, seems rather small. One of them is pgwatch2. I will check if there's an update which then uses Python 3. Thanks for helping. Cheers, Paul
Re: Question about using ICU
Hi Laurenz, > On 28. Sep, 2020, at 13:13, Laurenz Albe wrote: >> >> but then I'd have to do a reindex anyway, right? My goal was to avoid the >> reindex altogether, if possible. > > That couldn't be avoided anyway if you change the collation no matter > if you do it on the database or on the column level. ok, thanks. Cheers, Paul
Re: how to switch off: WARNING: psql major version 11, server major version 13
Hi Matthias, > On 28. Sep, 2020, at 12:06, Matthias Apitz wrote: > > > Hello, > > Maybe it's a FAQ, but I haven't seen the answer. I want to switch of the > warning (because I know the fact of version not matching): > > $ psql -Usisis -dsisis > SET > psql (11.4, server 13.0) > WARNING: psql major version 11, server major version 13. > Some psql features might not work. > Type "help" for help. > > sisis=# > > I tried to do it with the ~/.psqlrc file: > > $ cat ~/.psqlrc > SET client_min_messages = 'error' > > The command gets executed (as the 'SET' shows), but does not silent the > warning. try the -q switch to psql: $ psql -q That should do. Cheers, Paul
Re: Question about using ICU
Hi Laurenz, > On 28. Sep, 2020, at 11:04, Laurenz Albe wrote: > > There have been efforts to add this functionality: > https://www.postgresql.org/message-id/flat/5e756dd6-0e91-d778-96fd-b1bcb06c161a%402ndquadrant.com > but it didn't get done. seems to be a rather complex thing according to the thread. > Your best bet is to manually change the definition of all columns to use the > new collation. > psql's \gexec may help. but then I'd have to do a reindex anyway, right? My goal was to avoid the reindex altogether, if possible. Cheers, Paul
Question about using ICU
Hi, I have a general question about the use of ICU. Currently, we have PostgreSQL compiled from source (Linux) without ICU support. All database clusters and databases are UTF8 and of course relying on glibc. With the sooner or later upcoming glibc release 2.28, there will probably a big reindex operation be necessary. To avoid that, I'd like to use ICU. Compiling --with-icu into the new software, is there a way to make a whole database cluster and all its databases use an ICU without having to manually change all tables or indexes? Cheers, Paul
Re: pg_upgrade Python version issue on openSUSE
Hi Adrian, > On 27. Sep, 2020, at 19:30, Adrian Klaver wrote: > > Does: > > SELECT >lanname, proname, probin > FROM >pg_proc > JOIN >pg_language > ON >pg_language.oid = pg_proc.prolang > WHERE >pg_language.lanname='plpythonu' > AND > probin IS NOT NULL; > > show anything? This would need to be repeated for each cluster in database. nope, nothing on any database, not even on template1. template0 does not allow connections. Cheers, Paul
Re: pg_upgrade Python version issue on openSUSE
Hi Adrian, > On 27. Sep, 2020, at 00:09, Adrian Klaver wrote: > Could it be that at some point in these instances history plpython* where > installed as CREATE LANGUAGE and you are dealing with the vestiges of that? I do know for sure that that never happened because the database clusters with this effect are my personal test databases and I never used Python. I did some tests with plperl and plperlu, though, but never python, because I don't "speak" python. What is possible is, those databases are clones of a Patroni cluster database (primary) I used to experiment with. I just copied them to new PGDATAs back then and changed PGPORT of course. I know, I could have done initdb and pg_dumpall but just copying the whole database cluster was the fast way to go, even more so as the PostgreSQL software was exactly the same. Just PGDATA and PGPORT changed for the clone. From what I know this is a perfectly legal way to do it as long as the source database cluster is properly shut down during the copy process. Maybe Patroni did it then implicitly? I'm not sure how Patroni works internally but I know that it is written in Python. Maybe it does install something in the database which I don't know and can't find? I tried searching for anything owned by "replicator" but can't find anything. > Are you able to go back and reconstruct them and then do \dL (languages) and > \dx (extensions)? The machine in question is my personal test box at home. I don't do regular backups there. If I break something I just reinstall it. So going back into the past with backups is not possible for me. The only thing that I kept running a long time now is the Patroni cluster because I have some data stored in it. But this is the only "history" there is. However, \dx and \dL do not show any Python extension or language on the Patroni cluster too, which is still 12.4. Still, thanks for helping. Cheers, Paul
Re: pg_upgrade Python version issue on openSUSE
Hi Adrian, > On 26. Sep, 2020, at 17:43, Adrian Klaver wrote: > > I suppose getting them to install Python 2 is out of the question? It is an > official package. I can try, but chances are at 99% that they refuse. > Well there is always going to be versioning. If you mean the incompatibility > split, then for 2/3 that is not going away. There will be a Python 4, but the > core developers have said they learned their lesson and it will just be an > incremental upgrade. so you're saying there will always be two Pythons? One Python 2 and one Python x (with x>=3)? Oh my god... Why don't they just make Python 3 backward compatible? Cheers, Paul
Re: pg_upgrade Python version issue on openSUSE
Hi Adrian, > On 26. Sep, 2020, at 17:17, Adrian Klaver wrote: > > On 9/26/20 2:33 AM, Paul Förster wrote: >> Hi, >> the OS here is openSUSE Leap 15.2. I downloaded the PostgreSQL 13.0 source >> and built it without issues like so: > >> openSUSE has no python2 anymore, only python3. The source database does not >> even have plpython installed: > > Actually it does: > > https://software.opensuse.org/package/python?search_term=%22python%22 well, actually, it does not. There are still Python2 packages in the repo which I can happily install here at home but not in the company. So I will run into trouble if I install something here to make something else work, and then take it to the company where it does not work. So, to maintain the highest level of compatibility to the machines at work, I don't install extra packages. But yes, that would be the second most proper solution. The first and utmost proper solution would be for Python to finally stop that versioning crap after years have gone by now. Cheers, Paul
Re: pg_upgrade Python version issue on openSUSE
Hi Adrian, > On 26. Sep, 2020, at 17:07, Adrian Klaver wrote: > > I believe the issue is here: > > select * from pg_pltemplate ; > > > plpythonu | f | f | plpython_call_handler | > plpython_inline_handler | plpython_validator | $libdir/plpython2 | NULL > plpython2u | f | f | plpython2_call_handler | > plpython2_inline_handler | plpython2_validator | $libdir/plpython2 | NULL > plpython3u | f | f | plpython3_call_handler | > plpython3_inline_handler | plpython3_validator | $libdir/plpython3 | NULL > > > The default plpython is plpythonu and that points at $libdir/plpython2. > > The instructions here: > > https://www.postgresql.org/docs/12/plpython-python23.html > > offer a work around: > > "Daredevils, who want to build a Python-3-only operating system environment, > can change the contents of pg_pltemplate to make plpythonu be equivalent to > plpython3u, keeping in mind that this would make their installation > incompatible with most of the rest of the world." sounds like: update pg_pltemplate set tmplhandler='plpython3_call_handler', tmplinline='plpython3_inline_handler', tmplvalidator='plpython3_validator', tmpllibrary='$libdir/plpython3' where tmplname='plpythonu'; And that sounds somewhat dangerous to me, especially if I take the comment on the plpython-python23 page into account: "keeping in mind that this would make their installation incompatible with most of the rest of the world." I'd rather not... Cheers, Paul
Re: pg_upgrade Python version issue on openSUSE
Hi Tom, > On 26. Sep, 2020, at 16:49, Tom Lane wrote: > > Actually, now that I think about it, you're querying the wrong view. > I'm too lazy to check the source code right now, but I'm pretty sure > that pg_available_extension_versions is mostly driven off what control > files exist in the on-disk libdir. But that may have little to do with > what's in the system catalogs. You should have checked pg_extension, > or just "\dx" in psql. just created another new empty database cluster because I run out of them on my test box here at home. :-) After all, each drop/create extension seems to resolve the issue, so the cluster is unusable for repetition, unless I would restore it. Ok, I'm too lazy now... :-D Did the usual initdb -k on the new database cluster. Then the select plus your suggested \dx. Nothing there and drop extension didn't work, all as I would have expected. This is strange. I will check further next week on company databases. The ones I did it up to now are my private ones at home. I'm really curious about that next week. Thanks for the tips. Cheers, Paul
Re: pg_upgrade Python version issue on openSUSE
Hi Tom, > On 26. Sep, 2020, at 16:07, Tom Lane wrote: > > =?utf-8?Q?Paul_F=C3=B6rster?= writes: >> seems, I found some kind of solution: > >> - before running "pg_upgrade --check -k": >> drop extension plpythonu; >> - run pg_upgrade >> - after the upgrade: >> create extension plpython3u; > >> Is this the correct way? > > If you had plpythonu installed before, that's a plausible thing > to do. (There was discussion some time ago about making the > python-2-to-3 transition less painful for users, but we failed > to come to any consensus about how; so manual fixes like this > are going to be needed for a lot of people.) > > However, I don't understand how "drop extension plpythonu" > worked for you, given your previous query showing that > that extension wasn't installed. just checked with another 12.4. It's the same: postgres=# select * from pg_available_extension_versions where installed; name | version | installed | superuser | relocatable | schema | requires | comment -+-+---+---+-++--+-- plperlu | 1.0 | t | t | f | pg_catalog | | PL/PerlU untrusted procedural language dblink | 1.2 | t | t | t || | connect to other PostgreSQL databases from within a database plpgsql | 1.0 | t | f | f | pg_catalog | | PL/pgSQL procedural language plperl | 1.0 | t | f | f | pg_catalog | | PL/Perl procedural language (4 rows) postgres=# drop extension plpythonu ; DROP EXTENSION postgres=# create extension plpython3u ; CREATE EXTENSION The "plpython" and "plpython3u" for the drop and create extension statements came by entering "plpy" and then pressing tab. So PostgreSQL knew about them. Still, as you can see, I could drop pypythonu again though it did not appear in the query. After the create extension, it appears as it should: postgres=# select * from pg_available_extension_versions where installed; name| version | installed | superuser | relocatable | schema | requires | comment +-+---+---+-++--+-- plperlu| 1.0 | t | t | f | pg_catalog | | PL/PerlU untrusted procedural language dblink | 1.2 | t | t | t || | connect to other PostgreSQL databases from within a database plpython3u | 1.0 | t | t | f | pg_catalog | | PL/Python3U untrusted procedural language plpgsql| 1.0 | t | f | f | pg_catalog | | PL/pgSQL procedural language plperl | 1.0 | t | f | f | pg_catalog | | PL/Perl procedural language (5 rows) Is this a bug in 12.4 not showing the extension? Cheers, Paul
Re: pg_upgrade Python version issue on openSUSE
Hi Tom, > On 26. Sep, 2020, at 16:07, Tom Lane wrote: > > If you had plpythonu installed before, that's a plausible thing > to do. (There was discussion some time ago about making the > python-2-to-3 transition less painful for users, but we failed > to come to any consensus about how; so manual fixes like this > are going to be needed for a lot of people.) that's one of the things I very much don't like about Python. This version 2 and 3 gibberish has been going on for years now wherever it's used, be it packaging with operating systems or integrated into applications. > However, I don't understand how "drop extension plpythonu" > worked for you, given your previous query showing that > that extension wasn't installed. that is exactly what I don't understand too. It should have shown up in the query, but it didn't. The PostgreSQL 12.4 software was compiled exactly the same way, only without ICU support. The other configure options were the same. Other than that, I didn't change anything in my build script. For your reference, below are the two configs: PostgreSQL 12.4: $ pg_config BINDIR = /data/postgres/12.4/bin DOCDIR = /data/postgres/12.4/share/doc HTMLDIR = /data/postgres/12.4/share/doc INCLUDEDIR = /data/postgres/12.4/include PKGINCLUDEDIR = /data/postgres/12.4/include INCLUDEDIR-SERVER = /data/postgres/12.4/include/server LIBDIR = /data/postgres/12.4/lib64 PKGLIBDIR = /data/postgres/12.4/lib64 LOCALEDIR = /data/postgres/12.4/share/locale MANDIR = /data/postgres/12.4/share/man SHAREDIR = /data/postgres/12.4/share SYSCONFDIR = /data/postgres/12.4/etc PGXS = /data/postgres/12.4/lib64/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--prefix=/data/postgres/12.4' '--enable-nls' '--with-perl' '--with-python' '--with-openssl' '--with-ldap' '--with-libxml' '--with-tclconfig=/usr/lib64' CC = gcc CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2 CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -O2 CFLAGS_SL = -fPIC LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/data/postgres/12.4/lib64',--enable-new-dtags LDFLAGS_EX = LDFLAGS_SL = LIBS = -lpgcommon -lpgport -lpthread -lxml2 -lssl -lcrypto -lz -lreadline -lrt -lcrypt -ldl -lm VERSION = PostgreSQL 12.4 PostgreSQL 13.0: $ pg_config BINDIR = /data/postgres/13.0/bin DOCDIR = /data/postgres/13.0/share/doc HTMLDIR = /data/postgres/13.0/share/doc INCLUDEDIR = /data/postgres/13.0/include PKGINCLUDEDIR = /data/postgres/13.0/include INCLUDEDIR-SERVER = /data/postgres/13.0/include/server LIBDIR = /data/postgres/13.0/lib64 PKGLIBDIR = /data/postgres/13.0/lib64 LOCALEDIR = /data/postgres/13.0/share/locale MANDIR = /data/postgres/13.0/share/man SHAREDIR = /data/postgres/13.0/share SYSCONFDIR = /data/postgres/13.0/etc PGXS = /data/postgres/13.0/lib64/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--prefix=/data/postgres/13.0' '--enable-nls' '--with-icu' '--with-perl' '--with-python' '--with-openssl' '--with-ldap' '--with-libxml' '--with-tclconfig=/usr/lib64' CC = gcc CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2 CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -O2 CFLAGS_SL = -fPIC LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/data/postgres/13.0/lib64',--enable-new-dtags LDFLAGS_EX = LDFLAGS_SL = LIBS = -lpgcommon -lpgport -lpthread -lxml2 -lssl -lcrypto -lz -lreadline -lrt -ldl -lm VERSION = PostgreSQL 13.0 Cheers, Paul
