Re: Restoring default privileges on objects
On 8/29/23 13:27, Tom Lane wrote: Erik Wienhold writes: On 29/08/2023 18:43 CEST Stuart McGraw wrote: Shouldn't psql put *something* (like "(default)" or "-") in the "Access privileges" column to indicate that? Or conversely, something (like "(none)"?) in the revoked case? Indeed, that's confusing. Command \dp always prints null as empty string [1]. So \pset null '(null)' has no effect. Yeah, perhaps. The reason it so seldom comes up is that a state of zero privileges is extremely rare (because it's useless in practice). That being the case, if we were to do something about this, I'd vote for changing the display of zero-privileges to "(none)" or something along that line, rather than changing the display of NULL, which people are accustomed to. Fixing \dp to honor "\pset null" for this might be a reasonable thing to do too. I'm actually a bit surprised that that doesn't work already. regards, tom lane That change would still require someone using \dp to realize that the "Access privileges" value could be either '' or NULL (I guess that could be pointed out more obviously in the psql doc), and then do a '\pset null' before doing \dp? That seems a little inconvenient. As a possible alternative, in the query that \dp sends, what about replacing the line: select ..., pg_catalog.array_to_string(c.relacl, E'\n') as "Access privileges" ... with something like: CASE array_length(c.relacl,1) WHEN 0 THEN '(none)' ELSE pg_catalog.array_to_string(c.relacl, E'\n') END as "Access privileges" I realize that removes the ability to control with pset what is displayed, but maybe a little more foolproof for naive users like myself?
Re: Restoring default privileges on objects
On 8/29/23 08:14, Tom Lane wrote: Erik Wienhold writes: On 29/08/2023 03:23 CEST Stuart McGraw wrote: If I've done a GRANT or REVOKE on some of the tables, how do I restore the default privileges so that the “Access privileges” appears empty again? I re-granted what I think are the default privileges but the "Access privileges" column for that table contains "user1=arwdDxt/user1" rather than being blank. This is Postgresql-14. Yes, "user1=arwdDxt/user1" matches the default privileges if user1 is the table owner. Right. There is no (supported) way to cause the ACL entry to go back to null. It starts life that way as an ancient hack to save a step during object creation. But the moment you do anything to the object's privileges, the NULL is replaced by an explicit representation of the default privileges, which is then modified per whatever command you are giving. After that the privileges will always be explicit. There's been occasional discussion of changing this behavior, but it'd take work and it'd likely add about as much surprise as it removes. People have been used to this quirk for a long time. Thank you Erik and Tom for the explanations. I guess it's a it-is- what-it-is situation :-). But while trying to figure it out myself I found the following: test=# CREATE ROLE user1; test=# SET ROLE user1; test=> CREATE TABLE t1(x int); test=> \dp Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies +--+---+---+---+-- public | t1 | table | | | test=> SELECT FROM t1; (0 rows) test=> SET ROLE postgres; test=# REVOKE ALL ON t1 FROM user1; test=# SET ROLE user1; test=> \dp Schema | Name | Type | Access privileges | Column privileges | Policies +--+---+---+---+-- public | t1 | table | | | test=> SELECT FROM t1; ERROR: permission denied for table t1 How does one distinguish between (blank)=(default privileges) and (blank)=(no privileges)? Shouldn't psql put *something* (like "(default)" or "-") in the "Access privileges" column to indicate that? Or conversely, something (like "(none)"?) in the revoked case? It doesn't seem like a good idea to use the same visual representation for two nearly opposite conditions. It confused the heck out of me anyway... :-)
Restoring default privileges on objects
Hello, The Postgresql docs on object privileges, https://www.postgresql.org/docs/14/ddl-priv.html say this in regard to the output of the psql \dp command: | If the “Access privileges” column is empty for a given object, it | means the object has default privileges (that is, its privileges | entry in the relevant system catalog is null). [...] The first GRANT | or REVOKE on an object will instantiate the default privileges | (producing, for example, miriam=arwdDxt/miriam) and then modify them | per the specified request. If I've done a GRANT or REVOKE on some of the tables, how do I restore the default privileges so that the “Access privileges” appears empty again? I re-granted what I think are the default privileges but the "Access privileges" column for that table contains "user1=arwdDxt/user1" rather than being blank. This is Postgresql-14. Thanks for any suggestions!
Re: jsonb_set() strictness considered harmful to data
On 10/24/19 2:17 PM, Tom Lane wrote:
Laurenz Albe writes:
On Wed, 2019-10-23 at 13:00 -0600, Stuart McGraw wrote:
It is less sensible with compound values where the rule can apply to
individual scalar components.
I agree that JSON can sensibly be viewed as a composite value, but ...
And indeed that is what Postgresql does
for another compound type:
# select array_replace(array[1,2,3],2,NULL);
array_replace
---
{1,NULL,3}
The returned value is not NULL. Why the inconsistency between the array
type and json type?
... the flaw in this argument is that the array element is actually
a SQL NULL when we're done. To do something similar in the JSON case,
we have to translate SQL NULL to JSON null, and that's cheating to
some extent. They're not the same thing (and I'll generally resist
proposals to, say, make SELECT 'null'::json IS NULL return true).
Maybe it's okay to make this case work like that, but don't be too
high and mighty about it being logically clean; it isn't.
regards, tom lane
Sure, but my point was not that this was a perfect "logically clean"
answer, just that the argument, which was made multiple times, that
the entire result should be NULL because "that's the way SQL NULLs
work" is not really right.
It does seem to me that mapping NULL to "null" is likely a workable
approach but that's just my uninformed opinion.
Re: jsonb_set() strictness considered harmful to data
On 10/23/19 5:42 AM, Laurenz Albe wrote:
David G. Johnston wrote:
Now if only the vast majority of users could have and keep this level of
understanding
in mind while writing complex queries so that they remember to always add
protections
to compensate for the unique design decision that SQL has taken here...
You can only say that if you don't understand NULL (you wouldn't be alone).
If I modify a JSON with an unknown value, the result is unknown.
This seems very intuitive to me.
Would you expect modifying an array value with an unknown would result
in the entire array being unknown?
One could argue that whoever uses SQL should understand SQL.
But I believe that it is reasonable to suppose that many people who
use JSON in the database are more savvy with JSON than with SQL
(they might not have chosen JSON otherwise), so I agree that it makes
sense to change this particular behavior.
Yours,
Laurenz Albe
That (generally) SQL NULL results in NULL for any operation has been
brought up multiple times in this thread, including above, as a rationale
for the current jsonb behavior. I don't think it is a valid argument.
When examples are given, they typically are with scalar values where
such behavior makes sense: the resulting scalar value has to be NULL
or non-NULL, it can't be both.
It is less sensible with compound values where the rule can apply to
individual scalar components. And indeed that is what Postgresql does
for another compound type:
# select array_replace(array[1,2,3],2,NULL);
array_replace
---
{1,NULL,3}
The returned value is not NULL. Why the inconsistency between the array
type and json type? Are there any cases other than json where the entire
compound value is set to NULL as a result of one of its components being
NULL?
Re: Recomended front ends?
On 8/7/19 2:38 PM, Rich Shepard wrote: On Wed, 7 Aug 2019, Igor Korot wrote: On top of what already been said - make sure that the product you are about to start working on will have its requirements clear and concise. This is a critical process that needs to be developed in depth. One criterion that will guide your choice of UI is whether the database will be accessed only on the LAN or also remotely. For the former, consider using Python3 + psycopg + SQLAlchemy. For the latter, consider a web-based application using Django. HTH, Rich I would be a little cautious about Django. Last time I checked, like many other web frameworks, it treats the database as just another component, one that provides data persistence for it, and consequently imposes its own constraints the schemas it will work with. Specifically IIRC it insists that tables have a single-column primary keys. If the client's existing database is already designed this way then that may not be a problem but if it has composite PKs then another option may be better. Flask is another relatively easy to use framework, can be used with or without Sqlalchemy but doesn't have the wealth of addons available with Django and being simpler requires more work to build the end application. There are of course many other framework options (Bottle, Web2Py, etc) Although it's been a decade plus since I worked with Microsoft products I had fairly good luck back then using Microsoft Access / VBA connected to a Postgresql backend via ODBC. Even back then MS's frontend development tools were way more advanced and easy to use than anything available for free in the Linux world. The downside was having to program in VBA but things may be much better these days with .NET et.al.
regex DoS in postgresql?
I would like to deploy a web application with a Postgresql backend that allows a user to both add a record containing arbitrary text, and to search that text column with an arbitrary regular expression. My question is, is the app subject to a regex denial of service attack? I would have presumed so but I have been unable produce any problems when testing using the "evil" regexs described in the Wikipedia article on the subject: https://en.wikipedia.org/wiki/ReDoS Is there something in the way Postgresql implements its RE engine that makes it immune to this attack? Thanks for any enlightenment...
Re: existence of a savepoint?
On 05/29/2018 05:01 PM, Alvaro Herrera wrote: On 2018-May-29, Stuart McGraw wrote: Alternatively if there were a setting to tell Postgresql to follow the SQL standard behavior of overwriting rather stacking savepoints, that too would also solve my current problem I think. Perhaps it is just my limited experience but the former behavior has always seemed more useful in practice than the latter. I think if what we're doing breaks the semantics of the SQL spec, we're definitely open to changing our behavior. My comment was based on the statement in the Postqresql docs for SAVEPOINT: "SQL requires a savepoint to be destroyed automatically when another savepoint with the same name is established. In PostgreSQL, the old savepoint is kept, though only the more recent one will be used when rolling back or releasing. (Releasing the newer savepoint with RELEASE SAVEPOINT will cause the older one to again become accessible to ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT.) Otherwise, SAVEPOINT is fully SQL conforming." But that wouldn't solve your problem today. What I think could solve your problem today is a C-language extension that uses xact.c callbacks in order to expose a list that you can query from user space. Even that won't solve it unless I can find someone who has already written it and is willing to share it and my Googling hasn't found anything yet. :-(
Re: existence of a savepoint?
On 05/29/2018 05:18 PM, David G. Johnston wrote: On Tue, May 29, 2018 at 4:01 PM, Alvaro Herrera mailto:alvhe...@2ndquadrant.com>>wrote: On 2018-May-29, Stuart McGraw wrote: > Alternatively if there were a setting to tell Postgresql to > follow the SQL standard behavior of overwriting rather stacking > savepoints, that too would also solve my current problem I think. > Perhaps it is just my limited experience but the former behavior > has always seemed more useful in practice than the latter. I think if what we're doing breaks the semantics of the SQL spec, we're definitely open to changing our behavior. But that wouldn't solve your problem today. What I think could solve your problem today is a C-language extension that uses xact.c callbacks in order to expose a list that you can query from user space. Stuart: That said, have you measured this "leaking" and can show that it is non-trivial (given the large size of the overall transaction)? No I haven't and am not sure how I would. Are you saying I shouldn't worry about it and just not bother releasing any of the savepoints? I would feel a little uneasy about that the same way I would feel about a program that never freed allocated memory or closed open files. If I know there are relatively small limits on how much data will be processed or how long the program will run, sure. But in my case I don't control the size of the input data and I don't understand the internals of savepoints so I think caution is prudent. Also I'm not sure the warnings against premature optimization when talking about code performance tweaks apply to resource leaks. The former attempt to make a program run faster but don't (in theory) affect its correctness. Resource problems often show up unexpectedly and catastrophically. So being more preemptively concerned about the latter I think is justified. Beyond that bulk ETL leveraging SAVEPOINT is not something I've encountered or contemplated. Expecting and reacting to errors is expensive and itself error-prone. I'd much rather try to design something that where failure is simply bad - usually by bulk loading with fewer constraints and then ensuring that future queries don't attempt to do something illegal like insert duplicates. Funny you should say that :-) I am looking at rewriting these import programs (there are several) to do just that. But it is not a trivial job and in the meantime I need to keep what already exists, working.
Re: existence of a savepoint?
On 05/29/2018 08:38 AM, Alvaro Herrera wrote: On 2018-May-27, Stuart McGraw wrote: Is there some way to to test if a savepoint of a given name exists? Or better yet, the number of stacked savepoints of that name? What is the use case for this? I have a process that extracts data from a source and inserts it into a Postgresql database with one big commit at the end. The flow is not straight forward: the data is read in blocks that contain sub-blocks and certain database errors (duplicate keys, etc) may need to be rolled back to the previous insert, sub-block or block depending on context. Trying to keep track of which savepoints are active without "leaking" them (failing to release when no longer needed) in the presence of many branches and exception handlers is not impossible but would be much easier if I could introspect the savepoint state rather than having to try and track it myself. Alternatively if there were a setting to tell Postgresql to follow the SQL standard behavior of overwriting rather stacking savepoints, that too would also solve my current problem I think. Perhaps it is just my limited experience but the former behavior has always seemed more useful in practice than the latter.
Re: existence of a savepoint?
On 05/27/2018 04:39 PM, David G. Johnston wrote: On Sunday, May 27, 2018, Stuart McGraw mailto:smcg4...@mtneva.com>> wrote: Is there some way to to test if a savepoint of a given name exists? Or better yet, the number of stacked savepoints of that name? A scan of the documentation doesn't show any commands or functions that would provide this information. I was hoping that I was overlooking something. :-(
existence of a savepoint?
Is there some way to to test if a savepoint of a given name exists? Or better yet, the number of stacked savepoints of that name?
Re: source of connection fails at pg startup?
On 05/22/2018 07:58 AM, Tom Lane wrote: Stuart McGraw <smcg4...@mtneva.com> writes: When I start my postgresql server I get 11 messages reporting that "password authentication failed for user 'postgres'" spaced about ~.5sec apart. Sounds like the trace of something probing the postmaster to see if it's ready yet. Pre-v10 versions of pg_ctl did exactly that, but with a 1-second wait interval, so this couldn't be pg_ctl itself (even if you hadn't specified this is v10). This is on a Ububuntu-18.04 machine with postgresql-10.3 from Ubuntu. As distributed the pg_hba.conf line mentioned used "peer" authentication method, I have changed to "md5". When I change back to "peer" the error messages go away. In that case, whatever is doing it is running as the postgres user. Putting all this together, I'd bet on the connections coming from an Ubuntu-specific startup script. Poke around in their PG start script for something like a pg_isready call in a loop with an 0.5 second wait. I imagine that undoing that would be rather difficult, even if you wanted to run with a locally-modified script. They probably had a reason why they didn't want to leave it to pg_ctl to do the waiting. Personally, my recommendation would be to go back to "peer" auth, at least for local connections by postgres. There is no reason to think that passwords are a more secure approach: password management is a hard problem, especially for automated connections like these. Thanks, you were right, the issue is indeed from a Ubuntu (or Debian) specific startup script. I eventually found that they use a Perl script, /usr/bin/pg_ctlcluster, to run postgresql, and in there I found a function that runs psql up to 10 times at .5 second intervals to check if the server is ready. There didn't seem to be any obvious way to give it a password. The script intentionally sets the PGPASSWORD environment variable to a bogus value. Giving both OS users root and postgres .pgpass files didn't help, I guess because of the bogus PGPASSWORD value takes precedence. The reason for a password is not so much better security but that I have bunch of scripts that set up and manipulate databases that came over from my former Fedora system and that were written expecting the postgres account has a password. I made a couple stabs at changing some of them a while ago but it involved running the commands with "su - postgres ...". Some are embedded in yaml, involve variable substitution, and the multiple layers of quoting was just too much for my meager scripting skills. :-( Given that I understand the problem now thanks to you, Adrian and Luan Huynh, and that the errors don't seem to have any bad effects on the server's operation, I think I will just live with them for now. Thanks very much for everyone's help.
Re: postgresql-10.3 on unbuntu-17.10 - how??
On 03/21/2018 03:08 PM, Adrian Klaver wrote: On 03/21/2018 01:59 PM, Stuart McGraw wrote: On 03/21/2018 02:37 PM, Rob Sargent wrote: Thanks, I forgot that the older repos also received the pg-10 update. Unfortunately but no luck with Xenial either, slightly different but similar conflicts. My main motivation for updating to 10.3 was to be able to load data dumped from a 10.3 database. pg_restore complained about "unsupported version (1.13) in file header". However I just discovered I can load a plain sql dump from that database so panic mode is over :-) and I can wait until I can upgrade my machine to ubuntu-18.04. It still seems to me that the best advice for using Postgresql on Ubuntu is to use the Ubuntu version of Postgresql if you don't need the latest version; if you do need latest version, use the Pgdg version but only with a LTS version of Ubuntu. If you need the latest version of both Ubuntu and Postgresql, you may be out of luck. Or you compile it? That was going to be my next step. But I don't think a simple compile from source would do because Ubuntu's package manager wouldn't be aware that Postgresql was now available to satisfy other packages' dependencies. So I would need to rebuild the Ubuntu source package. I have done that on Fedora several times where it has been, in my limited experience, usually simple and problem free. But I have read that building packages on Ubuntu is much more arcane so I wasn't looking forward to it. That is pretty much the case when you build from source, it will live outside the OS packaging universe. I have built from source on Ubuntu it is not any more difficult then other distros, just remember to install build-essential. As far a dependencies basically the only things that will have a Postgres dependency will be other Postgres software e.g. psycopg2, etc. That means you will need to build them from source also, though that is helped along by pg_config which will find your source install and build the other software to match. It also means uninstalling the Ubuntu Postgres packages so you don't cross contaminate. I wasn't thinking of building Postgresql from the postgresql.org tarball -- I think that's what you are suggesting? I agree that in and of itself is not a problem. The problem for me is (or would have been), as you say, the corequisite rebuilding from source of other packages. Besides python's psycopg2 I have critical tools that use libdbd-pg-perl or libpq directly like the Bacula backup system. So what I was thinking of was rebuilding the Pgdg Ubuntu source package (I'm assuming one is available somewhere). I have had good results on Fedora backporting current versions of Postgresql from later fedora's to my invariably outdated version of Fedora by rebuilding the later version's Fedora postgresql source rpm. This produces an installable binary package that will satisfy the dependencies of all those other programs eliminating the need to rebuild them. I was guessing I could do something similar in the Ubuntu world. But, moot now fortunately :-)
Re: postgresql-10.3 on unbuntu-17.10 - how??
On 03/21/2018 02:38 PM, Adrian Klaver wrote: On 03/21/2018 01:31 PM, Stuart McGraw wrote: On 03/21/2018 12:14 PM, Adrian Klaver wrote: [...] It still seems to me that the best advice for using Postgresql on Ubuntu is to use the Ubuntu version of Postgresql if you don't need the latest version; if you do need latest version, use the Pgdg version but only with a LTS version of Ubuntu. If you need the latest version of both Ubuntu and Postgresql, you may be out of luck. I stick to LTS releases of Ubuntu as the in between releases tend to be too experimental for my taste:) Also the upgrades come to close together. Well, I'm coming to Ubuntu from Fedora so I'm kind of use to that. :-) But I think I will stay with 18.04 LTS after it is released.
Re: postgresql-10.3 on unbuntu-17.10 - how??
On 03/21/2018 02:37 PM, Rob Sargent wrote: Thanks, I forgot that the older repos also received the pg-10 update. Unfortunately but no luck with Xenial either, slightly different but similar conflicts. My main motivation for updating to 10.3 was to be able to load data dumped from a 10.3 database. pg_restore complained about "unsupported version (1.13) in file header". However I just discovered I can load a plain sql dump from that database so panic mode is over :-) and I can wait until I can upgrade my machine to ubuntu-18.04. It still seems to me that the best advice for using Postgresql on Ubuntu is to use the Ubuntu version of Postgresql if you don't need the latest version; if you do need latest version, use the Pgdg version but only with a LTS version of Ubuntu. If you need the latest version of both Ubuntu and Postgresql, you may be out of luck. Or you compile it? That was going to be my next step. But I don't think a simple compile from source would do because Ubuntu's package manager wouldn't be aware that Postgresql was now available to satisfy other packages' dependencies. So I would need to rebuild the Ubuntu source package. I have done that on Fedora several times where it has been, in my limited experience, usually simple and problem free. But I have read that building packages on Ubuntu is much more arcane so I wasn't looking forward to it.
Re: postgresql-10.3 on unbuntu-17.10 - how??
On 03/21/2018 12:14 PM, Adrian Klaver wrote: On 03/21/2018 10:59 AM, Stuart McGraw wrote: On 03/21/2018 07:02 AM, Adrian Klaver wrote: On 03/20/2018 10:52 PM, Stuart McGraw wrote: [...] If it where me I would simplify the above for the moment to : apt-get install postgresql-10 Tried on a fresh Ubuntu-17.10 install (with no postgresql at all installed) but similar problem persists with the postgresql from the pgdg bionic repo: # apt-get install postgresql-10 ... The following packages have unmet dependencies: postgresql-10 : Depends: postgresql-client-10 Depends: libicu60 (>= 60.1-1~) but it is not installable Depends: libpq5 (>= 9.3~) but it is not going to be installed Depends: libssl1.1 (>= 1.1.0) but it is not installable Recommends: sysstat but it is not going to be installed E: Unable to correct problems, you have held broken packages. Looks like forward compatibility issues, your 17.10 does not have the newer versions of files needed by the 18.04 Postgres packages and it will not install them as they probably would break other programs in 17.10. If it does not work going forward maybe it will work going backward, see if you have any better luck using the 16.04(Xenial) repo. Thanks, I forgot that the older repos also received the pg-10 update. Unfortunately but no luck with Xenial either, slightly different but similar conflicts. My main motivation for updating to 10.3 was to be able to load data dumped from a 10.3 database. pg_restore complained about "unsupported version (1.13) in file header". However I just discovered I can load a plain sql dump from that database so panic mode is over :-) and I can wait until I can upgrade my machine to ubuntu-18.04. It still seems to me that the best advice for using Postgresql on Ubuntu is to use the Ubuntu version of Postgresql if you don't need the latest version; if you do need latest version, use the Pgdg version but only with a LTS version of Ubuntu. If you need the latest version of both Ubuntu and Postgresql, you may be out of luck.
Re: postgresql-10.3 on unbuntu-17.10 - how??
On 03/21/2018 07:02 AM, Adrian Klaver wrote: On 03/20/2018 10:52 PM, Stuart McGraw wrote: Looks like these posts are coming through a news group to me. I am Ccing list to get response back there. Is this something I am doing wrong? I am posting through the gmane newsgroup which in turn is bi-directionally gatewayed to the list AIUI. >>[...] # apt-get -qy install postgresql postgresql-client postgresql-contrib \ postgresql-doc pgadmin3 postgresql-server-dev-10 libpq-dev [...] The following packages have unmet dependencies: libpq-dev : Depends: libpq5 (= 10.3-1.pgdg18.04+1) but it is not going to be installed pgadmin3 : Depends: libgcrypt20 (>= 1.8.0) but 1.7.8-2ubuntu1 is to be installed Depends: libpq5 (>= 8.4~) but it is not going to be installed Recommends: pgagent but it is not going to be installed [...] If it where me I would simplify the above for the moment to : apt-get install postgresql-10 Tried on a fresh Ubuntu-17.10 install (with no postgresql at all installed) but similar problem persists with the postgresql from the pgdg bionic repo: # apt-get install postgresql-10 ... The following packages have unmet dependencies: postgresql-10 : Depends: postgresql-client-10 Depends: libicu60 (>= 60.1-1~) but it is not installable Depends: libpq5 (>= 9.3~) but it is not going to be installed Depends: libssl1.1 (>= 1.1.0) but it is not installable Recommends: sysstat but it is not going to be installed E: Unable to correct problems, you have held broken packages. Is there any reason now not to conclude that the 10.3 bionic version is simply incompatible with Ubuntu-17.10 (at least without a lot more package wrangling chops than I have)? One can install postgresql-10.1 but one cannot upgrade it to get security fixes or to be able to load data dumped from another 10.3 database. Given that Ubuntu-18.04 will be out soon I guess this is pretty much moot except for a few unfortunates like me who absolutely need 10.3 but have no option to upgrade. I guess the lesson is that running the Pgdg versions of Postgresql on any but the LTS versions of Ubuntu is pretty risky. Live and learn. Maybe this will help someone else.
Re: postgresql-10.3 on unbuntu-17.10 - how??
On 03/20/2018 09:46 AM, Adrian Klaver wrote: On 03/20/2018 08:23 AM, Stuart McGraw wrote: I recently installed Ubuntu-17.10 and then discovered that Postgresql from the Pgdg repos is only supported on Ubuntu LTS releases (eg 16.04). However info on the internet said pg-10 could be installed from Pgdg zesty repo, which with some package version conflicts, I was able to do so and have a functional pg-10.1 install. However I need to upgrade to 10.3 and the normal "apt update; apt upgrade" does not offer 10.3. Also, doing a fresh install still installs 10.1. https://wiki.postgresql.org/wiki/Apt "2018-01-17: Ubuntu zesty (17.04) is unsupported now, Ubuntu removed it from their mirrors " Is Pgdg 10.3 even available for ubuntu 17.10? How the heck does one upgrade to it? 18.04 LTS (Bionic Beaver)?: http://apt.postgresql.org/pub/repos/apt/dists/bionic-pgdg/ I tried doing a full reinstall of Ubuntu-17.10 followed by a postgresql install from the bionic repo (instead of installing 10.1 per above and trying to upgrade) # apt-get -qy install postgresql postgresql-client postgresql-contrib \ postgresql-doc pgadmin3 postgresql-server-dev-10 libpq-dev Reading package lists... Building dependency tree... Reading state information... Some packages could not be installed. This may mean that you have requested an impossible situation or if you are using the unstable distribution that some required packages have not yet been created or been moved out of Incoming. The following information may help to resolve the situation: The following packages have unmet dependencies: libpq-dev : Depends: libpq5 (= 10.3-1.pgdg18.04+1) but it is not going to be installed pgadmin3 : Depends: libgcrypt20 (>= 1.8.0) but 1.7.8-2ubuntu1 is to be installed Depends: libpq5 (>= 8.4~) but it is not going to be installed Recommends: pgagent but it is not going to be installed postgresql : Depends: postgresql-10 but it is not going to be installed postgresql-client : Depends: postgresql-client-10 postgresql-contrib : Depends: postgresql-contrib-10 E: Unable to correct problems, you have held broken packages. Is there any reason now not to conclude that the 10.3 bionic version is simply incompatible with Ubuntu-17.10 (at least without a lot more package wrangling chops than I have)? One can install postgresql-10.1 but one cannot upgrade it to get security fixes or to be able to load data dumped from another 10.3 database. Given that Ubuntu-18.04 will be out soon I guess this is pretty much moot except for a few unfortunates like me who absolutely need 10.3 but have no option to upgrade. I guess the lesson is that running the Pgdg versions of Postgresql on any but the LTS versions of Ubuntu is pretty risky. Live and learn. Maybe this will help someone else.
Re: postgresql-10.3 on unbuntu-17.10 - how??
On 03/20/2018 06:42 PM, Stuart McGraw wrote: On 03/20/2018 05:34 PM, Adrian Klaver wrote: On 03/20/2018 02:25 PM, Stuart McGraw wrote: On 03/20/2018 02:19 PM, Christoph Berg wrote: [...] # apt-get dist-upgrade The following packages will be REMOVED: pgadmin3 The following packages will be upgraded: pgadmin3-data postgresql postgresql-client postgresql-client-common postgresql-common postgresql-contrib postgresql-doc postgresql-doc-10 postgresql-server-dev-10 9 upgraded, 0 newly installed, 1 to remove and 0 not upgraded. [...] Likely I am missing something obvious due to my newness with Ubuntu, but isn't the above supposed to work? 10.3 is a minor upgrade, yes? Yes it is a minor upgrade. What does pg_lsclusters show? # pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 10 main 5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log An additional bit of information. Picking one of the installed packages to look at: ~# apt-cache policy postgresql-client postgresql-client: Installed: 10+190.pgdg18.04+1 Candidate: 10+190.pgdg18.04+1 Version table: *** 10+190.pgdg18.04+1 500 500 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 Packages 500 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main i386 Packages 100 /var/lib/dpkg/status 9.6+184ubuntu1.1 500 500 cdrom://Ubuntu-Server 17.10 _Artful Aardvark_ - Release amd64 (20180108.1) artful/main amd64 Packages 500 http://us.archive.ubuntu.com/ubuntu artful-security/main amd64 Packages 500 http://us.archive.ubuntu.com/ubuntu artful-security/main i386 Packages 500 http://us.archive.ubuntu.com/ubuntu artful-updates/main amd64 Packages 500 http://us.archive.ubuntu.com/ubuntu artful-updates/main i386 Packages 9.6+184ubuntu1 500 500 http://us.archive.ubuntu.com/ubuntu artful/main amd64 Packages 500 http://us.archive.ubuntu.com/ubuntu artful/main i386 Packages I *think* the above is saying that the package was (or should be?) installed from the pgdg repository. So why isn't is getting the 10.3 versions? (Thanks for the help so far!)
Re: postgresql-10.3 on unbuntu-17.10 - how??
On 03/20/2018 05:34 PM, Adrian Klaver wrote: On 03/20/2018 02:25 PM, Stuart McGraw wrote: On 03/20/2018 02:19 PM, Christoph Berg wrote: > Re: Adrian Klaver 2018-03-20 <4c40e7c5-efa7-00d7-b891-acc9c1ec7...@aklaver.com> >>> However I need to upgrade to 10.3 and the normal "apt update; >>> apt upgrade" does not offer 10.3. Also, doing a fresh install >>> still installs 10.1. >> [...] Thank you Christoph and Adrian. I changed the apt source to deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main Then (transcripts edited for brevity)... # apt-get dist-update I am going to say that was apt-get update. More below. ... # apt-get dist-upgrade The following packages will be REMOVED: pgadmin3 The following packages will be upgraded: pgadmin3-data postgresql postgresql-client postgresql-client-common postgresql-common postgresql-contrib postgresql-doc postgresql-doc-10 postgresql-server-dev-10 9 upgraded, 0 newly installed, 1 to remove and 0 not upgraded. after completing the update and rebooting: $ psql --version psql (PostgreSQL) 10.1 $ psql -c 'select version()' PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.3.0-12ubuntu2) 6.3.0 20170406, 64-bit I suspect the above is coming from the Ubuntu repo, not the PGDG one. I had also tried 'apt-get upgrade' but that looked less promising # apt-get upgrade The following packages have been kept back: libdbd-pg-perl libpq-dev libpq5 pgadmin3 pgadmin3-data postgresql-10 postgresql-client-10 The following packages will be upgraded: postgresql postgresql-client postgresql-client-common postgresql-common postgresql-contrib postgresql-doc postgresql-doc-10 postgresql-server-dev-10 8 upgraded, 0 newly installed, 0 to remove and 10 not upgraded. The results turned out the same: postgresql-10.1, not 10.3. I took this to suggest using dist-upgrade: https://wiki.postgresql.org/wiki/Apt/FAQ#How_do_I_dist-upgrade.3F and that it should "just work". As an example from an Ubuntu machine that is using the PGDG repo: psql --version psql (PostgreSQL) 10.3 (Ubuntu 10.3-1.pgdg16.04+1) postgres=# select version(); PostgreSQL 10.3 (Ubuntu 10.3-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit Likely I am missing something obvious due to my newness with Ubuntu, but isn't the above supposed to work? 10.3 is a minor upgrade, yes? Yes it is a minor upgrade. What does pg_lsclusters show? # pg_lsclusters Ver Cluster Port Status OwnerData directory Log file 10 main5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
Re: postgresql-10.3 on unbuntu-17.10 - how??
On 03/20/2018 02:19 PM, Christoph Berg wrote: > Re: Adrian Klaver 2018-03-20 <4c40e7c5-efa7-00d7-b891-acc9c1ec7...@aklaver.com> >>> However I need to upgrade to 10.3 and the normal "apt update; >>> apt upgrade" does not offer 10.3. Also, doing a fresh install >>> still installs 10.1. >> >> https://wiki.postgresql.org/wiki/Apt >> "2018-01-17: Ubuntu zesty (17.04) is unsupported now, Ubuntu removed it from >> their mirrors " >> >>> Is Pgdg 10.3 even available for ubuntu 17.10? How the heck does >>> one upgrade to it? >> >> 18.04 LTS (Bionic Beaver)?: >> >> http://apt.postgresql.org/pub/repos/apt/dists/bionic-pgdg/ > > Ack. We skipped 17.10 and went straight to supporting the upcoming > 18.04 (you can already install it). Sorry, there's only 24h a day :( > > Christoph Thank you Christoph and Adrian. I changed the apt source to deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main Then (transcripts edited for brevity)... # apt-get dist-update ... # apt-get dist-upgrade The following packages will be REMOVED: pgadmin3 The following packages will be upgraded: pgadmin3-data postgresql postgresql-client postgresql-client-common postgresql-common postgresql-contrib postgresql-doc postgresql-doc-10 postgresql-server-dev-10 9 upgraded, 0 newly installed, 1 to remove and 0 not upgraded. after completing the update and rebooting: $ psql --version psql (PostgreSQL) 10.1 $ psql -c 'select version()' PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.3.0-12ubuntu2) 6.3.0 20170406, 64-bit Likely I am missing something obvious due to my newness with Ubuntu, but isn't the above supposed to work? 10.3 is a minor upgrade, yes?
postgresql-10.3 on unbuntu-17.10 - how??
I recently installed Ubuntu-17.10 and then discovered that Postgresql from the Pgdg repos is only supported on Ubuntu LTS releases (eg 16.04). However info on the internet said pg-10 could be installed from Pgdg zesty repo, which with some package version conflicts, I was able to do so and have a functional pg-10.1 install. However I need to upgrade to 10.3 and the normal "apt update; apt upgrade" does not offer 10.3. Also, doing a fresh install still installs 10.1. Is Pgdg 10.3 even available for ubuntu 17.10? How the heck does one upgrade to it?
Re: postgresql-10 for ubuntu-17.10 (artful)?
On 12/27/2017 11:07 AM, Steve Atkins wrote: >> On Dec 26, 2017, at 6:21 PM, Stuart McGraw <smcg4...@mtneva.com> wrote: >> Is there a repository for Postgresql-10 available at >> http://apt.postgresql.org/pub/repos/apt for Ubuntu-17.10 (artful)? >> When I look at the dist/ subdirectory there, there seem to be repos >> for all the other Ubuntu releases including an upcoming one >> (bionic) but not artful. >> >> Am I looking in the wrong place? (I am new to Ubuntu and Debian >> packaging.) > > I think that LTS releases (14.04, 16.04, 18.04) are the main releases > targeted, with intermediate releases only added if they're > incompatible with the previous LTS release. > > See the FAQ at https://wiki.postgresql.org/wiki/Apt/FAQ - I think > you should be able to use the 17.04 package on 17.10. > > Cheers, Steve Thanks. I added the 17.04 PGDG repository: deb http://apt.postgresql.org/pub/repos/apt/ zesty-pgdg main and postgresql-10 and friends installed ok. But later installing a different package: Some packages could not be installed. This may mean that you have requested an impossible situation or if you are using the unstable distribution that some required packages have not yet been created or been moved out of Incoming. The following information may help to resolve the situation: The following packages have unmet dependencies: postgresql-autodoc : Depends: libdbd-pg-perl but it is not going to be installed E: Unable to correct problems, you have held broken packages. further, # apt-get install libdbd-pg-perl [... same message as from ansible except: ] The following packages have unmet dependencies: libdbd-pg-perl : Depends: perlapi-5.24.1 but it is not installable # apt-get install perlapi E: Unable to locate package perlapi # apt show -a 'libdbd-pg-perl' [...output abbreviated...] Package: libdbd-pg-perl Version: 3.7.0-1~pgdg17.04+1 Depends: perl (>= 5.24.1-2ubuntu1), perl-dbdabi-94, perlapi-5.24.1, libc6 (>= 2.14), libpq5 (>= 8.4~), libdbi-perl (>= 1.614) Package: libdbd-pg-perl Version: 3.6.2-2build1 Depends: perl (>= 5.26.0-4), perl-dbdabi-94, perlapi-5.26.0, libc6 (>= 2.14), libpq5 (>= 8.4~), libdbi-perl (>= 1.614) As I said, I am new to Ubuntu packaging so whether the problem is something I need to research elsewhere and fix, or if it is an actual problem with the PGDG repository, is not clear to me. (Yes, I realize https://www.postgresql.org/download/linux/ubuntu/ says only LTS releases of Ubuntu are "fully" supported, but if it just takes a minor fix to make things work with 17.10...)
