PostgreSQL
From Luke Jackson
Revision as of 18:15, 12 December 2006 (edit) Ljackson (Talk | contribs) (→Create User) ← Previous diff |
Current revision (18:15, 12 December 2006) (edit) Ljackson (Talk | contribs) (→Create User) |
||
Line 91: | Line 91: | ||
[[Category:Linux]] | [[Category:Linux]] | ||
[[Category:Mac OS X]] | [[Category:Mac OS X]] | ||
- | [[Category:Windows]] | + | [[Category:Windows XP]] |
Current revision
Install
postgresql-server-<VERSION> postgresql-libs-<VERSION> postgresql-devel-<VERSION> postgresql-<VERSION>
On most linux systems you can install the above rpms via yum:
yum install postgresql-server postgresql-libs postgresql-devel postgresql
Configure PostgreSQL to be accessible from remote
The PostgreSQL database server configuration file is postgresql.conf. This file is located in the data directory of the server, typically /var/lib/postgres/data.
1. As root user edit the file
vi /var/lib/postgres/postgresql.conf
2. In the connections and authentications section uncomment or edit the listen_addresses line to your needs and take a careful look at the other lines.
listen_addresses = '*'
3. Hereafter insert the following line in the host-based authentication file pg_hba.conf. This file controls which hosts are allowed to connect, so be careful.
- IPv4 local connections:
host all all 127.0.0.1 255.255.255.255 trust
4. After this you should restart the postmaster for the changes to take effect with
/etc/rc.d/postgresql restart
5. Please consider that the port 5432 should be open, so make sure to configure your firewall correctly.
6. For troubleshooting take a look in the server log file
tail -f /var/log/postgresql.log
Create User
createdb
createuser creates a new PostgreSQL user. Usage: createuser [OPTION]... [USERNAME] Options: -a, --adduser user can add new users -A, --no-adduser user cannot add new users -d, --createdb user can create new databases -D, --no-createdb user cannot create databases -P, --pwprompt assign a password to new user -E, --encrypted encrypt stored password -N, --unencrypted do no encrypt stored password -i, --sysid=SYSID select sysid for new user -e, --echo show the commands being sent to the server -q, --quiet don't write any messages --help show this help, then exit --version output version information, then exit Connection options: -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port -U, --username=USERNAME user name to connect as (not the one to create) -W, --password prompt for password to connect If one of -a, -A, -d, -D, and USERNAME is not specified, you will be prompted interactively.
You have to create the superuser as postgres:
su - postgres
One option is to create a super-user with something like:
postgres$ createuser -d -a -P ljackson
then:
ljackson$ createdb ibmadb
then do administrative tasks with that user. I would advise *NOT* using root. If this is a tightly controlled (non-shared) machine, you could make a super user as your normal unix login (which hopefull is not root). Ideally you'll only need root to start the postgres service.