Useful PostgreSQL Commands

This scripts will create  postgres database with specified owner and resore a sql dump in to it

#The super user postgres  login along woth the password ‘admin’ in psql console

$psql  -h localhost -p 5432 <postgres> “admin”

#creating a database named ‘testdb’

postgres=# CREATE USER testuser;

postgres=# ALTER USER  testuser PASSWORD ‘letmein’;

#create a database named ‘mydb’ owned by the given user

postgres=# CREATE DATABASE mydb  WITH OWNER testuser;

#restoring dump to that database

postgres=# source<path/to/sql file>;

Some external commands 

1, Dumping  only data
$pg_dump -a -d <DB> -f <Filename.sql> -U <user> -p <passwd>

2, Dump only database schema

$pg_dump -s -d <DB> -f <Filename> -U <user> -p <passwd>
3, Complete   database dump

$pg_dump  -d <DB> -f <Filename>  -U <user> -p <passwd>

4,  To dump all databases

$ pg_dumpall > db.out

5, To reload this database use, for example:

$ psql -f db.out postgres

6, List the databases

$psql -l