Re: [ADMIN] [GENERAL] PGSQL Database Recovery in Portland Oregon Area needed ASAP
On Tue, May 16, 2006 at 09:53:54AM -0700, Mark Holm wrote: I have a client that is running an older version of Lyris List Manager against PostGres 7.1.2, that has crashed their database beyond my ability to recover it. The error that I am getting when we try and restart the database is: Very old database, consider upgrading. I have gone through the stuff on-line concerning this error and attempted to reinitialize the database and restore the night before's backup, but apparently the backups were not quite setup correctly as I cannot get a valid restore either. I am out of my depth on this one and am willing to contract somebody to help get this database back on-line again ASAP, as the client is getting frantic. If you have proven experience doing this sort of recovery, please contact me at the number or email address below. I am not a member of the lists, so please contact me directly. Rates are negotiable, but I will have to clear them with client before we proceed. Make sure you have a physical backup. pg_resetxlog may get you far enough to start the server. Them immediatly dump, destroy the cluster and restore. pg_resetxlog solves some problems, but you can't guarentee your integrity anymore... Have a ncie day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [ADMIN] does wal archiving block the current client connection?
On Thu, 18 May 2006, Tom Lane wrote: Jeff Frost [EMAIL PROTECTED] writes: I seem to get alot of these: May 17 21:34:04 discord postgres[20573]: [5-1] WARNING: could not rename file pg_xlog/archive_status/00010001.ready to May 17 21:34:04 discord postgres[20573]: [5-2] pg_xlog/archive_status/00010001.done: No such file or directory That seems odd ... Further interesting items: May 17 22:41:18 discord postgres[23817]: [3-1] LOG: archive command /usr/local/pgsql-8.1.3/bin/archive_test.sh pg_xlog/0001000E 0001000E May 17 22:41:18 discord postgres[23817]: [3-2] failed: return code 256 And in the window where I started postgres via pg_ctl, I had this: cat: pg_xlog/0001000E: No such file or directory cat: pg_xlog/0001000E: No such file or directory Seems bad. Currently I'm using pgbench to generate WAL rollover, do you guys have any other handy testing tools for this sort of job or is this the best tool? pgbench seems like an OK load for this, although it doesn't start/end any new connections while running. I *think* that that's not critical --- my guess is that your observation of new connections hanging is just because each new connection has to run one startup transaction, and transactions in general are at risk of hanging --- but that could be wrong. Also, you could increase the rate of WAL generation by decreasing the checkpoint segments/timeout parameters, if you need to. regards, tom lane -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] does wal archiving block the current client connection?
On Wed, 17 May 2006, Jeff Frost wrote: And in the window where I started postgres via pg_ctl, I had this: cat: pg_xlog/0001000E: No such file or directory cat: pg_xlog/0001000E: No such file or directory Hrmmm...my pgbench died with an integer out of range error: Client 56 aborted in state 8: ERROR: integer out of range client 81 receiving client 81 sending UPDATE branches SET bbalance = bbalance + 4512 WHERE bid = 1; client 86 receiving Client 86 aborted in state 8: ERROR: integer out of range client 85 receiving Client 85 aborted in state 8: ERROR: integer out of range client 81 receiving Client 81 aborted in state 8: ERROR: integer out of range client 78 receiving client 78 sending UPDATE branches SET bbalance = bbalance + 2868 WHERE bid = 1; client 78 receiving Client 78 aborted in state 8: ERROR: integer out of range Client 56 aborted in state 8: ERROR: integer out of range client 81 receiving client 81 sending UPDATE branches SET bbalance = bbalance + 4512 WHERE bid = 1; client 86 receiving Client 86 aborted in state 8: ERROR: integer out of range client 85 receiving Client 85 aborted in state 8: ERROR: integer out of range client 81 receiving Client 81 aborted in state 8: ERROR: integer out of range client 78 receiving client 78 sending UPDATE branches SET bbalance = bbalance + 2868 WHERE bid = 1; client 78 receiving Client 78 aborted in state 8: ERROR: integer out of range I'm guessing those bbalance + x updates ran bbalance up too high. Anyway, I keep getting these on occassion: May 17 23:01:22 discord postgres[23817]: [8-1] LOG: archive command /usr/local/pgsql-8.1.3/bin/archive_test.sh pg_xlog/00010011 00010011 May 17 23:01:22 discord postgres[23817]: [8-2] failed: return code 256 May 17 23:01:22 discord postgres[23817]: [9-1] WARNING: transaction log file 00010011 could not be archived: too many failures May 17 23:04:06 discord postgres[20573]: [40-1] LOG: archived transaction log file 00010013 May 17 23:04:06 discord postgres[20573]: [41-1] WARNING: could not rename file pg_xlog/archive_status/00010013.ready to May 17 23:04:06 discord postgres[20573]: [41-2] pg_xlog/archive_status/00010013.done: No such file or directory May 17 23:04:27 discord postgres[20228]: [22-1] LOG: archived transaction log file 00010014 and in the other window: cat: pg_xlog/00010011: No such file or directory cat: pg_xlog/00010011: No such file or directory cat: pg_xlog/00010011: No such file or directory How on earth can this happen? Currently I'm using pgbench to generate WAL rollover, do you guys have any other handy testing tools for this sort of job or is this the best tool? pgbench seems like an OK load for this, although it doesn't start/end any new connections while running. I *think* that that's not critical --- my guess is that your observation of new connections hanging is just because each new connection has to run one startup transaction, and transactions in general are at risk of hanging --- but that could be wrong. Also, you could increase the rate of WAL generation by decreasing the checkpoint segments/timeout parameters, if you need to. regards, tom lane -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(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: [ADMIN] does wal archiving block the current client connection?
On Wed, 2006-05-17 at 22:45 -0700, Jeff Frost wrote: On Thu, 18 May 2006, Tom Lane wrote: Jeff Frost [EMAIL PROTECTED] writes: I seem to get alot of these: May 17 21:34:04 discord postgres[20573]: [5-1] WARNING: could not rename file pg_xlog/archive_status/00010001.ready to May 17 21:34:04 discord postgres[20573]: [5-2] pg_xlog/archive_status/00010001.done: No such file or directory That seems odd ... Further interesting items: May 17 22:41:18 discord postgres[23817]: [3-1] LOG: archive command /usr/local/pgsql-8.1.3/bin/archive_test.sh pg_xlog/0001000E 0001000E May 17 22:41:18 discord postgres[23817]: [3-2] failed: return code 256 And in the window where I started postgres via pg_ctl, I had this: cat: pg_xlog/0001000E: No such file or directory cat: pg_xlog/0001000E: No such file or directory Seems bad. Seems so. Can you post the full test, plus full execution log. [You don't need to cat you could just do ls instead FWIW] Are you doing *anything* with pg_xlog directory or below? I understand your saying No to that question and pg_xlog has not been moved, its just underneath data directory, which is on normal disk? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] install_driver(Pg) failed: Can't load Pg.so
louis gonzales wrote: Software error: install_driver(Pg) failed: Can't load '/usr/local/lib/perl5/site_perl/5.8.7/sun4-solaris/auto/DBD/Pg/Pg.so' for module DBD::Pg: ld.so.1: perl: fatal: libgcc_s.so.1: open failed: No such file or directory at /usr/local/lib/perl5/5.8.7/sun4-solaris/DynaLoader.pm line 230. at (eval 7) line 3 Compilation failed in require at (eval 7) line 3. Perhaps a required shared library or dll isn't installed where expected at /var/apache/cgi-bin/scratch/entry.cgi line 197 I verified that the CGI module works. The above Pg.so file does exist in the stated location. Any ideas? How about libgcc_s.so.1? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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: [ADMIN] does wal archiving block the current client connection?
Jeff Frost [EMAIL PROTECTED] writes: Hrmmm...my pgbench died with an integer out of range error: That's normal, if you run it long enough without re-creating the tables. It keeps adding small values to the balances, and eventually they overflow. (Possibly someone should fix it so that the deltas are uniformly distributed around zero, instead of being always positive.) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] does wal archiving block the current client connection?
On Thu, 18 May 2006, Simon Riggs wrote: Seems so. Can you post the full test, plus full execution log. [You don't need to cat you could just do ls instead FWIW] Are you doing *anything* with pg_xlog directory or below? I understand your saying No to that question and pg_xlog has not been moved, its just underneath data directory, which is on normal disk? I did a fresh compile of 8.1.3 with --prefix=/usr/local/pgsql-8.1.3. On this particular system, this is actually located on / which is /dev/md1 (a mirror). I'll try and start a brand new version of the test so I can capture the logging to syslog for you guys as well as the stdout on the pg_ctl console. More later. Unfortunately, I'm not really sure this is related to the problems we saw before. (i.e. the system doesn't get unusually slow or anything, nor do the connections seems to block) -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[ADMIN] query optimization - mysql vs postgresql
Hello, my team is in the process of migrating some data from a mysql (5.0) database to our core postgres (8.1.3) database. We are running into some performance issues with the postgres versions of the queries. MySQL takes about 150ms to run the query where postgres is taking 2500ms. The servers for the two database clusters are identical (dual amd 64bit dual core opteron 4GB ram scsi raid array Suse 9.x) The table schemas have not changed (to the best of my knowledge) and the indexes were created to mimic the mysql versions as well. I have attached one particular query along with the explain output. Does anyone see anything in the explain that might help in diagnosing the problem. thx Warren J. Little CTO Meridias Capital 1018 West Atherton Dr Salt Lake City, UT 84123 Ph 866.369.7763
Re: [ADMIN] query optimization - mysql vs postgresql
It looks like you forgot to attach the query sample. Have you collected statistics on your tables/indexes to help the planner select a better plan? -- Husam http://firstdba.googlepages.com From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Warren LittleSent: Thursday, May 18, 2006 9:06 AMTo: pgsql-admin@postgresql.orgSubject: [ADMIN] query optimization - mysql vs postgresql Hello,my team is in the process of migrating some data from a mysql (5.0) database to our core postgres (8.1.3) database.We are running into some performance issues with the postgres versions of the queries.MySQL takes about 150ms to run the query where postgres is taking 2500ms.The servers for the two database clusters are identical (dual amd 64bit dual core opteron 4GB ram scsi raid array Suse 9.x)The table schemas have not changed (to the best of my knowledge) and the indexes were created to mimic the mysql versions as well.I have attached one particular query along with the explain output.Does anyone see anything in the explain that might help in diagnosing the problem.thx Warren J. LittleCTOMeridias Capital1018 West Atherton DrSalt Lake City, UT 84123Ph 866.369.7763 **This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged. If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited. If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter. Thank you. FADLD Tag**
[ADMIN] [JDBC] InsertRow problem with Serial
Hello, I have a problem with the driver JDBC3, MyTable(col1(SERIAL), col2(VARCHAR), Col3(VARCHAR)) When i do rs.moveToInsertRow(); rs.updateString(col2,col2); rs.updateString(col3,col3); rs.insertRow(); con.commit(); rs.next(); rs.refreshRow(); The ResulSet is not refreshed for the first column, the value for col1 is 0. Although insertion in the base worked and the serial have the good value !!! And this code works perfectly for MySql :( If somebody knows when that can come from ? Thank you, Mathias Laurent _ Retrouvez tout en un clin d'oeil avec la barre d'outil MSN Search ! http://desktop.msn.fr/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] query optimization - mysql vs postgresql
Sorry, here are the attachments. Not sure about the statistics question, I have done a vacuum analyze on every table in the database. On Thu, 2006-05-18 at 09:12 -0700, Tomeh, Husam wrote: It looks like you forgot to attach the query sample. Have you collected statistics on your tables/indexes to help the planner select a better plan? -- Husam http://firstdba.googlepages.com From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Warren Little Sent: Thursday, May 18, 2006 9:06 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] query optimization - mysql vs postgresql Hello, my team is in the process of migrating some data from a mysql (5.0) database to our core postgres (8.1.3) database. We are running into some performance issues with the postgres versions of the queries. MySQL takes about 150ms to run the query where postgres is taking 2500ms. The servers for the two database clusters are identical (dual amd 64bit dual core opteron 4GB ram scsi raid array Suse 9.x) The table schemas have not changed (to the best of my knowledge) and the indexes were created to mimic the mysql versions as well. I have attached one particular query along with the explain output. Does anyone see anything in the explain that might help in diagnosing the problem. thx Warren J. Little CTO Meridias Capital 1018 West Atherton Dr Salt Lake City, UT 84123 Ph 866.369.7763 ** This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged. If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited. If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter. Thank you. FADLD Tag ** Warren J. Little CTO Meridias Capital 1018 West Atherton Dr Salt Lake City, UT 84123 Ph 866.369.7763 ;; This buffer is for notes you don't want to save, and for Lisp evaluation. ;; If you want to create a file, visit that file with C-x C-f, ;; then enter the text in that file's own buffer. SELECT *, MAX(cashoutMaxAmt) cashoutMaxAmt, product.prodKey AS prodKey, UNIX_TIMESTAMP(product.lastModifiedTs) AS lastModifiedTs, product.comment AS prodComment, productGeneral.comment AS condComment FROM product, productCondition, productLockTerm, productGeneral LEFT JOIN productPropertyUse ON (productGeneral.prodGeneralKey=productPropertyUse.prodGeneralKey) LEFT JOIN productPrepay ON (productGeneral.prodGeneralKey=productPrepay.prodGeneralKey) LEFT JOIN productLoanPurpose ON (productGeneral.prodGeneralKey=productLoanPurpose.prodGeneralKey) LEFT JOIN productDocLevel ON (productGeneral.prodGeneralKey=productDocLevel.prodGeneralKey) LEFT JOIN productPropertyType ON (productGeneral.prodGeneralKey=productPropertyType.prodGeneralKey) LEFT JOIN productStateON (productGeneral.prodGeneralKey=productState.prodGeneralKey) LEFT JOIN productMortgageLate AS mortgageLate0 ON (productGeneral.mortLateKey =mortgageLate0.prodMortLateKey) LEFT JOIN productMortgageLate AS mortgageLate12 ON (productGeneral.mortLate12Key=mortgageLate12.prodMortLateKey) LEFT JOIN productMortgageLate AS mortgageLate24 ON (productGeneral.mortLate24Key=mortgageLate24.prodMortLateKey) WHERE product.prodKey = productLockTerm.prodKey AND product.prodKey = productCondition.prodKey AND productGeneral.prodGeneralKey = productCondition.prodGeneralKey AND lockTerm = 'B30' AND productGeneral.disable = 'E' AND product.disable = 'E' AND productLockTerm .disable = 'E' AND lienPri = '1' AND exception='YES' AND ((loanAmtFrom=0 AND loanAmtTo=0) || (loanAmtFrom IS NULL AND loanAmtTo IS NULL) || (loanAmtTo = '40' AND loanAmtFrom = '40')) AND ((ltvFromPct=0AND ltvToPct=0) || (ltvFromPct IS NULL AND ltvToPct IS NULL) || (ltvToPct = '80.000' AND ltvFromPct = '80.000')) AND ((cltvFromPct=0 AND cltvToPct=0) || (cltvFromPct IS NULL AND cltvToPct IS NULL) || (cltvToPct = '100.000'AND cltvFromPct = '100.000')) AND ((crdscrFrom=0AND crdscrTo=0) || (crdscrFrom IS NULL AND crdscrTo IS NULL) || (crdscrTo = '720' AND crdscrFrom = '720')) AND ((totalLienMinAmt=0 AND totalLienMaxAmt=0) || (totalLienMinAmt IS NULL AND totalLienMaxAmt IS NULL) || (totalLienMaxAmt = '40' AND totalLienMinAmt = '40')) AND ((secondaryFinance='NO' AND
[ADMIN] lock a database from new connections, without modifying pg_hba.conf
Is that possible? I've checked the docs for 8.1, and am not finding anything, nor anything in contrib ... the best I've been able to think of so far is to modify pg_hba.conf to not allow new connections for the duration of the operations I need to perform (drop and create a database) ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[ADMIN] find out all users under specific user
Hi List, I want to find out all the users under a specific user. postgres is a superuser of postgresql, under in postres user i made one user ABC and under this ABC user i made XYZ,MNO,IJK users. Now i want to find out all the users, which r exist under in ABC user so what is the query for it? plz. help me. Thanks Ashok mail2web - Check your email from the web at http://mail2web.com/ . ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] lock a database from new connections, without modifying pg_hba.conf
Marc G. Fournier [EMAIL PROTECTED] writes: Is that possible? I've checked the docs for 8.1, and am not finding anything, nor anything in contrib ... the best I've been able to think of so far is to modify pg_hba.conf to not allow new connections for the duration of the operations I need to perform (drop and create a database) ... Not sure I understand what you need. DROP DATABASE already locks out new connections. 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
