[GENERAL] Lost main database directory but not tablespace holding the database - how to recover?
My main disk for Postgresql crashed but I still have the database as it was stored on a tablespace on another disk. Now the problem is how to recover from this situation. What I have tried is to restore the database from a backup from a few houres before the crash and then replacing the directory within the pgsql-data/base directory holding the database with the version of the database from the uncorrupted disk. When I try to access the database I get ERROR: could not access status of transaction 1827968 DETAIL: could not open file "/home/software/postgres/DATA/pg_clog/0001": No such file or directory How can I solve this inconsistency and recover the database? :) Thanks! Geir. - Start.no tilbyr nå raskere bredbånd til lavere pris. Sjekk http://www.start.no/bredband/ for mer informasjon ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] PostgreSQL select
Hello, There's a problem with PostgreSQL 8.0.3 SELECT. All tables exist, all columns exist, I have no idea where's the problem :/ PostgreSQL reports this error: ERROR: relation "fieldx" does not exist... SELECT fieldx.field_id, COUNT(optionx.option_id) AS field_options FROM c_custom_fields AS fieldx, j_product_groups_fields AS join_table LEFT JOIN c_custom_fields_options AS optionx ON optionx.field_id = fieldx.field_id WHERE fieldx.field_id = join_table.field_id AND join_table.group_id = 10 GROUP BY fieldx.field_id ORDER BY fieldx.field_id Any clues? -- Jiří Němec, ICQ: 114651500 www.menea.cz - www stránky a aplikace ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL select
On Sat, 16 Jul 2005, [ISO-8859-2] Ji?? N?mec wrote: > There's a problem with PostgreSQL 8.0.3 SELECT. All tables exist, all > columns exist, I have no idea where's the problem :/ PostgreSQL > reports this error: ERROR: relation "fieldx" does not exist... > > SELECT fieldx.field_id, COUNT(optionx.option_id) AS field_options > FROM c_custom_fields AS fieldx, j_product_groups_fields AS join_table > LEFT JOIN c_custom_fields_options AS optionx ON optionx.field_id = > fieldx.field_id Our reading of the spec is that fieldx is not in scope for that left join's on condition since effectively join has higher precedence than comma separating items in the from list. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL select
Am Samstag, den 16.07.2005, 09:19 +0200 schrieb Jiří Němec: > Hello, > > There's a problem with PostgreSQL 8.0.3 SELECT. All tables exist, all > columns exist, I have no idea where's the problem :/ PostgreSQL > reports this error: ERROR: relation "fieldx" does not exist... > > SELECT fieldx.field_id, COUNT(optionx.option_id) AS field_options > FROM c_custom_fields AS fieldx, j_product_groups_fields AS join_table > LEFT JOIN c_custom_fields_options AS optionx ON optionx.field_id = > fieldx.field_id > WHERE fieldx.field_id = join_table.field_id AND join_table.group_id = 10 > GROUP BY fieldx.field_id > ORDER BY fieldx.field_id > > Any clues? May we see \dt please? I'm pretty sure fieldx does not exist. Maybe you have "FieldX" or something instead? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Nulls in timestamps
Thanks, that looks useful. I'll investigate once I've got the servers upgraded and replication running. Bruno Wolff III wrote: > > On Wed, Jul 13, 2005 at 18:15:12 +, > [EMAIL PROTECTED] wrote: > > Many thanks Tom. Inconvenient from the point of view of the application > > but still useful information. > > > > The situation is that I've got a query with numerous subselects, each of > > which has to return exactly one row so I was doing a union with a nulled > > record then selecting the most recent: obviously I need to see bona-fide > > data if it's there. > > you can order by datecol is null, datecol desc to get the most recent > non null date. For example: > area=> select day from (select 'today'::date as day union select > 'tomorrow'::date as day union select null as day) as un order by day is null, > day desc; > day > > 2005-07-16 > 2005-07-15 > > (3 rows) > > ---(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 -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] encoding and locale
I'm using postgres8 with encoding 'UNICODE'. I have some problems with unsupported mutlibyte... e.g. with upper() Now I know a I cannot use every lc_ctype with every encoding, but where can I find a list of valid encoding/locale combinations? regards Christian ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] 7.3.4 dump/restore error
Hi! I dumped from a 7.3.4 server on debian with kernel 2.6 using this command: pg_dump -F c -Z 9 -f backoffice.dmp.c backoffice I tried to restore on a similiar system using: pg_restore -vd backoffice backoffice.dmp.c and stepped into an error: ERROR: copy: line 270892, Missing data for column "id_tax" .. FATAL: Socket command type 3 unknown more detailed messages are attached. could somebody help me please fixing this problem? thanks in advance thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 7.3.4 dump/restore error
i forgot to attach the error messages. but now! t On 7/16/05, Thomas Chille <[EMAIL PROTECTED]> wrote: > Hi! > > I dumped from a 7.3.4 server on debian with kernel 2.6 using this command: > > pg_dump -F c -Z 9 -f backoffice.dmp.c backoffice > > > I tried to restore on a similiar system using: > > pg_restore -vd backoffice backoffice.dmp.c > > > and stepped into an error: > > ERROR: copy: line 270892, Missing data for column "id_tax" > .. > FATAL: Socket command type 3 unknown > > more detailed messages are attached. > > > could somebody help me please fixing this problem? > > thanks in advance > thomas > client message: ... pg_restore: restoring data for table tmp_order_data pg_restore: [archiver (db)] error returned by PQputline pg_restore: *** aborted because of error server mesage (log): ... 2005-07-16 15:42:25 [17889] DEBUG: StartTransactionCommand 2005-07-16 15:42:25 [17889] LOG: query: 2005-07-16 15:42:25 [17889] DEBUG: ProcessUtility 2005-07-16 15:42:34 [17889] ERROR: copy: line 270892, Missing data for column "id_tax" 2005-07-16 15:42:34 [17889] LOG: statement: -- -- Data for TOC entry 1754 (OID 1285883) -- Name: tmp_order_data; Type: TABLE DATA; Schema: public; Owner: postgres -- Data Pos: 9733254 -- COPY tmp_order_data (ss_id_transaction, ssd_transaction, id_transactiontype, business_day, transactiontime, id_company, id_operator, id_dining, id_sold_item, id_pos, id_pos_item, selling_price, quantity, id_invoice, invoice_number, reason, discount, id_tax, standard_price, id_dining_dest, id_pos_dest, id_invoice_dest, operator_name, item_name, plu, is_flow_item, pos_name, commodity_name, splitcode, id_sold_item_dest, id_sold_item_remain, state, processed, replication_id, id_price_level, id_gift_settle_cat, quantity_remain) FROM stdin; 2005-07-16 15:42:34 [17889] DEBUG: AbortCurrentTransaction 2005-07-16 15:42:34 [17889] FATAL: Socket command type 3 unknown 2005-07-16 15:42:34 [17889] DEBUG: proc_exit(0) 2005-07-16 15:42:34 [17889] DEBUG: shmem_exit(0) 2005-07-16 15:42:34 [17889] DEBUG: exit(0) 2005-07-16 15:42:34 [17771] DEBUG: child process (pid 17889) exited with exit code 0 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL select
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Sat, 16 Jul 2005, [ISO-8859-2] Jiøí Nìmec wrote: >> There's a problem with PostgreSQL 8.0.3 SELECT. All tables exist, all >> columns exist, I have no idea where's the problem :/ PostgreSQL >> reports this error: ERROR: relation "fieldx" does not exist... >> >> SELECT fieldx.field_id, COUNT(optionx.option_id) AS field_options >> FROM c_custom_fields AS fieldx, j_product_groups_fields AS join_table >> LEFT JOIN c_custom_fields_options AS optionx ON optionx.field_id = >> fieldx.field_id > Our reading of the spec is that fieldx is not in scope for that left > join's on condition since effectively join has higher precedence than > comma separating items in the from list. In other words: what you probably meant here is FROM (c_custom_fields AS fieldx CROSS JOIN j_product_groups_fields AS join_table) LEFT JOIN c_custom_fields_options AS optionx ON optionx.field_id = fieldx.field_id (The parentheses aren't required in this particular case but they help clarify your intent.) We are aware that there are certain standards-challenged products that get this wrong. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] encoding and locale
Christian Traber <[EMAIL PROTECTED]> writes: > Now I know a I cannot use every lc_ctype with every encoding, > but where can I find a list of valid encoding/locale combinations? If there were a standardized way of finding that out, we'd long since have made Postgres take advantage of it to prevent use of non-working combinations. But there's not. The best bet usually is to look at the output of "locale -a", and see if you can figure out the naming convention used for the locales. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 7.3.4 dump/restore error
Thomas Chille <[EMAIL PROTECTED]> writes: > I dumped from a 7.3.4 server on debian with kernel 2.6 using this command: > pg_dump -F c -Z 9 -f backoffice.dmp.c backoffice > I tried to restore on a similiar system using: > pg_restore -vd backoffice backoffice.dmp.c > and stepped into an error: > ERROR: copy: line 270892, Missing data for column "id_tax" Try asking pg_restore to dump to a text file (maybe dump just this one table) and look at the data to see if you can tell what happened to it. Note the line number is within the copy data, ie, the first data line after the COPY command is line 1. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL select
16. července 2005, 16:46:59, napsal jste: > In other words: what you probably meant here is > FROM (c_custom_fields AS fieldx CROSS JOIN > j_product_groups_fields AS join_table) > LEFT JOIN c_custom_fields_options AS optionx ON optionx.field_id = > fieldx.field_id Thank you, your query runs but returns weird records, returns correct records from "c_custom_fields" table but incorrect number of records from JOINed "c_custom_fields_options" table. There are 3 table such structures: c_custom_fields - field_id, field_name 1 RAM 2 HDD c_custom_fields_options - option_id, field_id, option_value 1 1128 2 1512 3 11024 4 280 5 2120 6 2160 7 2200 j_product_groups_fields - group_id, field_id 1 1 1 2 I need to select records from c_custom_fields table which belong to group_id = 1 (j_product_groups_fields) and count number of options which belong to selected field: field_id field_name COUNT(c_custom_fields_options.option_id) 1 RAM 3 2 HDD 4 -- Jiří Němec, ICQ: 114651500 www.menea.cz - www stránky a aplikace ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] encoding and locale
Christian Traber wrote: > Now I know a I cannot use every lc_ctype with every encoding, > but where can I find a list of valid encoding/locale combinations? Try something like the following: for x in $(locale -a); do echo -n "$x: "; LC_ALL=$x locale charmap; done Unfortunately, the name of the encoding that the locale system uses might differ from the locale name that PostgreSQL uses, so you will have to be able to see through that. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] foreign key constraints and inheritence
I have this structure:
CREATE TABLE invoicelineitemtypes (
invoicelineitemtypeid varchar(36) PRIMARY KEY,
otherstuff varchar(36)
);
CREATE TABLE invoicelineiteminventorytypes (
moresetuff varchar(36)
)
INHERITS (invoicelineitemtypes);
CREATE TABLE invoicelines (
invoicelineitemtypeid varchar(36) REFERENCES
invoicelineitemtypes ON DELETE CASCADE,
otherstuff varchar(36)
);
INSERT INTO invoicelineiteminventorytypes (invoicelineitemtypeid)
VALUES ('1234');
The following insert:
INSERT INTO invoicelines (invoicelineitemtypeid) VALUES ('1234');
invokes
"insert or update on table "invoicelines" violates foreign key
constraint "invoicelines_invoicelineitemtypeid_fkey"
Now, the rec with id '1234' is in the table
invoicelineiteminventorytypes, but I can't wrap my head around the
fact that
SELECT invoicelineitemtypeid FROM invoicelineitemtypes
yields the rec with invoicelineitemtypeid '1234', yet I can't
reference it in the invoicelines INSERT statement.
Am I missing something? Is there a workaround for this?
PostgreSQL 8.0.1 running on OS X 10.4.2.
Garrett
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] How to create unique constraint on NULL columns
I was faced with a similar issue. One suggestion I got from the Internet was to create a shadow column that contains the values used in the Index, with a dummy entry (in my case, the string ) for those records in which the primary column is NULL. It works well for my app.
Re: [GENERAL] Converting MySQL tinyint to PostgreSQL
Jim C. Nasby wrote: On Thu, Jul 14, 2005 at 11:29:23PM +0200, Martijn van Oosterhout wrote: On Thu, Jul 14, 2005 at 11:30:36AM -0500, Jim C. Nasby wrote: On Wed, Jul 13, 2005 at 07:52:04PM -0400, Bruce Momjian wrote: This is a good point. We have always stored data on disk that exactly matches its layout in memory. We could change that, but no one has shown it would be a win. [...] My thought was to convert as pages were read and written. That should minimize the code impact. If that were practical, even more radical I/O saving tricks might be possible beyond removing alignment bytes - like some compression algorithm. Jim C. Nasby wrote: > Or maybe as an alternative, would it be possible to determine how much > space in a given relation was being wasted due to padding? That could be > used to figure out how much IO could be saved on different transactions. Well, I do notice that if I gzip my larger tables's data files they tend to compress between 80-95% so it seems there's a fair amount of redundancy in at least some tables. Has anyone tried running postgresql on a filesystem that does compression? It seems that would be an easy way to guess at the ultimate potential I/O savings of separating memory layout from disk layout. Ron ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to create unique constraint on NULL columns
Andrus wrote: Then redesign this as a many to many relation. ... This means adding separate row for each department into permission table. Not really. You can of course store an Array of department IDs in that same table. That would probably cause the minimum impact on your queries too. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
