EDIT

# [[PostgreSQL]] Installation on Debian Etch

{{>toc}}

## Installing [[PostgreSQL]]

Currently, [[PostgreSQL]] versions 7.5.22 and 8.1.9 are included within the stable release of Debian Etch.

This installs [[PostgreSQL]] 7.5.22

~~~

# apt-get install postgresql

~~~

This installs [[PostgreSQL]] 8.1.9

~~~

# apt-get install postgresql-8.1

~~~

### Installing the Python Interface to [[PostgreSQL]]

In order to use [[PostgreSQL]] within Python-scripts, the package [_python-psycopg2_](http://initd.org/projects/psycopg2) should be installed.

~~~

# apt-get install python-psycopg2

~~~

### Installing the PHP Interface to [[PostgreSQL]]

In order to use [[PostgreSQL]] within PHP-scripts, the package php5-pgsql must be installed.

~~~

# apt-get install php5-pgsql

~~~

## Configuring [[PostgreSQL]]

### Administering [[PostgreSQL]]

Initially, [[PostgreSQL]]'s administration account user name is _postgres_. This user account is also added to the system and is not password protected initially. There are at least two ways to administer postgreSQL

1. Login as system user _postgres_

~~~

# su postgres

# psql

~~~

1. Login as any other system user (e.g. root)

~~~

# psql -h localhost -U postgres

~~~

You should now be logged onto the database shell environment.

Since there is no password set, you should set a password for the postgres administrator accout first (in the shell).

~~~

ALTER USER postgres PASSWORD 'secret';

ALTER ROLE

\q

~~~

The difference between the two login methods stated above, is that you don't have to enter a password using the first login method,if you want to login as the user you su'ed (e.g. postgres)! This may be useful within scripts.

### Create Users

Users are also called roles in [[PostgreSQL]].

Select a login method and login to the shell. Then enter

~~~

CREATE USER user PASSWORD 'secret';

\q

~~~

Alternatively, use the createdb command

~~~

# createuser user

~~~

### Create Databases

Usually, applications require an empty database for setup. An empty database (e.g. "_emptyDB_") can be created as follows.

Select a login method and login to the shell. Then, enter

~~~

CREATE DATABASE emptyDB OWNER user;

\q

~~~

Alternatively, use the createdb command

~~~

createdb -O user emptyDB

~~~

### Granting Access Rights to a Database

If you want to assign all access rights on a database (e.g. _emptyDB_) to the user _user_, login to the shell and enter

~~~

GRANT ALL ON DATABASE emptyDB TO user;

\q

~~~

## Backup Databases

In order to backup databases or tables, use one of the following_mysqldump_ command lines.

~~~

# mysqldump [options] db_name [tables]

# mysqldump [options] --databases db_name1 [db_name2 db_name3...]

# mysqldump [options] --all-databases

~~~

For instance, if you want to backup the database exampleDB completely, the following command line stores the database _exampleDB_ into the SQL-script file named _exampleDB.sql_

~~~

# mysqldump -u root --password='secret' exampleDB > exampleDB.sql

~~~

## Import Databases

Databases can be imported in [[PostgreSQL]] by executing SQL-script files, formerly generated by a database backup or manually.

The following commands imports the database from the file _exampleDB.sql_ into the _exampleDB_ database.

~~~

# su postgres -c "psql -d exampleDB < exampleDB.sql"

~~~

~~~

# su postgres -c "psql -d exampleDB -f exampleDB.sql"

~~~

## Delete Databases

To delete databases from [[PostgreSQL]], login to the shell and enter

~~~

DROP DATABASE emptyDB;

\q

~~~