Re: [GENERAL] pg on Debian servers
On Sat, Nov 11, 2017 at 01:03:18PM +, Mark Morgan Lloyd wrote: > Several legacy programs written in Delphi ground to a halt this morning, > which turned out to be because a Debian system had updated its copy of > PostgreSQL and restarted the server, which broke any live connections. > > At least some versions of Delphi, not to mention other IDE/RAD tools with > database-aware components, don't automatically try to reestablish a database > session that's been interrupted. In any event, an unexpected server restart > (irrespective of all investment in UPSes etc.) has the potential of playing > havoc on a clustered system. > > Is there any way that either the package maintainer or a site > administrator/programmer such as myself can mark the Postgres server > packages as "manual upgrade only" or similar? Or since I'm almost certainly > not the first person to be bitten by this, is there a preferred hack in > mitigation? Apart from that (putting packages on hold), PostgreSQL updates on Debian don't upgrade existing clusters automatically. They do create a new cluster but the old one is kept around and stays running, IIRC even on the very same port. (Having gone all the way from PG 7.1 to PG 10 on Debian :) What did pg_lsclusters say ? There must have been something additional at play. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Naming conventions for column names
On Mon, Nov 06, 2017 at 08:23:07PM +0530, Sachin Kotwal wrote: > You are right. Those naming conventions are old and that is why we have to > improve those where ever and when ever required. I'd love to see the "requirement" defined. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Where to find development builds of pg for windows
On Sat, Oct 28, 2017 at 02:18:52AM -0700, legrand legrand wrote: > Subject: Re: [GENERAL] Where to find development builds of pg for windows ... > I will be [...] pg 10 new features testing You can't because it's released. If you need dev builds of PG10 you'll probably have to roll them yourself from the VCS. https://www.postgresql.org/download/snapshots/ Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Data checksum with pg upgradecluster
> I'm updating my database from 9.4 to 9.6 (Debian Jessie to Stretch). I > think that it is a good opportunity to turn on data checksum. > > I don't have experience with cluster creation or moving a DB to a new cluster. > > I'll use pg_upgradecluster, but I don't see any option to turn of data > checksum. > > I took a look to the pg_upgradecluster script. I think that it invokes > pg_createcluster. Therefore I think that I should get data checksums > if I add in the file /etc/postgresql-common/createcluster.conf the > following line: > initdb_options = '--data-checksums' > > Is this correct? Yes. Been there done that. Karsten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Would you add a --dry-run to pg_restore?
On Wed, Aug 02, 2017 at 12:10:37PM -0500, Edmundo Robles wrote: > I imagine pg_restore can execute the instructions on dump but don't > write on disk. just like David said: "tell me what is going to happen > but don't actually do it" In fact, this already exists: pg_restore --file=commands.sql your-backup Then read commands.sql. It will tell you what is going to happen but not actually do it. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to drop column from interrelated views
On Sat, Jul 08, 2017 at 03:18:39PM -0700, Guyren Howe wrote: > I’ve a set of interrelated views. I want to drop a column from a table and > from all the views that cascade from it. > > I’ve gone to the leaf dependencies and removed the field from them. But I > can’t remove the field from the intermediate views because Postgres doesn’t > appear to be clever enough to see that the leafs no longer depend on the > column. Or did I just miss one? > > In general, this seems like a major weakness expressing a model in Postgres > (I get that any such weakness derives from SQL; that doesn’t stop me wanting > a solution). Not that it helps much with your immediate problem but this is typically the point where one realizes that database definitions should live under version control. That doesn't enable easy dropping of a column from interrelated views but does allow for more convenient ways of writing the required DDL change script. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Cookbook Testing
On Fri, Jun 23, 2017 at 06:33:56PM +0530, PAWAN SHARMA wrote: > > On Fri, Jun 23, 2017 at 05:57:44PM +0530, PAWAN SHARMA wrote: > > > > > Please help to configure kitchen.yml to test the PostgreSQL cookbook > > > testing. > > > > > > #-> kitchen converge > > > -> Starting Kitchen (v1.16.0) > > > >> --Exception--- > > > >> Class: Kitchen::UserError > > > >> Message: Error parsing /tmp/postgresql-master/.kitchen.yml as > > YAML. > > > Please run `kitchen diagnose --no-instances --loader' to help debug your > > > issue. > > > >> -- > > > >> Please see .kitchen/logs/kitchen.log for more details > > > >> Also try running `kitchen diagnose --all` for configuration > > > > So ? The output gives you THREE hints already. Care to do as the suggest > > ? > > nothing, it will show me the configuration of kitchen.yml "Nothing" is quite obviously not the same as "configuration of kitchen.yml". And, it is one of _three_. Also, whatever "it" _is_. You'll have to put in more effort, I suppose. But that's not the track record AFAIR. Bye, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Cookbook Testing
On Fri, Jun 23, 2017 at 05:57:44PM +0530, PAWAN SHARMA wrote: > Please help to configure kitchen.yml to test the PostgreSQL cookbook > testing. > > #-> kitchen converge > -> Starting Kitchen (v1.16.0) > >> --Exception--- > >> Class: Kitchen::UserError > >> Message: Error parsing /tmp/postgresql-master/.kitchen.yml as YAML. > Please run `kitchen diagnose --no-instances --loader' to help debug your > issue. > >> -- > >> Please see .kitchen/logs/kitchen.log for more details > >> Also try running `kitchen diagnose --all` for configuration So ? The output gives you THREE hints already. Care to do as the suggest ? Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] storing postgres data on dropbox
On Sun, Jun 18, 2017 at 06:29:50PM +, Martin Mueller wrote: > How close is close enough? In my case, the machines run OS > Sierra, and the installation uses the same directory paths > Keeping the Postgres version in sync should be simple. Is > that close enough? I am not an expert on that. Methinks the mailing list archive should have posts on that topic. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] storing postgres data on dropbox
On Sun, Jun 18, 2017 at 06:29:50PM +, Martin Mueller wrote: > In MySQL you can copy and paste individual tables if the > data are kept in ISAM, but INNO is hopeless that way. Is > Postgres more like INNO than ISAM when it comes to table > storage? *more* like INNO but not at all *like* INNO :-) Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] storing postgres data on dropbox
On Sun, Jun 18, 2017 at 05:30:44PM +, Martin Mueller wrote: > Thank for this very helpful answer, which can be > implemented for less than $100. For somebody who started > working a 128k Mac in the eighties, it is mindboggling that > for that amount you can buy a terabyte of storage in a device > that you put in a coat pocket. I'll read up on rsync I seem to remember that for this to work the two machines must be *very* close in architecture, and the PostgreSQL versions best be exactly the same. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Redo the filenode link in tablespace
On Thu, Jun 08, 2017 at 08:11:30AM -0300, tel medola wrote: > Sure! > It's going to be a little long, That's the point :-) That way, people of the future can benefit from Adrian's excellent effort. Thanks, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Redo the filenode link in tablespace
On Thu, Jun 08, 2017 at 07:53:01AM -0300, tel medola wrote: > I would like to thank Adrian very much for his great help and patience. > Without your help, most likely I would be looking for another job now, > thank you very much !!! > > Thanks to the database being Postgres and the community being so strong and > united, everything worked out in the end. > Thank you very much the people who maintains Postgres (I could even help in > some, because I'm a programmer too). And > Thanks also to the people who dedicate their time to helping unknow people > with problems, especially to Adrian. Hi, I wonder whether you might muster the time to do a writeup for the benefit of the list archive -- describe what the problem was, how it came about, and how it was solved ? Thanks, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump 8.3.3 ERROR: invalid page header in block 2264419 of relation "pg_largeobject"
On Wed, May 24, 2017 at 04:45:51PM -0700, David Wall wrote: > They do have a slave DB running via WAL shipping. Would that likely help us > in any way? If you can find out which blobs are afflicted you may be able to extract those from the slave and re-insert them into the new DB. > Because the DBs are big (they have two at 191GB and 127GB), it > takes a fair bit of time to do backups, transfers and restores. I'm trying > to find options as it likely means downtime for them that they are not > expecting (yet). Oh, they likely have downtime already, because what you first reported smells of bad hardware ? Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump 8.3.3 ERROR: invalid page header in block 2264419 of relation "pg_largeobject"
On Wed, May 24, 2017 at 07:18:14PM -0400, Tom Lane wrote: > If possible, I'd take a physical backup (e.g. with tar) of the entire $PGDATA > directory, Make sure the backup goes directly to a different physical volume in case the underlying hardware is bad. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Weird periodical pg log
On Fri, May 19, 2017 at 10:25:13AM +0200, cen wrote: > < 2017-05-15 17:00:41.861 CEST >LOG: parameter "archive_command" changed to > ""/opt/omni/lbin/pgsqlbar.exe" -stage %p -backup" This is the line that you'll have to base your research on. Also, you might want to check for a keylogger in the audio driver. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Python versus Other Languages using PostgreSQL
On Mon, May 08, 2017 at 05:45:53PM -0700, Paul Hughes wrote: > Why are Postgres and Python so married, I dare say that's a misconception. However, Python "works so well", that "professional amateurs" (like myself) who gravitate towards PostgreSQL for the obvious reasons might tend to chose Python for the very same reasons which may seem to create a bias. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] potential extension of psql's \df+ ?
On Wed, Apr 19, 2017 at 12:00:04PM +0200, Pavel Stehule wrote: > > Hence I wonder whether an approach along these lines: > > > > select > > row_number() over () > > || src_line ... > > ) as func_src; > > > > would be a worthwhile change to the query "\df+" uses ? > > > > > do you know \sf+ fce command? Sorry for the noise :-) Thanks, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] potential extension of psql's \df+ ?
Hi all ! Every so often, when working with functions, errors get reported with context information similar to this: Context: PL/pgSQL function "test_function" line 5 at SQL statement Often, the function source is kept under version control (or in a file annotated, commented, etc in certain ways) such that the file line number does not correspond to the function source line number. In such cases, a typical approach would be to go and do \df+ test_function which nicely produces function metadata and source as contained in the database. For non-trivial functions it is, however, slightly cumbersome to count the line numbers onscreen (let alone line break settings). Hence I wonder whether an approach along these lines: select row_number() over () || src_line from ( select unnest(string_to_array(prosrc, E'\n')) as src_line from pg_proc where proname = 'FUNC_NAME' ) as func_src; would be a worthwhile change to the query "\df+" uses ? (Resorting to "\ef" won't align temp file lines with function source line numbers either, because pgsql boilerplate is needed for function definition etc.) Thanks for considering, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to add columns to view with dependencies
On Sun, Apr 16, 2017 at 08:02:54PM -0700, Guyren Howe wrote: > I can imagine ways of sort-of dealing with this. I might > maintain a SQL file with views to create in a suitable order, > Then I could drop all views, edit the definition of one, then > run the file, but this is awfully tedious. This, kept under version control, seems best practice, regardless of what PG supports making the above easier. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] A change in the Debian install
On Thu, Apr 06, 2017 at 12:05:51AM -0400, Tom Lane wrote: > rob stone writes: > > Upgraded to version 9.6.2-2 and these are the log entries on start-up:- > > > 2017-04-05 08:03:29 AESTLOG: test message did not get through on > > socket for statistics collector ... > (But ... these statements are based on an assumption of out-of-the- > box Postgres behavior. I would not exactly put it past the Debian > packagers to have decided to change this for reasons of their own, > and their track record of telling us about such decisions is many > miles south of abysmal. So you might look at whatever patches > are in the Debian package to see if there's anything touching > pgstat.c's socket-setup logic.) PG 9.6 works just fine for me on mostly-Stable and Testing++ Debian systems. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] My humble tribute to psql -- usql v0.5.0
On Tue, Apr 04, 2017 at 03:48:16AM +0700, Kenneth Shaw wrote: > It should work. What database did you try that with? I haven't tried > to do heavy / extensive utf8 tests, but all of Go (including the > "readline" package that is used for capturing input are native utf8. > The problem is potentially a connect option in the DSN for the > database needs to be manually added Do you "SET client_encoding" ? Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres Permissions Article
On Tue, Mar 28, 2017 at 09:47:40AM -0700, Paul Jungwirth wrote: > I wrote a blog post about the Postgres permissions system, and I thought I'd > share: > > http://illuminatedcomputing.com/posts/2017/03/postgres-permissions/ > I also shared a few opinions amidst the facts (like that `USAGE` for schemas > doesn't add much), so I am very pleased to have those challenged. You can > consider them my own outstanding questions. I'd be especially grateful for > any feedback there. Not that I am an expert in any way but here's a thought on why a permission on foreign key creation might be useful: Being able to create foreign keys may allow to indirectly discover whether certain values exists in a table which I don't otherwise have access to (by means of failure or success to create a judiciously crafted FK). Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [ANNOUNCE] postgresql-unit 3 released
On Thu, Mar 23, 2017 at 02:51:58PM +0100, Christoph Berg wrote: > postgresql-unit 3 released > -- > > The PostgreSQL "unit" extension provides a datatype for values using > the SI base types, and Bytes. > > Highlight in version 3 of the extension is the ability to define new > prefixes and units at run-time using database tables, and the vast > number of predefined prefixes and units imported from the GNU "unit" > tool. Over 2400 units are available now. This is coming along really nicely ! May I ask whether UCUM has been considered for inclusion ? I do realize this is large (as in LARGE) -- I am not asking for something to be done, only for the current state of thinking regarding this data source. http://unitsofmeasure.org/trac https://en.wikipedia.org/wiki/Unified_Code_for_Units_of_Measure Thanks, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incremental / Level -1 backup in PG
On Wed, Mar 22, 2017 at 01:40:49AM -0700, rakeshkumar464 wrote: > upto Thu afternoon, which one do you think will be faster :- All in all, perhaps it is more a question of which one *came out* to be faster on your hardware with your load with your data after testing > I have reasons to believe that > the same should be true for PG also. As would be ? Best regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incremental / Level -1 backup in PG
On Tue, Mar 21, 2017 at 08:43:00PM -0400, Stephen Frost wrote: > Do not try to implement an incremental backup solution using > simple/naive tools like rsync with timestamp-based incrementals. It is > not safe. ... as long as the server is *running*. So, "stop" the server when using $RSYNC for $BACKUP. After which the OPs question becomes entirely independant from PostgreSQL as such, of course. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres goes to auto recovery mode after system restart(check this draft)
On Mon, Mar 20, 2017 at 06:48:36AM -0400, George Neuner wrote: > Windows informs all processes that it is shutting down (or entering > sleep, or waking up, etc.), but the notifications take different forms > depending on whether the process is a service or a normal application. > Services receive commands from the service manager, whereas > applications receive environment control messages sent to their main > window. > > pg_ctl is a command-line program that can run as a service. But since > it creates no window, when run as an application it cannot receive any > environment messages. Would it make sense to have pg_ctl create a non-visible window when run as an application in order to receive environment control messages ? http://stackoverflow.com/questions/2122506/how-to-create-a-hidden-window-in-c Just wondering, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using xmin and xmax for optimistic locking
On Mon, Feb 20, 2017 at 04:22:51PM -0500, Tom Lane wrote: > One other thought here --- if you do want to go with the "no other > updates" semantics, it still seems like it should be sufficient to > compare xmins. Comparing the xmax values would add nothing to that, > except that it would reject if another update had been attempted and > then failed, which seems undesirable. Right, we have been doing that (xmin only) in GNUmed for years in order to detect concurrent updates to our medical record. Works like a charm. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using xmin and xmax for optimistic locking
On Mon, Feb 20, 2017 at 03:44:49PM -0500, Tom Lane wrote: > >where table.*::text = (saved from select). > > > If the row was changed between the time it was first read and updated, the > > update will do touch any rows as the ::text will be different. > > > Why can't we use xmin and xmax columns to achieve the same. > > Well, that doesn't do quite the same thing: the cookbook query will > proceed if there was a no-op update in between (or maybe even two updates > that canceled each other out). If you look at xmin then you won't proceed > in such cases. I could imagine either behavior being "right" depending on > your application needs. Also a consideration: table.*::text may become quite unwieldy if there's one or more BYTEA columns in the table. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using xmin and xmax for optimistic locking
On Mon, Feb 20, 2017 at 07:27:34PM +, Rakesh Kumar wrote: > I tested it and it works. what I did was to select xmin and xmax and then > sleep for a min. > In the meantime, I update the same row in another session. > After 1 min the update session failed to update any row because the > combination of xmin > and xmax was no longer true. > > I was under the impression that xmin/xmax can not be used in the where clause > for business logic as described above. > > Am I missing anything ? If this works, it can make optimistic locking lot > easier due to generic coding using xmin/xmax. Works fine. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Missing feature - how to differentiate insert/update in plpgsql function?
On Wed, Feb 15, 2017 at 06:44:09AM -0800, Adrian Klaver wrote: > > Well, this wouldn't work for me as pkey will not change. > > Alright you lost me. If the pkey does not change then how do you get new > rows(INSERT)? I think OP is using natural (rather than surrogate) primary keys. So, the PK already exists or else is created. But the (then-returned) _value_ of either is the same. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bad planning data resulting in OOM killing of postgres
On Wed, Feb 15, 2017 at 01:04:51PM +0100, Karsten Hilbert wrote: > > Nope, that pops too. The query runs for a long time at a somewhat > > normal rate of ram consumption, using ~1G of RSS then suddenly spikes > > to about 6G, at which point the OOM killer pops it. Box has 8G of ram > > and 4G of swap. > > By any chance: > > - when it happens has the kernel considered using swap ? > > - which kernel are you running ? > > There's been (for some workloads) massive problems with RAM > exhaustion / swapping / OOM killer going wild with > 4.7/4.8/some 4.9 kernels. I guess what I'm trying to say is that it may actually not be PostgreSQL's fault but rather the kernel invoking the OOM killer way prematurely. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bad planning data resulting in OOM killing of postgres
On Mon, Feb 13, 2017 at 03:47:08PM -0600, David Hinkle wrote: > Nope, that pops too. The query runs for a long time at a somewhat > normal rate of ram consumption, using ~1G of RSS then suddenly spikes > to about 6G, at which point the OOM killer pops it. Box has 8G of ram > and 4G of swap. By any chance: - when it happens has the kernel considered using swap ? - which kernel are you running ? There's been (for some workloads) massive problems with RAM exhaustion / swapping / OOM killer going wild with 4.7/4.8/some 4.9 kernels. Just a shot in the dark, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Auto-Rollback option
On Mon, Feb 13, 2017 at 02:55:03PM +0100, Małgorzata Hubert wrote: > is there any way to set Auto-Rollback : ON, automaticly during instalation > process or using query (maybe something like set autocommit = 'on')? > We need it to automaticly close the transaction if an error occures during > implementing patches. Auto-Rollback is the implicit default. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Doubts regarding postgres Security
On Sun, Jan 22, 2017 at 12:32:21AM +0530, PAWAN SHARMA wrote: > I have few question regarding Postgres security. > > 1. How can we set user account block feature after max number of > invalid password entries? > 2. How can we use SSL encryption in Postgres on Linux environment? > 3. How can we transparent data encryption in Postgres? The information you need to answer your questions is found here: https://www.postgresql.org/docs/devel/static/index.html Regards, Karsten Hilbert -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Is it possible to specify an ESCAPE for a wildcard search using LIKE ANY (...)
On Tue, Jan 17, 2017 at 03:27:57PM +0100, Thomas Kellerer wrote: >> Do you need to have the _ NOT be recognized as a wildcard ? > > Yes, the underscore should NOT be a wildcard in this case. Understood. So, as Tom hinted at, your best bet might be to write a function escape_underscore_in_1dim_text_array(IN TEXT[], OUT TEXT[]) ... which takes an array and puts '\' in front of every '_' of each member such that you can write ... like any (escape_underscore_in_1dim_text_array(your_array)) ... Would that solve the problem ? (this assume *no* underscore is to be a wildcard in user provided input, not just some) Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it possible to specify an ESCAPE for a wildcard search using LIKE ANY (...)
On Tue, Jan 17, 2017 at 09:25:38AM +0100, Thomas Kellerer wrote: > I recently stumbled over the need to use a wildcard escape character for a > condition that makes use of LIKE ANY, something like: > >select * >from some_table >where name like any (array['foo_bar%', 'bar_foo%']) escape '/'; > > so that the underscore wouldn't be treated as a wildard May I ask for clarification: Do you need to have the _ NOT be recognized as a wildcard ? Or do yo need to have the _ be used as an escape character ? In the latter case I wonder whether the example was an unlucky choice since neither "b" nor "f" need escaping. Am I understanding you correctly that you need '_%' to have the meaning '\%' normally would in the context of a LIKE pattern ? Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Means to emulate global temporary table
On Wed, Jan 11, 2017 at 05:54:11PM -0700, David G. Johnston wrote: > I don't see where "call a setup function immediately after connecting" Sounds like a "login trigger", more generally an ON CONNECT event trigger, which we don't have at the moment as far as I know. One of the main arguments against it was that a failing trigger function might prevent all access while the suggested solution to that (I think by Tom Lane) was to auto-disable ON CONNECT triggers when starting up as --single. Unfortunately, I don't know what came of it by now. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LYDB: Feasible to use PG roles instead of application-level security?
On Fri, Dec 30, 2016 at 10:23:44AM -0500, Stephen Frost wrote: > One area that isn't fully addressed with the PG auth model today is > partial access to a certain column. Consider a table where you want > users to have access to all of the rows and all of the columns *except* > for column X for rows where ID is > 1000. The PG auth model today can > be used to say "you can't access column X" or to say "you can't access > rows where ID > 1000" but you can't combine those, yet. Do you mean that there is currently no way to say: if special_column is NOT in the SELECT list: show all rows if special_column IS in the SELECT list: show only those rows where special_column > 1000 ? Thanks, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LYDB: What advice about stored procedures and other server side code?
> Many applications are not designed to have a "stable" database API. It seems OP is arguing they should. Regards, Karsten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Looking for an online mentor
On Thu, Dec 08, 2016 at 07:47:56PM -0500, Metare Solve wrote: > But, what I'm gathering is, you think this is a crutch too. Will Python > enable me to do the same things that I do with that kind of big data > processing program? Yes and no. Python will enable you to do _way_ more (because you tell it what to do) but at first you can't do much of anything (because you need to learn programming in it first and don't have buttons to click). > Should I be aiming for that as the ultimate rather than "mastering" KNIME Yes. > I was educated as a pianist, writer, > and historian, lol. I have a lot to learn. Well, not really. Pianist - you need to tell the piano / computer _exactly_ what to do and there's a special notation for it in each realm Writer - plan you strategy for evolving a story/solving a problem Historian - diligently research what you don't know and don't know you don't know The one thing cross access people typically don't bring in is strong set-related skills. Maybe you can get a class on set management from somewhere ? Other than that try to see things from your background. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When to use COMMENT vs --
On Wed, Dec 07, 2016 at 07:57:54AM -0800, Rich Shepard wrote: > I have used '-- ' to enter comments about tables or columns and am curious > about the value of storing comments in tables using the COMMENT key word. > When is the latter more appropriate than the former? "--" only means "comment" to SQL code (such as in scripts). PostgreSQL itself simply ignores it. OTOH, using "comment on ... is ..." tells PostgreSQL to _store_ a comment on a database object for later retrieval. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Verify Option with pg_dump
Also this https://en.wikipedia.org/wiki/Silent_data_corruption#Countermeasures -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Verify Option with pg_dump
On Wed, Nov 30, 2016 at 01:53:21PM +, Howard News wrote: > Regarding the filesystem solution, the dump is currently written to a HP > > > RAID 10 array with an NTFS partition. What filesystems / raid arrays have > > > this ability? > > If you can't trust your RAID 10 (1 meaning mirrored) to > > actually store what you told it to you've got problems beyond > > somehow verifying a pg_dump. > > > > Regards, > > Karsten > I am told RAID can only protect you against disk failure. File writes to one > or more of the disks in an array are not typically compared so a RAID array > carrys on until the disk failure, or error count get to a certain level. So > RAID does not fully protect you from data corruption. True enough. So it seems you are referring to "silent data corruption". Does this link help ? http://www.raidix.com/knowledge-base/silent-data-corruption/ This link also seems relevant: http://stackoverflow.com/questions/13107783/pipe-output-to-two-different-commands Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Verify Option with pg_dump
On Wed, Nov 30, 2016 at 01:11:58PM +, Howard News wrote: > > You can try to suitably combine "pg_dump --format=plain" with > > "tee" and "md5sum" such that the output stream is diverted to > > both a file and a pipe-into-CRC-algorithm and eventually > > compare the pipe's sum with the sum generated from the file. > > > > But the better solution might be to stream to a filesystem > > that verifies disk writes immediately. Or to a suitable RAID > > array. > Thanks for this info Karsten. I will look into using "tee". As a matter of > interest, why does the format need to be plain? Actually, any of the formats producing a _single_ file right away are likely to work. So, any but "directory", I guess. > Regarding the filesystem solution, the dump is currently written to a HP > RAID 10 array with an NTFS partition. What filesystems / raid arrays have > this ability? If you can't trust your RAID 10 (1 meaning mirrored) to actually store what you told it to you've got problems beyond somehow verifying a pg_dump. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Verify Option with pg_dump
On Wed, Nov 30, 2016 at 12:00:07PM +, Howard News wrote: > recently I had problems with a corrupt pg_dump file. The problem with the > file was due to a faulty disk. The trouble with this is that I was unaware > of the disk problem and the pg_dump file corruption so I did not have a full > valid backup. In order to reduce the chances of this I was hoping that there > could be a verify option as in SQL Server for the backups. This could be as > simple as checking the CRC/MD5 as the stream is created. So pg_dump | > crc_save > > The idea being that the pg_dump is crc'd before it is streamed to disk, and > then the file re-read from disk to check the CRC. > > Is there a linux utility to do this or would it be simple to modify pg_dump > to do this? You can try to suitably combine "pg_dump --format=plain" with "tee" and "md5sum" such that the output stream is diverted to both a file and a pipe-into-CRC-algorithm and eventually compare the pipe's sum with the sum generated from the file. But the better solution might be to stream to a filesystem that verifies disk writes immediately. Or to a suitable RAID array. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Index size
On Thu, Dec 01, 2016 at 12:38:37AM +1300, Samuel Williams wrote: > Is there any reason why for the same data set, and same indexes, that > the data in postgres would be significantly larger than > innodb/mariadb? Sure: because they do entirely different things on-disk. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fwd: Creating multiple instances of postresql on Windows environment
>>> The issue is not with server , it is running fine . >>>Issue is not with port either , for local machine, >> The issue is with: >>I guess [...] >I have no idea what you're saying ... I figured the firewall might be an issue. Or it might not. But guessing won't tell. Regards, Karsten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fwd: Creating multiple instances of postresql on Windows environment
On Tue, Nov 15, 2016 at 10:49:42AM +0530, kaustubh kelkar wrote: > The issue is not with server , it is running fine . > Issue is not with port either , for local machine, The issue is with: > I guess [...] Regards, Karsten > firewall won't affect. > > > On Tue, Nov 8, 2016 at 8:14 PM, Adrian Klaver > wrote: > > > On 11/07/2016 07:27 PM, kaustubh kelkar wrote: > > > >> > >> Hi , > >> > >> I am a PostgreSQL user who wants to create multiple instances of > >> PostgreSQL database server. I am using PostgreSQL 9.4 and above. > >> > >> I tried to create more than 2 instances on Linux environment in which I > >> was successful. But, for windows environment, I tried with the help of > >> pgAdmin4 and with the help of commands (initdb and some more commands.) > >> In both cases , there is some issue related to the ports. Please find > >> the attached screenshot for the reference. > >> > > > > To run more then one instance of Postgres on one machine each needs its > > own port. Have you done that? > > > > The screenshot indicates a different problem, namely pgAdmin(?) cannot > > find a running server on port 5434 on the localhost. There can be several > > reasons for this: > > > > 1) The server is not running, eg the start up script failed. > > Solution: Verify the server has actually started > > > > 2) The server is not running on localhost. > > Solution: Verify where it is hosted. > > > > 3) It is running on localhost but not on port 5434. > > Solution: Verify that the port variable in postgresql.conf for > > that instance is set to 5434 and that the server was restarted to see the > > change. > > > > 3) It is running and is using port 5434, but a firewall rule is blocking > > access. > > Solution: Check whether you have a firewall running and whether it > > is blocking port 5434. > > > > > >> __ __ > >> > >> Please help me to resolve the issue. > >> > >> > >> __ __ > >> > >> > >> > >> > >> > >> > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com > > > > > > -- > > Kaus2bh Kelkar -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Way to quickly detect if database tables/columns/etc. were modified?
On Mon, Oct 31, 2016 at 09:14:07AM -0400, Melvin Davidson wrote: >> Maybe create an event trigger that updates a simple table with the last >> modification time or sends a notification? ... > That would certainly work, but > the problem is, that trigger would have to be created for every table in > the database. When you have more than a couple dozen tables, as in > hundreds, it becsmes a huge undertaking.*-- Well, it could be generated. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_sample
On Wed, Oct 19, 2016 at 01:24:10PM +1300, Patrick B wrote: > I'm using pg_sample to do that, but unfortunately it doesn't work well. > It doesn't get the first 100 rows. It gets random 100 rows. > > Do you guys have any idea how could I do this? For any relevant answer to this question you'll have to define what "first" means in the context of "first 100 rows". Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multi tenancy : schema vs databases
On Sat, Oct 01, 2016 at 07:21:47PM -0400, Melvin Davidson wrote: > *I would like to comment on the multiple schema vs databases situation. > First of all, 1000's of databases is insanity and just asking for trouble. > Next, 1000's of schemas is a nightmare to maintain. I understand the > requirement for client data to be "isolated", but in reality, data is never > really separated. Once it's on the server, any good hacker with a knowledge > of SQL can find it. So, IMHO, the best solution is to isolate by a client > ID in the tables of one database. Then make sure you have sufficient and > correct security on those tables.* The concern was raised that if tenant data is separated only by client_id within one and the same table a simple client_id related error in the app would expose another tenants data. Would not RLS help in avoiding this sort of thing ? Tie RLS based row visibility to the logged in user and hand out different accounts to tenants. That way, the app cannot see anything beyond what the user types into the application in terms of credentials. The app can't really do it wrong -- the user provides credentials and the database provides data based on those credentials. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Index scan is not working
On Mon, Sep 19, 2016 at 02:56:17PM +0200, Kiran wrote: > I want to know whatever the Analyze output I am getting is normal for a > table having few records or something is wrong. > Will the DB engine uses whatever the best way to execute a query > irrespective of the indexing in this case? It will use whatever it concluded to seem to be the "best" way at a given moment, yes. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Index scan is not working
On Mon, Sep 19, 2016 at 02:10:50PM +0200, Kiran wrote:
> EXPLAIN ANALYZE select * from question where weighted_tsv @@
> to_tsquery('Hur&ofta');
>
> I get the following output
>
> "Bitmap Heap Scan on question (cost=12.33..25.38 rows=10 width=731)
> (actual time=0.058..0.062 rows=3 loops=1)"
> " Recheck Cond: (weighted_tsv @@ to_tsquery('Hur&ofta'::text))"
> " Heap Blocks: exact=3"
> " -> Bitmap Index Scan on weighted_tsv_question_idx (cost=0.00..12.33
> rows=10 width=0) (actual time=0.052..0.052 rows=3 loops=1)"
> "Index Cond: (weighted_tsv @@ to_tsquery('Hur&ofta'::text))"
> "Planning time: 0.205 ms"
> "Execution time: 0.104 ms"
>
> Why the query is using the Bitmap ? Not the Index scan ?
You want PostgreSQL to use an index to try speed up a query
which takes 0.3 milliseconds to select 3 out of 10 rows ?
What is your exact workload that requires speedup of that
query ?
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers
On Sun, Aug 14, 2016 at 04:02:19PM +0200, Chris Travers wrote: > One example is of such a service locator is > http://search.cpan.org/dist/PGObject-Simple/lib/PGObject/Simple.pm > > It runs as a library which helps the program decide how to do the call. > Currently it looks in the system catalogs but you still have the ordinal > syntax too. > > One minor issue currently is that object properties override named > arguments when it should probably be the other way around. Aha, now I understand. Thank you, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers
Hello Chris, I am getting closer but ... > > > Sure. What I prefer to do is to allow for a (cacheable) lookup on the > > > basis of some criteria, either: > > > 1. Function name or > > > 2. Function name and first argument type > > > > > > This assumes that whichever discovery criteria you are using leads to > > > uniquely identifying a function. > > > > > > Then from the argument list, I know the names and types of the arguments, > > > and the service locator can map them in. This means: > > > > > > 1. You can expose an API which calls arguments by name rather than just > > > position, and > > > 2. You can add arguments of different types without breaking things as > > > long as it is agreed that unknown arguments are passed in as NULL. > > Ok. Two ways of doing this based on different discovery criteria.. The > first would be: > > CREATE FUNCTION person_save(in_id int, in_first_name text, in_last_name > text, in_date_of_birth date) > RETURNS person LANGUAGE ... as $$ ... $$; > > Then you have a service locator Which is what running where ? > that says How ? Thanks, Karsten > "I have a person object and want to call person_save." It then looks up the > function argument names and > calls it something like this: > > SELECT * FROM person_save(?, ?, ?, ?) > > with parameters > $object->id, $object->first_name, $object->last_name, $object->date_of_birth -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers
On Fri, Aug 12, 2016 at 01:32:33PM +0200, Chris Travers wrote: >>> My preference is stored procedures plus service locators >> >> Would you care to elaborate a little on the latter (service locators) ? >> > > Sure. What I prefer to do is to allow for a (cacheable) lookup on the > basis of some criteria, either: > 1. Function name or > 2. Function name and first argument type > > This assumes that whichever discovery criteria you are using leads to > uniquely identifying a function. > > Then from the argument list, I know the names and types of the arguments, > and the service locator can map them in. This means: > > 1. You can expose an API which calls arguments by name rather than just > position, and > 2. You can add arguments of different types without breaking things as > long as it is agreed that unknown arguments are passed in as NULL. Maybe I am a bit dense. Can you please give an example ? Thanks, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers
På fredag 12. august 2016 kl. 10:33:19, skrev Chris Travers : > My preference is stored procedures plus service locators I know your work on the former with respect to the financial app you are working on. Would you care to elaborate a little on the latter (service locators) ? Thanks, Karsten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres Pain Points: 1 pg_hba conf
On Thu, Aug 11, 2016 at 11:04:37AM -0600, support-tiger wrote: > #1) pg_hba conf > Out of the box the md5 setting blocks access. Most "advice" say change to > "all all trust" and indeed that works. But that seems a big security issue. > Specifying a postgres role, password, and peer does not seem to work. And > this approach is problematic if there are many roles or even dynamically > created roles. > > Or is pb_hba conf set up for web sockets and we should be using sockets? > > For general use, it seems we should not have to modify this file - it should > "just work" with good security. While I agree this sounds desirable I don't think it is possible (depending on the definition of "possible"). Would you like to offer a suggestion as to what pg_hba.conf should be configured as by default ? (Note that I am not soliciting a suggestion on behalf of the PostgreSQL team.) Methinks "deny-by-default" is Good Practice security-wise ? Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can stored procedures be deployed online
On Mon, Aug 01, 2016 at 12:48:57PM -0400, Rakesh Kumar wrote: > Can an existing stored procedure be modified online while other users > are executing it. In Oracle, the session doing CREATE OR REPLACE > PACKAGE would wait for other session to complete. Once the package is > changed, first time other sessions will get an error "package > invalidated". How is it in PG. If you Read The Fine Manual https://www.postgresql.org/docs/devel/static/index.html I am pretty sure there's something about DDL transactibility related to CREATE FUNCTION https://www.postgresql.org/docs/devel/static/sql-createfunction.html In particular, by extension, the example way at the bottom seems relevant to your question. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore out of memory
On Tue, Jul 12, 2016 at 12:25:08PM +0100, Miguel Ramos wrote: > > # pg_restore -d recovery /mnt/paysdeloire2013_convertida2.1.dump > > pg_restore: [custom archiver] out of memory > > 12:09:56.58 9446.593u+1218.508s 24.3% 167+2589k 6+0io 0pf+0sw 6968822cs ... > I suspect that the restore fails when constructing the indices. After the > process is aborted, the data appears to be all or most there, but no > indices. ... > I don't know what else to try. You could try restoring w/o indices and re-adding them later. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Stored procedure version control
On Thu, Jun 30, 2016 at 09:16:49AM -0500, Merlin Moncure wrote: > It's not really necessary to create version down scripts. In five > years of managing complex database environments we've never had to > roll a version back and likely never will; in the event of a disaster > it's probably better to restore from backup anyways. Also, a very robust approach to rollback is to clone the existing databse before upgrading the schema (if feasible due to size). This is the approach GNUmed uses - migration scripts are up only, as many of them as possible are idempotent, and we clone the existing database into a new one before the upgrade is run. That way, users can go back to the previous database immediately anytime and reattempt the upgrade when convenient. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Slides for PGCon2016; "FTS is dead ? Long live FTS !"
>> I submitted slides to pgcon site, but it usually takes awhile, so you can
>> download our presentation directly
>> http://www.sai.msu.su/~megera/postgres/talks/pgcon-2016-fts.pdf
Looking at slide 39 (attached) I get the impression that I
should be able to do the following:
- turn a coding system (say, ICD-10) into a dictionary
by splitting the terms into single words
say, "diabetes mellitus -> "diabetes", "mellitus"
- define stop words like "left", "right", ...
say, "fracture left ulna" -> the "left" doesn't
matter as far as coding is concerned
- also turn that coding system into queries by splitting
the terms into single words, concatenating them
with "&", and setting the ICD 10 code as tag on them
say, "diabetes mellitus" -> "diabetes & mellitus [E11]"
- run an inverse FTS (FQS) against a user supplied string
thereby finding queries (= tags = ICD10 codes) likely
relevant to the input
say, to_tsvector("patient was suspected to suffer from diabetes
mellitus")
-> tag = E11
Possible, not possible, insane, unintended use ?
Thanks,
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
pgcon-2016-fts-Seite-39.pdf
Description: Adobe PDF document
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Ascii Elephant for text based protocols - Final
> On Tue, May 17, 2016 at 8:25 AM, Victor Yegorov wrote:
> > I had a bit of fun with this SQL version and came up with this query:
> >
> > WITH src(s) AS (
> > VALUES
> > ('729472967293732174412176b12173b17111752171927491b1744171b17411217181417211718141734172b191721191724173b1714171912175b17221b1912174b1412178b121715122a172a1b2317d91a172a17f71b1a1912177')
> > ), str AS (
> > SELECT string_agg(repeat(translate(substr(s, p, 1), '123456789ab', '(/>)<+
> > o_|\'), ('x'||lpad(substr(s, p+1, 1), 8, '0'))::bit(32)::int), '') line
> > FROM src, generate_series(1, 182, 2) p
> > )
> > SELECT substr(line, p, 21) slon FROM str, generate_series(1, 189, 21) p;
I would nominate this to be called
select pg_logo_obfuscated();
Karsten
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Ascii Elephant for text based protocols - Final
On Tue, May 17, 2016 at 06:58:14AM +0200, Charles Clavadetscher wrote: > A question to the naming. I find pg_logo() also a good name, but is the > prefix pg_* not reserved for system functions? Of course I could use the > name I want, but was wondering if there is a policy or a best practice in > this area. pg_logo would only be suitable if it got blessing from "higher up". Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Ascii Elephant for text based protocols - Final
select pg_logo(); seems like a good idea to me :-) Karsten > SQL version by Melvin Davidson: > > CREATE TABLE elephant > (row_num integer NOT NULL, > row_dat varchar(30) NOT NULL, > CONSTRAINT elephant_pk PRIMARY KEY (row_num) > ); > > INSERT INTO elephant > (row_num, row_dat) > VALUES > ( 1,'++'), > ( 2,'| __ ___|'), > ( 3,'| /)/ \/ \ |'), > ( 4,'| ( / ___\) |'), > ( 5,'| \(/ o) ( o) ) |'), > ( 6,'| \_ (_ ) \ ) _/ |'), > ( 7,'| \ /\_/\)/ |'), > ( 8,'| \/ |'), > ( 9,'| _| ||'), > (10,'| \|_/ |'), > (11,'||'), > (12,'| PostgreSQL 1996-2016 |'), > (13,'| 20 Years of success |'), > (14,'++'); > > SELECT row_dat FROM elephant ORDER BY row_num; -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Lightest way of checking if postgresql is running at the other end of an ssh tunnel?
On Wed, May 11, 2016 at 02:28:47PM +0200, Vik Fearing wrote: > >> We have an ssh connection running from one server to our > >> postgresql database on another server. Some times we > >> experience that the ssh tunnel does not work anymore and > >> needs to be restarted, even though we use the autossh > >> package. I would like to write a script that “pings” > >> postgresql on the specified port, to check if the connection > >> goes through. I have tried with netcat, but it does not > >> really check if postgresql is in the other end of the tunnel, > >> it only check if there is as service (the tunnel) listing on > >> the port on the local machine. Is there another way of > >> pinging the port, to see if postgresql is alive at the other > >> end? If possible, I would like to NOT actually establishing a > >> connection to postgresql like if i used psql -c “select 1;”, > >> to avoid connection overhead. > > > > This > > > > http://www.postgresql.org/docs/devel/static/libpq-connect.html > > > > talks about ping functionality. Maybe you can use a tiny > > custom piece of code ? > > That tiny custom piece of code would be this: > > http://www.postgresql.org/docs/current/static/app-pg-isready.html That's what I had in mind :-) Thanks, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Lightest way of checking if postgresql is running at the other end of an ssh tunnel?
On Wed, May 11, 2016 at 11:17:54AM +0200, Niels Kristian Schjødt wrote: > We have an ssh connection running from one server to our > postgresql database on another server. Some times we > experience that the ssh tunnel does not work anymore and > needs to be restarted, even though we use the autossh > package. I would like to write a script that “pings” > postgresql on the specified port, to check if the connection > goes through. I have tried with netcat, but it does not > really check if postgresql is in the other end of the tunnel, > it only check if there is as service (the tunnel) listing on > the port on the local machine. Is there another way of > pinging the port, to see if postgresql is alive at the other > end? If possible, I would like to NOT actually establishing a > connection to postgresql like if i used psql -c “select 1;”, > to avoid connection overhead. This http://www.postgresql.org/docs/devel/static/libpq-connect.html talks about ping functionality. Maybe you can use a tiny custom piece of code ? Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it possible to recover the schema from the raw files?
On Sun, Apr 24, 2016 at 05:07:10PM +0100, Tomas J Stehlik wrote: >> Have you even tried a schema only dump from the original instance? > > That's an odd question. However, I understand that maybe a lot of beginners > come onto this mailing list. :-) > You can safely assume that I wrote the original request because all the > other approaches failed to bring a result. Everything has already been tried > and tested. Given the fact that no-one will know "all" approaches to "everything" it may help to bring together which approaches have actually been tried in which exact fashion. Personally, I'd be humble enough to assume (hope ?) someone would know Everything+1. In which case I'd rush to supply Everything in order to most quickly learn of +1. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it possible to recover the schema from the raw files?
On Sun, Apr 24, 2016 at 04:58:48PM +0100, Tomas J Stehlik wrote: >>> The question potentially targets someone who could tell >>> whether something like this is possible. >> >> "possible" depends no the exact circumstances, the details of >> which people have been trying to tease out. > > All this relevant information has already been supplied previously. In that case I must surely have missed it and feel I can no longer be of any assistance, even if it only amounted to but contributing leads to be investigated. Sorry. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it possible to recover the schema from the raw files?
On Sun, Apr 24, 2016 at 04:42:21PM +0100, Tomas J Stehlik wrote: >> If I recall correctly, you stated that the data isn't >> important in this > > Yes, exactly. However, pages in blocks apparently store also the > representations of the database schemas. And those are also corrupt. In that case _my_ knowledge also goes only so far as to be able to fear "no it is not possible". More knowledgeable people may - given more detailed information - still be able to suggest approaches to recover most if not all of the schema. Like replacing (some of) the pg_* containing raw files with those from an uncorrupted database (having been suggested earlier this year) which may work if the corrupted blocks in pg_* only affect data actually describing _that_ database rather than establishing relationships not unique to this database (say, encodings, default operators, ...). If those can be replaced and there is still corruption in some parts describing the local schema then it may work to apply zero_damaged_pages, pg_resetxlog, and similar tools in order to make some of the schema dumpable. It may help to look into disabling system indexe as well. Best regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it possible to recover the schema from the raw files?
On Sun, Apr 24, 2016 at 04:35:56PM +0100, Tomas J Stehlik wrote: >> Well if the corrupted raw files include the system information >> then I think you are out of luck. > > Well, this topic is not about "luck". Surely, English isn't my vernacular language - but "out of luck" has seemed to be an euphemism for "no, it ain't possible" to me unto now. Adrian cautiously added "I think" (as in 'he thinks'). Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it possible to recover the schema from the raw files?
On Sun, Apr 24, 2016 at 04:35:56PM +0100, Tomas J Stehlik wrote: > > So did you do a schema only dump or a complete dump? > > There is no dump. There are just raw files. I believe Adrian wanted to know whether you attempted a schema or complete dump *after* the fact, like what he suggested a mail ago or so. In case the FS corruption "only" affects raw files related to user data (as opposed to also affecting data in pg_* tables) a schema-only dump does have a slight chance of success. That chance might potentially be increased by judicious use of zero_damaged_pages and related low-level techniques the prerequisite conditions of which people seem to have been trying to inquire about upthread. > The question potentially targets someone who could tell whether something > like this is possible. "possible" depends no the exact circumstances, the details of which people have been trying to tease out. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it possible to recover the schema from the raw files?
On Sun, Apr 24, 2016 at 04:16:10PM +0100, Tomas J Stehlik wrote: > Please note that I mentioned previously that the database is corrupt. Given the facts that Adrian attempted to engage in a solution-bound conversation all the while mentioning that he doesn't know how to recover the schema from the raw files I feel inclined to consider it a fair assumption that he did, indeed, note that you mentioned that the database is corrupt. :-) > "pg_class" table can be queried but it is not possible to dump the database > in question as some of the pages in blocks are missing. If I recall correctly, you stated that the data isn't important in this case. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it possible to recover the schema from the raw files?
On Sun, Apr 24, 2016 at 03:00:09PM +0100, Tomas J Stehlik wrote: > > What happened if you connected to another database in the cluster? > > That's irrelevant. I dare assume Adrian asked for a reason :-) Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created
On Thu, Apr 21, 2016 at 09:40:18AM -0400, Melvin Davidson wrote: > "and what about user objects added to a database which is > then used as a template for creating another DB ?" > > This existence of objects that are part of the default schema is NOT a > problem. Developers and users should never have access to a template. Just one example of why that assertion does not hold: GNUmed stores medical records. There's no allowance for loosing data. One measure it takes to protect data is to execute (roughly) the following sequence when a database schema upgrade is needed (currently at major release 21 thereof). Say, going from v20 to v21: - create database 'gnumed_v21' template 'gnumed_v20' - from this point on gnumed_v20 is NOT TOUCHED anymore - at this point gnumed_v21 is identical to gnumed_v20 as far as GNUmed is concerned - apply - to gnumed_v21 - those SQL fixups scripts intended to bring v20 up to the very latest minor release of v20 - apply - to gnumed_v21 - the v20.latest->v21 upgrade SQL scripts - apply - to gnumed_v21 - the SQL fixup scripts intended to bring v21 up to the very latest minor release of v21 Whatever goes wrong after having cloned gnumed_v20 into gnumed_v21 doesn't matter to the user because they can _always_ go back to using the gnumed_v20 database until a future upgrade run succeeds at which point they can switch over. Of course, this can also be done via dump v20 / restore into v21 but that's slightly more fragile (more things can go wrong). > The point is to be able to track down rogue objects created > by developers and users That is easy. Compare dumps of the current schema against the official schema. In fact, GNUmed does so. The upgrade does not even start if the template schema does not pass an md5 comparison and it does not consider success unless the upgraded schema passes another (target) md5 comparison. Furthermore, the client refuses to connect to a given database if it cannot verify that database's schema via expected md5 thereof. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created
On Wed, Apr 20, 2016 at 07:09:24PM -0400, Melvin Davidson wrote: > There is also the situation of tables with limitited use. EG: > history_mm, in which case it would facilitate dropping of tables that > are no longer needed after x amount of time. select * from pg_class where to_timestamp(substring(relname from 9), 'MM') CONDITION; Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created
On Wed, Apr 20, 2016 at 05:17:20PM -0500, Kevin Grittner wrote: > if someone had been allowed to run ad hoc > reports or data cleanup on a database it was a quick way to look > for stray tables they may have generated to keep intermediate > results or exceptions, so we could follow up on disposition of > those tables. Would pg_dump -schema-only sort diff official-DDL.sql.sorted do, too ? Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created
On Wed, Apr 20, 2016 at 03:02:52PM -0700, Adrian Klaver wrote: > No one is arguing that slapping a new column on pg_class is not easy, just > that the implications of doing so requires a good deal of thought. The first > thing that comes to my mind(also in threads on --hackers) is what is the > creation time?: > > The first time an object was ever created? > > The time it was created in a new database during a > dump-restore/pg_upgrade/replication? ... and what about user objects added to a database which is then used as a template for creating another DB ? - initial add time ? - template-reuse time ? Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function PostgreSQL 9.2
On Wed, Apr 20, 2016 at 03:55:50PM -0700, Adrian Klaver wrote: > >If I am following, this duplicates the information in > >companies.client_code_increment, in that they both return the last > >non-user code. Of course this assumes, as David mentioned, that the > >client is not using a numeric code system. Then you are left trying > >to figure whether a number is 'your' number or 'their' number? > > > > > >The customer can add any value into users.code: > > > >code CHARACTER VARYING, > > > > > >But he also can let it blank/null if he wants to. > >That's when the trigger do its job.. Put a value (starting in 1000) in > >that column. > > Understood, but what happens if the customer has been using a code of: > > ... 998, 999, 1000 > > They then left the code null on the next two items and your function stuck > in 1001 and 1002. Then they figured out what they wanted to do with the > codes on their end but wanted the items to have codes of 1002, 1001 for the > items you coded 1001, 1002 respectively. > > > > >Of course that has to be unique, as nobody can use the same value of others. > > Unique within a customer, which is what your code implied or unique across > all customers? > > > > > > >- I was hoping you cans could help me to start doing the function... > > Well, I am with David on this, either the customer is totally in charge of > the codes or you are. The thought of mixing systems gives me a headache. How about _two_ columns (pseudo code) .user_picked_code (can be null) .assigned_code not null serial starts_with 1000 would that help any ? .assigned_code would always be set from a sequence but .user_picked_code would be _used_ (say, via a view) IF NOT NULL. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Enhancement request for pg_dump
On Sat, Apr 16, 2016 at 01:33:21PM -0600, Sergei Agalakov wrote: > Currently as in PG 9.4, 9.5 the order of the statements in the script > produced by pg_dump is uncertain even for the same versions of the databases > and pg_dump. > One database may script grants like > > REVOKE ALL ON TABLE contracttype FROM PUBLIC; > REVOKE ALL ON TABLE contracttype FROM madmin; > GRANT ALL ON TABLE contracttype TO madmin; > GRANT SELECT ON TABLE contracttype TO mro; > GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE contracttype TO musers; > > and the other may change the order of grants like > > REVOKE ALL ON TABLE contracttype FROM PUBLIC; > REVOKE ALL ON TABLE contracttype FROM madmin; > GRANT ALL ON TABLE contracttype TO madmin; > GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE contracttype TO musers; > GRANT SELECT ON TABLE contracttype TO mro; > > It complicates the usage of pg_dump to compare the structures of the two > similar databases like DEV and PROD, two development branches etc. > If the order of the statements generated by pg_dump would be guaranteed then > it will be very easy to compare the structures and > security rights of the two databases using only pg_dump and a diff/merge > tool. Currently we encounter a lot of false differences. > A sorted order of the DDL and DCL statements in a dump can be implemented as > a flag to pg_dump or even better as a default behavior. Since the actual order of statements inside the text mode dump file does not matter (no restore is being attempted) -- rather only that the order is predictable -- would it not suffice to run the two dumps through a generic text sort program ? pg_dump -D DEV ... | sort > broken-but-sorted-dump-1.txt pg_dump -D PROD ... | sort > broken-but-sorted-dump-2.txt diff ... broken-but-sorted-dump-1.txt broken-but-sorted-dump-2.txt Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] what database schema version management system to use?
On Fri, Apr 08, 2016 at 09:09:22AM -0500, Merlin Moncure wrote: > I rolled my own in bash. It wasn't that difficult. The basic tactic is to: > > *) separate .sql that can be re-applied (views, functions, scratch tables, > etc) from .sql that can't be re-applied (create table, index, deployment > data changes etc). I call the former 'soft' and latter 'hard' changes. > *) keep each database tracked in its own folder in the tree and put all the > soft stuff there. I keep all the hard stuff in a folder, 'schema'. I also > ha ve a special library folder which tracks all databases > *) redeploy 'soft' changes every release. The bash script deploys files in > mtime order after setting mtime to git commit time since git doesn't track > mtime > *) keep a tracking table in each database tracking deployed scripts GNUmed does pretty much the same thing except we call it "static" vs "dyamic" changes. Also, with modern PostgreSQL versions (UPSERT, ON CONFLICT, IF EXISTS) many items among "index, deployment data changes" can be turned into soft (dynamic) changes. We've never had a single bit of patient data get lost among GNUmed database versions up to the current v21 (but of course we are paranoid and check md5 sums of the schema before/after upgrades and run automated data conversion sanity checks after an upgrade). Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] what database schema version management system to use?
On Thu, Apr 07, 2016 at 06:21:10AM -0400, Berend Tober wrote: > I would be interested in knowing specifically how the ".SQL file which > updates the previous version to the new version" is generated. Is there a > tool that does that based on the difference between new and old? Or is that > update script coded by hand? We (GNUmed) create it manually. In fact, those scripts are a by-product of sane database layout development. Starting from what is, developers write scripts which modify the layout to what shall be. It doesn't seem to be good practice to do point-and-click-based "development" of databases. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Uninstalled working db by mistake
On Thu, Mar 24, 2016 at 05:34:21PM +, David Wilson wrote: > So long as you haven't touched anything else, simply reinstalling the > package should restore your cluster. Debian packages only do > initialization if the data directories are missing. Just for good measure I would strongly suggest taking a full file level copy of the datadir before reinstalling the package. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Schema Size - PostgreSQL 9.2
On Fri, Mar 18, 2016 at 09:38:30AM +1300, drum.lu...@gmail.com wrote: > Can you please provide me a Query that tells me how much space is a Schema > in my DB? There's been a discussion on that recently (like last month) which can be found in the archive. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore fails
On Sat, Mar 12, 2016 at 05:49:56PM -0700, David G. Johnston wrote: > I'd operate under the premise that all warnings and errors are fatal > (i.e., keep --exit-on-error) until you cannot for some very specific > reason. --exit-on-error will exit on _any_ perceived error, regardless of whether it could be ignored and the restore still succeed later on. Hence I cannot keep that option in use in order to implement the below. The unfortunate thing is that *any* restore will "fail" because the schema PUBLIC is copied from the template and that alone will produce an (ignorable) error... > I'd decide how to proceed at that point. For instance pg_restore > does provide an ignored error count at the end - you could scan the log for > expected errors, count them, and compare to that value and fail if the > count differs. That is a good idea. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore fails
On Sat, Mar 12, 2016 at 05:31:38PM -0700, David G. Johnston wrote: > > The reason being, of course, that I want to check the exit > > code in a pg_restore wrapper script. > > > > > I mistakenly thought public only came from template1...I wouldn't be > opposed to that change. This all seems awfully familiar too... > > You probably should just drop the existing database and use --create by > itself. > > You can even use the dropdb command to avoid SQL in your script. I already do something similar: the wrapper fails if the target db exists before a restore is even attempted. The restore itself now uses --create and works as expected. The only thing left ATM is that I cannot distinguish success-with-or-without-ignored-errors from real failure. I _can_ partly work around that by attempting to connect to the target and checking the md5 sum of the schema definition against a known hash. That won't help with detecting whether pg_restore thought that _data_ was successfully restored ... Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore fails
On Sat, Mar 12, 2016 at 04:53:20PM -0700, David G. Johnston wrote:
> The docs could probably use improvement here - though I am inferring
> behavior from description and not code.
>
> The create option tells restore that it is pointless to use conditions or
> actively drop objects since the newly created database is expected to be
> empty. The --clean option will cause pg_restore to drop the database if it
> exists but only the database. The --if-exists option would seem to be
> extraneous.
>
> The clean option with create seems to be misleading since the advice later
> in the document is to ensure the created database is empty by using
> template0 - which you cannot specify directly within pg_restore and so
> createdb or an equivalent command should be used to stage up the empty
> database before performing a simple (no create or clean) restore.
>
> I'm not certain why the create database command constructed when specifying
> --create isn't just defaulted to template0...and for completeness a
> --template option added for user template specification
The thing is, even when defaulting --create to template0 it
would contain a copy of the PUBLIC schema from template0,
which is then attempted to be restored from the dump, if
included.
As Adrian pointed out, that's not a problem as the restore
continues anyway (which I was able to confirm).
However, pg_restore.c seems to suggest
420 /* done, print a summary of ignored errors */
421 if (AH->n_errors)
422 fprintf(stderr, _("WARNING: errors ignored on restore: %d\n"),
423 AH->n_errors);
424
425 /* AH may be freed in CloseArchive? */
426 exit_code = AH->n_errors ? 1 : 0;
427
428 CloseArchive(AH);
that the exit code is set to 1 if any errors ensued (but were
ignored). Thusly the restore may have succeeded semantically
but is still flagged as (technically) failed. That wouldn't
be a problem if the condition
really-fully-failed
could be differentiated from
technical-failure-but-ignored-and-semantically-succeeded
at the exit code level since the latter outcome can be
expected to happen under the circumstances described above.
Am I thinking the wrong way ?
The reason being, of course, that I want to check the exit
code in a pg_restore wrapper script.
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore fails
On Sun, Mar 13, 2016 at 12:37:02AM +0100, Karsten Hilbert wrote: > > > pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC: > > > pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 8; > > > 2615 2200 SCHEMA public postgres > > > pg_restore: [Archivierer (DB)] could not execute query: FEHLER: Schema > > > „public“ existiert bereits > > > Die Anweisung war: CREATE SCHEMA public; > > > > > >I am sure I am doing something wrong, but what ? > > > > Did it actually fail or did it just throw an error? > > In other words did the restore continue past the error? > > Good question. I'll remove the --exit-on-error and retry :-) It actually went through with the last line saying WARNING: 1 error during restor was ignored pg_restore does not return exit code 0 anymore, however, and offhand I can't find documentation as to whether pg_restore returns different error codes between success and success-with-ignored-errors. It does not seem to return 0 when it "ignores" errors. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore fails
On Sat, Mar 12, 2016 at 03:32:15PM -0800, Adrian Klaver wrote: > > pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC: > > pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 8; > > 2615 2200 SCHEMA public postgres > > pg_restore: [Archivierer (DB)] could not execute query: FEHLER: Schema > > „public“ existiert bereits > > Die Anweisung war: CREATE SCHEMA public; > > > >I am sure I am doing something wrong, but what ? > > Did it actually fail or did it just throw an error? > In other words did the restore continue past the error? Good question. I'll remove the --exit-on-error and retry :-) Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore fails
On Sun, Mar 13, 2016 at 12:09:19AM +0100, Karsten Hilbert wrote: In case it is needed: > pg_restore: erstelle SCHEMA „public“ creating SCHEMA "public" > pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC: Error in Phase ... > pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 8; > 2615 2200 SCHEMA public postgres Error in TOC entry 8 ... > pg_restore: [Archivierer (DB)] could not execute query: FEHLER: Schema > „public“ existiert bereits ERROR: Schema "public" already exists > Die Anweisung war: CREATE SCHEMA public; The command was: CREATE ... Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_restore fails
Hi, Debian Stretch PG 9.5.1 I am trying to pg_restore from a directory dump. However, despite using --clean --create --if-exists I am getting an error because schema PUBLIC already exists. That schema is, indeed, included in the dump to be restored and also cannot be omitted from either the dump or the restore because it still contains a few relevant things which I haven't yet moved to their own app specific schema. I am assuming (wrongly ?) that pg_restore uses template1 to re-create the target database. I had to re-create template1 today from template0 (as is suggested) because I erroneously added a few tables to template1 earlier. So, the newly created target DB will, indeed, contain a schema PUBLIC initially. That should not (?) matter however, because of the above options which I would have expected to drop the schema before (re)creating it (--clean). Here is the log: sudo -u postgres pg_restore --verbose --create --clean --if-exists --exit-on-error --disable-triggers --dbname=template1 -p 5432 /tmp/gnumed/gm-restore_2016-03-12_23-58-05/backup-gnumed_v20-GNUmed_Team-hermes-2016-03-07-21-15-06.dir/ pg_restore: verbinde mit der Datenbank zur Wiederherstellung pg_restore: entferne DATABASE gnumed_v20 pg_restore: erstelle DATABASE „gnumed_v20“ pg_restore: verbinde mit neuer Datenbank „gnumed_v20“ pg_restore: verbinde mit Datenbank „gnumed_v20“ als Benutzer „postgres“ pg_restore: erstelle SCHEMA „au“ pg_restore: erstelle SCHEMA „audit“ pg_restore: erstelle SCHEMA „bill“ pg_restore: erstelle COMMENT „SCHEMA bill“ pg_restore: erstelle SCHEMA „blobs“ pg_restore: erstelle SCHEMA „cfg“ pg_restore: erstelle COMMENT „SCHEMA cfg“ pg_restore: erstelle SCHEMA „clin“ pg_restore: erstelle SCHEMA „de_de“ pg_restore: erstelle SCHEMA „dem“ pg_restore: erstelle SCHEMA „gm“ pg_restore: erstelle SCHEMA „i18n“ pg_restore: erstelle SCHEMA „pgtrgm“ pg_restore: erstelle SCHEMA „public“ pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC: pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 8; 2615 2200 SCHEMA public postgres pg_restore: [Archivierer (DB)] could not execute query: FEHLER: Schema „public“ existiert bereits Die Anweisung war: CREATE SCHEMA public; I am sure I am doing something wrong, but what ? Thanks, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Q: extract database name from directory dump
On Sat, Mar 12, 2016 at 02:59:05PM -0700, David G. Johnston wrote:
> And a much more reasonable assumption would have been 9.5 - let the user
> complain if/when the advice doesn't work because they are on an unstated
> older release that doesn't support the feature in question.
>
> I guess the O/S would be needed for syntax purposes but the typical
> responder would simply provide an answer if whatever O/S shell they are
> familiar with and deal with the issue of making it work elsewhere if needed.
>
> It is, however, quite needed to report the version (and usually O/S) when
> posting to the -bugs list. This here is the -general list and the need to
> do so is generally not all that frequent though is appreciated as it makes
> giving targeted advice a bit easier.
Thanks but no worry. I am myself actually in favor of
providing sufficient information. So I did and took the
possibly (!) ever so slight chiding undertones (?) as a
lesson in humility :-))
Eventually, I went with
TARGET_DB=`pg_restore -C -s ${BACKUP}.dir | head -n 40 | grep -i
"create database gnumed_v" | cut -f 3 -d " "`
which is intended to be used under
bash:
Installiert: 4.3-14+b1
Installationskandidat: 4.3-14+b1
Versionstabelle:
*** 4.3-14+b1 0
990 ftp://ftp.de.debian.org/debian/ stretch/main i386 Packages
500 ftp://ftp.de.debian.org/debian/ unstable/main i386 Packages
100 /var/lib/dpkg/status
4.3-11+b1 0
500 http://ftp.de.debian.org/debian/ jessie/main i386 Packages
Note that I changed the "head -1" to "head -n 40"
coreutils:
Installiert: 8.25-2
Installationskandidat: 8.25-2
Versionstabelle:
*** 8.25-2 0
990 ftp://ftp.de.debian.org/debian/ stretch/main i386 Packages
500 ftp://ftp.de.debian.org/debian/ unstable/main i386 Packages
100 /var/lib/dpkg/status
8.23-4 0
500 http://ftp.de.debian.org/debian/ jessie/main i386 Packages
Thanks,
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Q: extract database name from directory dump
On Sat, Mar 12, 2016 at 04:17:07PM -0500, Melvin Davidson wrote: > BTW, other than the obvious of including the name in path or file, if you > are referring to previous/existing dumps I do. > grep -i some_dump_file 'CREATE DATABASE' That will not work (directly) because the dump is in directory format. > If nothing is found, then the dump can be applied to ANY database. Since one can create an SQL dump from the directory dump the above becomes possible by appropriate use of pg_restore -C ... | grep CREATE DATABASE This is, indeed, the option (after pg_restore -l | grep ...) I consider least fragile. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Q: extract database name from directory dump
On Sat, Mar 12, 2016 at 01:12:52PM -0800, John R Pierce wrote: >> Constraints of the question: >> >> - existing dump in directory format >> - dump was taken of only one particular database > > I know of no documentation on the format of the toc.dat file contained in > that directory format pg_dump output (short of reading the source to > pg_dump/restore?) but I tried a hexdump... > > $ hexdump -C junky/toc.dat > 50 47 44 4d 50 01 0c 00 04 08 03 01 01 00 00 00 > |PGDMP...| > 0010 00 24 00 00 00 00 2d 00 00 00 00 0c 00 00 00 00 > |.$-.| > 0020 0c 00 00 00 00 02 00 00 00 00 74 00 00 00 00 00 > |..t.| > 0030 00 00 00 00 04 00 00 00 6a 75 6e 6b 00 06 00 00 > |junk| > 0040 00 39 2e 33 2e 31 31 00 06 00 00 00 39 2e 33 2e > |.9.3.11.9.3.| > (tons more deleted) > > and note that 'junk' is in fact the name of the database. But I doubt the > format of this toc.dat file is guaranteed to be immutable I looked at that, too, but was quite worried that this solution would be very fragile. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Q: extract database name from directory dump
On Sat, Mar 12, 2016 at 10:05:47PM +0100, Karsten Hilbert wrote: > :-) Sorry. I am on 9.5.1 on Debian 8.0. Debian Testing to be precise: root@hermes:~/tmp# apt-cache policy postgresql postgresql: Installiert: 9.5+172 Installationskandidat: 9.5+172 Versionstabelle: *** 9.5+172 0 990 ftp://ftp.de.debian.org/debian/ stretch/main i386 Packages 500 ftp://ftp.de.debian.org/debian/ unstable/main i386 Packages 100 /var/lib/dpkg/status 9.4+165 0 500 http://ftp.de.debian.org/debian/ jessie/main i386 Packages root@hermes:~/tmp# su - postgres postgres@hermes:~$ psql Ausgabeformat ist „wrapped“. psql (9.5.1) Geben Sie „help“ für Hilfe ein. Linux hermes 4.4.0-1-686-pae #1 SMP Debian 4.4.4-2 (2016-03-09) i686 GNU/Linux in case it should matter. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Q: extract database name from directory dump
On Sat, Mar 12, 2016 at 03:55:32PM -0500, Melvin Davidson wrote: > hmmm, let's see. You haven't specified PostgreSQL version or O/S as is > common sense and courtesy, so I will choose one for you. :-) Sorry. I am on 9.5.1 on Debian 8.0. OTOH, in the wild it could be any OS and PG 9.1.0 upwards. And thanks for investing time anyway ! > You are using PostgreSQL version 8.4 on Ubuntu 14.04 > Since pg_dump requires an output file, and the database you are dumping > must be known, just just the db name in the path. > eg: pg_dump unknown_db > /dumpdir/unknown_db/whatever_filename_you_want.dmp Thanks for this suggestion. This option was already included in the ones I figured out myself :-) However, I had listed the assumptions about the dump and the db name being in the dump name wasn't one of them 8-) Thanks, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Q: extract database name from directory dump
On Sat, Mar 12, 2016 at 09:38:13PM +0100, Karsten Hilbert wrote: > > Not-so-nice solutions coming to mind: > > > > - rely on the dump file name > > - use pg_restore to create an SQL dump > > with --create and grep the SQL file > > for "create database ..." > > - restore and compare psql -l output > > before/after the fact Another option that comes to mind is pg_restore -l $DUMPDIR | grep dbname: | cut -f 7 -d ' ' -s but that is quite fragile on the -f 7 -d ' ' side of things but that's another question. Start of pg_restore -l output: ; ; Archive created at 2016-03-07 21:15:06 CET ; dbname: gnumed_v20 ; TOC Entries: 5187 ; Compression: 0 ; Dump Version: 1.12-0 ; Format: DIRECTORY ; Integer: 4 bytes ; Offset: 8 bytes ; Dumped from database version: 9.5.1 ; Dumped by pg_dump version: 9.5.1 ; ; ; Selected TOC Entries: ; 8525; 1262 181294 DATABASE - gnumed_v20 gm-dbo Any better suggestions ? Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Q: extract database name from directory dump
On Sat, Mar 12, 2016 at 09:33:33PM +0100, Karsten Hilbert wrote: > Not-so-nice solutions coming to mind: > > - rely on the dump file name > - use pg_restore to create an SQL dump > with --create and grep the SQL file > for "create database ..." > - restore and compare psql -l output > before/after the fact I _have_ tried pg_filedump toc.dat to no avail. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Q: extract database name from directory dump
Hi, I have been searching but haven't been able to find the answer to the following question: How can I (programmatically) find out which database a dump was taken from given the dump file ? Constraints of the question: - existing dump in directory format - dump was taken of only one particular database Not-so-nice solutions coming to mind: - rely on the dump file name - use pg_restore to create an SQL dump with --create and grep the SQL file for "create database ..." - restore and compare psql -l output before/after the fact However, I'd wish for a less fragile solution, letting me learn the database directly from the directory dump (given the above assumptions about the dump). Am I missing options for doing so ? Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
