Re: [GENERAL] REINDEX "is not a btree"
Hello, Thanks for all your answers! I discovered the table that was causing the error, delete it and create it again (I miss some data but at least everything else is working now) Yes, for the backup we copy everything we had under /data (the directory containing "base", "global", and so on ... we do backups every day from the server), and then we restore the whole /data directory at once ... but it did not solve the problem .. What do you mean by we can't simply take a filesystem copy of a running database? :-O ... How should we then do the backups (so next time I will not have the same problem again) ? Millions of thanks again! Vanessa On 10 Jul 2009, at 04:06, decibel wrote: On Jul 4, 2009, at 8:06 AM, Craig Ringer wrote: On Fri, 2009-07-03 at 15:00 +0100, Vanessa Lopez wrote: I don't know much about postgre, I have no clue what else I can do. Please, please any help is very very much appreciated I have lots of databases and months of work in postgre (also lots of backups for the data in /data) When you say "in /data", do you mean the directory that contains the directories "pg_xlog", "base", "global", "pg_clog", etc ? Did you back up and restore the WHOLE data directory at once? Or did you restore only parts of it? And how exactly did you make the backups? You can't simply take a filesystem copy of a running database; that won't work. -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] REINDEX "is not a btree"
Hello! I got into a situation I don't know how the get out .. First, I could not access to my biggest database in postgre anymore because it suddenly gave the error (after long time working with no problems) ERROR: could not open relation 1663/392281/530087: No such file or directory After trying with several backups with no success, I did a vacuum and I tried to REINDEX the database (in the standalone back-end). Unfortunately the process was interrupted, and when I tried to start postgres again I got the error: 'SQL select * from pg_database order by datname failed : index "pg_authid_rolname_index" is not a btree" I connected as a standalone mode again to REINDEX the database: pg_ctl stop -D /data/pgsql/data /usr/bin/postgres -D /data/pgsql/data dbpedia_infoboxes REINDEX database dbpedia_infoboxes The REINDEX was successful this time but I was still having the "is not a btree" problem, so I tried again with: pg_ctl stop -D /data/pgsql/data /usr/bin/postgres -D /data/pgsql/data dbpedia_infoboxes REINDEX SYSTEM dbpedia_infoboxes The process finish, but I was still having the "is not a btree" problem. And even more, now not only the same problem "is not a btree" is still there, but also I can not connect in the standalone mode anymore: bash-3.2$ /usr/bin/postgres -D /data/pgsql/data dbpedia_infoboxes FATAL: index "pg_database_datname_index" is not a btree (I tried with other databases as well and the same) I don't know much about postgre, I have no clue what else I can do. Please, please any help is very very much appreciated I have lots of databases and months of work in postgre (also lots of backups for the data in /data) but I don't know how to make postgres to work again. (it is working in unix red hat). Millions of thanks in advance, solving this problem is crucial for me. Vanessa
[GENERAL] Is it possible to compress a table any further?
I have very large database that is in postgresql and in particular one table that take up too much space. I know that in mysql one can compress individual tables with isam-compression. I understand that postgresql compresses text automatically, is there anyway to compress my large table any further? Cheers. Vanessa -- View this message in context: http://www.nabble.com/Is-it-possible-to-compress-a-table-any-further--tf3237859.html#a8999365 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] How to append the contents of a table to a file
Hello! Does anyone have any idea about how to append the contents of a table to a file? Thanks. Vanessa -- View this message in context: http://www.nabble.com/How-to-append-the-contents-of-a-table-to-a-file-tf3237484.html#a8998198 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] How to use slash commands in a function
Hi guys, I was wondering how could i (if at all possible) to use say a command like: \! touch fred.txt in a function? i.e. CREATE FUNCTION myfunc() RETURNS TRIGGER AS 'BEGIN \! touch fred.txt RETURN NEW; END;' LANGUAGE 'plpgsql'; At the moment i get: ERROR: syntax error at or near "\" Does this mean i should encapsulate the line in quotation marks or something like that? Cheers. Vanessa -- View this message in context: http://www.nabble.com/How-to-use-slash-commands-in-a-function-tf3237240.html#a8997475 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] converting a specified year and week into a date
hi guys, i was just wondering if it was at all possible to turn a year and a given week number into a real date just using postgresql commands? e.g. if i have year = 2004 and week = 1, can i turn that into say 2004-01-01 (so that the specified date is the one for the beginning of week 1 in the year 2004 thanks vanessa :) -- View this message in context: http://www.nabble.com/converting-a-specified-year-and-week-into-a-date-tf3223753.html#a8954235 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
