Activities

March 2008
M T W T F S S
    May »
 12
3456789
10111213141516
17181920212223
24252627282930
31  

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

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>