Getting started with Postgre

These are the steps to get started with Postgre in Ubuntu. The instructions are taken from a reference and not mine. But I did a little bit of compilation and made this. Hope it helps you to get started.

Installation using APT:
sudo apt-get install postgresql

By default, PostgreSQL allows connections only from localhost, for security reasons. Connections from other hosts can be allowed by modifying the configuration file pg_hba.conf and postgresql.conf as follows:

Install pgadmin3 GUI client:
sudo apt-get install pgadmin3

Administration using the database shell: To administer the PostgreSQL database, perform the following tasks:

  • Login as the unix user postgres using the command: “sudo su postgres”
  • Run the command “psql”. This command opens a postgresql database shell.

Then, execute the SQL commands given below within the psql shell.

  • Type +D to exit the posgreSQL shell.

Administrative tasks

Changing the password:
Type \password postgres in the PSQL database shell and enter a new password. In general the command \password can be used to change the password for . *DON’T FORGET THE ABOVE PASSWORD*; but if you do, follow the above steps of logging into the postgres account to change the password.

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

Connect to a database:
Run the below command. Once connected to a database, all schemas and tables are created in that database.
\c dbms2010

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.)
(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.