Q. What is PostgreSQL ?

-- PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language. It is a popular choice for many small and large projects and has the advantage of being standards-compliant and having many advanced features like reliable transactions and concurrency without read locks.

Step: 1. Install PostgreSQL :
   
# apt-get updat
# apt-get -y upgrade
# apt-get install postgresql postgresql-contrib
# vi /etc/postgresql/9.3/main/pg_hba.conf
   
Step: 2. Edit on line 90 & 92 to make sure the file has :

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust

-- Save & Quit (:wq)
       
# sudo /etc/init.d/postgresql restart
   
Step: 3. Login to postgres :

# sudo -i -u postgres

Step: 4. Set a password for the "postgres" Database role using the Command :

postgres=# \password postgres

Step: 5. To Create a  User :

postgres=# CREATE USER koushik WITH password 'redhat';
CREATE ROLE

Step: 6. To Create a Database :

postgres=# CREATE DATABASE mydb WITH OWNER koushik;
CREATE DATABASE

Step: 7. List of the Database present on the Server :

postgres=# \l

Step: 8. To See all Databases :

postgres=# \l
                                  

Step: 9. To Select a Database :

postgres=# \connect mydb

Step: 10. To Create a Table :

postgres=# CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Step: 11. To view Created Tables :

postgres=# \d














Step: 12. Change the Owner of the Table :

postgres=# \connect mydb

mydb=# ALTER TABLE blocks OWNER to koushik;


From the psql Command line Interface :

mydb=# \dt

Step: 13. Drop Database :

postgres@ser4:~$ psql
postgres=# DROP DATABASE mydb;
                            
Step: 14. Restoring the dump Backup of SQL :
   
# sudo -i -u postgres
   
$ psql database_name < /data/backup.sql
   
From Linux/Debian Terminal without login to postgres :

Step: 15. To Create a Database with a User that have full rights on the Database, use the Following Command :

# sudo -u postgres createuser -D -A -P koushik

# sudo -u postgres createdb -O koushik mydb

Note: The first command line creates the user with no database creation rights (-D) with no add user rights -A) and will prompt you for entering a password (-P). The second command line create the database 'mydb with 'koushik' as owner.

Thanks For Visiting on My Blog, For More Tutorials Keep Visiting My Blog