Обсуждение: Can't connect on Windows
I installed cygwin-based postgreSQL. I have not been able yet to start it as a service (net start postmaster), apparently because of permissions problems. I’m waiting on some suggestions, hopefully, about that.
I can connect to the DB w/o starting a service from the cygwin command line, however, in that case I only have 1 user (myself), no postgres user is there yet. This way, I cannot connect to the DB from pgAdmin (localhost), it says “Could not connect to the server”, “Could not connect to remote socket”.
Do I get it right that to connect on localhost with pgAdmin, I have to have postmaster running as a service? I think adding postgres user and running w/o net start postmaster will not make any difference. Is that right?
Thank you,
r.
Hi, I have an Access DB, that's been running OK in Access, and as part of a web app based on JDBC. When I convert it to postreSQL, all table creation, data insertion, and primary key creation statements complete just fine. However, the first foreign key attempt fails: Creating Foreign Key: CompanyContactInfo SQL (DBName): ALTER TABLE "ContactInfo" ADD CONSTRAINT "CompanyContactInfo_fk" FOREIGN KEY(id) REFERENCES "Company" (id) ON DELETE CASCADE ON UPDATE CASCADE SQL (DBName): ALTER TABLE "ContactInfo" ADD CONSTRAINT "CompanyContactInfo_fk" FOREIGN KEY(id) REFERENCES () ON DELETE NO ACTION ON UPDATE NO ACTION SQL (DBName): ROLLBACK I'm pretty sure that Access doesn't have a foreign key to null table and null column and would appreciate any suggestions as to why this 2nd statement might be coming up. Might there be a bug either somewhere? P.S. I ran this with 3 different modes, including FULL DEBUG. The statements above are the most descriptive (Errors and SQL Queries). P.P.S. This is run on XP Pro, and from MS site I'm seeing that MSAD 2.7 for XP, which I have, already has the Jet Engine for ODBC. I could install their service pack for that, but it appears to be unnecessary (and possibly undesirable). P.P.P.S. There is a couple of dozens of foreign keys, and using the logs I could just add the relevant statements and make the migration half-manual. Needless to say, it would be a lot better if that wasn't necessary. P.P.P.P.S. I once looked up SQL statements corresponding to tables or queries in an older version of Access, but I can't find a way to do the same in the current one (not for table definitions anyway). That might be of a little help. Although the best I can think if is that \r\n is processed incorrectly by ODBC bridge or pgAdmin, and that results in null table with null column. Thanks in advance, r.
Actually exactly 1 out of every 10 foreign keys couldn't be re-created. So that means almost everything is automatic. And that is already good enough for production. Nevertheless, I'd appreciate any feedback on the problem below. Thanks, r. -----Original Message----- From: pgadmin-support-owner@postgresql.org [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Reshat Sabiq Sent: Sunday, December 22, 2002 9:00 AM To: pgadmin-support@postgresql.org Subject: [pgadmin-support] Access-postgreSQL migration (extra invalid foreign key added) Hi, I have an Access DB, that's been running OK in Access, and as part of a web app based on JDBC. When I convert it to postreSQL, all table creation, data insertion, and primary key creation statements complete just fine. However, the first foreign key attempt fails: Creating Foreign Key: CompanyContactInfo SQL (DBName): ALTER TABLE "ContactInfo" ADD CONSTRAINT "CompanyContactInfo_fk" FOREIGN KEY(id) REFERENCES "Company" (id) ON DELETE CASCADE ON UPDATE CASCADE SQL (DBName): ALTER TABLE "ContactInfo" ADD CONSTRAINT "CompanyContactInfo_fk" FOREIGN KEY(id) REFERENCES () ON DELETE NO ACTION ON UPDATE NO ACTION SQL (DBName): ROLLBACK I'm pretty sure that Access doesn't have a foreign key to null table and null column and would appreciate any suggestions as to why this 2nd statement might be coming up. Might there be a bug either somewhere? P.S. I ran this with 3 different modes, including FULL DEBUG. The statements above are the most descriptive (Errors and SQL Queries). P.P.S. This is run on XP Pro, and from MS site I'm seeing that MSAD 2.7 for XP, which I have, already has the Jet Engine for ODBC. I could install their service pack for that, but it appears to be unnecessary (and possibly undesirable). P.P.P.S. There is a couple of dozens of foreign keys, and using the logs I could just add the relevant statements and make the migration half-manual. Needless to say, it would be a lot better if that wasn't necessary. P.P.P.P.S. I once looked up SQL statements corresponding to tables or queries in an older version of Access, but I can't find a way to do the same in the current one (not for table definitions anyway). That might be of a little help. Although the best I can think if is that \r\n is processed incorrectly by ODBC bridge or pgAdmin, and that results in null table with null column. Thanks in advance, r. ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
What is the significance of public schema (for migration and otherwise). Is it optional to select a schema? I believe it is for security constraints. I'm just not used to working with SQL statements like: public."TableName" What schema should I have for a DB that will be on the web mostly for queries? In the short term, I might be connecting to it remotely, but in the long term I should have add/update/delete interface on the web (running as me via JDBC). Should I keep it public, none at all, or make a custom one? Thank you, r.
On Sat, Dec 21, 2002 at 08:51:08PM -0500, Reshat Sabiq wrote: > I installed cygwin-based postgreSQL. I have not been able yet to start > it as a service (net start postmaster), apparently because of > permissions problems. I'm waiting on some suggestions, hopefully, about > that. Have you followed all the steps in the Cygwin README file for Postgres, exactly? That file is located at /usr/doc/Cygwin/postgresql-*.README. Note, in particular, that those steps have you run initdb a second time to create the empty database owned by the 'postgres' user, a necessary step for running postgresql as a service associated with that postgres user. -- Fred Yankowski fred@ontosys.com tel: +1.630.879.1312 OntoSys, Inc PGP keyID: 7B449345 fax: +1.630.879.1370 www.ontosys.com 38W242 Deerpath Rd, Batavia, IL 60510-9461, USA
-----Original Message-----
From: Reshat Sabiq [mailto:sabiq@purdue.edu]
Sent: 22 December 2002 01:51
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] Can't connect on WindowsI installed cygwin-based postgreSQL. I have not been able yet to start it as a service (net start postmaster), apparently because of permissions problems. I’m waiting on some suggestions, hopefully, about that.
I can connect to the DB w/o starting a service from the cygwin command line, however, in that case I only have 1 user (myself), no postgres user is there yet. This way, I cannot connect to the DB from pgAdmin (localhost), it says “Could not connect to the server”, “Could not connect to remote socket”.
The 'postgres' user is whoever ran initdb.
Do I get it right that to connect on localhost with pgAdmin, I have to have postmaster running as a service? I think adding postgres user and running w/o net start postmaster will not make any difference. Is that right?
Yes, that's right - you don't need to run as a service. In fact, on my dev laptop I prefer it that way as I can start/stop PG in a bash windows when I like so I'm not wasting cycles when I'm doing other things.
Regards, Dave.
Note, in particular, that those steps have you run initdb a second time to create the empty database owned by the 'postgres' user, a necessary step for running postgresql as a service associated with that postgres user. Yes, I have shortcut to the readme, cause I've been using it so much recently. However, I think there are 2 different procedures, 1 with 4 steps (simplified), and another 1 with 7 steps to install as a service. I believe the initdb statement in the 2nd procedure is unrelated to the one in the first. In short, when I was trying to initdb after switching to postgres OS user (instead su), I ended up with a semget ... Function not implemented error. Eventually, I got it to complete installation and can now connect to it from pgAdmin, but not as a service, just running as myself. I am now inclined to believe that due to absence of su, the best way to run it as a service is to make the service owned by the user installing postgreSQL, rather than a separate postgres user (bottom of page): http://www.pgsql.info/pg_win.html (I don't speak German, but the screenshots are understandable). That was on my mind, but I got it to work as myself and was too excited to back off w/o a confirmation of this idea. Sincerely, r.
-----Original Message----- From: pgadmin-support-owner@postgresql.org [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Dave Page Sent: Sunday, December 22, 2002 2:25 PM To: Reshat Sabiq; pgadmin-support@postgresql.org Subject: Re: [pgadmin-support] Can't connect on Windows ------ The 'postgres' user is whoever ran initdb. ------ From the readme I thought I should make a new user account called postgres (the way its usually done in Linux/UNIX). If that is correct, then I still dont know how to initdb under postgres account, because of semget Function not implemented error I mentioned earlier (after switching to postgres user instead su). As I mentioned in previous message, however, this page recommends making the user installing postgres to be the owner of the service (bottom): http://www.pgsql.info/pg_win.html And if that is the preferred way of doing it on Windows, then I would mention that in the readme. Or maybe everybody gets it and I'm not... :) I think that will be my next try to get it running as a service. Thank you, r.