Thread
-
Login problem
Danjel Jungersen <danjel@jungersen.dk> — 2005-12-17T16:10:54Z
Hi... First: I'm a complete newbie on sql-servers ;-) I have installed pgsql on a OpenBSD, and also phpPgAdmin. I had some trouble doing that, so I'm not 100% what I did, regarding to database names, users, passwords... My problem is that i cannot get throught the login, I keep getting a login failed. This happen from both phpPgAdmin and psql... Is there anywhere I can see what databases that are created, and which users are created? Is there anyway to see the users passwords, or to "reset" them ? I have spent the whole day trying to get through, googling and man'ing, but to no use :( Therefore I hope to hear from you ;-) Best regards Danjel Jungersen
-
Re: Login problem
John DeSoi <desoi@pgedit.com> — 2005-12-18T03:51:55Z
On Dec 17, 2005, at 11:10 AM, Danjel Jungersen wrote: > Is there anywhere I can see what databases that are created, and > which users are created? > Is there anyway to see the users passwords, or to "reset" them ? Modify your pg_hba.conf file to trust local connections. Then you can login with psql without a password. From there you can use the ALTER USER command to reset passwords. Don't forget to reverse your pg_hba.conf changes when you are done. http://www.postgresql.org/docs/8.0/interactive/client- authentication.html#AUTH-PG-HBA-CONF John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
-
Re: Login problem
Danjel Jungersen <danjel@jungersen.dk> — 2005-12-18T10:44:00Z
On 17 Dec 2005 at 22:51, John DeSoi wrote: > > On Dec 17, 2005, at 11:10 AM, Danjel Jungersen wrote: > > > Is there anywhere I can see what databases that are created, and > > which users are created? Below are instructions on how to login without a password, and a point in the direction on how to change it, but I keep getting: psql: FATAL: User "me" does not exist. After some tries I got pass the database does not exist ;-) Is there a user that is allways there, or can I see a list somewhere ? The "postmaster" is mentioned, so I have tried that "root" and "Postmaster", no luck... TIA Danjel > > Is there anyway to see the users passwords, or to "reset" them ? > > > Modify your pg_hba.conf file to trust local connections. Then you can > login with psql without a password. From there you can use the ALTER > USER command to reset passwords. Don't forget to reverse your > pg_hba.conf changes when you are done. > > http://www.postgresql.org/docs/8.0/interactive/client- > authentication.html#AUTH-PG-HBA-CONF > > > John DeSoi, Ph.D. > http://pgedit.com/ > Power Tools for PostgreSQL > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
-
COPY command, linking foreign keys
Srinivas Iyyer <srini_iyyer_bio@yahoo.com> — 2005-12-18T13:59:42Z
Hello group: I am working on a huge datasets (every dataset will have over 50K lines and I have 100 such datasets). Previously I wrote SQL statements (inserts and insert within Insert statements). However, the process went on and on for days due to many lines and large number of datasets. I am about to try COPY command. Here is snippet of my data. Table Gene: gene_no | gene_name -------------------- 1 | abc 2 | def 3 | kir . | ... . | ... N | xxx -------------------- Here is how my data looks in a tab delim format: gene_name expr_value exp_value2 def 100.9 300.3 kir 200.4 334.4 sir 39.9 4.9 .. .... ..... (30K lines like these) Now I want to use copy command and upload this whole tab delim file into the following table: Table: exp exp_no | gene_no(FK) | exp_one | exp_two --------------------------------------- There are two questions : 1. In table 'exp' I have gene_no and not gene_name. In the tab delim file I have gene_name. So, what is the ideal way to link these up - a fast one after 'COPY'ing this tab delim file in to exp_table. should I include gene_name also into the 'exp' table or ask postgres to link up with 'Gene' table through 'gene_no' foreign key. 2. Can this be done by simple SQL statement or should I have to write a pl/pgql script. would any one please help me in teaching to get around this problem. Thank you. Srini __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
-
Re: Login problem
John DeSoi <desoi@pgedit.com> — 2005-12-18T14:00:00Z
On Dec 18, 2005, at 5:44 AM, Danjel Jungersen wrote: > Below are instructions on how to login without a password, and a > point in the direction on how to change it, but I keep getting: > psql: FATAL: User "me" does not exist. > > After some tries I got pass the database does not exist ;-) > > Is there a user that is allways there, or can I see a list somewhere > ? > The "postmaster" is mentioned, so I have tried that "root" and > "Postmaster", no luck... > The default superuser name is "postgres". There is generally a default database called template1. In PostgreSQL 8.1 and later, I believe the default database name has changed to "postgres" as well. So try psql -U postgres template1 or psql -U postgres postgres John John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
-
Re: COPY command, linking foreign keys
Sean Davis <sdavis2@mail.nih.gov> — 2005-12-19T12:03:02Z
On 12/18/05 8:59 AM, "Srinivas Iyyer" <srini_iyyer_bio@yahoo.com> wrote: > Hello group: > > I am working on a huge datasets (every dataset will > have over 50K lines and I have 100 such datasets). > Previously I wrote SQL statements (inserts and insert > within Insert statements). > > However, the process went on and on for days due to > many lines and large number of datasets. > > I am about to try COPY command. Here is snippet of my > data. > > > Table Gene: > > gene_no | gene_name > -------------------- > 1 | abc > 2 | def > 3 | kir > . | ... > . | ... > N | xxx > -------------------- > > Here is how my data looks in a tab delim format: > > > gene_name expr_value exp_value2 > def 100.9 300.3 > kir 200.4 334.4 > sir 39.9 4.9 > .. .... ..... > (30K lines like these) > > Now I want to use copy command and upload this whole > tab delim file into the following table: > > Table: exp > > exp_no | gene_no(FK) | exp_one | exp_two > --------------------------------------- > > > There are two questions : > > 1. In table 'exp' I have gene_no and not gene_name. In > the tab delim file I have gene_name. So, what is the > ideal way to link these up - a fast one after > 'COPY'ing this tab delim file in to exp_table. should > I include gene_name also into the 'exp' table or ask > postgres to link up with 'Gene' table through > 'gene_no' foreign key. > 2. Can this be done by simple SQL statement or should > I have to write a pl/pgql script. > > would any one please help me in teaching to get around > this problem. Srini, One way to do this that works well for me is to "copy" the data into a temporary table and then use regular SQL to do the inserts into separate tables. This technique is quite fast. Sean
-
Re: COPY command, linking foreign keys
Srinivas Iyyer <srini_iyyer_bio@yahoo.com> — 2005-12-19T14:27:25Z
Table: gene_tab gene_id | gene_name ---------+----------- 1 | AARS 2 | AGC1 3 | APOA5 4 | APOB 5 | ATP13A2 6 | C9orf106 7 | CCNI 8 | CENTG3 9 | CITED4 10 | GPR24 Table: gene_exp gene_id | gene_exp ---------+---------- (0 rows) Table : gene_exp_temp temp_name | temp_exp -----------+---------- AARS | 100 AGC1 | 200 APOA5 | 201 APOB | 202 question : Inserting into gene_exp from gene_exp_temp: insert into gene_exp (gene_id, gene_exp) values ( (select gene_id from gene_tab, gene_exp_temp where gene_name = temp_name), (selct temp_exp from gene_exp_temp, gene_tab where temp_exp = gene_id)); Something is wrong somewhere. I know I am not still matured enough in terms of linking data. Could you help me where the problem is? Thanks Sean. -Srini > > There are two questions : > > > > 1. In table 'exp' I have gene_no and not > gene_name. In > > the tab delim file I have gene_name. So, what is > the > > ideal way to link these up - a fast one after > > 'COPY'ing this tab delim file in to exp_table. > should > > I include gene_name also into the 'exp' table or > ask > > postgres to link up with 'Gene' table through > > 'gene_no' foreign key. > > 2. Can this be done by simple SQL statement or > should > > I have to write a pl/pgql script. > > > > would any one please help me in teaching to get > around > > this problem. > > Srini, > > One way to do this that works well for me is to > "copy" the data into a > temporary table and then use regular SQL to do the > inserts into separate > tables. This technique is quite fast. > > Sean > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map > settings > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -
Re: COPY command, linking foreign keys
Sean Davis <sdavis2@mail.nih.gov> — 2005-12-19T16:40:40Z
On 12/19/05 9:27 AM, "Srinivas Iyyer" <srini_iyyer_bio@yahoo.com> wrote: > Table: gene_tab > > gene_id | gene_name > ---------+----------- > 1 | AARS > 2 | AGC1 > 3 | APOA5 > 4 | APOB > 5 | ATP13A2 > 6 | C9orf106 > 7 | CCNI > 8 | CENTG3 > 9 | CITED4 > 10 | GPR24 > > Table: gene_exp > > gene_id | gene_exp > ---------+---------- > (0 rows) > > > > Table : gene_exp_temp > > temp_name | temp_exp > -----------+---------- > AARS | 100 > AGC1 | 200 > APOA5 | 201 > APOB | 202 > > > question : Inserting into gene_exp from gene_exp_temp: > > Srini, I like the construct: INSERT INTO table1 (col1,col2,...) SELECT .... This lets me see what I am inserting before I do so.... Something like: INSERT INTO gene_exp (gene_id,gene_exp) SELECT gt.gene_id, ge.temp_exp FROM gene_temp gt join gene_exp_temp ge on gt.temp_name=ge.gene_name; I haven't tested this, but this might make the thought process a little easier. One thing it looks like you need (you have probably already thought of this) is an "experiment ID" somewhere in your gene_exp table.... Sean -
Re: Login problem
Danjel Jungersen <danjel@jungersen.dk> — 2005-12-20T07:33:31Z
On 18 Dec 2005 at 9:00, John DeSoi wrote: > > On Dec 18, 2005, at 5:44 AM, Danjel Jungersen wrote: > > > Below are instructions on how to login without a password, and a > > point in the direction on how to change it, but I keep getting: > > psql: FATAL: User "me" does not exist. > > > > After some tries I got pass the database does not exist ;-) > > > > Is there a user that is allways there, or can I see a list somewhere > > ? > > The "postmaster" is mentioned, so I have tried that "root" and > > "Postmaster", no luck... > > > > > The default superuser name is "postgres". There is generally a > default database called template1. In PostgreSQL 8.1 and later, I > believe the default database name has changed to "postgres" as well. > So try > > psql -U postgres template1 > > or > > psql -U postgres postgres With some testing I have found that I have the databasename correct, but still: "user does not exist" :-( Hope I can get throught this ;-) btw, I'm running 7.4.3... best regards Danjel > > John > > > > John DeSoi, Ph.D. > http://pgedit.com/ > Power Tools for PostgreSQL >
-
Re: Login problem
John DeSoi <desoi@pgedit.com> — 2005-12-20T13:37:00Z
On Dec 20, 2005, at 2:33 AM, Danjel Jungersen wrote: > With some testing I have found that I have the databasename correct, > but still: > "user does not exist" :-( > > Hope I can get throught this ;-) > > btw, I'm running 7.4.3... Do you know who created the database cluster with initdb? Perhaps they used their own user name rather than postgres. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
-
Re: Login problem
Danjel Jungersen <danjel@jungersen.dk> — 2005-12-20T16:54:51Z
On 20 Dec 2005 at 8:37, John DeSoi wrote: > > On Dec 20, 2005, at 2:33 AM, Danjel Jungersen wrote: > > > With some testing I have found that I have the databasename correct, > > but still: > > "user does not exist" :-( > > > > Hope I can get throught this ;-) > > > > btw, I'm running 7.4.3... > > > Do you know who created the database cluster with initdb? Perhaps > they used their own user name rather than postgres. I did :-) as root... Danjel > > > John DeSoi, Ph.D. > http://pgedit.com/ > Power Tools for PostgreSQL >
-
Re: Login problem
Tom Lane <tgl@sss.pgh.pa.us> — 2005-12-20T17:05:01Z
"Danjel Jungersen" <danjel@jungersen.dk> writes: > On 20 Dec 2005 at 8:37, John DeSoi wrote: >> Do you know who created the database cluster with initdb? Perhaps >> they used their own user name rather than postgres. > I did :-) > as root... I don't think so. initdb refuses to run as root. However, the default PG superuser account will be named the same as the Unix account that you used to run initdb. regards, tom lane
-
Re: Login problem
Danjel Jungersen <danjel@jungersen.dk> — 2005-12-20T17:22:43Z
On 20 Dec 2005 at 12:05, Tom Lane wrote: > "Danjel Jungersen" <danjel@jungersen.dk> writes: > > On 20 Dec 2005 at 8:37, John DeSoi wrote: > >> Do you know who created the database cluster with initdb? Perhaps > >> they used their own user name rather than postgres. > > > I did :-) > > as root... > > I don't think so. initdb refuses to run as root. However, the default > PG superuser account will be named the same as the Unix account that you > used to run initdb. Well, I did run it as root, but I have just this moment figured it out, the name was _postgresql (I have no idea where that name comes from)... I got the tip from the startupscript hint... My next problem, is that it refuses to let me log in from phpPgAdmin, but I will post again, if I cannot get it to work after a bit more testing :-) Thank you all for your help so far !! Best regards Danjel > > regards, tom lane
-
Re: Login problem
Frank Bax <fbax@sympatico.ca> — 2005-12-20T18:27:36Z
At 12:22 PM 12/20/05, Danjel Jungersen wrote: >On 20 Dec 2005 at 12:05, Tom Lane wrote: > > > "Danjel Jungersen" <danjel@jungersen.dk> writes: > > > On 20 Dec 2005 at 8:37, John DeSoi wrote: > > >> Do you know who created the database cluster with initdb? Perhaps > > >> they used their own user name rather than postgres. > > > > > I did :-) > > > as root... > > > > I don't think so. initdb refuses to run as root. However, the default > > PG superuser account will be named the same as the Unix account that you > > used to run initdb. > >Well, I did run it as root, but I have just this moment figured it >out, the name was _postgresql (I have no idea where that name comes >from)... The username "_postgresql" came from your own fingers; it's a naming standard for OpenBSD services. You also did not run initdb as root - the OpenBSD installation instructions directed you to create a user called _postgresql, then su to that user and run initdb. Perhaps you should reread: /usr/local/share/doc/postgresql/README.OpenBSD Frank