Re: [ADMIN] Problems doing a restore under 7.4.2
Chris White (cjwhite) [EMAIL PROTECTED] writes: I have done a backup of my 7.4.2 database using pg_dump. When I restore the database using the -c option I get the following error message and pg_restore fails pg_restore: [archiver (db)] could not execute query: ERROR: trigger RI_ConstraintTrigger_17371 for table vm_message does not exist Probably an ordering issue --- easiest workaround is not to use -c, I'd think. If you like, you could try CVS-tip pg_dump to see if it gets the ordering right. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Problems doing a restore under 7.4.2
I got around the problem by using the --disable-triggers option on the restore. Chris -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 9:02 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [ADMIN] Problems doing a restore under 7.4.2 Chris White (cjwhite) [EMAIL PROTECTED] writes: I have done a backup of my 7.4.2 database using pg_dump. When I restore the database using the -c option I get the following error message and pg_restore fails pg_restore: [archiver (db)] could not execute query: ERROR: trigger RI_ConstraintTrigger_17371 for table vm_message does not exist Probably an ordering issue --- easiest workaround is not to use -c, I'd think. If you like, you could try CVS-tip pg_dump to see if it gets the ordering right. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] set_curcfg('default') + tsearch1 call = postmaster crash
Tom Lane wrote: Rajesh Kumar Mallah [EMAIL PROTECTED] writes: in my database i have both tsearch1 and tsearch2 . the following sequence of commands lead to this repeatble problem . I get a t result in 7.4 tip. Any solution ? Possibly you should be running something newer than 7.4RC2. OT: can i do it without dump -- initdb -- reload? 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
[ADMIN] Restoring a Databases that features tserach2
Hi I'm trying to restore tsearch2 featuring database like discribed in the 'tsearch-V2-intro' document. (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html) In point '5) Restore the data for the database' I get the following Error: pg_restore: ERROR: duplicate key violates unique constraint pg_ts_dict_pkey CONTEXT: COPY pg_ts_dict, line 1: simple 215247 \N 215248 Simple example of dictionary. pg_restore: [archiver (db)] error returned by PQendcopy pg_restore: *** aborted because of error Does anybody have some experience in this task? Thanks Ulrich -- Ulrich G. Fischer Dipl. Natw. ETH Center Aerodynamics Ruag Aerospace P.O. Box 301CH-6032 Emmen Tel. +41 41 268 23 53 Fax. +41 41 268 38 97 [EMAIL PROTECTED] www.ruag.com ---(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
[ADMIN] Problems with select lower()
Hello *,
I have a problem with select lower() using capital letters with diaeresis. I
tried with encoding SQL_ASCII und UNICODE.
newsdb=# select lower('kosteuer');
lower
kosteuer
(1 row)
newsdb=# select lower('Andreas');
lower
-
andreas
(1 row)
Any Ideas ?
regards,
-Andreas
--
---(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] Restoring a Databases that features tserach2
Some additional infos to my problem Old System: Postgresql 7.3.2 New System: Postgresql 7.4.1 pg_dump (PostgreSQL) 7.3.2 (pg_dumpall -g GLOBALobjects.sql) pg_dump -s DATABASE DBschema.sql pg_dump -Fc DATABASE DBdata.tar and rebuilt on the new system with: psql (PostgreSQL) 7.4.1 1. createdb DATABASE 2. (psql DATABASE GLOBALobjects.sql) 3. psql DATABASE tsearch2.sql 3.a. psql DATABASE setup_ge_ispell.sql 4. psql DATABASE DBschema.sql 5. pg_restore -N -a -v -d DATABASE DBdata.tar There is no difference when dumped with pg_dump (PostgreSQL) 7.4.1. I get the same error. When I do not update the dict-settings (setup_ge_ispell.sql) I can not even restore the other datas. Ulrich Andreas Schmitz wrote: How exactly is the dump created and the restore started ? On Thursday 22 April 2004 10:55, Fischer Ulrich wrote: Hi I'm trying to restore tsearch2 featuring database like discribed in the 'tsearch-V2-intro' document. (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-int ro.html) In point '5) Restore the data for the database' I get the following Error: pg_restore: ERROR: duplicate key violates unique constraint pg_ts_dict_pkey CONTEXT: COPY pg_ts_dict, line 1: simple 215247 \N 215248 Simple example of dictionary. pg_restore: [archiver (db)] error returned by PQendcopy pg_restore: *** aborted because of error Does anybody have some experience in this task? Thanks Ulrich -- Ulrich G. Fischer Dipl. Natw. ETH Center Aerodynamics Ruag Aerospace P.O. Box 301CH-6032 Emmen Tel. +41 41 268 23 53 Fax. +41 41 268 38 97 [EMAIL PROTECTED] www.ruag.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Problems with select lower()
Andreas Schmitz [EMAIL PROTECTED] writes: I have a problem with select lower() using capital letters with diaeresis. I tried with encoding SQL_ASCII und UNICODE. upper/lower currently only work with single-byte characters, so you need to use one of the LATINn database encodings rather than UNICODE if you want this functionality. 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
[ADMIN] Transaction log file screwed up
Hello, A client database crashed last night and I think I found the solution to the problem at http://www.varlena.com/varlena/GeneralBits/45.php Database Recovery Procedures . The solution was to append the file with enough zeros to get the file size correct. The solution was to run dd bs=8k count=1 /dev/zero $PGDATA/pg_clog/0005 which added the zeros to the end. However my error values are a little different from ones in the example and the person who posted this noted that this should NOT be tried haphazardly. Since I'd rather not crash a client's database to the point of no recovery I wouldn't mind having someone with more knowledge of PostgreSQL post the command with the correct arguments to fix the problem, or tell me I'm off my rocker trying to fix it this way. Following is pg_ctl's output when trying to start the database. Following that is the directory listing for the pg_clog directory. LOG: checkpoint record is at 1/383BDFC0 LOG: redo record is at 1/383BDFC0; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 15038948; next oid: 3293693 LOG: database system was not properly shut down; automatic recovery in progress LOG: ReadRecord: unexpected pageaddr 1/363BE000 in log file 1, segment 56, offset 3923968 LOG: redo is not required PANIC: XLogWrite: write request 1/383BE000 is past end of log 1/383BE000 LOG: startup process (pid 1555) was terminated by signal 6 LOG: aborting startup due to startup process failure pgsql/data/pg_clog/* -rw---1 postgres postgres 262144 Mar 14 13:10 -rw---1 postgres postgres262144 Mar 24 15:35 0001 -rw---1 postgres postgres262144 Mar 27 00:04 0002 -rw---1 postgres postgres262144 Mar 29 18:06 0003 -rw---1 postgres postgres262144 Apr 1 00:39 0004 -rw---1 postgres postgres262144 Apr 3 09:54 0005 -rw---1 postgres postgres262144 Apr 5 21:39 0006 -rw---1 postgres postgres262144 Apr 8 03:26 0007 -rw---1 postgres postgres262144 Apr 10 10:15 0008 -rw---1 postgres postgres262144 Apr 12 21:05 0009 -rw---1 postgres postgres262144 Apr 15 00:32 000A -rw---1 postgres postgres262144 Apr 17 03:38 000B -rw---1 postgres postgres262144 Apr 19 13:07 000C -rw---1 postgres postgres262144 Apr 21 13:38 000D -rw---1 postgres postgres 90112 Apr 22 07:01 000E Thanks Kevin ---(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] Problems with select lower()
Am Donnerstag, 22. April 2004 10:55 schrieb Andreas Schmitz: I have a problem with select lower() using capital letters with diaeresis. I tried with encoding SQL_ASCII und UNICODE. You need to set your locale to something useful (de_DE), not the encoding. And lower() doesn't work with Unicode, but that is an independent issue. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] Transaction log file screwed up
Kevin Schroeder [EMAIL PROTECTED] writes: PANIC: XLogWrite: write request 1/383BE000 is past end of log 1/383BE000 The correct solution to this is to update your 7.3.3 installation to a version without that page-boundary-case bug; as long as you're on 7.3.3 you have a one-in-several-hundred chance of the above startup failure. 7.3.6 is the right version to be using if you're on the 7.3.* line. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] Cascade delete question
On Thu, Apr 22, 2004 at 16:46:31 -0400, Glenn MacGregor [EMAIL PROTECTED] wrote: No problem this works great! Now I have changed my data to vid vname parentname 1 [EMAIL PROTECTED] 2 [EMAIL PROTECTED] n1 3 [EMAIL PROTECTED] n1 4 [EMAIL PROTECTED] n1 5 [EMAIL PROTECTED] n3 Can I change my delete cascade constraint to do this substring query instead of an exact match? No. Is there anyway to accomplish what I need to do? If the local part of the email address is a candidate key, separate it from the domain name and make it the primary key. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] [ADMIN] Restoring a Databases that features tserach2
Fischer, probable scenario: after step 3. you have tables pg_ts_* and when you restore your database you tried to insert duplicated data. I recommend to see remove any entries related to pg_ts_* tables. Oleg On Thu, 22 Apr 2004, Fischer Ulrich wrote: Some additional infos to my problem Old System: Postgresql 7.3.2 New System: Postgresql 7.4.1 pg_dump (PostgreSQL) 7.3.2 (pg_dumpall -g GLOBALobjects.sql) pg_dump -s DATABASE DBschema.sql pg_dump -Fc DATABASE DBdata.tar and rebuilt on the new system with: psql (PostgreSQL) 7.4.1 1. createdb DATABASE 2. (psql DATABASE GLOBALobjects.sql) 3. psql DATABASE tsearch2.sql 3.a. psql DATABASE setup_ge_ispell.sql 4. psql DATABASE DBschema.sql 5. pg_restore -N -a -v -d DATABASE DBdata.tar There is no difference when dumped with pg_dump (PostgreSQL) 7.4.1. I get the same error. When I do not update the dict-settings (setup_ge_ispell.sql) I can not even restore the other datas. Ulrich Andreas Schmitz wrote: How exactly is the dump created and the restore started ? On Thursday 22 April 2004 10:55, Fischer Ulrich wrote: Hi I'm trying to restore tsearch2 featuring database like discribed in the 'tsearch-V2-intro' document. (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-int ro.html) In point '5) Restore the data for the database' I get the following Error: pg_restore: ERROR: duplicate key violates unique constraint pg_ts_dict_pkey CONTEXT: COPY pg_ts_dict, line 1: simple 215247 \N 215248 Simple example of dictionary. pg_restore: [archiver (db)] error returned by PQendcopy pg_restore: *** aborted because of error Does anybody have some experience in this task? Thanks Ulrich Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(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: [GENERAL] [ADMIN] Restoring a Databases that features tserach2
Fischer Ulrich [EMAIL PROTECTED] writes: pg_dump (PostgreSQL) 7.3.2 (pg_dumpall -g GLOBALobjects.sql) pg_dump -s DATABASE DBschema.sql pg_dump -Fc DATABASE DBdata.tar and rebuilt on the new system with: psql (PostgreSQL) 7.4.1 1. createdb DATABASE 2. (psql DATABASE GLOBALobjects.sql) 3. psql DATABASE tsearch2.sql 3.a. psql DATABASE setup_ge_ispell.sql 4. psql DATABASE DBschema.sql 5. pg_restore -N -a -v -d DATABASE DBdata.tar This is certainly not going to work because the dump from the old database will already have all the tsearch2 objects, not to mention whatever setup_ge_ispell.sql may create. You should get rid of steps 3 and 3a. There is no difference when dumped with pg_dump (PostgreSQL) 7.4.1. I get the same error. When I do not update the dict-settings (setup_ge_ispell.sql) I can not even restore the other datas. If you're still having trouble, try dropping the -N option to pg_restore. I don't know why that option even exists ... there is no practical use to it AFAIK. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] [ADMIN] Restoring a Databases that features tserach2
Oleg, this is exactly what I tryed to. But after cleaning all the new pg_ts_* tables I wasn't able to restore my datas. My work arround is now to rename all keys in the pg_ts_* tables (Ex: pg_ts_dict: dict_name: simple to simple_old). With this modification the pg_restore worked! Now I only have to clean the pg_ts_* tables. regards, Ulrich Oleg Bartunov wrote: Fischer, probable scenario: after step 3. you have tables pg_ts_* and when you restore your database you tried to insert duplicated data. I recommend to see remove any entries related to pg_ts_* tables. Oleg On Thu, 22 Apr 2004, Fischer Ulrich wrote: Some additional infos to my problem Old System: Postgresql 7.3.2 New System: Postgresql 7.4.1 pg_dump (PostgreSQL) 7.3.2 (pg_dumpall -g GLOBALobjects.sql) pg_dump -s DATABASE DBschema.sql pg_dump -Fc DATABASE DBdata.tar and rebuilt on the new system with: psql (PostgreSQL) 7.4.1 1. createdb DATABASE 2. (psql DATABASE GLOBALobjects.sql) 3. psql DATABASE tsearch2.sql 3.a. psql DATABASE setup_ge_ispell.sql 4. psql DATABASE DBschema.sql 5. pg_restore -N -a -v -d DATABASE DBdata.tar There is no difference when dumped with pg_dump (PostgreSQL) 7.4.1. I get the same error. When I do not update the dict-settings (setup_ge_ispell.sql) I can not even restore the other datas. Ulrich Andreas Schmitz wrote: How exactly is the dump created and the restore started ? On Thursday 22 April 2004 10:55, Fischer Ulrich wrote: Hi I'm trying to restore tsearch2 featuring database like discribed in the 'tsearch-V2-intro' document. (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-int ro.html) In point '5) Restore the data for the database' I get the following Error: pg_restore: ERROR: duplicate key violates unique constraint pg_ts_dict_pkey CONTEXT: COPY pg_ts_dict, line 1: simple 215247 \N 215248 Simple example of dictionary. pg_restore: [archiver (db)] error returned by PQendcopy pg_restore: *** aborted because of error Does anybody have some experience in this task? Thanks Ulrich Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 -- Ulrich G. Fischer Dipl. Natw. ETH Center Aerodynamics Ruag Aerospace P.O. Box 301CH-6032 Emmen Tel. +41 41 268 23 53 Fax. +41 41 268 38 97 [EMAIL PROTECTED] www.ruag.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [ADMIN] Restoring a Databases that features tserach2
Fischer Ulrich [EMAIL PROTECTED] writes: Tom Lane wrote: This is certainly not going to work because the dump from the old database will already have all the tsearch2 objects, not to mention whatever setup_ge_ispell.sql may create. You should get rid of steps 3 and 3a. Get rid of Step 3 and 3a produces Errors in Step 4 like: ERROR: type tsvector does not exist Yeah, not surprising if you used ALTER TABLE to add a tsvector column to a table created before you'd loaded tsearch2 into the database. The dump will create the objects in the wrong order. This whole class of problems should be gone in 7.5, since CVS-tip pg_dump understands dependencies properly. As a short-term workaround I'd suggest using pg_dump -Fc db as the source data and manually adjusting the load order with pg_restore's -L option. The separate-schema-and-data approach is not very good since it's a lot slower to load large databases that way. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] [ADMIN] Restoring a Databases that features tserach2
Tom Lane wrote: Fischer Ulrich [EMAIL PROTECTED] writes: pg_dump (PostgreSQL) 7.3.2 (pg_dumpall -g GLOBALobjects.sql) pg_dump -s DATABASE DBschema.sql pg_dump -Fc DATABASE DBdata.tar and rebuilt on the new system with: psql (PostgreSQL) 7.4.1 1. createdb DATABASE 2. (psql DATABASE GLOBALobjects.sql) 3. psql DATABASE tsearch2.sql 3.a. psql DATABASE setup_ge_ispell.sql 4. psql DATABASE DBschema.sql 5. pg_restore -N -a -v -d DATABASE DBdata.tar This is certainly not going to work because the dump from the old database will already have all the tsearch2 objects, not to mention whatever setup_ge_ispell.sql may create. You should get rid of steps 3 and 3a. Get rid of Step 3 and 3a produces Errors in Step 4 like: : CREATE TABLE REVOKE GRANT CREATE TABLE REVOKE GRANT ERROR: type tsvector does not exist ERROR: relation mitarbeiter does not exist : So I think this is not a good idea ;-) I've got now a Sollution which I'll post after writing and testing my own HowTo! regards Ulrich There is no difference when dumped with pg_dump (PostgreSQL) 7.4.1. I get the same error. When I do not update the dict-settings (setup_ge_ispell.sql) I can not even restore the other datas. If you're still having trouble, try dropping the -N option to pg_restore. I don't know why that option even exists ... there is no practical use to it AFAIK. regards, tom lane -- Ulrich G. Fischer Dipl. Natw. ETH Center Aerodynamics Ruag Aerospace P.O. Box 301CH-6032 Emmen Tel. +41 41 268 23 53 Fax. +41 41 268 38 97 [EMAIL PROTECTED] www.ruag.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
