[GENERAL] Unable to connect to a dabase
I had to kill a vacuum in the middle with -9. I shut down and restarted the postgres server several times after that but I am unable to connect to the db that I was initially running vacuum on I'm doing psql dbname and it hangs for a while. I'm still waiting. Any ideas? Thanks ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Unable to connect to a dabase
I'm sorry that was not intentional. From: Jim C. Nasby [EMAIL PROTECTED] To: Sally Sally [EMAIL PROTECTED] CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] Unable to connect to a dabase Date: Fri, 13 Jan 2006 16:55:16 -0600 Please don't hijack threads fo new questions. On Fri, Jan 13, 2006 at 10:45:51PM +, Sally Sally wrote: I had to kill a vacuum in the middle with -9. I shut down and restarted the postgres server several times after that but I am unable to connect to the db that I was initially running vacuum on I'm doing psql dbname and it hangs for a while. I'm still waiting. Any ideas? What's the logfile say about it? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Unable to connect to a dabase
Yes a backend gets spewed. Actually it did connect me finally (I am not sure exactly how long it took). I checked back after about four hours and I was connected. I have also noticed in the past that after running vacuum the client (psql) takes longer than usual to connect. Thanks all sally From: Joshua D. Drake [EMAIL PROTECTED] To: Jim Nasby [EMAIL PROTECTED] CC: Sally Sally [EMAIL PROTECTED], pgsql-general@postgresql.org Subject: Re: [GENERAL] Unable to connect to a dabase Date: Fri, 13 Jan 2006 16:02:11 -0800 LOG: database system was shut down at 2006-01-13 18:04:05 EST LOG: checkpoint record is at 505/C80F1010 LOG: redo record is at 505/C80F1010; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 1341794294; next OID: 1358710904 LOG: database system is ready Well, that looks like a clean startup. I'm not sure if anyone else on the list has any ideas. Maybe getting a stack trace of psql trying to connect would shed some light... Does a backend get spawned when you try running psql? (Look at ps aux|grep postgres before and after running psql). If you telnet to localhost 5432 is it listening or does it hang? If it hangs I would guess a firewall but I would think you would pick up on the unix socket. J From: Jim C. Nasby [EMAIL PROTECTED] To: Sally Sally [EMAIL PROTECTED] CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] Unable to connect to a dabase Date: Fri, 13 Jan 2006 16:55:16 -0600 Please don't hijack threads fo new questions. On Fri, Jan 13, 2006 at 10:45:51PM +, Sally Sally wrote: I had to kill a vacuum in the middle with -9. I shut down and restarted the postgres server several times after that but I am unable to connect to the db that I was initially running vacuum on I'm doing psql dbname and it hangs for a while. I'm still waiting. Any ideas? What's the logfile say about it? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] keeping track of when a row was last modified
Does postgres automatically keep track of when a row was last modified? Thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] autocommit and transactions
Using perl DBI I recently read that if autocommit is turned on, it does a commit after every transaction. I wanted to confirm if this also applies to select statements. When I'm loading data, I need to do a select before inserting a new row and I don't want to commit after the select because it's unnecessary. So I am thinking of turning off the autocommit. Any thoughts? Thanks Sally ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] primary key and the default index operator class
I am trying to figure out how I can change the default operator class of the index created for my primary key field. Is it even possible since I am not able to find the syntax? Or do I need to create the primary key and then an additional index with the operator class I want. (It would be a waste of space) Thanks Sally ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] primary key and existing unique fields
Dawid, I am interested in the first point you made that: having varchar(12) in every referencing table, takes more storage space. The thing is though, if I have a serial primary key then it would be an additional column. Or you are saying the space taken by a VARCHAR(12) field is more than two INT fields? ( or is it the fact that when it is referenced it will appear several times?) I guess the reason I am resisting the idea of an additional primary key field is to avoid the additional lookup in some queries. Perhaps it's a minor almost irrelevant performance factor. Thanks Sally _ Is your PC infected? Get a FREE online computer virus scan from McAfee® Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(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] primary key and existing unique fields
I think the same too but sometimes it seems in the real world performance is given more value than a properly designed db. Or the long term flexiblity is not taken into account given the short term requirements. regards Sally From: Bruno Wolff III [EMAIL PROTECTED] To: Sally Sally [EMAIL PROTECTED] CC: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: [GENERAL] primary key and existing unique fields Date: Thu, 28 Oct 2004 12:44:00 -0500 On Thu, Oct 28, 2004 at 14:31:32 +, Sally Sally [EMAIL PROTECTED] wrote: Dawid, I am interested in the first point you made that: having varchar(12) in every referencing table, takes more storage space. The thing is though, if I have a serial primary key then it would be an additional column. Or you are saying the space taken by a VARCHAR(12) field is more than two INT fields? ( or is it the fact that when it is referenced it will appear several times?) I guess the reason I am resisting the idea of an additional primary key field is to avoid the additional lookup in some queries. Perhaps it's a minor almost irrelevant performance factor. I think it is better to worry about what is going to make it easiest to have clean data and to support future changes than worry about performance. Over the long run hardware is cheaper than people. ---(end of broadcast)--- TIP 8: explain analyze is your friend _ Check out Election 2004 for up-to-date election news, plus voter tools and more! http://special.msn.com/msn/election2004.armx ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] primary key and existing unique fields
Hi all, I am wandering about the pros and cons of creating a separate serial field for a primary key when I already have a single unique field. This existing unique field will have to be a character of fixed length (VARCHAR(12)) because although it's a numeric value there will be leading zeroes. There are a couple more tables with similar unique fields and one of them would need to reference the others. Does anybody see any good reason for adding a separate autoincrement primary key field for each table? or either way is not a big deal. Sally _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] primary key and existing unique fields
Can you please elaborate on the point you just made as to why the primary key should not relate to the data (even for a case when there is an existing unique field that can be used to identify the record) From: Joshua D. Drake [EMAIL PROTECTED] To: Sally Sally [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: [GENERAL] primary key and existing unique fields Date: Tue, 26 Oct 2004 09:48:50 -0700 Sally Sally wrote: Hi all, I am wandering about the pros and cons of creating a separate serial field for a primary key when I already have a single unique field. This existing unique field will have to be a character of fixed length (VARCHAR(12)) because although it's a numeric value there will be leading zeroes. There are a couple more tables with similar unique fields and one of them would need to reference the others. Does anybody see any good reason for adding a separate autoincrement primary key field for each table? or either way is not a big deal. Your primary key should not be directly related to the data being stored. Outside of the fact that it is the primary reference or the row. Sincerely, Joshua D. Drake Sally _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL jd.vcf ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org _ Check out Election 2004 for up-to-date election news, plus voter tools and more! http://special.msn.com/msn/election2004.armx ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] adding a db-specific user
I was wandering how I can add a user to a specific db. There are several databases but I want to add the user to only one of them. It seems when I add a new user, it is entered in the pg_user table and I can log in to any one of the other dbs (ofcourse I won't be able to do anything until I grant permission still I don't even want to be able to log in to the other dbs and see the db schema etc). Does anybody know how to avoid this? Thanx Sally _ Is your PC infected? Get a FREE online computer virus scan from McAfee® Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(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
[GENERAL] pg_restore quick question
I want to dump a database containing about five tables, each containing about 25 million records. I wanted to run pg_restore per table all at the same time. Would this have any undesirable consequences. I tried it on a smaller database and it seems to work fine. Let me know your thoughts ASAP Thanks Sally _ Is your PC infected? Get a FREE online computer virus scan from McAfee® Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] DBI remote connection problem
When I try to connect to a remote server using DBI I get an error expected authentication request from server, but received S Has anyone encountered this? I am not quite sure what it is refering to? Sally _ Is your PC infected? Get a FREE online computer virus scan from McAfee® Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] backup and restore questions
Thank you all for replying. I appreciate the tips. Apologies to those who were offended by the html formating. Scott, quick question. The reason I assumed insert would be safer than copy is because the docs say that in the case of copy it fails on a single corrupted row whereas insert won't? Sally _ Stay informed on Election 2004 and the race to Super Tuesday. http://special.msn.com/msn/election2004.armx ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] backup and restore questions
I had a few questions concerning the backup/restore process for pg. 1) Is it possible to dumpdata onto an existing database that contains data (assumning the schema of both are the same). Has anyone done this? I am thinking of this in order to expediate the data load process 2) I read that when dumping and restoring data the insert option is safer but slower than copy? Does anyone know from experience how much slower (especially for a database containing millions of records). 3) can pg_restore accept a file that is not archived like a zipped file or plain text file (file.gz or file) 4) Is the general practise to have onewholedump of a database or several separate dumps (by table etc...)? Thanks a lot Sally Take off on a romantic weekend or a family adventure to these great U.S. locations.
