Installing PostgreSQL and Usefull commands

Installing PostgreSQL

Before installing PostgreSQL, make sure that you have the latest information from the Debian repositories by updating the apt package list with:

sudo apt-get update
sudo apt-get install postgresql-9.4 postgresql-client-9.4

ps -ef | grep postgre

You should see something like this on the terminal:

postgres 32164     1  0 21:58 ?        00:00:00 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/   postgresql/9.4/main -c config_file=/etc/postgresql/9.4/main/postgresql.conf
postgres 32166 32164  0 21:58 ?        00:00:00 postgres: checkpointer process
postgres 32167 32164  0 21:58 ?        00:00:00 postgres: writer process
postgres 32168 32164  0 21:58 ?        00:00:00 postgres: wal writer process
postgres 32169 32164  0 21:58 ?        00:00:00 postgres: autovacuum launcher process
postgres 32170 32164  0 21:58 ?        00:00:00 postgres: stats collector process 

Success! PostgreSQL has been successfully installed and is running.

Accessing the PostgreSQL Database

On Debian, PostgreSQL is installed with a default user and default database both called postgres. To connect to the database, first you need to switch to the postgres user by issuing the following command while logged in as root (this will not work with sudo access):

su - postgres

ou now should be logged as postgres. To start the PostgreSQL console, type psql:

psql

Done! You should be logged on the PostgreSQL console. You should see the following prompt:

psql (9.4.2)
Type "help" for help.

postgres=# 

To exit the psql console just use the command \q.

Creating a New Database

PostgreSQL is set up by default with authenticating roles that are requested by matching system accounts. (You can get more information about this at postgresql.org). It also comes with the assumption that a matching database will exist for the role to connect to. So if I have a user called test1, that role will attempt to connect to a database called test1 by default.

You can create the appropriate database by simply calling this command as the postgres user:

createdb test1

The new database test1 now is created.

Connecting to PostgreSQL with the New User

Let's assume that you have a Linux account named test1, created a PostgreSQL test1 role to match it, and created the database test1. To change the user account in Linux to test1:

su - test1

Then, connect to the test1 database as the test1 PostgreSQL role using the command:

psql

Now you should see the PostgreSQL prompt with the newly created user test1 instead of postgres.

Useful Commands

• \?: Get a full list of psql commands, including those not listed here.
• \h: Get help on SQL commands. You can follow this with a specific command to get help with the syntax.
• \q: Quit the psql program and exit to the Linux prompt.
• \d: List available tables, views, and sequences in current database.
• \du: List available roles
• \dp: List access privileges
• \dt: List tables
• \l: List databases
• \c: Connect to a different database. Follow this by the database name.
• \password: Change the password for the username that follows.
• \conninfo: Get information about the current database and connection.

For password less login:

sudo -u user_name psql db_name

To reset the password if you have forgotten:

ALTER USER "user_name" WITH PASSWORD 'new_password';

Also you can find details http://www.postgresql.org/docs/9.1/static/sql-commands.html

ref:https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-9-4-on-debian-8