Re: [GENERAL] Re: COPY TO returns "ERROR: could not open file for writing: No such file or directory"
I got it fella's, thanks. It was a really simple oversight on my part. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] COPY TO returns "ERROR: could not open file for writing: No such file or directory"
COPY (SELECT * FROM page WHERE "PublishDate" between '2014-03-01' and
'2014-04-01') TO
'/home/ygg/sql/backup/pagedump.2014-03-01.to.2014-04-01.copy';
gives me:
ERROR: could not open file
"/home/ygg/sql/backup/pagedump.2014-03-01.to.2014-04-01.copy" for
writing: No such file or directory
Running it in pgadmin3 I get the additional output:
SQL state: 58P01
I've tried creating a file named
"pagedump.2014-03-01.to.2014-04-01.copy", changing its permissions,
and changing its owner to pgsql, all with no luck.
The syntax for COPY is:
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | PROGRAM 'command' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]
...so it *should* be running just fine. I can't figure out why it's not.
FreeBSD. PostgreSQL 9.3.
Has anyone run into this before?
--
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] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)
> There is nothing wrong with LATERALs, they just have no business being > used here. Sorry for the noise. Ah. No trouble. In fact I'm glad you chimed in -- it motivated me to learn about laterals so now I know some new SQL syntax! -- 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] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)
> For 9.3, you can write that as: > > select p.*, s.NoOfSentences > from page p, > lateral (select count(*) as NoOfSentences > from sentence s > where s."PageURL" = p."URL") s > where "Classification" like case ... end > order by "PublishDate" desc > limit 100; > > Performance will be much, much better than what you have but it won't > work at all on the 9.2 server. Some interesting feedback on that query you provided. It took nearly 80 seconds to complete. I rewrote it* as a join and it took .8 seconds to complete: select p.*, count(*) as NoOfSentences from page p inner join sentence c on p."URL" = c."URL" where "Classification" = 'health' group by p."URL" *I may have written it incorrectly but it does _seem_ to produce correct output. Something seems odd with laterals. I'll have to dig into it more later and report back, I'm not sure it behaves this way. For the record, with modification the query you provided wound up getting executed looking like this: select p.*, s.NoOfSentences from page p, lateral (select count(*) as NoOfSentences from sentence s where s."PageURL" = p."URL") s where "Classification" = 'health' order by "PublishDate" desc limit 100; -- 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] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)
The FreeBSD system is running 9.3, the Windows systems are running 9.2. I am waiting on the output from the other developer. On Tue, Apr 29, 2014 at 8:46 AM, Tom Lane wrote: > David Noel writes: >> Both queries are run from a Java project using the latest JDBC driver. >> The PostgreSQL Server versions it's being run on are 9.2 and 9.3. The >> query executes and returns just fine when run on a FreeBSD-based >> platform, but executes forever when run under Windows. > > Um .. which one is 9.2, and which one is 9.3? Do you get the same > plan according to EXPLAIN on both systems? (Even if you do, let's > see the EXPLAIN output. And maybe EXPLAIN ANALYZE, on the system > where it completes.) > > regards, tom lane -- 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] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)
Very strange. I ran the query and it seemed slow so I rewrote it with a join instead. Using join it finished in 800ms. The query using the lateral finished in more than a minute. I guess I need to do some analysis on those queries to figure out why there was such a vast difference in performance. %10, %20, %50, even %100 differences in performance are huge, but for something to take nearly 100x -- %1 longer to complete? Something just doesn't seem right. On Tue, Apr 29, 2014 at 3:38 AM, Vik Fearing wrote: > On 04/29/2014 09:44 AM, David Noel wrote: >> Ahh, sorry, copied the query over incorrectly. It should read as follows: >> >> select page.*, coalesce((select COUNT(*) from sentence where >> sentence."PageURL" = page."URL" group by page."URL"), 0) as >> NoOfSentences from page WHERE "Classification" LIKE CASE WHEN >> 'health'<>'' THEN 'health' ELSE '%' END ORDER BY "PublishDate" DESC >> Offset 0 LIMIT 100 >> >> Does that make any more sense? > > For 9.3, you can write that as: > > select p.*, s.NoOfSentences > from page p, > lateral (select count(*) as NoOfSentences > from sentence s > where s."PageURL" = p."URL") s > where "Classification" like case ... end > order by "PublishDate" desc > limit 100; > > Performance will be much, much better than what you have but it won't > work at all on the 9.2 server. > > -- > Vik > -- 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] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)
> Is both server/client running on FreeBSD or Windows, or are you switching > only part of the stack? When I run it it's all FreeBSD. When the other developer working on it runs it it's all Windows. > It shouldn't get stuck. It might be slower on some platforms, but it > shouldn't really get stuck, so it might be a bug. That's what I was starting to thing. > On linux I'd recommend perf/strace/... to investigate the issue, but I'm > not familiar with similar tool on Windows. On Windows I'm not sure what he could run either. > Is the query eating a lot of CPU, or is it just sitting there idle, doing > nothing? Or is there some other process doing a lot of CPU (e.g. Java)? IIRC it's eating CPU. I'll double check though. > Can you try running the query through 'psql' directly, to rule out JDBC > issues etc.? Try to collect explain plans for the query (maybe there's > something wrong with it). That's a good idea. I'll see what he can come up with. -- 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] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)
> But there are two of them : ' and ' makes ''. If you use only one > psql/parser will complain. Ha! Wow. That one totally flew by me. It's not a double quotation mark (one character), it's a double _single_ quotation mark (two characters). Yeah, that makes complete sense. Wow. Can't believe I missed that one. I blame it on it being 5am. Yeah. Wow. -- 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] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)
> 'health'<>'' (if that is what you have) means a boolean expression that > compares the > literal 'health' with the empty literal '' which is of course always false. Ah. Gotcha. Thanks. I didn't know you could use a single double quotation mark in a query -- I thought like in most languages that you needed two of them for it to be valid. > Maybe *health* is a column name somewhere ? In this case it should be > written : > "health" <> '' (i.e. comparison between the value of column "health" and the > literal value '') 'health' is one of the accepted values of the page table's "Classification" column. Many thanks, -David -- 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] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)
Ehh, to clarify I'm referring to the lone _double_ quotation mark at the end of the condition 'health'<>''. I called it a "single quotation mark" because it was a quotation mark all by itself, but realize that could be misread. Single quotation marks are technically this: ' Sorry for the newbie spam -- I can't run less-than/greater-than/quotation marks through Google for answers. On 4/29/14, David Noel wrote: >> select p.*, s.NoOfSentences >> from page p, >> lateral (select count(*) as NoOfSentences >> from sentence s >> where s."PageURL" = p."URL") s >> where "Classification" like case ... end >> order by "PublishDate" desc >> limit 100; > > Great. Thanks so much! > > Could I make it even simpler and drop the case entirely? > > select p.*, s.NoOfSentences > from page p, > lateral (select count(*) as NoOfSentences > from sentence s > where s."PageURL" = p."URL") s > where "Classification" like 'health' > order by "PublishDate" desc > limit 100; > > I'm not sure what "case WHEN 'health'<>'' THEN 'health' ELSE '%' end" > does. I follow everything just fine until I get to the 'health'<>'' > condition. What does the single quotation mark mean? I can't seem to > find it in the documentation. > > -David > -- 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] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)
> select p.*, s.NoOfSentences > from page p, > lateral (select count(*) as NoOfSentences > from sentence s > where s."PageURL" = p."URL") s > where "Classification" like case ... end > order by "PublishDate" desc > limit 100; Great. Thanks so much! Could I make it even simpler and drop the case entirely? select p.*, s.NoOfSentences from page p, lateral (select count(*) as NoOfSentences from sentence s where s."PageURL" = p."URL") s where "Classification" like 'health' order by "PublishDate" desc limit 100; I'm not sure what "case WHEN 'health'<>'' THEN 'health' ELSE '%' end" does. I follow everything just fine until I get to the 'health'<>'' condition. What does the single quotation mark mean? I can't seem to find it in the documentation. -David -- 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] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)
Ahh, sorry, copied the query over incorrectly. It should read as follows: select page.*, coalesce((select COUNT(*) from sentence where sentence."PageURL" = page."URL" group by page."URL"), 0) as NoOfSentences from page WHERE "Classification" LIKE CASE WHEN 'health'<>'' THEN 'health' ELSE '%' END ORDER BY "PublishDate" DESC Offset 0 LIMIT 100 Does that make any more sense? On 4/29/14, David Noel wrote: > On 4/29/14, Achilleas Mantzios wrote: >> On 29/04/2014 09:59, David Noel wrote: >>> "select page.*, coalesce((select COUNT(*) from sentence where >>> sentence."PageURL" = page."URL" group by page."URL"), 0) as >>> NoOfSentences from page WHERE "Classification" LIKE CASE WHEN "<>" >>> THEN " ELSE '%' END ORDER BY "PublishDate" DESC Offset 0 LIMIT 100" >> >> In all honesty, this query is very badly written. It seems like it was >> ported from some other >> system. The inner group by in the coalesce is redundant since the result >> is >> always one row, >> moreover, it is wrong since coalesce accepts a scalar value, it hits the >> eye >> at first sight. >> Additionally, ''<>'' always returns false, what's the purpose of the CASE >> statement? > > Ok, thanks for the heads up. It confused me, too. It's code I'm just > picking up from another developer, so I don't know why it was done the > way it was done. I'm not super proficient with SQL but I'll take a > stab at rewriting it. > >> Try to re-write the query in a good form, and then perform EXPLAIN >> ANALYZE >> on both systems to see what's wrong. > > Will do. Thanks for the advice. > -- 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] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)
On 4/29/14, Achilleas Mantzios wrote: > On 29/04/2014 09:59, David Noel wrote: >> "select page.*, coalesce((select COUNT(*) from sentence where >> sentence."PageURL" = page."URL" group by page."URL"), 0) as >> NoOfSentences from page WHERE "Classification" LIKE CASE WHEN "<>" >> THEN " ELSE '%' END ORDER BY "PublishDate" DESC Offset 0 LIMIT 100" > > In all honesty, this query is very badly written. It seems like it was > ported from some other > system. The inner group by in the coalesce is redundant since the result is > always one row, > moreover, it is wrong since coalesce accepts a scalar value, it hits the eye > at first sight. > Additionally, ''<>'' always returns false, what's the purpose of the CASE > statement? Ok, thanks for the heads up. It confused me, too. It's code I'm just picking up from another developer, so I don't know why it was done the way it was done. I'm not super proficient with SQL but I'll take a stab at rewriting it. > Try to re-write the query in a good form, and then perform EXPLAIN ANALYZE > on both systems to see what's wrong. Will do. Thanks for the advice. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)
On 4/29/14, David Noel wrote:
> The query I'm running is:
>
> "select page.*, coalesce((select COUNT(*) from sentence where
> sentence."PageURL" = page."URL" group by page."URL"), 0) as
> NoOfSentences from page WHERE "Classification" LIKE CASE WHEN "<>"
> THEN " ELSE '%' END ORDER BY "PublishDate" DESC Offset 0 LIMIT 100"
Relevant schema below:
-
CREATE TABLE page
(
"URL" text NOT NULL,
"Title" text,
"Article" text,
"PublishDate" timestamp with time zone,
"SiteName" text,
"Classification" text,
...etc...
CONSTRAINT page_pkey PRIMARY KEY ("URL")
)
WITH (
OIDS=FALSE
);
ALTER TABLE page
OWNER TO dba;
-
CREATE TABLE sentence
(
"UUID" serial NOT NULL,
"IDSentence" text NOT NULL,
"Contents" text,
"IDAuthor" text,
"CreatedAt" text,
"PageURL" text NOT NULL,
CONSTRAINT sentence_pkey PRIMARY KEY ("UUID"),
CONSTRAINT idpage_fkey FOREIGN KEY ("PageURL")
REFERENCES page ("URL") MATCH Unknown
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE sentence
OWNER TO dba;
-
-David
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)
The query I'm running is: "select page.*, coalesce((select COUNT(*) from sentence where sentence."PageURL" = page."URL" group by page."URL"), 0) as NoOfSentences from page WHERE "Classification" LIKE CASE WHEN "<>" THEN " ELSE '%' END ORDER BY "PublishDate" DESC Offset 0 LIMIT 100" I can post the table definitions if that would be helpful but I don't have them on hand at the moment. The gist of it though is that "page" and "sentence" are two tables. page.URL maps to sentence.PageURL. The page table has the columns "Classification", and "PublishDate". URL, PageURL, and Classification are strings. PublishDate is a timestamp with timezone. Both queries are run from a Java project using the latest JDBC driver. The PostgreSQL Server versions it's being run on are 9.2 and 9.3. The query executes and returns just fine when run on a FreeBSD-based platform, but executes forever when run under Windows. Does anyone have any idea why this might be happening? Are there platform/syntax compatibility issues I'm triggering here that I'm unaware of? Is there something wrong with the query? We're going to try to test it under Linux too, but that system will have to be set up first so it might be a while before we know those results. Any thoughts would be appreciated, David Noel -- 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] Small PosgreSQL locking function request - with bounty
> ...have you used the "for update" clause in your select statements? Hi Ralf, thanks for the reply. I was unaware of the "for update" construct. Thank you! > My understanding is, that "for update" does what you need. I've read through the documentation, found a few examples using "for update" syntax, and it looks like you're right. Would transaction level SERIALIZABLE also work? It does seem messier though, having to handle errors thrown due to concurrent transactions. I'm running to some trouble though with the SELECT FOR UPDATE documentation found here -- http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-FOR-UPDATE-SHARE. It says: "...if an UPDATE, DELETE, or SELECT FOR UPDATE from another transaction has already locked a selected row or rows, SELECT FOR UPDATE will wait for the other transaction to complete, and will then lock and return the updated row (or no row, if the row was deleted). Within a SERIALIZABLE transaction, however, an error will be thrown if a row to be locked has changed since the transaction started." In my case I have a boolean "Active" column as a flag to indicate whether the selected row is currently being crawled. This column is used in querySelect to identify inactive rows. It is then modified by queryUpdateActive within the same transaction to identify the row as "currently being processed". Under the default READ COMMITTED isolation level (without using the SELECT FOR UPDATE construct) I'm running into the issue that concurrent transactions select the same row to be crawled, and don't see the update from the other transaction. My question is: according to the documentation, it seems that SELECT FOR UPDATE may still run into the same concurrency issue. If two concurrent transactions select the same row, the first will be given the lock. The second transaction will encounter the lock and be forced to wait. The update from the first transaction will occur, the lock will be released, and control will be passed to the second transaction. According to the documentation, the row will already have been selected, so the transaction, it seems, will continue processing the row as if it were marked "Inactive". In essence, the way I read it, it won't care that the row had been updated by the first transaction, and so essentially I will be running into the same problem I'm facing now. Am I reading this correctly? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Small PosgreSQL locking function request - with bounty
;SiteName\",\"Classification\",\"Special\",\"NextCrawlDate\"
from \"crawlq\" " +
"where \"Special\" = ? "+
"AND \"Active\" = 'true' "
+
"AND \"TimeoutDate\" <= now() " +
"AND \"CrawlError\" = 'false' " +
"OR "
+
"\"Special\" = ? " +
"AND \"Active\" = 'false' "
+
"AND \"CrawlError\" = 'false' " +
"order by \"NextCrawlDate\" asc limit 1";
String queryUpdateActive =
"update \"crawlq\" "
+
"set \"Active\" = 'true', " +
"\"TimeoutDate\" = now() + interval '5
minutes' " +
"where \"URL\" = ? "
;
try {
stmt = dbq.prepareStatement(querySelect);
stmt.setEscapeProcessing(true);
stmt.setString(1, cs);
stmt.setString(2, cs);
rset = stmt.executeQuery();
if(rset.next()){
ret = new FetchType(
rset.getString("URL"),
rset.getString("SiteName"),
rset.getString("Classification"),
rset.getDate("PublishDate"),
rset.getString("Special")
);
} else
ret = null;
rset.close();
stmt.close();
if (ret != null){
stmt2 = dbq.prepareStatement(queryUpdateActive);
stmt2.setEscapeProcessing(true);
stmt2.setString(1, ret.getURL());
stmt2.execute();
stmt2.close();
dbq.commit();
}
if(debug_level == 1)
System.out.println(new
java.util.Date(System.currentTimeMillis())
+ " : DAO : fetch complete " + ret.getURL());
return ret;
} catch (SQLException e) {
try {
e.printStackTrace();
dbq.rollback();
stmt.close();
stmt2.close();
e.printStackTrace();
return null;
} catch (SQLException e2) {
e2.printStackTrace();
return null;
}
}
}
Running on one machine I'm bypassing the transaction concurrency issue
by synchronizing the method. But the Java concurrency constructs I'm
using here don't scale to multiple machines.
At any rate, have I provided enough information to get the solution
I'm looking for? Have I provided enough financial incentive to get
this implemented? If so, please respond with code here to the list so
multiple people don't implement it and expect to be paid. I can only
pay one person, though if another person fixes a bug in a proposed
solution I'm open to splitting the bounty however seems fair.
Thanks for reading, hope to hear back!
-David Noel
--
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: CPU utilization creeping to 100%
On 4/3/13, Eduardo Morras wrote: > a) Perhaps process are waiting to I/O, do you take zfs snapshots? How often? > It can limit your i/o performance. Check the output of #zpool iostat 5 > > b) Is the zpool ok? If one of the disks lags behind the others (because > hardware errors) reconstructing the raidz should show what you say. Check > the output of #zpool status when the "cpu storm" happens. > > c) If you do a simple #top -U postgres (or the user that executes your > postgres server), what does the STATE column show? Check that to know the > kernel state of the process. > > d) Do you use the standard values for zfs? Specially arc values. Hmm, your points do make sense. Tuning postgresql.conf seems to have done the trick for now, but if this issue pops up again I'll definitely run through the diagnostics you have suggested. Standard zfs arc values, yes. Thanks, -David -- 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: CPU utilization creeping to 100%
On 4/2/13, John R Pierce wrote: > On 4/2/2013 3:35 PM, David Noel wrote: >> The hardware is a Dell PowerEdge 1420, dual Xeon Nocona's, 3.2ghz, >> 16gb ram. The disks are 4 Kingston HyperX SATA3's attached to a >> HighPoint RocketRAID 2721 controller, ZFS, RAID10. > . >> postgresql.conf, all standard/default except for: >> max_connections = 256 > > A) use a connection pool so you don't NEED 256 active database connections. > > B) shared_buffers, work_mem, and maintenance_work_mem all need to be > tuned. I'd suggest 4gb, 16mb, 1gb respectively as a starting point on > a 16GB ram system. if you can, shrink your max_connections by using a > connection pooler (my target is generally no more than 2-4 active > queries per CPU core or hardware thread). Ouch, Xeon Nocona was a > single core, dual thread CPU, with rather poor performance, essentially > just a Pentium-4... 3Ghz on a P4 is like 2Ghz on other CPUs. > > when you said raid10, do you mean zfs mirrored, or are you doing > hardware raid10 in the Highpoint? I would have configured the raid > card for JBOD, and done ZFS mirroring in the OS, so you can take > advantage of ZFS's data integrity features. Those are consumer > grade SSD's, are they even qualified for use with that Highpoint > controller ? > > > -- > john r pierce 37N 122W > somewhere on the middle of the left coast It looks like you guys were spot on, thanks. I've incorporated some of the suggested values, done a little RTFM'ing (chapter 18.4), made a few additional tweaks, and have brought things to a seemingly stable state. Still testing, but so far so good. Glad it was such a simple "fix". Many thanks, -David -- 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: CPU utilization creeping to 100%
On 4/3/13, Kevin Grittner wrote: > David Noel wrote: >> On 4/2/13, Kevin Grittner wrote: >>> David Noel wrote: >>> >>>> 'select * from pg_stat_activity' shows that the queries are not >>>> waiting, and are in the idle state. >>> >>> The process is idle or the process is running the query? If the >>> latter, what do you mean when you say "the queries ... are in the >>> idle state"? >> >> select * from pg_stat_activity returns a table containing a column >> labeled "state". When the postgres process is at 100% utilization and >> the application has hung, this query returns the value "idle" in that >> field. When things are running properly, as they are for the moment >> now, the value is "active". > > When a connection shows "idle", the related process should be > showing zero CPU usage. Are you seeing something different? If > so, is the transaction or query start time changing from one sample > to the next? The related process was maxed out at or near 100%. I've scanned chapter 18.4 of the documentation and along with some suggested values from a reply earlier have brought things to the point where it *seems* to be stable. I'm continuing to test though, so if the issue pops up again I'll check to see whether the query start time is changing or not. Thanks for the help, -David -- 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: CPU utilization creeping to 100%
On 4/2/13, John R Pierce wrote: > On 4/2/2013 3:35 PM, David Noel wrote: >> The hardware is a Dell PowerEdge 1420, dual Xeon Nocona's, 3.2ghz, >> 16gb ram. The disks are 4 Kingston HyperX SATA3's attached to a >> HighPoint RocketRAID 2721 controller, ZFS, RAID10. > . >> postgresql.conf, all standard/default except for: >> max_connections = 256 > > A) use a connection pool so you don't NEED 256 active database connections. > > B) shared_buffers, work_mem, and maintenance_work_mem all need to be > tuned. I'd suggest 4gb, 16mb, 1gb respectively as a starting point on > a 16GB ram system. if you can, shrink your max_connections by using a > connection pooler (my target is generally no more than 2-4 active > queries per CPU core or hardware thread). Great, thanks. I'll get those tunables modified and see if that smooths things out. > Ouch, Xeon Nocona was a > single core, dual thread CPU, with rather poor performance, essentially > just a Pentium-4... 3Ghz on a P4 is like 2Ghz on other CPUs. I won't tell them you said that. Feelings might get hurt. > when you said raid10, do you mean zfs mirrored, or are you doing > hardware raid10 in the Highpoint? I would have configured the raid > card for JBOD, and done ZFS mirroring in the OS, so you can take > advantage of ZFS's data integrity features. RAID10 under ZFS. Yes, JBOD. ZFS is neat! > Those are consumer grade SSD's, are they even qualified for use > with that Highpoint controller? Consumer grade SSD's, indeed. They've held together so far though. Fingers crossed. Thanks again, -David -- 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: CPU utilization creeping to 100%
On 4/2/13, Kevin Grittner wrote: > David Noel wrote: > >> 'select * from pg_stat_activity' shows that the queries are not >> waiting, and are in the idle state. > > The process is idle or the process is running the query? If the > latter, what do you mean when you say "the queries ... are in the > idle state"? select * from pg_stat_activity returns a table containing a column labeled "state". When the postgres process is at 100% utilization and the application has hung, this query returns the value "idle" in that field. When things are running properly, as they are for the moment now, the value is "active". -- 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: CPU utilization creeping to 100%
On 4/2/13, Ian Lawrence Barwick wrote:
> 2013/4/3 David Noel :
>> I'm running into a strange issue whereby my postgres processes are
>> slowly creeping to 100% CPU utilization. I'm running
>> postgresql-server-9.2.3, FreeBSD 8.3-RELEASE-p6, and using the
>> postgresql-9.2-1002.jdbc4 driver.
>
> (...)
>> postgresql.conf, all standard/default except for:
>> max_connections = 256
>
> It's very likely the default settings are woefully inadequate for your
> server; some basic
> tuning (especially the value of shared_buffers and other
> memory-related parameters)
> should help.
>
>> Any thoughts? What other information can I provide?
>
> Sample EXPLAIN/EXPLAIN ANALYZE output from the query in question, and
> if possible relevant table definitions etc. would certainly be useful.
>
> Regards
>
> Ian Barwick
Thanks for the feedback. I'll look into pg tunings. Hopefully the
problem's there somewhere.
explain analyze select * from ((select * from "crawlq" where "Active"
= 'true' AND "TimeoutDate" <= now()) UNION (select * from "crawlq"
where "Active" = 'false')) as RS order by "NextCrawlDate" asc limit 1
"Limit (cost=4092.39..4092.39 rows=1 width=203) (actual
time=23.447..23.450 rows=1 loops=1)"
" -> Sort (cost=4092.39..4096.34 rows=1583 width=203) (actual
time=23.442..23.442 rows=1 loops=1)"
"Sort Key: public.crawlq."NextCrawlDate""
"Sort Method: top-N heapsort Memory: 25kB"
"-> HashAggregate (cost=4052.81..4068.64 rows=1583
width=236) (actual time=18.195..20.486 rows=877 loops=1)"
" -> Append (cost=0.00..3997.41 rows=1583 width=236)
(actual time=0.015..13.423 rows=877 loops=1)"
"-> Seq Scan on crawlq (cost=0.00..1995.14
rows=18 width=236) (actual time=0.011..3.397 rows=49 loops=1)"
" Filter: ("Active" AND ("TimeoutDate" <= now()))"
" Rows Removed by Filter: 828"
"-> Seq Scan on crawlq (cost=0.00..1986.43
rows=1565 width=236) (actual time=0.013..7.152 rows=828 loops=1)"
" Filter: (NOT "Active")"
" Rows Removed by Filter: 49"
"Total runtime: 23.633 ms"
Relevant rows from table crawlq:
CREATE TABLE crawlq
(
"URL" text NOT NULL,
"LastCrawlDate" timestamp with time zone DEFAULT now(),
"NextCrawlDate" timestamp with time zone,
"Active" boolean DEFAULT false,
"TimeoutDate" timestamp with time zone,
CONSTRAINT crawlq_pkey PRIMARY KEY ("URL")
)
--
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: CPU utilization creeping to 100%
What's strange is that the crawler will run just fine for up to several hours. At some point though the CPU utilization slowly begins to creep higher. Eventually everything locks and the program hangs. 'top' shows the processes connected to the queue database at or near %100, and the program ceases output (I have debugging messages built in to show current activity "[timestamp] : crawling [URL]"). At some point--anywhere from 30 minutes to several hours later--CPU utilization drops to normal and the program resumes operation as if everything were fine. This goes on for up to several hours, then the utilization issue repeats. So it's a very odd issue I've run into. On 4/2/13, David Noel wrote: > I'm running into a strange issue whereby my postgres processes are > slowly creeping to 100% CPU utilization. I'm running > postgresql-server-9.2.3, FreeBSD 8.3-RELEASE-p6, and using the > postgresql-9.2-1002.jdbc4 driver. > > I'm not sure what information here is relevant, so I'll give > everything I can as concisely as I can. > > The hardware is a Dell PowerEdge 1420, dual Xeon Nocona's, 3.2ghz, > 16gb ram. The disks are 4 Kingston HyperX SATA3's attached to a > HighPoint RocketRAID 2721 controller, ZFS, RAID10. > > There are 2 databases, one a queue, and one containing tables storing > crawled data. > > The application is a webcrawler. The application pulls URLs from the > queue and marks them active in a single transaction. It then feeds the > URLs to the crawler threads who crawl the URL, populate the tables > with data, and signal the main thread to update the queue database, > marking the item as inactive and rescheduling it with a new > "NextCrawlDate". > > The processes that hang are the postgres processes that interact with > the queue database. > > 'select * from pg_stat_activity' shows that the queries are not > waiting, and are in the idle state. > > Essentially, the query in question is responsible for returning 1 URL > from the union of the list of URL's whose crawl session has timed out > and the list of URL's next in line to be crawled according to the > schedule (NextCrawlDate). The query is: "select * from ((select * from > "crawlq" where "Active" = 'true' AND "TimeoutDate" <= now()) UNION > (select * from "crawlq" where "Active" = 'false')) as RS order by > "NextCrawlDate" asc limit 1" > > Beyond this I don't know what useful debugging information to include. > I'll take a guess and start with some vmstat output. > > Under normal conditions (with the crawler running) vmstat shows the > following: > > procs memory pagedisks faults > cpu > r b w avmfre flt re pi pofr sr da0 da1 in sy > cs us sy id > 2 0 0119G 8450M 1143 0 1 0 900 0 0 0 128 6700 > 8632 32 4 65 > 2 0 0119G 8444M 1937 0 0 0 100 0 4 4 280 112206 > 7683 36 5 59 > 1 0 0119G 8443M 427 0 0 0 1377 0 90 90 222 115889 > 9020 35 7 58 > 1 0 0119G 8442M 1798 0 0 018 0 1 1 153 104954 > 7071 30 4 66 > 3 0 0119G 8443M 528 0 0 0 681 0 10 10 293 125170 > 14523 40 15 46 > 1 0 0119G 8432M 15227 0 0 4 2850 0 3 3 205 83830 > 6663 55 12 33 > 8 0 0119G 8433M 3306 0 0 0 445 0 2 2 249 113792 > 7068 34 5 61 > 10 0 0118G 8461M 1190 0 0 0 9909 0 72 73 451 120263 > 10036 56 9 35 > 14 0 0118G 8456M 5887 0 0 0 1202 0 2 2 272 130795 > 9506 44 12 44 > 9 0 0118G 8444M 7750 0 0 0 1070 0 9 9 298 87643 > 9584 80 13 7 > 3 0 0118G 8442M 1335 0 0 0 648 0 5 5 189 143691 > 9234 36 6 58 > 1 0 0118G 8442M 689 0 1 1 472 0 2 2 206 153868 > 8635 32 7 61 > 1 0 0118G 8441M 203 0 0 0 1124 0 75 75 191 142598 > 8909 31 10 60 > 2 0 0118G 8440M 9508 0 0 0 684 0 8 8 231 132785 > 10247 47 13 41 > 4 0 0118G 8456M 4046 0 0 0 5469 0 11 11 299 143119 > 12475 54 22 24 > 4 0 0117G 8490M 1076 0 0 0 9858 0 16 16 291 140701 > 14849 58 25 17 > 1 0 0116G 8524M 344 0 0 0 8936 0 4 4 234 149103 > 12137 45 15 40 > 2 0 0114G 8586M 715 0 0 5 17719 0 73 75 322 151002 > 11430 34 10 56 > 5 0 0112G 8648M 2773 0 0 0 16997 0 6 6 225 118339 > 8700 30 10 61 > 1 0 0110G 8705M 4429 0 0 0 15763 0 7 7 423 139590 > 10354 40 11 49 > 1 0 0108G 8760M 1443 0 0 0 14519 0 7 7
[GENERAL] PostgreSQL: CPU utilization creeping to 100%
I'm running into a strange issue whereby my postgres processes are slowly creeping to 100% CPU utilization. I'm running postgresql-server-9.2.3, FreeBSD 8.3-RELEASE-p6, and using the postgresql-9.2-1002.jdbc4 driver. I'm not sure what information here is relevant, so I'll give everything I can as concisely as I can. The hardware is a Dell PowerEdge 1420, dual Xeon Nocona's, 3.2ghz, 16gb ram. The disks are 4 Kingston HyperX SATA3's attached to a HighPoint RocketRAID 2721 controller, ZFS, RAID10. There are 2 databases, one a queue, and one containing tables storing crawled data. The application is a webcrawler. The application pulls URLs from the queue and marks them active in a single transaction. It then feeds the URLs to the crawler threads who crawl the URL, populate the tables with data, and signal the main thread to update the queue database, marking the item as inactive and rescheduling it with a new "NextCrawlDate". The processes that hang are the postgres processes that interact with the queue database. 'select * from pg_stat_activity' shows that the queries are not waiting, and are in the idle state. Essentially, the query in question is responsible for returning 1 URL from the union of the list of URL's whose crawl session has timed out and the list of URL's next in line to be crawled according to the schedule (NextCrawlDate). The query is: "select * from ((select * from "crawlq" where "Active" = 'true' AND "TimeoutDate" <= now()) UNION (select * from "crawlq" where "Active" = 'false')) as RS order by "NextCrawlDate" asc limit 1" Beyond this I don't know what useful debugging information to include. I'll take a guess and start with some vmstat output. Under normal conditions (with the crawler running) vmstat shows the following: procs memory pagedisks faults cpu r b w avmfre flt re pi pofr sr da0 da1 in sy cs us sy id 2 0 0119G 8450M 1143 0 1 0 900 0 0 0 128 6700 8632 32 4 65 2 0 0119G 8444M 1937 0 0 0 100 0 4 4 280 112206 7683 36 5 59 1 0 0119G 8443M 427 0 0 0 1377 0 90 90 222 115889 9020 35 7 58 1 0 0119G 8442M 1798 0 0 018 0 1 1 153 104954 7071 30 4 66 3 0 0119G 8443M 528 0 0 0 681 0 10 10 293 125170 14523 40 15 46 1 0 0119G 8432M 15227 0 0 4 2850 0 3 3 205 83830 6663 55 12 33 8 0 0119G 8433M 3306 0 0 0 445 0 2 2 249 113792 7068 34 5 61 10 0 0118G 8461M 1190 0 0 0 9909 0 72 73 451 120263 10036 56 9 35 14 0 0118G 8456M 5887 0 0 0 1202 0 2 2 272 130795 9506 44 12 44 9 0 0118G 8444M 7750 0 0 0 1070 0 9 9 298 87643 9584 80 13 7 3 0 0118G 8442M 1335 0 0 0 648 0 5 5 189 143691 9234 36 6 58 1 0 0118G 8442M 689 0 1 1 472 0 2 2 206 153868 8635 32 7 61 1 0 0118G 8441M 203 0 0 0 1124 0 75 75 191 142598 8909 31 10 60 2 0 0118G 8440M 9508 0 0 0 684 0 8 8 231 132785 10247 47 13 41 4 0 0118G 8456M 4046 0 0 0 5469 0 11 11 299 143119 12475 54 22 24 4 0 0117G 8490M 1076 0 0 0 9858 0 16 16 291 140701 14849 58 25 17 1 0 0116G 8524M 344 0 0 0 8936 0 4 4 234 149103 12137 45 15 40 2 0 0114G 8586M 715 0 0 5 17719 0 73 75 322 151002 11430 34 10 56 5 0 0112G 8648M 2773 0 0 0 16997 0 6 6 225 118339 8700 30 10 61 1 0 0110G 8705M 4429 0 0 0 15763 0 7 7 423 139590 10354 40 11 49 1 0 0108G 8760M 1443 0 0 0 14519 0 7 7 405 139806 10214 37 5 58 1 0 0104G 8863M 333 0 0 0 26537 0 5 5 284 107770 9947 34 6 60 1 0 0104G 8859M 1331 0 0 0 1700 0 114 114 464 103248 12113 40 9 51 1 0 0104G 8854M 1708 0 0 0 272 0 6 6 279 99817 9470 40 5 55 9 0 0104G 8850M 3653 0 0 0 4809 0 28 28 346 160041 54071 42 32 26 12 3 0105G 8845M 20576 0 0 0 18344 0 7 7 383 95019 32533 46 53 1 20 0 0114G 8721M 46913 0 0 0 2941 0 11 11 461 77480 9794 72 28 0 12 1 0110G 8759M 25109 0 0 0 35881 0 70 70 413 72631 10161 76 24 0 2 0 0110G 8716M 12993 0 1 1 265 0 8 8 292 83085 10073 61 30 9 3 0 0110G 8716M 2144 0 0 045 0 3 3 183 100994 7410 39 20 41 ...and when postgres goes bonkers: procs memory pagedisks faults cpu r b w avmfre flt re pi pofr sr da0 da1 in sy cs us sy id 2 0 0 98G10G 1091 0 1 0 849 0 0 0 114 2641 8582 30 4 66 2 0 0 98G10G20 0 0 0 0 0 0 0 197 20500 10454 46 2 53 2 0 0 98G10G59 0 0 0 0 0 0 0 284 23715 11180 46 3 51 3 0 0 98G10G17 0 0 0 652 0 69 70 288 21968 11571 46 4 50 2 0 0
[GENERAL] initdb error
I'm running into the following error message when running initdb (FreeBSD host): ygg# /usr/local/etc/rc.d/postgresql initdb -D /zdb/pgsql/data --debug The files belonging to this database system will be owned by user "pgsql". This user must also own the server process. The database cluster will be initialized with locales COLLATE: C CTYPE:en_US.UTF-8 MESSAGES: en_US.UTF-8 MONETARY: en_US.UTF-8 NUMERIC: en_US.UTF-8 TIME: en_US.UTF-8 The default text search configuration will be set to "english". creating directory /zdb/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 32MB creating configuration files ... ok creating template1 database in /zdb/pgsql/data/base/1 ... FATAL: could not open file "pg_xlog/00010001" (log file 0, segment 1): No such file or directory child process exited with exit code 1 initdb: removing data directory "/zdb/pgsql/data" My best guess is that it has something to do with permissions, but I really have no idea. Has anyone seen this before and found a way around it? -David -- 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] initdb error
Ugh. It turns out I had security.bsd.hardlink_check_gid=1 and security.bsd.hardlink_check_uid=1 in sysctl.conf. Setting them to 0 fixed everything. That's frustrating. Sorry for all the trouble. Many thanks for helping resolve this. On 12/15/12, Amitabh Kant wrote: > David > > I just tried installing PG 9.2.2 on FreeBSD 8.3 and initdb went smoothly > without any problems. the only difference would be that I had it running in > a virtualbox instance. My config were as follows; > > FreeBSD 8.3-RELEASE-p5 (binary updated through freebsd-update) > PG 9.2.2 (installed through ports) > root filesystem on UFS > ZFS mounted on /example > PG data directory set to /example/pgsql/data > > relevant rc.conf entries: > postgresql_enable="YES" > postgresql_data="/example/pgsql/data" > > running "/usr/local/etc/rc.d/postgresql initdb" works perfectly. Just > needed to set the correct permissions on /example/pgsql directory. > > Amitabh > > > > > On Sat, Dec 15, 2012 at 10:32 AM, David Noel > wrote: > >> > You need to talk to some FreeBSD kernel hackers about why link() >> > might be failing here. Since you see it on UFS too, we can probably >> > exonerate the ZFS filesystem-specific code. >> > >> > I did some googling and found that EPERM can be issued if the >> > filesystem >> > doesn't support hard links (which shouldn't apply to ZFS I trust). >> > Also, Linux has a "protected_hardlinks" option that causes certain >> > attempts at creating hard links to fail --- but our use-case here >> > doesn't fall foul of any of those restrictions AFAICS, and of course >> > FreeBSD isn't Linux. Still, I wonder if you're running into some >> > misdesigned or misimplemented security restriction. You might want >> > to look at your kernel parameters and see if any of them look like >> > they might have to do with restricting hard-link operations. >> > >> > Also, since Amitabh failed to duplicate the failure on both earlier >> > and later FreeBSD kernels, and we've not heard reports of this from >> > anybody else either, it seems more than possible that it's a plain >> > old bug in the specific kernel version you're using. >> > >> > As a short-term workaround, I'd suggest rebuilding with >> > HAVE_WORKING_LINK disabled. (Just remove that #define from >> > src/include/pg_config_manual.h and rebuild.) >> >> OK, thanks. I've gotten in touch with the freebsd-hackers mailing >> list. Hopefully we'll be able to get this one figured out. >> >> I compiled a GENERIC kernel and tried it again. Still no luck. But at >> least we know now it wasn't a configuration error. >> > -- 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] initdb error
> You need to talk to some FreeBSD kernel hackers about why link() > might be failing here. Since you see it on UFS too, we can probably > exonerate the ZFS filesystem-specific code. > > I did some googling and found that EPERM can be issued if the filesystem > doesn't support hard links (which shouldn't apply to ZFS I trust). > Also, Linux has a "protected_hardlinks" option that causes certain > attempts at creating hard links to fail --- but our use-case here > doesn't fall foul of any of those restrictions AFAICS, and of course > FreeBSD isn't Linux. Still, I wonder if you're running into some > misdesigned or misimplemented security restriction. You might want > to look at your kernel parameters and see if any of them look like > they might have to do with restricting hard-link operations. > > Also, since Amitabh failed to duplicate the failure on both earlier > and later FreeBSD kernels, and we've not heard reports of this from > anybody else either, it seems more than possible that it's a plain > old bug in the specific kernel version you're using. > > As a short-term workaround, I'd suggest rebuilding with > HAVE_WORKING_LINK disabled. (Just remove that #define from > src/include/pg_config_manual.h and rebuild.) OK, thanks. I've gotten in touch with the freebsd-hackers mailing list. Hopefully we'll be able to get this one figured out. I compiled a GENERIC kernel and tried it again. Still no luck. But at least we know now it wasn't a configuration error. -- 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] initdb error
On 12/14/12, Tom Lane wrote: > David Noel writes: >> I didn't have any luck with the rc script but I was able to use it to >> get a ktrace dump as root (ktrace as user pgsql doesn't seem to work). >> So hopefully that will show something(!) > > The relevant part of the ktrace output is > > 71502 postgres CALL unlink(0x7fffc130) > 71502 postgres NAMI "pg_xlog/xlogtemp.71502" > 71502 postgres RET unlink -1 errno 2 No such file or directory > 71502 postgres CALL > open(0x7fffc130,O_RDWR|O_CREAT|O_EXCL,S_IRUSR|S_IWUSR) > 71502 postgres NAMI "pg_xlog/xlogtemp.71502" > 71502 postgres RET open 3 > 71502 postgres CALL write(0x3,0x801a56030,0x2000) > 71502 postgres GIO fd 3 wrote 4096 bytes > a lot of uninteresting write() calls snipped ... > 71502 postgres RET write 8192/0x2000 > 71502 postgres CALL close(0x3) > 71502 postgres RET close 0 > 71502 postgres CALL unlink(0x7fffbc60) > 71502 postgres NAMI "pg_xlog/00010001" > 71502 postgres RET unlink -1 errno 2 No such file or directory > 71502 postgres CALL link(0x7fffc130,0x7fffbc60) > 71502 postgres NAMI "pg_xlog/xlogtemp.71502" > 71502 postgres NAMI "pg_xlog/00010001" > 71502 postgres RET link -1 errno 1 Operation not permitted > 71502 postgres CALL unlink(0x7fffc130) > 71502 postgres NAMI "pg_xlog/xlogtemp.71502" > 71502 postgres RET unlink 0 > 71502 postgres CALL open(0x7fffc530,O_RDWR,0x180) > 71502 postgres NAMI "pg_xlog/00010001" > 71502 postgres RET open -1 errno 2 No such file or directory > > This corresponds to the execution of XLogFileInit(), and what's > evidently happening is that we successfully create and zero-fill > the first xlog segment file under a temporary name, but then > the attempt to rename it into place with link() fails with EPERM. > > This is really a WTF kind of failure, I think. The directory is > certainly writable --- it was made under our own UID, and what's > more we just managed to create the file there under its temp name. > So how can we get an EPERM failure from link()? > > I think this is a kernel bug. > > regards, tom lane > > PS: one odd thing here is that the ereport(LOG) in > InstallXLogFileSegment isn't doing anything; otherwise we'd have gotten > a much more helpful error report about "could not link file". I don't > think we run the bootstrap mode with log_min_messages set high enough to > disable LOG messages, so why isn't it printing? Nonetheless, this error > shouldn't have occurred. Thanks so much for the analysis. Where to from here? The freebsd-datab...@freebsd.org mailing list? The postgresql port maintainer? Who should I be in touch with? -- 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] initdb error
>> Interestingly, I have a second--virtually identical--server that I >> just tried initdb on. FreeBSD 8.3-RELEASE-p5, postgresql-server-9.2.2. >> Exact same "FATAL: could not open file pg_xlog" error. So it is >> reproducible. > > Does virtually identical extend to architecture, amd64? Yes... and hardware. They only vary in the amount of CPU cache--one has twice the other. -- 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] initdb error
On 12/13/12, David Noel wrote: > I'm running into the following error message when running initdb (FreeBSD > host): > > ygg# /usr/local/etc/rc.d/postgresql initdb -D /zdb/pgsql/data --debug > The files belonging to this database system will be owned by user "pgsql". > This user must also own the server process. > > The database cluster will be initialized with locales >COLLATE: C >CTYPE:en_US.UTF-8 >MESSAGES: en_US.UTF-8 >MONETARY: en_US.UTF-8 >NUMERIC: en_US.UTF-8 >TIME: en_US.UTF-8 > The default text search configuration will be set to "english". > > creating directory /zdb/pgsql/data ... ok > creating subdirectories ... ok > selecting default max_connections ... 100 > selecting default shared_buffers ... 32MB > creating configuration files ... ok > creating template1 database in /zdb/pgsql/data/base/1 ... FATAL: > could not open file "pg_xlog/00010001" (log file 0, > segment 1): No such file or directory > child process exited with exit code 1 > initdb: removing data directory "/zdb/pgsql/data" > > My best guess is that it has something to do with permissions, but I > really have no idea. Has anyone seen this before and found a way > around it? > > -David Interestingly, I have a second--virtually identical--server that I just tried initdb on. FreeBSD 8.3-RELEASE-p5, postgresql-server-9.2.2. Exact same "FATAL: could not open file pg_xlog" error. So it is reproducible. -- 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] initdb error
> Did you use ports to install postgresql? Yes > What is the version of postgresql and freebsd you are using? postgresql client and server v. 9.2.2. If all else fails I could try downgrading to a previous version of postgres. FreeBSD 8.3-RELEASE-p5 > I am getting a different output while running > the initdb command through the rc script, and it's not using the -D path to > initialize the cluster, it falls back to the default location > /usr/local/pgsql/data . Setting postgresql_data="/usr/local/pgsql1/data" > in /etc/rc.conf makes it initialize at the right location . I did not had > to mess with an permissions and the rc commands were all run as root. The > above was tested on FreeBSD 9.1 with Postgresql 9.2 on UFS. I didn't have any luck with the rc script but I was able to use it to get a ktrace dump as root (ktrace as user pgsql doesn't seem to work). So hopefully that will show something(!) -- 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] initdb error
> Well this has definitely moved up a support level and past anything I > know about. > For the record what version of FreeBSD are you running in case someone > is searching the archives? Any help is always appreciated. FreeBSD 8.3-RELEASE-p5. (I wonder if upgrading to 9.1 would do anything..) -- 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] initdb error
On 12/13/12, Tom Lane wrote: > David Noel writes: >> /zdb is a zfs volume I've created for cvs and postgres. > > zfs eh? What happens if you point initdb at a non-zfs volume? > > (I"m wondering if zfs has issues with the O_DIRECT flag that we'll > probably try to use with pg_xlog files.) I /boot off of a UFS volume so I created a directory there, chown and chmod'ed it, then ran initdb again. Same error, unfortunately. -- 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] initdb error
Unfortunately no luck there. Still stick with the same error. On 12/13/12, David Noel wrote: > On 12/13/12, Adrian Klaver wrote: >> On 12/13/2012 10:47 AM, David Noel wrote: >>> On 12/13/12, Tom Lane wrote: >> >>> ygg:/usr/home/ygg> ll /zdb/pgsql/data/pg_xlog/ >>> total 2 >>> drwx-- 2 pgsql wheel 2 Dec 13 12:42 archive_status >>> >> >> Different train of thought, away from permissions. >> >> How was Postgres installed? >> >> Where there any errors during the install process?-- >> >> Is there another version of Postgres on this machine? >> >> Adrian Klaver >> adrian.kla...@gmail.com >> > > I'd logged in as ygg via `su ygg`. > > /zdb is a zfs volume I've created for cvs and postgres. > > Postgres was installed via `make install` and there were no errors > during the install process. > > I could try deinstalling and installing it again... maybe that will > fix something. > -- 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] initdb error
On 12/13/12, Adrian Klaver wrote: > On 12/13/2012 10:47 AM, David Noel wrote: >> On 12/13/12, Tom Lane wrote: > >> ygg:/usr/home/ygg> ll /zdb/pgsql/data/pg_xlog/ >> total 2 >> drwx-- 2 pgsql wheel 2 Dec 13 12:42 archive_status >> > > Different train of thought, away from permissions. > > How was Postgres installed? > > Where there any errors during the install process?-- > > Is there another version of Postgres on this machine? > > Adrian Klaver > adrian.kla...@gmail.com > I'd logged in as ygg via `su ygg`. /zdb is a zfs volume I've created for cvs and postgres. Postgres was installed via `make install` and there were no errors during the install process. I could try deinstalling and installing it again... maybe that will fix something. -- 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] initdb error
On 12/13/12, Tom Lane wrote: > David Noel writes: >> I've tried initdb directly: >> initdb -D /zdb/pgsql/data >> ...and still seem to wind up with the error: >> creating template1 database in /zdb/pgsql/data/base/1 ... FATAL: >> could not open file "pg_xlog/00010001" (log file 0, >> segment 1): No such file or directory > > Hm, that eliminates my first theory that the rc.d script is using > initdb's --xlogdir switch to try to put pg_xlog someplace that doesn't > exist on your system. But it seems like this must be some variant of > that. A simple permissions problem is not very credible, because at > this point initdb has successfully made the data directory and a bunch > of files within it already. > > You could get more information by using the --noclean switch to prevent > removal of the datadir after failure, and then having a look at the > debris. Is there a pg_xlog subdirectory inside /zdb/pgsql/data, and if > so what permissions has it got? Perhaps it is not a directory, but a > symlink to somewhere else? > > regards, tom lane > ygg:/usr/home/ygg> initdb -D /zdb/pgsql/data --noclean ... creating template1 database in /zdb/pgsql/data/base/1 ... FATAL: could not open file "pg_xlog/00010001" (log file 0, segment 1): No such file or directory child process exited with exit code 1 initdb: data directory "/zdb/pgsql/data" not removed at user's request ygg:/usr/home/ygg> ll /zdb/pgsql/ total 2 drwx-- 14 pgsql wheel 18 Dec 13 12:42 data ygg:/usr/home/ygg> ll /zdb/pgsql/data/ total 53 -rw--- 1 pgsql wheel 4 Dec 13 12:42 PG_VERSION drwx-- 3 pgsql wheel 3 Dec 13 12:42 base drwx-- 2 pgsql wheel 2 Dec 13 12:42 global drwx-- 2 pgsql wheel 2 Dec 13 12:42 pg_clog -rw--- 1 pgsql wheel 4467 Dec 13 12:42 pg_hba.conf -rw--- 1 pgsql wheel 1636 Dec 13 12:42 pg_ident.conf drwx-- 4 pgsql wheel 4 Dec 13 12:42 pg_multixact drwx-- 2 pgsql wheel 3 Dec 13 12:42 pg_notify drwx-- 2 pgsql wheel 2 Dec 13 12:42 pg_serial drwx-- 2 pgsql wheel 2 Dec 13 12:42 pg_snapshots drwx-- 2 pgsql wheel 2 Dec 13 12:42 pg_stat_tmp drwx-- 2 pgsql wheel 2 Dec 13 12:42 pg_subtrans drwx-- 2 pgsql wheel 2 Dec 13 12:42 pg_tblspc drwx-- 2 pgsql wheel 2 Dec 13 12:42 pg_twophase drwx-- 3 pgsql wheel 3 Dec 13 12:42 pg_xlog -rw--- 1 pgsql wheel 19713 Dec 13 12:42 postgresql.conf ygg:/usr/home/ygg> ll /zdb/pgsql/data/pg_xlog/ total 2 drwx-- 2 pgsql wheel 2 Dec 13 12:42 archive_status -- 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] initdb error
> You are doing the above as the database user ex:postgres? confirmed: ygg:/usr/home/ygg> whoami pgsql > The database user has permissions on /zdb/pgsql/data? confirmed: ygg:/usr/home/ygg> ll /zdb/ total 3 drwxrwxrwx 3 cvswheel 3 Dec 12 15:33 cvsroot drwxrwxrwx 2 pgsql wheel 2 Dec 13 10:17 pgsql -- 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] initdb error
On 12/13/12, Adrian Klaver wrote: > On 12/13/2012 07:38 AM, David Noel wrote: >> I'm running into the following error message when running initdb (FreeBSD >> host): >> >> ygg# /usr/local/etc/rc.d/postgresql initdb -D /zdb/pgsql/data --debug >> The files belonging to this database system will be owned by user >> "pgsql". >> This user must also own the server process. >> >> The database cluster will be initialized with locales >>COLLATE: C >>CTYPE:en_US.UTF-8 >>MESSAGES: en_US.UTF-8 >>MONETARY: en_US.UTF-8 >>NUMERIC: en_US.UTF-8 >>TIME: en_US.UTF-8 >> The default text search configuration will be set to "english". >> >> creating directory /zdb/pgsql/data ... ok >> creating subdirectories ... ok >> selecting default max_connections ... 100 >> selecting default shared_buffers ... 32MB >> creating configuration files ... ok >> creating template1 database in /zdb/pgsql/data/base/1 ... FATAL: >> could not open file "pg_xlog/00010001" (log file 0, >> segment 1): No such file or directory >> child process exited with exit code 1 >> initdb: removing data directory "/zdb/pgsql/data" >> >> My best guess is that it has something to do with permissions, but I >> really have no idea. Has anyone seen this before and found a way >> around it? > > > Not quite what is in the the init script in rc.d, have you tried running > the initdb command directly? As you say I believe there are permissions > problems. So following the instructions found below may solve your problem: > > http://www.postgresql.org/docs/9.2/interactive/app-initdb.html > > > >> >> -David >> >> > > > -- > Adrian Klaver > adrian.kla...@gmail.com > I've tried initdb directly: initdb -D /zdb/pgsql/data ...and also through pg_ctl: pg_ctl initdb -D /zdb/pgsql/data ...and still seem to wind up with the error: creating template1 database in /zdb/pgsql/data/base/1 ... FATAL: could not open file "pg_xlog/00010001" (log file 0, segment 1): No such file or directory -David -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] initdb error
I'm running into the following error message when running initdb (FreeBSD host): ygg# /usr/local/etc/rc.d/postgresql initdb -D /zdb/pgsql/data --debug The files belonging to this database system will be owned by user "pgsql". This user must also own the server process. The database cluster will be initialized with locales COLLATE: C CTYPE:en_US.UTF-8 MESSAGES: en_US.UTF-8 MONETARY: en_US.UTF-8 NUMERIC: en_US.UTF-8 TIME: en_US.UTF-8 The default text search configuration will be set to "english". creating directory /zdb/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 32MB creating configuration files ... ok creating template1 database in /zdb/pgsql/data/base/1 ... FATAL: could not open file "pg_xlog/00010001" (log file 0, segment 1): No such file or directory child process exited with exit code 1 initdb: removing data directory "/zdb/pgsql/data" My best guess is that it has something to do with permissions, but I really have no idea. Has anyone seen this before and found a way around it? -David -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
