PostgreSQL Installation on Debian Etch

Table of Contents

  1. Installing PostgreSQL
    1. Installing the Python Interface to PostgreSQL
    2. Installing the PHP Interface to PostgreSQL
  2. Configuring PostgreSQL
    1. Administering PostgreSQL
    2. Create Users
    3. Create Databases
    4. Granting Access Rights to a Database
  3. Backup Databases
  4. Import Databases
  5. Delete Databases

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 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
  2. 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 followingmysqldump 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