postgresql basics

bentz · 04-10-2004, 02:09 PM

Hello, I am trying to port a php application from mysql to PEAR DB. Everything is going well, but I'd like to test using the application with postgresql instead of MySQL but I'm an idiot and don't know what I'm doing.

I've installed the nessasary rpms on a Fedora system. I have the server running (the initscript has run initdb) and listening on the tcp port and unix domain socket.

My problem is that I can't seen to be able to create any users or databases, it keeps telling me that 'root' isn't a valid user. I'm not using ident (what, are you kidding me?) but instead have modified pg_hba.conf so that root from localhost is 'trusted'. But 'createdb whateva' produces:

psql: FATAL: user "root" does not exist
createdb: database creation failed

I also can't get into the command-line interface 'psql' either. With MySQL, I'm used to a 'root' user with a blank password which can be corrected afterwards. Currently, I'm too stupid to be able to figure out what account I need to attach to postgresql as in order to create users and databases.

Any help will be appreciated.

Crito · 04-10-2004, 07:18 PM

from a root console type
# su postgres
then
$ psql -d template1
from there it's regular SQL DDL and DML stuff: create database, create table, alter table, create user, alter user, etc. Only annoying thing is psql doesn't support the "use" command; switching between databases is accomplished with \c instead.

bentz · 04-10-2004, 08:10 PM

This was a big help, thanks. I can now get into psql. I didn't realize I had to do *anything* with the new shell user, I thought it was just a privilege separation account for postmaster.

Boy, none of my MySQL sql works with postgres; I thought that the ANSI/SQL-92 semi-compliance of both of these databases would make the transition and easy one, but I guess not. How many different ways could there be to say 'create table ...'?

Any way, thanks for your help.

littlemidget · 08-07-2004, 04:10 PM

Quote:

Originally posted by Crito
from a root console type
# su postgres
then
$ psql -d template1

What password would I use there?..

createdb: could not connect to database template1: FATAL: user "root" does not exist

# psql -d template1
psql: FATAL: user "root" does not exist

createuser: could not connect to database template1: FATAL: user "root" does not exist

How do i create new users?..

EDIT: uh, i got it.. :-)

NEW EDIT: does anyone have a url with all the commands used in PostgreSQL? :-)