Re: [GENERAL] postgresql-ctl systemd failed: permission denied
On Tue, Jul 28, 2015 at 01:31:29PM +0200, arnaud gaboury wrote: > On Tue, Jul 28, 2015 at 1:26 PM, Francisco Olarte > wrote: > > > Hi Arnaud... > > > > On Tue, Jul 28, 2015 at 12:13 PM, arnaud gaboury > > wrote: > > > On Fedora 22 > > ... > > > Jul 28 11:49:57 poppy postgresql-ctl[307]: FATAL: 42501: could not > > > open log file "/storage/log/postgresql/postgresql-Tue.log": Permission > > > denied > > > Jul 28 11:49:57 poppy postgresql-ctl[307]: LOCATION: logfile_open, > > > syslogger.c:1160 > > > Jul 28 11:50:01 poppy postgresql-ctl[307]: pg_ctl: could not start server > > > > > > > > % ls -al /storage/log > > > drwxr-xr-x 1 postgres postgres0 Jul 28 11:29 postgresql/ > > > > May be stating the obvious, but can you try 'touch > > /storage/log/postgresql/postgresql-Tue.log" as postgres to see it it > > works. > > % ls -al /storage/log/postgresql > total 0 > drwxrwxr-x 1 postgres postgres 36 Jul 28 13:27 ./ > drwxr-x--- 1 root wheel208 Jul 27 13:34 ../ > -rw-rw-r-- 1 postgres postgres 0 Jul 28 13:27 postgresql-Tue.log > > Still permission denied If the logfile is being written to as the user postgres then .. is what is preventing it. Only root and wheel have access so unless postgres is in group wheel you need to chmod o+x /storage/log at least. -- "A search of his car uncovered pornography, a homemade sex aid, women's stockings and a Jack Russell terrier." - http://www.dailytelegraph.com.au/news/wacky/indeed/story-e6frev20-118083480 -- 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 Developer Best Practices
On Mon, Aug 24, 2015 at 08:22:17PM -0400, Melvin Davidson wrote: > On Mon, Aug 24, 2015 at 8:00 PM, Berend Tober wrote: > >> 1) What happens if someone mis-types the account-id? > >> To correct that, you also need to correct the FK field in the > >> other dozen tables. > > > > ... ON UPDATE CASCADE ? > > I believe he's talking about triggers. Huh? Why would you use a trigger when FOREIGN KEY has ON UPDATE CASCADE? -- "A search of his car uncovered pornography, a homemade sex aid, women's stockings and a Jack Russell terrier." - http://www.dailytelegraph.com.au/news/wacky/indeed/story-e6frev20-118083480 -- 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 Developer Best Practices
On Mon, Aug 24, 2015 at 08:53:43PM -0400, Melvin Davidson wrote: > You are right, he was probably talking about FK's. I was just so frustrated > about people insisting that using "ID" as the primary key in every table is > a "good" idea, > I didn't bother to reply previously. I stand firm on my belief that the > primary key should be something meaningful and NOT "id" just for the sake > of having a unique numeric key. I think there is a case to be made for pragmatism. Perhaps you should split your points up into "ideal" and "pragmatic" requirements. ie you should aim for the "ideal" but, with good justification, "pragmatic" will be acceptable. Get those doing the work to think about their decisions. -- "A search of his car uncovered pornography, a homemade sex aid, women's stockings and a Jack Russell terrier." - http://www.dailytelegraph.com.au/news/wacky/indeed/story-e6frev20-118083480 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [SPAM] [GENERAL] COPY command & binary format
On Tue, May 10, 2016 at 01:38:12PM +0200, Nicolas Paris wrote: > The way I want is : > csv -> binary -> postgresql > > And if possible, transforming csv to binary throught java. > > Use case is ETL process. Not sure what the point would be tbh if the data is already in CSV. You might aswell submit the CSV to postgres and let it deal with it. It'll probably be faster. It'll also be more portable. The BINARY format is what Postgres uses internally (more or less). I had to look at the source code to figure out how to insert a timestamp (FYI: Postgres stores timestamps as epoch based off the year 2000 not 1970 amongst other fun things). -- "A search of his car uncovered pornography, a homemade sex aid, women's stockings and a Jack Russell terrier." - http://www.dailytelegraph.com.au/news/wacky/indeed/story-e6frev20-118083480 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [SPAM] [GENERAL] COPY command & binary format
On Tue, May 10, 2016 at 03:00:55PM +0200, Nicolas Paris wrote: > > The way I want is : > > csv -> binary -> postgresql > > > > Is this just to be quicker or are you going to add some business logic > > while converting CSV data? > > As you mentioned ETL, I assume the second, as I don't think that > > converting CSV to binary and then loading it to PostgreSQL will be more > > convenient than loading directly from CSV... as quicker as it can be, you > > have anyway to load data from CSV. > > > > Right, ETL process means huge business logic. > get the data (csv or other) -> transform it -> produce a binary -> copy > from binary from stdin > > Producing 100GO CSVs, is a waste of time. Ah. You need to fiddle with the data. Then you need to weigh the pros of something agnostic to Postgres's internals to something that needs to be aware of them. You will need to delve into the source code for data types more complex than INTEGER, TEXT and BYTEA (which was the majority of my data when I was just looking into it). -- "A search of his car uncovered pornography, a homemade sex aid, women's stockings and a Jack Russell terrier." - http://www.dailytelegraph.com.au/news/wacky/indeed/story-e6frev20-118083480 -- 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] INSERT RETURNING
On Sun, Oct 14, 2007 at 12:01:45AM -0600, Cesar Alvarez wrote: > Hello > im working in a proyect in ORACLE 10g and one of the things i like is > using the INSERT RETURNING with the parameters, im not sure that i read > about postgres 8.2 suporting this feature... if its true where can i > read more about it. On http://www.postgresql.org/docs/8.2/interactive/index.html section VI has a reference section on sql commands. RETURNING is available on UPDATE, DELETE and INSERT I believe. -- "To the extent that we overreact, we proffer the terrorists the greatest tribute." - High Court Judge Michael Kirby ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Announcing PostgreSQL RPM Buildfarm
On Mon, Jan 07, 2008 at 04:54:46PM -0800, Richard Broersma Jr wrote: > > wrote: > > > what is a botherboard? > > > > Brotherboard -- motherboard is a bit old. > > Maybe a Freudian slip? ;) His brother is his mother? Man... that's just weird. -- "To the extent that we overreact, we proffer the terrorists the greatest tribute." - High Court Judge Michael Kirby ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] OT - pg perl DBI question
On Tue, Jan 29, 2008 at 04:45:48AM -0800, Glyn Astill wrote: > Should the subdirectory DBD be in there? Perl was setup using apt. How are you using it and what's your DBI db connect line? I'm about to go to sleep so if I don't reply it'll be because I'm unconcious. :) -- "To the extent that we overreact, we proffer the terrorists the greatest tribute." - High Court Judge Michael Kirby ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] OT - pg perl DBI question
On Tue, Jan 29, 2008 at 04:24:05AM -0800, Glyn Astill wrote: > I'm trying yo run a perl script that uses DBI (Slonys > psql_replication_check.pl to be precise) and I'm getting the error: > > Can't locate Pg.pm in @INC > > Now I do have the file in /usr/lib/perl5/DBD/ but the script can't > see it. I'm guessing that unless I fudge it and put the absoloute > path in the script, I need to set some sort of path somewhere, but > I'm lost as to what or where. > > Could someone point me in the correct direction please? Run perl -V and see if the path is there for @INC. If it's there, check such things as permissions. If not, ponder deeply as to why this is so (as /usr/lib/perl5 is a standard path for perl in /usr) and if you can't fix it 'properly' you can work-around with the use of 'use lib' (see perldoc perlvar and search for @INC). -- "To the extent that we overreact, we proffer the terrorists the greatest tribute." - High Court Judge Michael Kirby ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] OT - pg perl DBI question
On Tue, Jan 29, 2008 at 05:10:00AM -0800, Glyn Astill wrote: > well its using the pgsql_replication_check.pl, which does: > > -- > use Pg; ... > So I assume it's not getting past the use Pg line as although it's > looking in /usr/lib/perl5 it's not going to look in > /usr/lib/perl5/DBD ?? No. It's not using the DBI Pg. There's another module. A standalone one that I believe it may be using. Under Debian this is available as libpg-perl. Your OS/Distro may vary. If you don't have it available as a package, check with CPAN. Zzz... -- "To the extent that we overreact, we proffer the terrorists the greatest tribute." - High Court Judge Michael Kirby ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Recover PostgreSQL database folder data
On Fri, Apr 21, 2017 at 08:20:38PM -0300, Edson Lidorio wrote: > Ls -la /var/lib/pgsql/9.6/data > > drwx--. 20 postgres postgres 4096 Abr 21 17:52 . > drwx--. 4 root root51 Abr 21 06:33 .. Ensure that the user 'postgres' has permissions to get to this dir from / up. This may either mean changing permissions on some directories or changing ownership. More than likely / /var /lib are a permissions thing (likely need to be u+rwx,g+rx,o+rx) and /var/lib/pgsql/ and up is an ownership thing (postgres:postgres) but this is not guaranteed so take care. -- "A search of his car uncovered pornography, a homemade sex aid, women's stockings and a Jack Russell terrier." - http://www.dailytelegraph.com.au/news/wacky/indeed/story-e6frev20-118083480 -- 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] REGEXP_REPLACE woes
On Tue, Jun 10, 2008 at 03:43:02PM +0200, Leif B. Kristensen wrote: > On Tuesday 10. June 2008, Leif B. Kristensen wrote: > >Hey, I told it not to be greedy, didn't I? > > Found it. I must make *both* atoms non-greedy: That makes no sense. Take this bit of perl, which works as expected: $str = '[p=123|John Smith] and [p=456|Jane Doe]'; print "before: '$str'\n"; $str =~ s#\[p=(\d+)\|(.+?)\]#1 = $1, 2 = $2 rest of str = #; print "after: '$str'\n"; There's a bug in your version of pcre I think as postgres would have little to do with the regex itself (I be guessing). -- "Police noticed some rustling sounds from Linn's bottom area and on closer inspection a roll of cash was found protruding from Linn's anus, the full amount of cash taken in the robbery." - http://www.smh.com.au/news/world/robber-hides-loot-up-his-booty/2008/05/09/1210131248617.html -- 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] Absolute value of intervals
On Mon, Nov 02, 2009 at 10:52:40AM +, Jasen Betts wrote: > what's the absolute value of '1month -30 days'::interval > > if I add it to the first of march it goes forwards if I add it to the In march a month has 31 days. > first of february if goes backwards. if I add it to the first of april 29 or 28 days. > it goes nowhere. 30 days so the math below is 0. > select '2008-02-01'::date + '1month -30days'::interval; > select '2008-03-01'::date + '1month -30days'::interval; > select '2008-04-01'::date + '1month -30days'::interval; > > I'm guessing that '1 month 30 days' is the only mathematically > consistent answer, not that it's likely to be useful. A month is not fixed width. It depends on which month you're talking about. And then there is http://en.wikipedia.org/wiki/Month. Ow. Having given it some more 1am thinking the above probably works out to: select '2008-02-01'::date + '1month -30days'::interval; 2008-02-01 + 1 month = 2008-03-01 2008-03-01 - 30 days = 2008-01-31 (feb has 29 days) select '2008-03-01'::date + '1month -30days'::interval; 2008-03-01 + 1 month = 2008-04-01 2008-04-01 - 30 days = 2008-03-02 (march has 31 days) select '2008-04-01'::date + '1month -30days'::interval; 2008-04-01 + 1 month = 2008-05-01 2008-05-01 - 30 days = 2008-04-01 (april has 30 days) I'm guessing (no postgres avail to me atm) and half asleep so my math my be boogered. In the end, though, it all makes sense. :) -- "A search of his car uncovered pornography, a homemade sex aid, women's stockings and a Jack Russell terrier." - http://www.news.com.au/story/0%2C27574%2C24675808-421%2C00.html -- 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] Open Source Forum Software using PostgreSQL?
On Sun, Jul 04, 2010 at 03:10:36PM +0100, Andre Lopes wrote: > Hi, > > I need to use an Forum Software. There is any Open Souce Forum Script using > PostgreSQL? phpBB3 off the top of my head. -- "A search of his car uncovered pornography, a homemade sex aid, women's stockings and a Jack Russell terrier." - http://www.news.com.au/story/0%2C27574%2C24675808-421%2C00.html -- 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] Null vs. Empty String in Postgres 8.3.8
On Sun, Apr 04, 2010 at 08:03:13PM -0700, Wang, Mary Y wrote: > I still don't get it. I do want a zero for the subversion_flags to be stored > in the table. But it returned an error because it didn't like > subversion_flags='' in the UPDATE SQL statement. > > subversion_flags | integer | not null default 0 Thde default will not apply because you attempted to input a value. Unless I'm mistaken the provision of any value (erroneous or otherwise) obviates the activation of the default value. There is an exception to this and that is using the DEFAULT keyword (ie subversion_flags=DEFAULT). Otherwise the only way it activates is if you leave subversion_flags out totally. If you want input data mangling then a TRIGGER may be the way to go. -- "A search of his car uncovered pornography, a homemade sex aid, women's stockings and a Jack Russell terrier." - http://www.news.com.au/story/0%2C27574%2C24675808-421%2C00.html -- 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] Streaming PUG meeting: PostgreSQL Genetics!
On Wed, Mar 11, 2009 at 05:41:55PM -0700, Josh Berkus wrote: > All, > > There *may* be a streaming presentation of PostgreSQL, Unison-DB and > Genetech in 1/2 hour: > > http://cubic.org/sfpug.html > 6:15 or 6:30pm PST/PDT, March 11th > > Join us and see if it works! Will there be a saved version of this available for later viewing? Don't make me choose between steak and beer and postgres. 8( -- "A search of his car uncovered pornography, a homemade sex aid, women's stockings and a Jack Russell terrier." - http://www.news.com.au/story/0%2C27574%2C24675808-421%2C00.html -- 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 per user?
On Thu, May 07, 2009 at 09:45:30PM +0200, Anders Steinlein wrote: > Has anyone done something like this, or is it simply A Bad Idea? How > many schemas can a database contain, until one hits a (hard or soft) > limit? Keep in mind that this is not a "regular" web-app with thousands > of users, but more in the range of 500-1000 for the foreseeable future. I'm doing something similarish for our blog server. We have a blog db and a schema for each user. This was mainly for backup, restore and maintenance reasons. Being able to take one dump of all the blogs and then selectively rip them out by schema is nice. Currently we're at 632 schemas and roles and climbing. No problems as yet. -- "A search of his car uncovered pornography, a homemade sex aid, women's stockings and a Jack Russell terrier." - http://www.news.com.au/story/0%2C27574%2C24675808-421%2C00.html -- 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 Certification
On Sun, Jun 12, 2005 at 12:24:30PM +0900, Tatsuo Ishii wrote: > Thank you for interested in PostgreSQL CE. There is a sample > examination problems page: > > http://osb.sra.co.jp/postgresql-ce/sys/quiz.php?titleid=S74_en > > You could find some idea what the exam looks like. My mind broke parsing this question: Choose two options in "postgresql.conf" which the most suited values depending on the OS status is automatically assigned at "initdb" execution time. I think I maybe, almost kind-of know what it says but I am surer that my mind is broken. It seems you may need an editor for your english translations (or I need to learn to read :). -- "To the extent that we overreact, we proffer the terrorists the greatest tribute." - High Court Judge Michael Kirby ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql
On Thu, Feb 22, 2007 at 09:13:13AM +0100, Zoltan Boszormenyi wrote: > Chris ?rta: > >CaT wrote: > >>On Thu, Feb 22, 2007 at 01:08:04PM +1100, Chris wrote: > >>>create table a(a text not null check (char_length(a) > 0)); > >> > >>What's wrrong with using > >> > >>a <> '' > > > >Nothing, I just thought of the other way first :) > > I would do a CHECK (trim(a) <> '') Whitespaces are values too, you know. -- "To the extent that we overreact, we proffer the terrorists the greatest tribute." - High Court Judge Michael Kirby ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql
On Thu, Feb 22, 2007 at 11:27:18AM +0100, Zoltan Boszormenyi wrote: > >>I would do a CHECK (trim(a) <> '') > > > >Whitespaces are values too, you know. > > Yes, I know. But e.g. for a real people name, would you store > accidentally entered spaces before or after the actual name, too? > Which would also ruin sorting by name. But of course, it doesn't > make sense in every case. Yeah but if you're going down that path then you either trim on the insert or use a trigger (rule?) to automatically trim your data for you. Doing it in a check wont do much of anything for you in the case you describe. -- "To the extent that we overreact, we proffer the terrorists the greatest tribute." - High Court Judge Michael Kirby ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql vs mysql
On Thu, Feb 22, 2007 at 01:08:04PM +1100, Chris wrote: > In postgres, to stop an empty blank string: > > create table a(a text not null check (char_length(a) > 0)); What's wrrong with using a <> '' sd the check? Or is this just a flavour thing? -- "To the extent that we overreact, we proffer the terrorists the greatest tribute." - High Court Judge Michael Kirby ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
