PostgreSQL is an object-relational database management system (ORDBMS) based on POSTGRES, Version 4.2, developed at the University of California at Berkeley Computer Science Department. POSTGRES pioneered many concepts that only became available in some commercial database systems much later.

PostgreSQL is an open-source descendant of this original Berkeley code. It supports a large part of the SQL standard and offers many modern features:

  • complex queries
  • foreign keys
  • triggers
  • views
  • transactional integrity
  • multiversion concurrency control

You can login to shell by


# su - postgres

bash$

How to create a database

Normally, the database user who executes this command becomes the owner of the new database. However a different owner can be specified via the -O option, if the executing user has appropriate privileges.


bash$ createdb sparksuppport

CREATE USER

Accessing a Database

Once you have created a database, you can access it by running the PostgreSQL interactive terminal program, called psql, which allows you to interactively enter, edit, and execute SQL commands.


bash$ psql sparksupport

Welcome to psql 8.0.13, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms

       \h for help with SQL commands

       \? for help with psql commands

       \g or terminate with semicolon to execute query

       \q to quit

sparksupport=>

Creating a User

createuser creates a new PostgreSQL user. Only superusers (users with usesuper set in the pg_shadow table) can create new PostgreSQL users, so createuser must be invoked by someone who can connect as a PostgreSQL superuser.

Being a superuser also implies the ability to bypass access permission checks within the database, so superuserdom should not be granted lightly.


bash$ createuser spark

Shall the new user be allowed to create databases? (y/n) n

Shall the new user be allowed to create more new users? (y/n) n

Dropping a database


bash$ dropdb sparksupport

DROP DATABASE

Dropping a User


bash$ dropuser spark

DROP USER

Creating, Dropping Groups

Currently, there is no easy interface to set up user groups. You have to explicitly insert/update the pg_group table. For example.


# su - postgres

bash$ psql sparksupport

..... in psql press up/down arrow keys for history line editing or \s

sparksupport=> insert into pg_group (groname, grosysid, grolist)

sparksupport=> values ('hackers', '1234', '{5443, 8261}' );

INSERT 58224

sparksupport=> grant insert on foo to group hackers;

CHANGE

To drop the group


# su - postgres

bash$ psql sparksupport

sparksupport=> delete from pg_group where groname = 'hackers';

Backup and Restore database

pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers).

To dump a database:


bash$ pg_dump sparksupport > db.out

To reload this database:


bash$ psql -d newdatabasename -f db.out

or

bash$ cat db.out | psql newdatabasename

To dump all databases


bash$ man pg_dumpall

bash$ pg_dumpall -o > db_all.out
To reload (restore) all databases dumped with pg_dumpall:
bash$ psql -e template1 < db_all.out
Backup large databases

To dump a database called sparksupport that contains large objects to a tar file:


bash$ pg_dump -Ft -b sparksupport > db.tar
Reload with :
bash$ createdb

bash$ gunzip -c filename.dump.gz | psql

Or

bash$ cat filename.dump.gz | gunzip | psql

Use split:


bash$ pg_dump  | split -b 1m - filename.dump.

Note: There is a dot (.) after filename.dump in the above command!!

You can reload with:


bash$ man createdb

bash$ createdb

bash$ cat filename.dump.* | pgsql

For further referrence http://www.postgresql.org/docs/8.0/static/index.html

VN:F [1.9.6_1107]
Rating: 5.0/10 (1 vote cast)
VN:F [1.9.6_1107]
Rating: +1 (from 1 vote)
Postgresql Command line Tips , 5.0 out of 10 based on 1 rating
Share and Enjoy:
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • Live
  • StumbleUpon
  • Twitter
  • Yahoo! Buzz
  • Reddit
  • Technorati