PostgreSQL Installation on Debian Etch¶
- Table of contents
- PostgreSQL Installation on Debian Etch
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
- Login as system user postgres
# su postgres # psql
- 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
Updated by Andreas Müller almost 2 years ago · 10 revisions