PostgreSQL

From Gentoo Linux Wiki

[edit] Preamble

This wiki contains a lot of useful information, but not enough: It still requires information about 1) Generating a certificate for your postgres server 2) setting the default configfile location (/etc/init.d/?) 3) generating the default tables and users -TS20070906 on Gentoo 2007.1

[edit] Introduction

In almost all programs data storing is an important part of your program. For instance you don't want to configure your Linux kernel every time you boot your system. Now your Linux kernel configuration is stored in clear text and in 1 file on your hard drive so you don't have to worry about it. But saving data in clear text and in 1 file has its limitations. Imagine a bank with thousands of customers and with hundreds of product information to store for each customer. Storing that into 1 file would simply not fit on the file system and search it for 1 customer would take forever. To solve this problem database programs were made. The language that most of the database programs use is called SQL.

Now if you have a web application or an application which (potentially) has a lot of information you might want to consider putting the data into a database. That would save you lots of time because you don't have to bother how it is stored or how to search through all the data. Just feed your SQL-statement to the database program and the database program will return the desired information. Now it shouldn't surprise you that many database systems exists today, just summarizing a few : sqlite, mysql or oracle and many more. Which program works best for you is a matter of checking supported options and testing it. For a small overview of supported options check this page. For the testing part : If you write your SQL statements according to the SQL 92 standard (if you don't need all the fancy new SQL features) almost all database programs will work. And you can simply change the database and update your database connectors and rerun your program without changing your SQL statements.

WARNING : mysql doesn't support nested queries (in other words : a select into where clause) and the table creation is different (you must supply a storage method)

This tutorial will show you how to install and configure PostgreSQL in Gentoo. PostgreSQL is one of the most powerful open source database programs; the project attempts to implement many high-end features expected out of enterprise database solutions as well as the full the SQL 92 standard, making it a powerful and highly scalable database solution for any configuration (even at home). Please consult the postgresql documentation for any additional information you want since this tutorial only concerns itself with how to install/config and use postgresql.

[edit] Installation and Setup

[edit] Before Emerging

Make sure that the "postgres" is in the USE variable in /etc/make.conf . This will ensure that future programs you emerge will be built with postgresql support installed (like php for instance). At least make sure it is your USE variable when emerging postgresql.

Then because of the large community development of postgresql is going steadily. Recently some significant changes have been implemented, making the latest 8.x.x versions unfortunately not always compatible with previous ones. Of course this doesn't effect people with a fresh install but might cause trouble when migrating from a previous version. The best way around this is to dump your postgresql data into a file before proceeding. The information how to do just that is in the section 'Upgrading Postgresql'.

[edit] Emerging

Get the latest stable postgresql with

# emerge -av postgresql

If you want the latest ebuild, find the latest postgresql ebuild. I got the latest version from gentoo-portage.com. You can always check for new versions on the postgresql website. For more information about installing custom ebuilds, see HOWTO Installing 3rd Party Ebuilds. After you got the ebuild, we have to unmask the package:

# nano -w /etc/portage/package.keywords

Add the following packages, replacing the .x.x with your version:

~dev-db/postgresql-8.x.x
~dev-db/libpq-8.x.x

This will unmask the postgresql ebuild. Now run

# emerge -av postgresql

to emerge your latest postgresql version.

[edit] Configuring

Time to edit some of the configuration files. Tons and tons of options are available which I will not go through. I will only go through the most obvious/important ones. Feel free to add any options which YOU feel are important and are not mentioned here ... That is why it is a wiki :D

[edit] Main Configuration

After emerging you need to create a place where the databases and the configuration files are stored. By default you can use the gentoo default configuration:

# emerge --config =postgresql-8.x.x  //Replace "8.x.x" with correct version number

If the configurations fails with a message like

failed to initialize lc_messages to ""

set up your LC_MESSAGE locale variable. You might use

# export LC_MESSAGES="en_EN.utf8"

for English locales.

This will install the config files and database files into : /var/lib/postgresql/data . If you want the config and data files somewhere else here is an example of how to use the directory : /data/postgresql/data

#  mkdir -p /data/postgresql/data
#  chown -R postgres:postgres /data/postgresql/data
#  su - postgres
#  initdb -D /data/postgresql/data

Then change the PGDATA to the appropriate directory

# nano -w /etc/conf.d/postgresql

 
  PGDATA="/data/postgresql/data"
  

You probably want to comment out the PGOPTS="-N 40 -B 80" line and either go with what the initdb defaults to in postgresql.conf, or set your own ( the PGOPTS="-N 40 -B 80" sets max connections to 40 and shared_buffers to 640k -- the documentation says that several tens of MB are required for production servers and that the minimum must be the greater of 128 kilobytes or #connections * 16 kilobytes. The initdb on my system defaulted these two values in postgresql.conf to 100 connections and 24MB of shared_buffers) Since lots of additional programs, that depend on or work with postgresql, expect the postgresql configuration files to be located in '/var/lib/postgresql/data' I will create a symbolic link so they work as well. This will save you lots of configuration in the future.

# mkdir -p /var/lib/postgresql
# ln -s /data/postgresql/data/ /var/lib/postgresql/

[edit] For '/etc/' Configuration users

Postgresql puts the .conf files into the PGDATA directory instead of '/etc'. For users that are used to edit their configuration files in '/etc' this will help them out.

# mkdir /etc/postgresql

For default

# ln -s /var/lib/postgresql/data/pg_hba.conf /etc/postgresql/pg_hba.conf
# ln -s /var/lib/postgresql/data/postgresql.conf /etc/postgresql/postgresql.conf

For changed PGDATA dir

# ln -s /data/postgresql/data/pg_hba.conf /etc/postgresql/pg_hba.conf
# ln -s /data/postgresql/data/postgresql.conf /etc/postgresql/postgresql.conf

(Note for changed PGDATA users who have created a symbolic link it doesn't matter which one they use)

[edit] Settings External Connections

By default postgresql ONLY accepts local connection in other words it accepts only connections to "localhost". This is the most secure option if all your the database connections originates from the same host as you run postgresql on. But if you have postgresql running on an external host here is how to make postgresql listen for external connections :

Open the postgresql.conf file

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

Uncomment the listen_addresses option and add the allowed connections, for instance : listen_addresses = '192.168.1.200, 192.168.1.201' or listen_addresses = '*' (to listen on all of the server IP addresses)

 
 #--------------------------------------------------------------------
 # CONNECTIONS AND AUTHENTICATION
 #--------------------------------------------------------------------
 # - Connection Settings -
 listen_addresses = '*'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
 #port = 5432 
 max_connections = 100
 # note: increasing max_connections costs ~400 bytes of shared memory per
 # connection slot, plus lock space (see max_locks_per_transaction).  You
 # might also need to raise shared_buffers to support more connections.
 #superuser_reserved_connections = 2
 #unix_socket_directory = ''
 #unix_socket_group = ''
 #unix_socket_permissions = 0777         # octal
 #bonjour_name = ''                      # defaults to the computer name
 
 # - Security & Authentication -
 
 #authentication_timeout = 60            # 1-600, in seconds
 ssl = on
 #password_encryption = on
 #db_user_namespace = off
  

If you want to connect over a network or worse the internet. Consider encrypting your data and connection with SSL and/or Kerberos. The options are found in the same section. Be sure to also read the next section on Setting Internal Connection to set authentication method for the network connections.

[edit] Setting Internal Connections

The default settings will allow ANY local connection for as long as the username checks out. I find it more secure if all users also must provide a password in order to get their database connection. I think most of us would agree on that !

Open the pg_hba.conf file

# nano -w /var/lib/postgresql/data/pg_hba.conf

Make it look like this

 
 # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
 # "local" is for Unix domain socket connections only
 local   all         postgres                          trust
 local   all         all                               md5
 # IPv4 local connections:
 host    all         all         127.0.0.1/32          md5
 # IPv6 local connections:
 host    all         all         ::1/128               md5
 

This will ensure that all local users must provide a valid password and the password is encrypted by md5 (kerberos encryption is more secure ... but requires additional settings and 'kerberos' to be set in your USE variable). The only exception is 'postgres' user since it needs this in order to create database users and databases for you. This of course dictates to you as database administrator that ALL database users MUST have a password (which is always a good idea). But if you follow this wiki that won't be a problem. Then for external connection add the following line (provided they are ssl encrypted)

 
  hostssl all         all         *           md5
  

[edit] Logging

By default the logging is off. This is not recommended since logs are the only way to properly determine what went wrong in cases of emergency (crashes, slow performance etc). Even if you or your own (written) programs are the only users the postgresql server it is still recommended you set some kind of logging. How much to log is up to you a just keep in mind: The more you log the more likely you are to find your problem but the more time it will take to find it. Here is my default logging configuration which should suffice for most users.

In order for postgres to log to the '/var/log' directory it must first have access to it. Therefore we make a directory and make postgres its owner. You can skip this if you decided to log into (a sub directory) of your PGDATA directory.

# mkdir /var/log/pglog
# chown postgres:postgres -R /var/log/pglog/

Open the postgresql.conf file

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

Then make the following changes check also below for full details

• Uncomment 'redirect_stderr' and set to 'on' - now the logs will be written to file
• Changed the log_directory to '/var/log/pglog' so the logs are placed in the (sys)logs directory
• Change log_filename to 'pgsql-%Y-%m-%d.log' so the logs are easier dated (seconds in a logfile name isn't handy)
• Uncomment and set 'log_min_error_statement' to 'error' - will show you when random hurtful statements are given
• Uncomment and set 'log_connections' to 'on' - gives info on who (IP instead of username)
• Uncomment and set 'log_line_prefix' to '<%t>' - provides a timestamp for all loglines
• Uncomment and set 'log_statement' to 'ddl' - provides info when the tables themselves were changed/created or dropped

NOTE: As of postgresql 8.0.15 instead of on/off there are true/false switches in configuration. I think it's also the case for every 8.0.x, so beware.

 
 #---------------------------------------------------------------------------
 # ERROR REPORTING AND LOGGING
 #---------------------------------------------------------------------------

 # - Where to Log -

 #log_destination = 'stderr'            

 # This is used when logging to stderr:
 redirect_stderr = on                    

 # These are only used if redirect_stderr is on:
 log_directory = '/var/log/pglog'       

 log_filename = 'pgsql-%Y-%m-%d.log'

 #log_truncate_on_rotation = off         

 #log_rotation_age = 1440     

 #log_rotation_size = 10240    


 # These are relevant when logging to syslog:
 #syslog_facility = 'LOCAL0'
 #syslog_ident = 'postgres'


 # - When to Log -

 #client_min_messages = notice      

 #log_min_messages = notice             

 #log_error_verbosity = default

 log_min_error_statement = error

 #log_min_duration_statement = -1

 #silent_mode = off 

 # - What to Log -

 #debug_print_parse = off
 #debug_print_rewritten = off
 #debug_print_plan = off
 #debug_pretty_print = off
 log_connections = on
 #log_disconnections = off
 #log_duration = off
 log_line_prefix = '<%t> '

 log_statement = 'ddl'
 #log_hostname = off

[edit] Log Rotation

After you've decided to configure some kind of logging for Postgresql, the next step is to prevent accumulating log data from taking over all your hard disk space. Postgresql will rotate the log file but doesn't delete obsolete logs (rotate 6 weeks old logs out of existence). When you installed Gentoo you (should) have emerged logrotation to handle your syslog files. I will use this tool to rotate the logfiles of postgresql. You can also make a bash script and load it into your cron daemon however I found it cleaner to keep logrotation in one place and better for maintenance. In case you didn't install logrotation for your syslog, here is how to do it:

 emerge logrotate

Main configuration file is /etc/logrotate.conf and additional logrotate files are placed in /etc/logrotate.d/. Then it is time to make our logrotation entry

 nano -w /etc/logrotate.d/postgresql

 
# Compress postgresql log files
# Provided by DouweQuerty
/var/log/pglog/pglog.tar {
  missingok
  ifempty
  rotate 7
  weekly
  sharedscripts
  prerotate
    cd /var/log/pglog/
    tar -C /var/log/pglog/ -cp --remove-files --wildcards -f pglog.tar --exclude *$(date +%Y-%m-%d)* *.log
  endscript
  postrotate
  endscript
}

This will collect (tar) all the existing logfiles, except today's logfile, before rotating them. Because the active logfile isn't touched, no reload of postgresql is needed. But we do need to create a dummy tar file else logrotation doesn't do its job.

 echo "" > /var/log/pglog/pglog.tar

Then check whether it is working by forcing a logrotation:

 logrotate -f /etc/logrotate.conf

[edit] Install a Procedural Language

You can install procedural languages, such as postgresql's pl/pgsql, into a template. Using the newly created template will make the procedural languages available to all databases created using that template. This is purely optional and if you don't know or want to know what templates are please skip this section. You can read this section in the future, should the need for procedural languages arise. For those who are interested, here we go: First create a template called 'template1' and include the postgresql's pl/pgsql

# su postgres
# createlang plpgsql template1

Since 'template1' will be automatically added to any new database which will be created we are all done here! If you decided to skip ahead and now want to add the 'pl/pgsql' language to an existing database here is how:

# su postgres
# createlang plpgsql MY_DATABASE

Make sure you replace MY_DATABASE with your own database's name.

[edit] Installing JDBC driver

JDBC is the intermediate between Java and postgresql. Thus allowing Java application to execute SQL statements and retrieve information from the database. Since the JDBC driver are written in Java itself there is a significant speed advantage over the JDBC-ODBC bridge. Installing it is very easy :

# emerge -av jdbc-postgresql

Make sure you have the latest version of java installed on your system for these drivers to work. I also must point out that the new jdbc4 drivers are coming. At this moment they are released by postgres and will in time be part of the Gentoo.

[edit] Finish and Start

After you have configured postgresql it is time to fire it up :

#/etc/init.d/postgresql start

The following command will ensure that postgresql is loaded at boot

# rc-update add postgresql default

Time for a quick test of your newly installed postgresql server. Thus

# psql -U postgres

Then enter the following command

select current_date;

The result should look like this :

 
Welcome to psql 8.1.8, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# select current_date;
    date
------------
 2007-04-02
(1 row)

postgres=# \q

[edit] 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.

[edit] 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

[edit] 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 :

 
  # 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

[edit] 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.

[edit] 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

 
  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 :

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

[edit] 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

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

Restart your server, and start pgadmin3 :

# /etc/init.d/postgresql restart
# pgadmin3

[edit] Upgrading Postgresql

First a warning : This was taken from an page where there was some discussion whether this should work or not. If you are smart you backup your postgresql before trying this (which is always a sensible thing to do when you are upgrading). I have added this section with remarks and comments thus if you know something more or worked out how this works please add it or contribute to the discussion part of this page.

When upgrading emerge often says

 * Postgres n.n.n cannot upgrade your existing databases, you must
 * use pg_dump to export your existing databases to a file, and then
 * pg_restore to import them when you have upgraded completely.

This has caused me some grief in the past and I'm clearly not the only one so here's my attempt at documenting how to do it. The key thing is that emerge speaks with forked tongue, it's not pg_restore that you need, but more on this below.

As mentioned, you first need to backup your databases. There are two ways:

1. If you have multiple databases, it is easier to use the pg_dumpall program. This causes all data in the database to be dumped to the a single file. The syntax is:

    pg_dumpall -U sqluser > /backupplace/backup.sql 

   Because pg_dumpall needs to connect to each database separately, it will ask you for the user password each time it tries to connect to a new database. So configure an account to use trust or ident authentication in pg_hba.conf before running pg_dumpall if you want to avoid this. Or, use the "postgres" user. Note. If you have chanced pg_hba.conf then you need to back that up.
2. If you have only one database, it is also possible to backup only this one (but you will loose your self created internal postgresql users ...not if you back them up using pg_dump -U sqlusr -s database1 > /backupplace/users.sql). You need to perform a command something like this:

    pg_dump -C -U sqlusr database1 > /backupplace/backup1.sql 

   You'll need to repeat the above for each database you've got using different output filenames each time.
   
   You can also do

   pg_dumpall --globals-only > globals.pgsql

   when dumping individual database with pg_dump. That way, you'll have the separate databases, and the globals (like users).

Having backed up your database(s) to files, stop postgresql:

/etc/init.d/postgresql stop

You then need to "remove" the database directory. You'll remember that emerge says:

 * You must remove your entire database directory to continue.
 * (database directory = /var/lib/postgresql).

If you're paranoid like me then rather than remove it what you'll do is move it to one side thus:

cd /var/lib
mv postgresql postgresql.old

Now let emerge update postgresql. Once that's done re-install it using the emerge --config command used above. Be sure to set an appropriate LANG environment before issuing this command. An unset LANG will lead to an ASCII-encoded database, while for example LANG=de_DE sets it up using latin1 encoding.

After that, you should set up your config files by copying your pg_hba.conf and/or (portions of your) postgresql.conf file from your backup to the new database location. Then fire postgresql up so you can start populating the database with your data again.

/etc/init.d/postgresql start

1. If you used the pg_dumpall method, we can simply use the following command to restore your entire database (including self-created users):

    psql template1 < /backupplace/backup.sql 

   We do this from the command line as user postgres, so make sure that backup.sql is readable by this user.
2. Remember to create the user(s) (in my case sql-ledger) and each database (in my case the-hug). Then use psql (yup, not pg_restore) to restore the database(s) thus:

    psql -U sqluser template1 < /backupplace/database1.sql 

If you have large databases, it is possible to use some command-line options of pg_dumpall to output the sql code to an archive. You will need to use pg_restore to restore your database then though.

A note from the Postgresql manual: Once restored, it is wise to run ANALYZE on each database so the optimizer has useful statistics. You can also run vacuumdb -a -z to analyze all databases.

Remember that if this fails you can always use emerge to fall back to the old release and then restore your database which, thanks to your foresight, still lurks in /var/lib/postgresql.old

See the postgres upgrade FAQ entry for more info.

[edit] Troubleshooting

In the past (postgresql 7.x.x) the following message could show itself. It shouldn't display itself anymore but just in case it does here is how to fix it :

su: Authentication service cannot retrieve authentication info.

This indicates that su cannot find an entry for the user posgres in /etc/shadow. This can be solved by just adding one:

pwconv

If you get errors like "psql: FATAL: user "root" does not exist" when running commands such as

#  psql -l

then remember to run them as the postgres user

#  su - postgres

[edit] Virtual Mail System

This section will show you one use for your postgresql server namely a virtual mail system. If you don't want or need a virtual mail server just read through it might give you some ideas. Personally I added this section because it was on one of the postgresql howto's and I don't want to destroy somebody else his work (and it provides atleast a nice exercise). Nevertheless it might very handy for you so here we go : For a virtual mail system we first need a system user and a system group. These will provide the backbone of all virtual domain users which we will create later. Then we need to create several database tables which can hold the virual domain users information. Then use postfix so virtual domain users can send email to one another.

Also please check : postfixadmin which will provide a simular solution. But also closer to home there is an nice howto : HOWTO Email: A Complete Virtual System - PostgreSQL

[edit] The vmail User

The vmail user account will serve as the base linux account that all virtual domain users will share. This is not an actual account they can use to log into the system, it is simply an account that provides a valid group and user id for virtual users. I decided that I setup gid=uid in my system, so after creating the vmail user, I got the uid from webmin (which was 1000) and then created a new group, vmail with a gid of 1000. I then reassigned the vmail user to this group. Depending on your particular machine, you are likely to get a value other then 1000. No worries just use whatever you found with webmin. At this point, most of the HOWTO’s out there will tell you to get setup your /home/vmail directory which is where you will end up storing all your virtual mail. Because of the way I have partitioned data across my hard drives, I took a different approach and decided to stuff all my mail under /var/mail/vmail.

 
  # useradd -d /var/mail/vmail -s /bin/false vmail
  # groupadd -g 1000 vmail
  # mkdir /var/mail/vmail
  # chown vmail:vmail /var/mail/vmail
  

[edit] Creating the Database

It is now time to create the database and tables required. Code Listing 8: Creating the database

 
  // Create the database
  # createdb -U postgres postfix
  CREATE DATABASE
  

The listing below outlines the tables to be created. The schema is based on the postfixadmin schema, with a few extra elements that are included to support some additional (and optional) functionality. Postfixadmin will not be able to address all of these tables, however I have included them in my installation as I may make use of them by either directly inserting values into the table, or if I am feeling really ambitious, I may extend webmin or postfixadmin to address them (yah … right).

The simplest way is to copy the script below and paste it into a file. You can then use the psql utility to run the script. The following tables are used in postfix database:

• alias - local email alias and mailman alias information.
• relocated - relocated user email address maps
• transport – supports delivery to non-postfix mailbox stores for hosted domains.
• mailbox - all user account and mailbox information
• virtual - virtual domain email alias map

Do not forget to edit the data that is specific to your setup. Default settings for gid and uid in the mailbox table should be set (in my case, based on the gid and uid of the vmail user setup earlier). To make life easy, you should probably copy the text below and paste it into a file so you can use psql to create your tables.

 
  // Create the database
-- Postfix Admin Release 2.x --
----------------------------------------------------------
--
-- Copyright (c) 2002 - 2005 High5!
-- Created by: Mischa Peters <mischa at high5 dot net>
-- Updated by: Angus Muir
--
-- This is the complete database structure to support virtual
-- domains and Postfix Admin on a PostgreSQL database.
--
-- There are 2 entries for a database user in the file.
-- One you can use for Postfix and one for Postfix Admin.
--

--
-- Table structure for table admin
--
DROP TABLE admin;
CREATE TABLE admin (
  username     varchar(255)             NOT NULL   default '',
  password     varchar(255)             NOT NULL   default '',
  created      timestamp with time zone            default now(),
  modified     timestamp with time zone            default now(),
  active       boolean                  NOT NULL   default true,
  PRIMARY KEY (username)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON admin to postfixadmin, postfix;


--
-- Table structure for table alias
--
DROP TABLE alias;
CREATE TABLE alias (
  address      varchar(255)             NOT NULL   default '',
  goto         text                     NOT NULL,
  domain       varchar(255)             NOT NULL   default '',
  created      timestamp with time zone            default now(),
  modified     timestamp with time zone            default now(),
  active       boolean                  NOT NULL   default true,
  PRIMARY KEY (address)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON alias to postfixadmin, postfix;


--
-- Table structure for table domain
--
DROP TABLE domain;
CREATE TABLE domain (
  domain       varchar(255)             NOT NULL   default '',
  description  varchar(255)             NOT NULL   default '',
  aliases      integer                  NOT NULL   default 0,
  mailboxes    integer                  NOT NULL   default 0,
  maxquota     integer                  NOT NULL   default 0,
  transport    varchar(255)                        default NULL,
  backupmx     boolean                  NOT NULL   default false,
  created      timestamp with time zone            default now(),
  modified     timestamp with time zone            default now(),
  active       boolean                  NOT NULL   default true,
  PRIMARY KEY (domain)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON domain to postfixadmin, postfix;


--
-- Table structure for table mailbox
--
DROP TABLE mailbox;
CREATE TABLE mailbox (
  username     varchar(255)             NOT NULL   default '',
  password     varchar(255)             NOT NULL   default '',
  name         varchar(255)             NOT NULL   default '',
  uid          integer                  NOT NULL   default '1000',
  gid          integer                  NOT NULL   default '1000',
  homedir      text                     NOT NULL   default '',
  maildir      text                     NOT NULL   default '',
  quota        integer                  NOT NULL   default '0',
  domain       varchar(255)             NOT NULL   default '',
  created      timestamp with time zone            default now(),
  modified     timestamp with time zone            default now(),
  active       boolean                  NOT NULL   default true,
  PRIMARY KEY (username)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON mailbox to postfixadmin, postfix;


--
-- Table structure for table relocated
--

DROP TABLE relocated;
CREATE TABLE relocated (
  email        varchar(255)             NOT NULL   default '',
  destination  varchar(255)             NOT NULL   default '',
  created      timestamp with time zone            default now(),
  modified     timestamp with time zone            default now(),
  active       boolean                  NOT NULL   default true,
  PRIMARY KEY  (email)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON relocated to postfixadmin, postfix;

--
-- Table structure for table 'transport'
--

DROP TABLE transport;
CREATE TABLE transport (
  domain       varchar(255)            NOT NULL   default '',
  destination  varchar(255)            NOT NULL   default '',
  created      timestamp with time zone            default now(),
  modified     timestamp with time zone            default now(),
  active       boolean                  NOT NULL   default true,
  PRIMARY KEY (domain)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON transport to postfixadmin, postfix;


--
-- Table structure for table 'log'
--

DROP TABLE log;
CREATE TABLE log (
 timestamp      timestamp with time zone           default now(),
 username       varchar(255)            NOT NULL   default '',
 domain         varchar(255)            NOT NULL   default '',
 action         varchar(255)            NOT NULL   default '',
 data           varchar(255)            NOT NULL   default ''
 );
GRANT SELECT, INSERT, UPDATE, DELETE ON log to postfixadmin, postfix;

DROP TABLE domain_admins;
CREATE TABLE domain_admins (
 username       varchar(255)            NOT NULL   default '',
 domain         varchar(255)            NOT NULL   default '',
 created        timestamp with time zone           default now(),
 modified       timestamp with time zone           default now(),
 active         boolean                 NOT NULL   default true,
PRIMARY KEY (username)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON domain_admins to postfixadmin, postfix;


--
-- Table structure for table vacation
--
DROP TABLE vacation;
CREATE TABLE vacation (
  email         varchar(255)            NOT NULL   default '',
  subject       varchar(255)            NOT NULL   default '',
  body          text                    NOT NULL,
  cache         text                    NOT NULL,
  domain        varchar(255)            NOT NULL   default '',
  created       timestamp with time zone           default now(),
  modified      timestamp with time zone           default now(),
  active        boolean                 NOT NULL   default true,
  Constraint "vacation_key" Primary Key ("email")
);
GRANT SELECT, INSERT, UPDATE, DELETE ON vacation to postfixadmin, postfix;
  

If you pasted the above into a file (say postfixdb.sql) then we can load it straight away (otherwise you will need to type it all in by hand).

 
  # psql –U postgres postfix < postfixdb.sql
  

If this is the first time you have run this script, you will get a bunch of errors from the DROP TABLE command. Because I ran this script many times as I tweaked and changed things, it was easier to include the DROP and ensure I was getting a clean install.

[edit] Closing Remarks

I have invested quite some time in writing this article and I hope it has and will service you in the future. I started out intending to just merge two postgresql articles which covered more or less the same matter but ended up (re)writing most of the code and adding whole sections as I went along. I would like to thank all people who have contributed in the past to postgresql articles and if your section was somehow forgotten I'm very sorry correct it by adding it. I also would like to ask YOU as reader to keep this article up to date : When new options become available please don't hesatate to add them or If sections become obsolete please remove them. The same is true for language/grammer/spelling and punctuation errors (I know it needs some improvement).

Rest me nothing more then to sign off --DouweQuerty 15:47, 8 may 2007 (UTC)

[edit] See also

http://www.gentoo.org/doc/en/postgres-howto.xml