Search:  
Gentoo Wiki

PostgreSQL/Usage

Contents

Using Postgresql

Now we are all done with the configuration it is time to create database users and databases. You might want to skip ahead to the 'administrator tools' to let other programs do that for you. But these programs are not needed as you can control postgresql completely with the tools provided by postgresql. Again it is a matter of taste which you prefer I must admit though most people favor the use of 'administrator tools' since they don't weekly create new users. Hence will tend to forget the commands ... Anyway I will just give you the info so you can go either way. Please make sure postgresql is running and all commands are executed as root.

Creating Databases

The first step in database land is creating a database. Since there are 2 ways of doing just that I will show them both. First one :

# createdb -U postgres mydb 

This will create a database named 'mydb'. Second way is in the SQL interface of postgresql :

# psql -U postgres
# CREATE DATABASE mydb;
# \q

Will also create a database named 'mydb'.

If for some reason you want to delete the database it won't surprise you there are again 2 ways of doing so. First :

# dropdb -U postgres mydb

This will drop a database named 'mydb'. Second way :

# psql -U postgres
# DROP DATABASE mydb;
# \q

Will also drop a database named 'mydb'.

When you have created a database user you can set a database owner for the database. This can be done like this

# psql -U postgres
# ALTER DATABASE mydb OWNER TO new_owner;
# \q

This will set the owner to 'new_owner' for the database with the name 'mydb'. For more information please see the documentation on the postgresql website

Creating Database Users

Second step is to create some database users. Since in the configuration we required every user (except postgres) to be authenticated by md5 we must ensure every database user has a password. Which seems the only sensible thing to do unfortunately postgresql doesn't ask for passwords by default thus we have to make it ask for passwords. Again there are two ways of database user creation and again I will show both. Here is the first one :


Code: Database User creation
 
  # createuser -U postgres –P
  Enter name of role to add: joe
  Enter password for new role:
  Enter it again:
  Shall the new role be a superuser? (y/n) n
  Shall the new role be allowed to create databases? (y/n) n
  Shall the new role be allowed to create more new roles? (y/n) n
  CREATE ROLE

If you want the user to be able to create databases or create more database users answer the questions differently than me. Superusers have the ability to create databases and new database users. Then time for the second way :

# psql -U postgres 
# CREATE USER joe PASSWORD 'secret' NOSUPERUSER NOCREATEDB NOCREATEROLE;
# \q

If you want the user to be able to create databases remove the 'NO' in the word 'NOCREATEDB' the same goes for database user creation and superuser.

Now you know how to create database users it is time to learn how to delete them. Without more text here is how : First way :

# dropuser -U postgres joe

Second way :

# psql -U postgres
# DROP USER joe;
# \q

This concludes this section about database user creation. If you want to know more about this subject please visit the documentation on the postgresql website


Administrator Tools

Now you have got a taste of how to create database users and databases you most likely think :"Why not make it any easier ... I forget these commands in a matter of hours". In a matter of fact I would be surprised if you didn't think that :D The solution to your problem is to install an administrator tool. In which you have a graphical interface and by clicking on buttons you control your postgresql service. There are 2 kind of administrating tools : First of all those who run in a browser and the second which runs locally on your server. In the first case the server on which postgresql is running also runs a web server together with a scripting language (like php or perl). This will allow remote administrating in a browser without the need for graphical packages to be installed on your server (which take lots of system resources). The second kind is an administrator tool that runs on the server itself and thus relies on the graphical packages on your server to show all the options and settings. But since it runs locally it can be much more complex and therefore can give the user far more options. Please note that you can still remotely administrate your server by connecting to your server with ssh (together with an X11 connection). Which kind of administrator tool is better is a matter of opinion and taste. Therefore I will show you for each kind one example and you can take your pick. Moreover these two were selected by me because of their functionally and popularity but there are many more programs out there that you may try if you don't like these ones.

Webmin

In the past I have used other administrator tools for postgresql but my all time favorite is webmin. Even though other packages have a little more functionality webmin has all the functions I need and has a good interface. Moreover webmin can be used for almost ALL server daemons from Apache to Webalizer and perform system tasks from Partitions to Clustering. For more info check out their webpage. For now I will provide a very minimal install and config description if you got problems/questions go here. Installing webmin

# emerge webmin

Start webmin

# /etc/init.d/webmin start

Starting webmin at boot

# rc-update add webmin default

Then open a browser and enter as adres (replace YOU_SERVER_NAME or YOUR_SERVER_IP with you own data)

https://YOUR_SERVER_NAME:10000

or

https://YOUR_SERVER_IP:10000

Accept the SSL certificate (for it is YOUR certificate!) and login with your root account. Then click on 'Servers' and then click 'PostgreSQL Database Server'. No problems should arise and you can start using it but when the following error shows itself here is how to solve it

Code: Webmin : PostgreSQL error
 
  The PostgreSQL host configuration file /var/lib/postgresql/data/pg_hba.conf was not found on your system.
  Maybe PostgreSQL has not been initialised, or your module configuration postgresql is incorrect.
 

Solve this by clicking on 'module configuration' and make sure the following info is correct :

Code: Webmin : PostgreSQL Module Edit

Path to postmaster PID file /data/postgresql/data/postmaster.pid Path to host access config file /data/postgresql/data/pg_hba.conf

That is that ! The interface should be self explaining and you never have to remember user creation commands again !

pgAdmin III

The other very nice administrator tool is pgAdmin III. In contrast with webmin pgAdmin III can only work with PostgreSQL but it has far more options and settings for PostgreSQL than webmin. Thus even for the more experienced postgresql admins all administrating can be done through this piece of software. Because of the many options this program doesn't run a webinterface but a GTK one. GTK is a graphical interface lib thus this program runs locally on the server (and requires the GTK graphical libary to be installed which is about 20 MB). Again for more details check their homepage I will provide here only a minimal installation decription below. Installing pgadmin3

# emerge pgadmin3

Start pgadmin3

# pgadmin3

It might happen that when starting pgadmin3 this error pops up

Code: pgAdmin III error
Error connecting to the server: could not connect to server:
Connection refused
Is the server running on host "xxx.xxx.xxx.xxx" and accepting
TCP/IP connections on port 5432? 

Then you are probably connecting to a UNIX socket (localhost) and not to a tcp/ip (192.168.1.200) one. To solve this open the postgresql.conf file and set a listen adress.

# nano -w /var/lib/postgresql/data/postgresql.conf
Code: postgresql.conf

listen_addresses = '*'

Restart your server, and start pgadmin3 :

# /etc/init.d/postgresql restart
# pgadmin3
Retrieved from "http://www.gentoo-wiki.info/PostgreSQL/Usage"

Last modified: Fri, 29 Aug 2008 04:56:00 +0000 Hits: 1,387