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 with PostgreSQL Command.
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
[bash]
# su – postgres
bash$
[/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]
bash$ createdb sparksuppport
CREATE USER
[/bash]
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]
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=>
[/bash]
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]
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
[/bash]
Dropping a database
[bash]
bash$ dropdb sparksupport
DROP DATABASE
[/bash]
Dropping a User
[bash]
bash$ dropuser spark
DROP USER
[/bash]
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.
[bash]
# 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
[/bash]
To drop the group
[bash]
# su – postgres
bash$ psql sparksupport
sparksupport=> delete from pg_group where groname = ‘hackers’;
[/bash]
Backup and Restore database of PostgreSQL Command
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]
bash$ pg_dump sparksupport > db.out
[/bash]
To reload this database:
[bash]
bash$ psql -d newdatabasename -f db.out
or
bash$ cat db.out | psql newdatabasename
[/bash]
To dump all databases
[bash]
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
[/bash]
To dump a database called sparksupport that contains large objects to a tar file:
[bash]
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
[/bash]
Use split:
[bash]
bash$ pg_dump | split -b 1m – filename.dump.
[/bash]
Note: There is a dot (.) after filename.dump in the above command!!
You can reload with:
[bash]
bash$ man createdb
bash$ createdb
bash$ cat filename.dump.* | pgsql
[/bash]
For further referrence on PostgreSQL Command http://www.postgresql.org/docs/8.0/static/index.html
