PostgreSQL Command line Tips

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

Leave a Reply

Your email address will not be published. Required fields are marked *