PostgreSQL server

From MEPIS Documentation Wiki

Jump to: navigation, search

PostgreSQL is a powerful open-source RDBMS server, very popular among enterprise application developers. It is often compared to Oracle, but has a much smaller resource footprint. Its advantages over MySQL include support for transactional sessions, foreign keys, and stored procedures, though it requires more resources than MySQL and can be more difficult to configure. Full details can be found at the PostgreSQL.org site.

Contents

Installation

PostgreSQL can be installed from the repositories. The Etch repositories contain both version 7.4 and 8.1; unless you have a program with specific compatibility requirements, you should install 8.1:

apt-get install postgresql-8.1 postgresql-doc-8.1

This will cause the client to be installed as well. There are additional components you can optionally download, including stored procedure support for various programming languages, spatial extensions (for GIS), and contributed tools and utilities. A search for "postgresql" in Synaptic will yield all of these packages.

Post-install

Once PostgreSQL is installed, you will need to configure it. At minimum, we want to set up some users, create a database, and make sure the database is accessible on the network.

Method 1: Webmin

If you are using Webmin, setting up postgresql is quite easy.

  1. Go to https://localhost:10000 and log in to webmin
  2. In the "servers" section, go to "PostgreSQL Database server"
  3. At the time of writing, Webmin expects to see Postgresql 7.4. So it will complain if you have 8.1 installed about not being able to find the configuration files. Click on "module configuration".
  4. In the module configuration, simply replace any instance of "7.4" with "8.1". This should include the fields for "Command to start", "Command to stop", and "Path to host access config file".
  5. Apply the configuration. You should now have a nice friendly administration screen.
Creating a user:
  1. Click on "PostgreSQL users".
  2. Click on "Create a New User".
  3. Enter a user name and password. Optionally, set the privileges and/or expiration date.
  4. Click "create"
Creating a database:
  1. On the main module screen, click "Create a new database"
  2. Enter the database name and set the owner. Optionally set the encoding and/or custom file path for the data files.
  3. Click "create"
Make the database accessible to the network:

By default, postgresql is only available on the local machine. Postgresql allows you to granularly define what is available to the network, to whom, and under what conditions. To do this:

  1. On the main module screen, click "Allowed Hosts"
  2. Click on "Create new allowed host"
  3. On this screen you can create your host entry. You can specify a network to allow, a specific IP address, localhost only, or open it up to any network host. You can open up all databases, or only certain databases. You can specify a user who has access, and what kind of password encryption must be used.
  4. Once you have specified all this, click "create".

You will have to make one change to the postgresql configuration file to make sure the database is listening for outside connections:

  1. Open /etc/postgresql/8.1/main/postgresql.conf as root
  2. Add this line:
    listen_addresses = '*'
  3. Save and close.
  4. Restart Postgresql:
/etc/init.d/postgresql-8.1 restart

Method 2: Command line

Creating a user:
  1. Become root:
    su
  2. Now, become the postgres user:
    su postgres
  3. Log in to the database:
    psql template1
  4. Create your user:
    CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
  5. You can add privileges to the user, such as the "CREATEDB" privilege, with the "ALTER USER" command:
    ALTER USER myuser WITH CREATEDB;
Creating a database:
  1. Log in to the database. Make sure your user has the "CREATEDB" privilege.
  2. Create your database. By default, the postgresql client looks for a database with the same name as your username; so unless you have a compelling reason to call it something different, create the database with that name:
    CREATE DATABASE myuser;
Opening the server to the network:

As mentioned above, postgresql has a very granular host/user permissions system to control network or local access to the database. To configure it, you need to edit the file /etc/postgresql/8.1/main/pg_hba.conf. To allow login with md5-encrypted passwords to the entire network, add this line:

host    all    all    all    md5

Then, you need to make sure postgresql is listening for outside connections. Open the file /etc/postgresql/8.1/main/postgresql.conf and add this line:

listen_addresses = '*'

Finally, restart the database to put the changes into effect:

/etc/init.d/postgresql-8.1 restart

Important Notes

  • The default data directory for postgres is /var/lib/postgresql/8.1/main/. If you are dealing with a very large database, you might want to move /var to a new partition, or expand your root partition to give it room.
  • PostgreSQL can be backed up with the pg_dump and pg_dumpall commands. These commands allow you to dump the contents of your database to a SQL script, which can be piped back into the database client to restore the data.
  • If you install the postgresql-doc-8.1 package, complete documentation and tutorials are available in /usr/share/doc/postgresql-doc-8.1/
Personal tools