[ADMIN] problem with pg_dump - reg.
Dear Group, Thanks for all your support to my previous postings. I am currently using Postgresql 7.3.4-2PGDG. The problem I am facing is that when I take a dump of the DB using pg_dump and restore it using the cat pg_dump_file |psql dbname, I find that some views and functions are missing. But the same views when created manually in the newly restored database works fine. I would appreciate your input in this regard. I am using Redhat 7.3 on one machine and Redhat 8.0 on another. The same behavior is found on both. Thanks in advance, Shan. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] [ADMIN] Raw devices vs. Filesystems
...and on Wed, Apr 07, 2004 at 09:09:16AM -0700, Josh Berkus used the keyboard: > > Does it work, though? Without Oracle admin tools? Hello, Josh. :) Well, as I said, that's why I was asking - I'm willing to give it a go if nobody can prove me wrong. :) > > Now, if both goals can be achieved in one go, hell, I'm willing to try > > it out myself in an attempt to extract off of it, some performance > > indicators that could be compared to other database performance tests > > sent to both this and the PERFORM mailing list. > > Hey, any test you wanna run is fine with us.I'm pretty sure that OCFS > belongs to Oracle, though, patent & copyright, so we couldn't actually use it > in practice. I thought you knew - OCFS, OCFS-Tools and OCFSv2 have not only been open- source for quite a while now - they're released under the GPL. http://oss.oracle.com/projects/ocfs/ http://oss.oracle.com/projects/ocfs-tools/ http://oss.oracle.com/projects/ocfs2/ I don't know what that means to you (probably nothing good, as PostgreSQL is released under the BSD license), but it most definitely can be considered a good thing for the end user, as she can download it, compile, and set it up on her disks, without the need to pay Oracle royalties. :) > If your intention in this test is to show the superiority of raw devices, let > me give you a reality check: barring some major corporate backing getting > involved, we can't possibly implement our own PG-FS for database support. We > already have a TODO list which is far too long for our developer pool, and > implementing a custom FS either takes a large team (OCFS) or several years of > development (Reiser). Not really - I was just thinking about something not-entirely-a-filesystem and POK!, OCFS sprang to mind. It omits many POSIX features that slow down a traditional filesystem, yet it does know the concept of inodes and most of all, it's _really_ heavy on caching. As such, it sounded quite promising to me, but trial, I think, is the best test. The question does spring up though, that Steve raised in another post - just for the record, what POSIX semantics can a postmaster live without in a filesystem? Cheers, -- Grega Bremec Senior Administrator Noviforum Ltd., Software & Media http://www.noviforum.si/ pgp0.pgp Description: PGP signature
Re: [ADMIN] Query with Max, Order by is very slow.......
Sam Barnett-Cormack <[EMAIL PROTECTED]> writes: > On Wed, 7 Apr 2004, Hemapriya wrote: >> Column |Type | Modifiers >> ---+-+--- >> origindb | character(1)| not null >> uid | integer | not null >> ... >> Indexes: >> "request_pkey" primary key, btree (origindb, uid) >> >> select max(uid) from request where originDB=1; > You really want an index on origindb and uid - He's got one ;-). The real problem with this is the datatype mismatch is preventing use of the index. The query should be select max(uid) from request where originDB='1'; or else change the datatype of origindb to be integer. This query will still want to access all the rows with originDB='1', however. If there are a lot of those then you'll want to use the order by/limit hack. Correct application of the hack to this case goes like regression=# explain select uid from request where originDB='1' order by originDB desc, uid desc limit 1; QUERY PLAN Limit (cost=0.00..3.41 rows=1 width=9) -> Index Scan Backward using request_pkey on request (cost=0.00..17.07 rows=5 width=9) Index Cond: (origindb = '1'::bpchar) (3 rows) If EXPLAIN doesn't show you a sort-free plan then you haven't gotten it right. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Query with Max, Order by is very slow.......
On Wed, Apr 07, 2004 at 14:03:54 -0700, Hemapriya <[EMAIL PROTECTED]> wrote: > Indexes: > "request_pkey" primary key, btree (origindb, uid) > > I do max Query like this > > select max(uid) from request where originDB=1; > > it took around 20 min to return the result.. Since > max, count functions do the full table scan, i tried > the workaround given.. > > select uid from request where originDB=1 order by uid > desc limit 1; > > this query runs forever.. i tried even without where > condition..no result.. Because the index is on both origindb and uid and the planner doesn't know that it can use this index when origindb is fixed but you are ordering on uid, you need to rewrite the query slightly. Try using: select uid from request where originDB=1 order by origindb desc, uid desc limit 1; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] [admin] index in pk
sorry if my questions are so primitive or show all my poor knowledge but ask is the way to find the right path. the dumber the question the smarter i will become. _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] Out of space
"Tom Bakken" <[EMAIL PROTECTED]> writes: > OK, it's truncated and now I've got plenty of space, but it's still > complaining that it can't find the xlogtemp.1405: > FATAL 2: ZeroFill(/var/lib/pgsql/data/pg_xlog/xlogtemp.1405) failed: No > such file or directory I think the "no such file" errno is probably actively misleading. I took another look at the CVS logs and realized that in 7.1.2, there is no guarantee that that message actually reflects the cause of the write failure --- if write() indicates it couldn't write all the bytes, but does not set errno, then the reported errno will be left over from the last failed operation. We had patched this by 7.1.3, which is the version I was looking at locally. Since ENOENT can't be returned by write() AFAIK, it seems certain that this is indeed a leftover errno setting. In short, I still think you are running into some kind of out-of-disk-space failure. I'm not sure what, but you might look to whether you've exceeded the postgres user's disk space quota, or anything along that line. Keep in mind also that an unprivileged user account normally can't fill the disk as full as root can. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] Query with Max, Order by is very slow.......
On Wed, 7 Apr 2004, Hemapriya wrote: > Hi, > > we have table having 23 million rows. > This is the table structure. > Table Request: > > Column |Type | Modifiers > ---+-+--- > origindb | character(1)| not null > uid | integer | not null > rtype | integer | > senderid | integer | > destaddr | character varying(15) | > opid | integer | > devmodel | integer | > ikind | integer | > itemid| character varying(10) | > tranfk| integer | > enteredon | timestamp without time zone | > status| integer | > accountid | integer | > Indexes: > "request_pkey" primary key, btree (origindb, uid) > > I do max Query like this > > select max(uid) from request where originDB=1; > > it took around 20 min to return the result.. Since > max, count functions do the full table scan, i tried > the workaround given.. > > select uid from request where originDB=1 order by uid > desc limit 1; > > this query runs forever.. i tried even without where > condition..no result.. You really want an index on origindb and uid - the order by ... desc limit 1 workaround is only quick if there's an index on the order by field, and and where clause is faster if it can use an index to speed up the query. I would say you might want an index on both of them together, a joint index. -- Sam Barnett-Cormack Software Developer | Student of Physics & Maths UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] binary fields
The keyword you want is "large objects". -kaolin fire -http://erif.org/code/fallingup/ On Apr 7, 2004, at 2:42 PM, Jaime Casanova wrote: hi all, again. i can't find how can i store bynary fields (images, ¿¿pdfs?? is it possible?) in postgres. i'm using vb as frontend. any help?? thanx in advance, Jaime Casanova _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Out of space
Tom, Doh!! I created that log file and let it get out of hand. OK, it's truncated and now I've got plenty of space, but it's still complaining that it can't find the xlogtemp.1405: DEBUG: database system was shut down at 2004-04-07 12:14:38 CDT DEBUG: CheckPoint record at (1, 516646732) DEBUG: Redo record at (1, 516646732); Undo record at (0, 0); Shutdown TRUE DEBUG: NextTransactionId: 28728439; NextOid: 9098648 FATAL 2: ZeroFill(/var/lib/pgsql/data/pg_xlog/xlogtemp.1405) failed: No such file or directory /usr/bin/postmaster: Startup proc 1405 exited with status 512 - abort Again, I know I didn't delete it, but regardless, I'm unsure where to go from here. Thanks for all your help. I hope we're close to a fix. Tom Bakken Information Resource Manager Texas USDA, Rural Development -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 3:57 PM To: Tom Bakken Cc: [EMAIL PROTECTED] Subject: Re: [ADMIN] Out of space "Tom Bakken" <[EMAIL PROTECTED]> writes: > Here's the situation: > [EMAIL PROTECTED] init.d]# cd /var/lib/pgsql/data/ > [EMAIL PROTECTED] data]# ls -l > total 1494316 > -rw---1 postgres postgres4 Jun 21 2001 PG_VERSION > drwx--6 postgres postgres 4096 Sep 17 2003 base > drwx--2 postgres postgres 4096 Oct 27 13:40 global > -rw-r--r--1 root root 7640 Jun 29 2001 h > -rw---1 postgres postgres 9070 Mar 2 10:56 pg_hba.conf > -rw---1 postgres postgres 1118 Jun 21 2001 pg_ident.conf > -rw---1 postgres postgres 1528630320 Apr 7 14:26 pg_log > drwx--2 postgres postgres 4096 Apr 7 14:26 pg_xlog > -rw---1 postgres postgres 3137 Jun 21 2001 postgresql.conf > -rw---1 postgres postgres 52 Apr 7 14:26 postmaster.opts > [EMAIL PROTECTED] data]# ls -l pg_xlog/ > total 16404 > -rw---1 postgres postgres 16777216 Apr 7 12:14 0001001E > I do have a limited amount of space in the partition but I'd like to get = > rid of more. Just not sure what to delete if anything. Hm, what is that pg_log file? It's not part of the normal Postgres fileset. Is it perhaps just the postmaster's stderr output? If so, you're in luck: truncate that as you see fit, and you'll have some breathing room. regards, tom lane BEGIN:VCARD VERSION:2.1 N:Bakken;Tom FN:[EMAIL PROTECTED] ORG:Texas State Office TEL;WORK;VOICE:(254) 742-9726 TEL;WORK;FAX:(254) 742-9709 ADR;WORK;ENCODING=QUOTED-PRINTABLE:;;101 South Main=0D=0ASuite 102;Temple;TX;76502;United States of America LABEL;WORK;ENCODING=QUOTED-PRINTABLE:101 South Main=0D=0ASuite 102=0D=0ATemple, TX 76502=0D=0AUnited States of Am= erica EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:20040301T143942Z END:VCARD ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] binary fields
hi all, again. i can't find how can i store bynary fields (images, ¿¿pdfs?? is it possible?) in postgres. i'm using vb as frontend. any help?? thanx in advance, Jaime Casanova _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] [admin] index in pk
On Wed, 7 Apr 2004, Jaime Casanova wrote: > hi all, there is a way to use my own index with pk constraint instead of the > implicitly created one? > why? just because i don't like the name, maybe can i change the generated > name? You should be able use the full version with the constraint naming clause: CONSTRAINT PRIMARY KEY to make the name of the index. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] [admin] index in pk
hi all, there is a way to use my own index with pk constraint instead of the implicitly created one? why? just because i don't like the name, maybe can i change the generated name? thanx in advance, Jaime Casanova _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Query with Max, Order by is very slow.......
Hi, we have table having 23 million rows. This is the table structure. Table Request: Column |Type | Modifiers ---+-+--- origindb | character(1)| not null uid | integer | not null rtype | integer | senderid | integer | destaddr | character varying(15) | opid | integer | devmodel | integer | ikind | integer | itemid| character varying(10) | tranfk| integer | enteredon | timestamp without time zone | status| integer | accountid | integer | Indexes: "request_pkey" primary key, btree (origindb, uid) I do max Query like this select max(uid) from request where originDB=1; it took around 20 min to return the result.. Since max, count functions do the full table scan, i tried the workaround given.. select uid from request where originDB=1 order by uid desc limit 1; this query runs forever.. i tried even without where condition..no result.. I'm not able to figure out what could be the reason.. can anybody help? Thanks in Advance Regards Priya __ Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway http://promotions.yahoo.com/design_giveaway/ ---(end of broadcast)--- TIP 3: 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
Re: [ADMIN] Out of space
"Tom Bakken" <[EMAIL PROTECTED]> writes: > Here's the situation: > [EMAIL PROTECTED] init.d]# cd /var/lib/pgsql/data/ > [EMAIL PROTECTED] data]# ls -l > total 1494316 > -rw---1 postgres postgres4 Jun 21 2001 PG_VERSION > drwx--6 postgres postgres 4096 Sep 17 2003 base > drwx--2 postgres postgres 4096 Oct 27 13:40 global > -rw-r--r--1 root root 7640 Jun 29 2001 h > -rw---1 postgres postgres 9070 Mar 2 10:56 pg_hba.conf > -rw---1 postgres postgres 1118 Jun 21 2001 pg_ident.conf > -rw---1 postgres postgres 1528630320 Apr 7 14:26 pg_log > drwx--2 postgres postgres 4096 Apr 7 14:26 pg_xlog > -rw---1 postgres postgres 3137 Jun 21 2001 postgresql.conf > -rw---1 postgres postgres 52 Apr 7 14:26 postmaster.opts > [EMAIL PROTECTED] data]# ls -l pg_xlog/ > total 16404 > -rw---1 postgres postgres 16777216 Apr 7 12:14 0001001E > I do have a limited amount of space in the partition but I'd like to get = > rid of more. Just not sure what to delete if anything. Hm, what is that pg_log file? It's not part of the normal Postgres fileset. Is it perhaps just the postmaster's stderr output? If so, you're in luck: truncate that as you see fit, and you'll have some breathing room. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Out of space
Tom, Here's the situation: [EMAIL PROTECTED] init.d]# cd /var/lib/pgsql/data/ [EMAIL PROTECTED] data]# ls -l total 1494316 -rw---1 postgres postgres4 Jun 21 2001 PG_VERSION drwx--6 postgres postgres 4096 Sep 17 2003 base drwx--2 postgres postgres 4096 Oct 27 13:40 global -rw-r--r--1 root root 7640 Jun 29 2001 h -rw---1 postgres postgres 9070 Mar 2 10:56 pg_hba.conf -rw---1 postgres postgres 1118 Jun 21 2001 pg_ident.conf -rw---1 postgres postgres 1528630320 Apr 7 14:26 pg_log drwx--2 postgres postgres 4096 Apr 7 14:26 pg_xlog -rw---1 postgres postgres 3137 Jun 21 2001 postgresql.conf -rw---1 postgres postgres 52 Apr 7 14:26 postmaster.opts [EMAIL PROTECTED] data]# ls -l pg_xlog/ total 16404 -rw---1 postgres postgres 16777216 Apr 7 12:14 0001001E I do have a limited amount of space in the partition but I'd like to get rid of more. Just not sure what to delete if anything. Tom Bakken Information Resource Manager Texas USDA, Rural Development -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 2:57 PM To: Tom Bakken Cc: [EMAIL PROTECTED] Subject: Re: [ADMIN] Out of space "Tom Bakken" <[EMAIL PROTECTED]> writes: > FATAL 2: ZeroFill(/var/lib/pgsql/data/pg_xlog/xlogtemp.1091) failed: No > such file or directory > /usr/bin/postmaster: Startup proc 1091 exited with status 512 - abort > I'm sure I didn't delete it. This is just trying to make a new, empty xlog file. I don't quite understand why the errno is "No such file or directory" --- you wouldn't think that write() could return that errno. But the most likely bet is that you don't yet have enough free space on the disk. These files are 16MB each, and it could be that more than one needs to be made. How much stuff is there in /var/lib/pgsql/data/pg_xlog anyway? I think that 7.1.2 predates some changes we made to keep down the number of xlog files that would be kept around. regards, tom lane BEGIN:VCARD VERSION:2.1 N:Bakken;Tom FN:[EMAIL PROTECTED] ORG:Texas State Office TEL;WORK;VOICE:(254) 742-9726 TEL;WORK;FAX:(254) 742-9709 ADR;WORK;ENCODING=QUOTED-PRINTABLE:;;101 South Main=0D=0ASuite 102;Temple;TX;76502;United States of America LABEL;WORK;ENCODING=QUOTED-PRINTABLE:101 South Main=0D=0ASuite 102=0D=0ATemple, TX 76502=0D=0AUnited States of Am= erica EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:20040301T143942Z END:VCARD ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Out of space
Of course, I was planning to upgrade but as with most things, too little, too late... At this point, I just want to keep it running until I can move to my planned new platform. Can you tell me where to start with CHECKPOINT? If it's any help, my problem appears to be a missing file. This is from my pg_log: DEBUG: database system was shut down at 2004-04-07 12:14:38 CDT DEBUG: CheckPoint record at (1, 516646732) DEBUG: Redo record at (1, 516646732); Undo record at (0, 0); Shutdown TRUE DEBUG: NextTransactionId: 28728439; NextOid: 9098648 FATAL 2: ZeroFill(/var/lib/pgsql/data/pg_xlog/xlogtemp.1091) failed: No such file or directory /usr/bin/postmaster: Startup proc 1091 exited with status 512 - abort Thanks Tom Bakken Information Resource Manager Texas USDA, Rural Development -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 2:46 PM To: Tom Bakken Cc: [EMAIL PROTECTED] Subject: Re: [ADMIN] Out of space "Tom Bakken" <[EMAIL PROTECTED]> writes: > I'm not finding any mention of CHECKPOINT in my references. Is that > something from a version newer than 7.1.2? You're running 7.1.2? My, that *is* an old installation. You really ought to think about an update, particularly if you might be approaching the 4-billion-transaction event horizon. You do not want to suffer XID wraparound in a 7.1 installation :-(. See this link for explanations: http://www.postgresql.org/docs/7.4/static/maintenance.html#VACUUM-FOR-WRAPAR OUND 7.1 does have the CHECKPOINT command, though, whether you see it documented or not. regards, tom lane BEGIN:VCARD VERSION:2.1 N:Bakken;Tom FN:[EMAIL PROTECTED] ORG:Texas State Office TEL;WORK;VOICE:(254) 742-9726 TEL;WORK;FAX:(254) 742-9709 ADR;WORK;ENCODING=QUOTED-PRINTABLE:;;101 South Main=0D=0ASuite 102;Temple;TX;76502;United States of America LABEL;WORK;ENCODING=QUOTED-PRINTABLE:101 South Main=0D=0ASuite 102=0D=0ATemple, TX 76502=0D=0AUnited States of Am= erica EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:20040301T143942Z END:VCARD ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] Out of space
"Tom Bakken" <[EMAIL PROTECTED]> writes: > FATAL 2: ZeroFill(/var/lib/pgsql/data/pg_xlog/xlogtemp.1091) failed: No > such file or directory > /usr/bin/postmaster: Startup proc 1091 exited with status 512 - abort > I'm sure I didn't delete it. This is just trying to make a new, empty xlog file. I don't quite understand why the errno is "No such file or directory" --- you wouldn't think that write() could return that errno. But the most likely bet is that you don't yet have enough free space on the disk. These files are 16MB each, and it could be that more than one needs to be made. How much stuff is there in /var/lib/pgsql/data/pg_xlog anyway? I think that 7.1.2 predates some changes we made to keep down the number of xlog files that would be kept around. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Out of space
"Tom Bakken" <[EMAIL PROTECTED]> writes: > I'm not finding any mention of CHECKPOINT in my references. Is that > something from a version newer than 7.1.2? You're running 7.1.2? My, that *is* an old installation. You really ought to think about an update, particularly if you might be approaching the 4-billion-transaction event horizon. You do not want to suffer XID wraparound in a 7.1 installation :-(. See this link for explanations: http://www.postgresql.org/docs/7.4/static/maintenance.html#VACUUM-FOR-WRAPAROUND 7.1 does have the CHECKPOINT command, though, whether you see it documented or not. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Out of space
I looked in the pg_log file and it's missing xlogtemp.1091.: [EMAIL PROTECTED] data]# tail pg_log DEBUG: Redo record at (1, 516646732); Undo record at (0, 0); Shutdown TRUE DEBUG: NextTransactionId: 28728439; NextOid: 9098648 FATAL 2: ZeroFill(/var/lib/pgsql/data/pg_xlog/xlogtemp.1033) failed: No such file or directory /usr/bin/postmaster: Startup proc 1033 exited with status 512 - abort DEBUG: database system was shut down at 2004-04-07 12:14:38 CDT DEBUG: CheckPoint record at (1, 516646732) DEBUG: Redo record at (1, 516646732); Undo record at (0, 0); Shutdown TRUE DEBUG: NextTransactionId: 28728439; NextOid: 9098648 FATAL 2: ZeroFill(/var/lib/pgsql/data/pg_xlog/xlogtemp.1091) failed: No such file or directory /usr/bin/postmaster: Startup proc 1091 exited with status 512 - abort I'm sure I didn't delete it. Regardless, hopefully based on this one of you might have a suggestion. [EMAIL PROTECTED] data]# Tom Bakken Information Resource Manager Texas USDA, Rural Development -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 12:22 PM To: Tom Bakken Cc: [EMAIL PROTECTED] Subject: Re: [ADMIN] Out of space "Tom Bakken" <[EMAIL PROTECTED]> writes: > I've been running a postgres for 2 or 3 years without a problem. This > morning my disk space for the database filled up. I need to know what > transaction/log files I can truncate or delete without compromising the > system. These files are located under /var/lib/pgsql/data/ I wouldn't recommend deleting *any* files manually --- unless you find core files or old files underneath a pgsql_tmp subdirectory. Those you could zap at little risk. The best approach is to free up a small amount of space elsewhere, enough so you can get through a CHECKPOINT without failing. The checkpoint will hopefully free up some space in pg_xlog. After that you can look at dropping tables you don't need any more, VACUUM FULL, etc. regards, tom lane BEGIN:VCARD VERSION:2.1 N:Bakken;Tom FN:[EMAIL PROTECTED] ORG:Texas State Office TEL;WORK;VOICE:(254) 742-9726 TEL;WORK;FAX:(254) 742-9709 ADR;WORK;ENCODING=QUOTED-PRINTABLE:;;101 South Main=0D=0ASuite 102;Temple;TX;76502;United States of America LABEL;WORK;ENCODING=QUOTED-PRINTABLE:101 South Main=0D=0ASuite 102=0D=0ATemple, TX 76502=0D=0AUnited States of Am= erica EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:20040301T143942Z END:VCARD ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Out of space
Tom, I'm not finding any mention of CHECKPOINT in my references. Is that something from a version newer than 7.1.2? Tom Bakken Information Resource Manager Texas USDA, Rural Development -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 12:22 PM To: Tom Bakken Cc: [EMAIL PROTECTED] Subject: Re: [ADMIN] Out of space "Tom Bakken" <[EMAIL PROTECTED]> writes: > I've been running a postgres for 2 or 3 years without a problem. This > morning my disk space for the database filled up. I need to know what > transaction/log files I can truncate or delete without compromising the > system. These files are located under /var/lib/pgsql/data/ I wouldn't recommend deleting *any* files manually --- unless you find core files or old files underneath a pgsql_tmp subdirectory. Those you could zap at little risk. The best approach is to free up a small amount of space elsewhere, enough so you can get through a CHECKPOINT without failing. The checkpoint will hopefully free up some space in pg_xlog. After that you can look at dropping tables you don't need any more, VACUUM FULL, etc. regards, tom lane BEGIN:VCARD VERSION:2.1 N:Bakken;Tom FN:[EMAIL PROTECTED] ORG:Texas State Office TEL;WORK;VOICE:(254) 742-9726 TEL;WORK;FAX:(254) 742-9709 ADR;WORK;ENCODING=QUOTED-PRINTABLE:;;101 South Main=0D=0ASuite 102;Temple;TX;76502;United States of America LABEL;WORK;ENCODING=QUOTED-PRINTABLE:101 South Main=0D=0ASuite 102=0D=0ATemple, TX 76502=0D=0AUnited States of Am= erica EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:20040301T143942Z END:VCARD ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Out of space
I'm running version 7.1.2. I was able to drop several tables. That cleared up some disk space, but for some reason now, the database won't restart. How can you determine where the problem is when you're running /etc/rc.d/init.d/postgresql restart? Any ideas on that would be appreciated. I've got database dumps so I can always start over. Here's a listing of /var/lib/pgsql/data: .: total 1494316 -rw---1 postgres postgres4 Jun 21 2001 PG_VERSION drwx--6 postgres postgres 4096 Sep 17 2003 base drwx--2 postgres postgres 4096 Oct 27 13:40 global -rw-r--r--1 root root 7640 Jun 29 2001 h -rw---1 postgres postgres 9070 Mar 2 10:56 pg_hba.conf -rw---1 postgres postgres 1118 Jun 21 2001 pg_ident.conf -rw---1 postgres postgres 1528627890 Apr 7 12:32 pg_log drwx--2 postgres postgres 4096 Apr 7 12:32 pg_xlog -rw---1 postgres postgres 3137 Jun 21 2001 postgresql.conf -rw---1 postgres postgres 52 Apr 7 12:32 postmaster.opts As far as log files to delete, here are some I thought might be safe to delete under ./base: total 20 drwx--2 postgres postgres 4096 Jul 13 2001 1 drwx--2 postgres postgres 8192 Apr 7 12:11 185174 drwx--2 postgres postgres 4096 Jun 21 2001 18719 drwx--2 postgres postgres 4096 Apr 7 12:11 213304 ./base/1: total 1556 -rw---1 postgres postgres0 Jun 21 2001 1215 -rw---1 postgres postgres0 Jun 21 2001 1216 -rw---1 postgres postgres 8192 Jun 21 2001 1219 -rw---1 postgres postgres16384 Jul 16 2001 1247 -rw---1 postgres postgres73728 Jul 13 2001 1249 -rw---1 postgres postgres 229376 Jun 21 2001 1255 -rw---1 postgres postgres16384 Jul 16 2001 1259 -rw---1 postgres postgres0 Jun 21 2001 16567 -rw---1 postgres postgres 8192 Jun 21 2001 16579 -rw---1 postgres postgres16384 Jun 21 2001 16600 -rw---1 postgres postgres73728 Jun 21 2001 16617 -rw---1 postgres postgres 8192 Jun 21 2001 16642 -rw---1 postgres postgres 8192 Jun 21 2001 16653 -rw---1 postgres postgres16384 Jun 21 2001 16685 -rw---1 postgres postgres 8192 Jun 21 2001 16867 -rw---1 postgres postgres 8192 Jun 21 2001 16934 -rw---1 postgres postgres0 Jun 21 2001 16948 -rw---1 postgres postgres 8192 Jun 21 2001 16960 -rw---1 postgres postgres0 Jun 21 2001 17033 -rw---1 postgres postgres0 Jun 21 2001 17045 -rw---1 postgres postgres 8192 Jun 21 2001 17058 . . . . I've got a couple of directories that I suspect have stale files. One of them: ./base/185174 contains what appears to be current information. Thanks Tom Bakken Information Resource Manager Texas USDA, Rural Development -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Peter Eisentraut Sent: Wednesday, April 07, 2004 12:20 PM To: Tom Bakken; [EMAIL PROTECTED] Subject: Re: [ADMIN] Out of space Tom Bakken wrote: > I've been running a postgres for 2 or 3 years without a problem. > This morning my disk space for the database filled up. I need to > know what transaction/log files I can truncate or delete without > compromising the system. These files are located under > /var/lib/pgsql/data/ The answer is normally "none" unless you have experienced crashes or other problems that might have left stale files lying around. But you say have had no problem ... If you have any suspicion in that direction, please show us the exact files you're thinking about. A note about which PG version you are running would also help. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html BEGIN:VCARD VERSION:2.1 N:Bakken;Tom FN:[EMAIL PROTECTED] ORG:Texas State Office TEL;WORK;VOICE:(254) 742-9726 TEL;WORK;FAX:(254) 742-9709 ADR;WORK;ENCODING=QUOTED-PRINTABLE:;;101 South Main=0D=0ASuite 102;Temple;TX;76502;United States of America LABEL;WORK;ENCODING=QUOTED-PRINTABLE:101 South Main=0D=0ASuite 102=0D=0ATemple, TX 76502=0D=0AUnited States of Am= erica EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:20040301T143942Z END:VCARD ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Out of space
"Tom Bakken" <[EMAIL PROTECTED]> writes: > I've been running a postgres for 2 or 3 years without a problem. This > morning my disk space for the database filled up. I need to know what > transaction/log files I can truncate or delete without compromising the > system. These files are located under /var/lib/pgsql/data/ I wouldn't recommend deleting *any* files manually --- unless you find core files or old files underneath a pgsql_tmp subdirectory. Those you could zap at little risk. The best approach is to free up a small amount of space elsewhere, enough so you can get through a CHECKPOINT without failing. The checkpoint will hopefully free up some space in pg_xlog. After that you can look at dropping tables you don't need any more, VACUUM FULL, etc. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Out of space
Tom Bakken wrote: > I've been running a postgres for 2 or 3 years without a problem. > This morning my disk space for the database filled up. I need to > know what transaction/log files I can truncate or delete without > compromising the system. These files are located under > /var/lib/pgsql/data/ The answer is normally "none" unless you have experienced crashes or other problems that might have left stale files lying around. But you say have had no problem ... If you have any suspicion in that direction, please show us the exact files you're thinking about. A note about which PG version you are running would also help. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] installation problem
mopeace pliz wrote: > i have a problem adding a user i.e > addusr does not exist in my redhut linux. Try useradd. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Raw devices vs. Filesystems
On Wednesday, April 07, 2004 1:26 AM Tom Lane wrote: > > But to get back to the point of this discussion: to allow PG > to use raw devices instead of filesystems, we'd first have to do a ton of > portability work ... [The following is said in a low, tentative voice :) ] I wonder if writing the postgresql data structures as HDF5 data structures (http://hdf.ncsa.uiuc.edu/whatishdf5.html) within a single HDF5 file (perhaps the WAL files would still reside elsewhere) would improve performance while allowing HDF5 to handle portability, and other useful features, is a better solution than the relying on filesystem features. HDF5 actually provides an added portability advantage that postgresql does not currently enjoy: "a completely portable file format, so that a file can be written on any system and read on any other" (See http://hdf.ncsa.uiuc.edu/HDF5/RD100-2002/All_About_HDF5.pdf). The HDF5 "distribution" includes tools for dumping data structures, etc. so if you're hooked on filesystem level operations, you have the ability to inspect postgresql data structures within the HDF5 file, i.e., "outside postgresql". HDF5's is also designed for clustered/grid computing systems: "The HDF5 format and library provide a powerful means of organizing and accessing data in a manner that allows scientists to share, process, and manipulate data in today's heterogeneous and quickly-evolving high-performance computational environment, including the emerging computational GRIDs." (http://hdf.ncsa.uiuc.edu/HDF5/RD100-2002/All_About_HDF5.pdf, p. 3). So, the main purpose of this post is to suggest that HDF5's design moves a postgresql version built on a HDF5 datastore that much closer to being ready for cluster-computing environments, with respect to the datastore (there's still the shared memory, etc., that need to be addressed, but ...). We're playing with HDF5 from Python (see the pytables project) for our "analytics" work, but that requires moving data out of postgresql. I suspect that an SQL interface to HDF5 data structures using postgresql would be a lot more convenient, and that postgresql would gain multiple benefits from having all its data structures in a single HDF5 file. OTOH, maybe us analytics types are better off with Python over HDF5 and "postgresql on HDF5" is not a net win for postgresql. Still, there seems to a great advantage to having rich data structures to operate on rather than just "files", and allowing the HDF5 library to deal with portability, I/O efficiency, and clustering. Hope my $0.02 worth was. Cheers, Murthy ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] [ADMIN] Raw devices vs. Filesystems
On Wed, Apr 07, 2004 at 09:09:16AM -0700, Josh Berkus wrote: > If your intention in this test is to show the superiority of raw devices, let > me give you a reality check: barring some major corporate backing getting > involved, we can't possibly implement our own PG-FS for database support. We > already have a TODO list which is far too long for our developer pool, and > implementing a custom FS either takes a large team (OCFS) or several years of > development (Reiser). Is there any documentation as to what guarantees PostgreSQL requires from the filesystem, or what posix semantics can be relaxed? Cheers, Steve ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[ADMIN] Out of space
I've been running a postgres for 2 or 3 years without a problem. This morning my disk space for the database filled up. I need to know what transaction/log files I can truncate or delete without compromising the system. These files are located under /var/lib/pgsql/data/ Many of them have dates of more than a year ago. I'm kind of rusty with this. Postgres works too well to keep me fluent with troubleshooting. Tom Bakken Information Resource Manager Texas USDA, Rural Development BEGIN:VCARD VERSION:2.1 N:Bakken;Tom FN:[EMAIL PROTECTED] ORG:Texas State Office TEL;WORK;VOICE:(254) 742-9726 TEL;WORK;FAX:(254) 742-9709 ADR;WORK;ENCODING=QUOTED-PRINTABLE:;;101 South Main=0D=0ASuite 102;Temple;TX;76502;United States of America LABEL;WORK;ENCODING=QUOTED-PRINTABLE:101 South Main=0D=0ASuite 102=0D=0ATemple, TX 76502=0D=0AUnited States of Am= erica EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:20040301T143942Z END:VCARD ---(end of broadcast)--- TIP 3: 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
Re: [PERFORM] [ADMIN] Raw devices vs. Filesystems
Grega, > Furthermore, this filesystem would be a blazing one stop solution for > all replication issues PostgreSQL currently suffers from, as its main > design goal was to present "a consistent file system image across the > servers in a cluster". Does it work, though? Without Oracle admin tools? > Now, if both goals can be achieved in one go, hell, I'm willing to try > it out myself in an attempt to extract off of it, some performance > indicators that could be compared to other database performance tests > sent to both this and the PERFORM mailing list. Hey, any test you wanna run is fine with us.I'm pretty sure that OCFS belongs to Oracle, though, patent & copyright, so we couldn't actually use it in practice. If your intention in this test is to show the superiority of raw devices, let me give you a reality check: barring some major corporate backing getting involved, we can't possibly implement our own PG-FS for database support. We already have a TODO list which is far too long for our developer pool, and implementing a custom FS either takes a large team (OCFS) or several years of development (Reiser). Now, if you know somebody who might pay for one, then great -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
[ADMIN] installation/verification of postgres w/ pgtcl
Good morning, I am porting my application from Unix/Linux to Windows (NT, 2000) and need to use postgres with tcl extension library. Is there a good location to obtain both/either of these? The only postgres I found at this point is some beta version, however, it tells me that it cannot find the pgtcl libraries or some library it references. Is there an env variable I am missing? Any help in this capacity would be appreciated. Regards, Ronald C. Nolte Sr. Software Engineer, SAIC 1710 SAIC Drive, Suite 300 McLean, VA 22102 [EMAIL PROTECTED] Office: (703) 676-2018 Fax:(703) 893-3084 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] PostgreSQL backup issue
At this time, we do not have point in time recovery. That is slated for 7.5 (we hope). There is also no way to perform incremenatals (yet). -Original Message- From: [EMAIL PROTECTED] on behalf of Eric Yum Sent: Mon 4/5/2004 7:34 PM To: [EMAIL PROTECTED] Cc: Subject: [ADMIN] PostgreSQL backup issue Dear all I am using PostgreSQL 7.3 as DB server in my company. I am preparing some DB backup and maintenance procedures. I found from Administrator guide that we can use âdumpâ command to backup the DB. It just allow me to backup the whole DB and restore the whole DB. I wonder that is any command or feature in PostgreSQL allow me to have incremental backup policy like SQL Server. In addition, when I restore the DB, is it possible to select the specify time frame to restore. Million Thanks. Best Regards, Eric Yum -- CONFIDENTIALITY NOTICE -- This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[ADMIN] installation problem
i have a problem adding a user i.e addusr does not exist in my redhut linux. thanks in advance. __ Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway http://promotions.yahoo.com/design_giveaway/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] restoring large objects
Good news - the restore worked fine with the new column type and the application worked fine once I'd added a duplicate column of type text. So at least I have a workable recovery procedure! Many thanks for your help. Marion -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Marion McKelvie Sent: 07 April 2004 08:44 To: [EMAIL PROTECTED] Subject: Re: [ADMIN] restoring large objects Good idea but I'm not sure if the application will then handle the changed column type correctly. I'll give it a go and let you know... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tom Lane Sent: 07 April 2004 07:08 To: Marion McKelvie Cc: [EMAIL PROTECTED] Subject: Re: [ADMIN] restoring large objects "Marion McKelvie" <[EMAIL PROTECTED]> writes: > Many thanks - you're right, the references are stored (by the application > using the database) in a column of type text. Yuck :-(. A possible solution is to dump the schema and data separately (schema in a plain text dump, data in an -Fc or -Ft dump since plain text doesn't support dumping LOs). Then manually edit the schema file to change the column data type to "oid". Then load. I honestly am not sure that this will work cleanly, but it's worth trying --- and if it doesn't work, please let me know where it goes wrong. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] restoring large objects
Good idea but I'm not sure if the application will then handle the changed column type correctly. I'll give it a go and let you know... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tom Lane Sent: 07 April 2004 07:08 To: Marion McKelvie Cc: [EMAIL PROTECTED] Subject: Re: [ADMIN] restoring large objects "Marion McKelvie" <[EMAIL PROTECTED]> writes: > Many thanks - you're right, the references are stored (by the application > using the database) in a column of type text. Yuck :-(. A possible solution is to dump the schema and data separately (schema in a plain text dump, data in an -Fc or -Ft dump since plain text doesn't support dumping LOs). Then manually edit the schema file to change the column data type to "oid". Then load. I honestly am not sure that this will work cleanly, but it's worth trying --- and if it doesn't work, please let me know where it goes wrong. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Raw devices vs. Filesystems
...and on Wed, Apr 07, 2004 at 01:26:02AM -0400, Tom Lane used the keyboard: > > After that, we get to implement our own filesystem-equivalent management > of disk space allocation, disk I/O scheduling, etc. Are we really > smarter than all those kernel hackers doing this for a living? I doubt it. > > After that, we get to re-optimize all the existing Postgres behaviors > that are designed to sit on top of a standard Unix buffering filesystem > layer. > > After that, we might reap some performance benefits. Or maybe not. > There's not a heck of a lot of hard evidence that we would --- and > what there is traces to twenty-year-old assumptions about disk drive > and OS behavior, which are quite unlikely to still apply today. > > Personally, I have a lot of more-promising projects to pursue... > Has anyone tried PostgreSQL on top of OCFS? Personally, I'm not sure it would even work, as Oracle clearly state that OCFS was _never_ meant to be a fully fledged UNIX filesystem with POSIX features such as correct timestamp updates, inode changes, etc., but OCFSv2 brings some features that might lead one into thinking they're about to make it suitable for uses beyond that of just having Oracle databases sitting on top of it. Furthermore, this filesystem would be a blazing one stop solution for all replication issues PostgreSQL currently suffers from, as its main design goal was to present "a consistent file system image across the servers in a cluster". Now, if both goals can be achieved in one go, hell, I'm willing to try it out myself in an attempt to extract off of it, some performance indicators that could be compared to other database performance tests sent to both this and the PERFORM mailing list. So, anyone? :) Cheers, -- Grega Bremec Senior Administrator Noviforum Ltd., Software & Media http://www.noviforum.si/ pgp0.pgp Description: PGP signature
