Setting up Postgres 8 on Debian
Introduction
The PostgreSQL database is one of the oldest and most developed free available relational databases. It nearly implements the full SQL92/SQL99/SQL2003 standard including some database specific features. By the way.. geekmonkey.org is powered by PostgreSQL. :)Installing
For this tutorial I assume you use debian or a debian based distribution as I will make use of the apt-get utility. You can also use your own package manager (like YaST, aptitude, emerge, etc.). To install the database simply type:ktulu:~# apt-get install postgresql-8.3 postgresql-client-8.3
If you are working on a local machine you can also install [http://pgadmin.org pgadmin3] which is a very nice gui-based tool to administrate the database.
ktulu:~# apt-get install pgadmin3
After the installation the database is running and listening on port 5432 at localhost (unix domain socket). If you want the database to listen on a specific IP open the postgresql.conf located in /etc/postgresql/8.3/main/ and edit the line containing listen_addresses to:
listen_adresses = 'localhost, 127.0.0.1, yourIP'
After saving the file restart the database /etc/init.d/postgresql-8.2 restart.
Setting the root password
Next thing to do, is to set the password of the superuser named 'postgres'. To do this you have to be root on your system and su to the user postgres (which was created during the install).ktulu:~# su postgres
You can now access the postgres commandline tool named psql and start typing SQL-Statements. The one used to change the password is ALTER ROLE postgres WITH PASSWORD 'pw'; where pw in the single quotes represents you password. After confirming that statement by hitting ENTER the new password is set and we are done. Leave psql by typing \q.
postgres@ktulu:~$ psql
Welcome to psql 8.3.11, 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=# ALTER ROLE postgres WITH PASSWORD 'abcdef';
ALTER ROLE
postgres=# \q
postgres@ktulu:~$
Connecting with pgadmin3
A very nice gui-based tool to administer the RDBMS is PGAdmin III ([http://pgadmin.org pgadmin.org]). In case your postgres-server isn't located on your working machine you have to setup postgres to allow IP connections from your IP. Open up pg_hba.conf (usually located unter /etc/postgresql/8.3/main/) and add the following line to the bottom:host all all WW.XX.YY.ZZ/32 md5
Where you replace the WW.XX.YY.ZZ by your current IP address. You then have to reload your postgres server to make the changes become valid (/etc/init.d/postgresql reload on debian). You should now be able to connect to your postgres server using PGAdmin III. Note that your IP normally changes everyday (unless your ISP allocates you a static IP) and you'll have to edit the pg_hba.conf every 24 hours.
Recommended books on this topic
 |
PostgreSQL (2nd Edition) by Korry Douglas |
Comments (0) |