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

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 @

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

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

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

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

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

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

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.

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

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

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

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

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

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 @

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

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

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

[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

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.

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

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

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

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

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

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

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

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

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 >

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

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

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.

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 >

Re: [GENERAL] Doubts regarding postgres Security

gt; 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 4

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

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%'])

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

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

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

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

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

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 >

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

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 >

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

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

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,

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

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

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 >

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

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'); > > 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)" > "

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

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

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

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

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

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

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

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

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

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

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 >

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

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

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

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

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

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

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

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 >

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

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

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,

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

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

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 @

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

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

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

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

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 ... &g

[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

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

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

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

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:

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

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 databa

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 ..." > - res

[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

Re: [GENERAL] pg_restore without dropping db/table

On Thu, Mar 10, 2016 at 03:59:58PM -0500, Melvin Davidson wrote: > fyi, since the version of PostgreSQL was NOT stated (or O/S) as is the > proper thing to do when posting, I gave a generic solution which covers all > versions and O/S's That's an important point. Karsten -- GPG key ID E4071346

Re: [GENERAL] pg_restore without dropping db/table

On Thu, Mar 10, 2016 at 01:49:42PM -0500, Melvin Davidson wrote: > The best way to accomplish what you want is to create a table with the same > structure in the first database as the one you want to restore to. Then you > can truncate that table, restore the data from the other db into it, and >

Re: [GENERAL] pg_restore without dropping db/table

On Thu, Mar 10, 2016 at 10:51:05AM -0500, anj patnaik wrote: > Does pg_restore only add new rows if I restore without deleting old db? No. For one thing, pg_restore cannot know what you consider to be a "new row". Best, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291

Re: [GENERAL] Unable to match same value in field.

On Thu, Mar 10, 2016 at 11:09:00AM +0200, Condor wrote: > Can some one point me what can be the problem with this value and how to > resolve it ? I think probably index problem but I reindex that table and > problem is not resolved For the fun of it try dropping all indexes on the table and

Re: [GENERAL] pg_restore man page question

On Mon, Mar 07, 2016 at 11:03:39AM -0700, David G. Johnston wrote: > > The man page of pg_restore says > > > > --disable-triggers > > This option is relevant only when performing a > > data-only restore. It instructs pg_restore to execute > >

[GENERAL] pg_restore man page question

The man page of pg_restore says --disable-triggers This option is relevant only when performing a data-only restore. It instructs pg_restore to execute commands to temporarily disable triggers on the target tables while the

Re: [GENERAL] Designing tables based on user input and defined values

On Sun, Feb 28, 2016 at 09:09:02AM -0800, Adrian Klaver wrote: > I have found that my best design tool is a legal pad and a pencil/pen. http://www.howtomakesenseofanymess.com/ Karsten Hilbert -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A

Re: [GENERAL] check constraint problem during COPY while pg_upgrade-ing

On Wed, Feb 24, 2016 at 05:24:44PM -0700, David G. Johnston wrote: > ​Then you must record the "INSERT/UPDATE time" into the table, as a > constant, and refer to that value instead of having "now()" which happens > to be correct at the time of the insert/update but is not correct at any > future

  1   2   3   4   5   6   7   >