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
