Procedure to install PostgreSQL and PGADMIN3

Installing PostgreSQL
Make sure your package repositories and installed programs are up to date by issuing the following commands:
apt-get update
apt-get upgrade --show-upgraded
Issue the following command to install PostgreSQL, required dependencies, and some packages that provide additional functionality:
apt-get install postgresql postgresql-contrib
The current version of the database server will be installed, along with several supporting packages.

Creating a Database
Switch to the "postgres" user and create a database by issuing the following commands:
su - postgres
createdb mytestdb
Connect to the test database by issuing the following command:
psql mytestdb
You should see output similar to the following:
psql (8.4.1)
Type "help" for help.

mytestdb=#
This is the PostgreSQL client shell; you may use it to issue SQL statements. To see a list of available commands, use the following command in the shell:
\h
You may find more information on a specific command by adding it after the \h command.

Creating Tables
To create a table in your test database called "employees", issue the following command:
CREATE TABLE employees (employee_id int, first_name varchar, last_name varchar);
To insert a record into the table, you would issue a statement like this:
INSERT INTO employees VALUES (1, 'Jack', 'Sprat');
To see the contents of the "employees" table, you would issue a SELECT statement similar to the following:
SELECT * FROM employees;
This would produce output similar to the following:
mytestdb=# SELECT * FROM employees;
 employee_id | first_name | last_name
-------------+------------+-----------
           1 | Jack       | Sprat
(1 row)
To exit the psql shell, issue this command:
\q









PGADMIN3

Once you have set up PostgreSQL, you need to set up the administrative password, and create users. This page explains how to do so.



Open the terminal window :


Administration using the database shell: To administer the PostgreSQL database, perform the following tasks:
1. Login as the unix user postgres using the command: "sudo su postgres"
2. Run the command "psql". This command opens a postgresql database shell.
3. Then, execute the SQL commands given below within the psql shell.
4. Type <Ctrl>+D to exit the posgreSQL shell.
 
Administrative tasks

1. Creating a new Database:
A default database called postgres is created when you install PostgreSQL. Create a new database called dbms2010, using the command:
CREATE DATABASE dbms2010;

2. Connect to a database:
Run the below command. Once connected to a database, all schemas and tables are created in that database.
\c dbms2010
3. Creating Schemas and Users:
To create a schema and users in the database 'dbms2010', run the following commands:
 
\c dbms2010
CREATE USER user1 WITH PASSWORD 'user123'; (You can use any name and password you wish.)
CREATE SCHEMA AUTHORIZATION user1;
(This creates a schema where the tables created by the user1 will be stored; the schema will be named user1).
Perform steps 2 and 3 for each user you need to create in your resource center.


1.  Install pgadmin3 GUI client
Run the following command:

sudo apt-get install pgadmin3

Using pgAdmin III
1. Type pgadmin3 in the terminal. You will be presented with the pgAdmin III interface.
2. Click on File > "Add server" to open the "New server registration dialog".
3. Enter the following information and click OK:
1. Name: Can be any name (say 'dbserver')
2. Host: localhost
(if the database is on another computer, use its IP address here)
3. Port: 5432
(this is the default port for PostgreSQL)
4. Username: user1
(or any other user name as appropriate; use "postgres" user only for admin work, do NOT create tables or run queries using the postgres user)
5. Password: user123
(or whatever password you created for this user)
 
4. Steps 2 and 3 are to be done only on the first use of pgadmin3. Subsequently, the database name (eg. dbserver) can be double-clicked to connect to it.
5. In the 'Object browser', navigate to Servers > dbserver(localhost:5432) > Databases > dbms2010 > Schemas > user1. This is necessary to run the queries in the specific schema.
6. Open the SQL query window by pressing <Ctrl>+E or through the "Tools > Query tool" menu item.
7. In the query window, you can type any SQL query and run it using F5 or by clicking the "Execute query" toolbar button. You may have to first clear the contents of the window if there are any.

--

Rajwinder Singh
Head, Dept of Computer Science and Engineering
First Floor , Engineering Block I,
Chandigarh Engineering College, Landran (Mohali)-140307, Punjab, India
Phone : +91-0172-3984200 Ext  216
             +91-0172-3984216 (Direct)
             + 91-  9781925246 (Mobile)
Fax : +91-0172-3984207
E-Mail:cecm.cse@gmail.com
URL : www.cecmohali.org/cse.asp

Blog URL : www.ac2dc.blogspot.com