Next Previous Contents

7. Quick Start Guide

Refer also to Quick Installation chapter.

7.1 Creating, Dropping, Renaming Database

You can use the user friendly GUI called 'pgaccess' to create and drop databases, or you can use the command line 'psql' utility.


If you are logged in as root, switch user to 'postgres' :
# xhost +  (To give display access for pgaccess)
# su - postgres
bash$ man createdb
bash$ createdb mydatabase
bash$ man psql
bash$ psql mydatabase
..... in psql press up/down arrow keys for history line editing or \s

bash$ export DISPLAY=<hostname>:0.0
bash$ man pgaccess
bash$ pgaccess mydatabase

Now you can start rapidly BANGING away SQL commands at psql or pgaccess !!

To drop the database do :


bash$ man dropdb
bash$ dropdb <dbname>

It is also possible to destroy a database from within an SQL session by using:
> drop database <dbname>

To rename a database see Backup and Restore

7.2 Creating, Dropping users

To create new users, login as unix user 'postgres'. You can use user friendly GUI tool called 'pgacess' to create, drop users.


bash$ man pgaccess
bash$ pgaccess <database_name>

and click on "Users" tab and then click Object|New or Object|Delete

You can also use command line scripts. Use the shell script called 'createuser' which invokes psql


bash$ man createuser
bash$ createuser <username>
bash$ createuser -h host -p port -i userid <username>

To drop a postgres user, use shell script 'destroyuser' -


bash$ man destroyuser
bash$ destroyuser

7.3 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 <database_name>
..... in psql press up/down arrow keys for history line editing or \s

psql=> insert into pg_group (groname, grosysid, grolist)
psql=> values ('posthackers', '1234', '{5443, 8261}' );
INSERT 58224
psql=> grant insert on foo to group posthackers;
CHANGE
psql=>

The fields in pg_group are: groname The group name. This name should be purely alphanumeric; do not include underscores or other punctuation.

grosysid The group id. This is an int4, and should be unique for each group.

grolist The list of pg_user IDs that belong in the group. This is an int4[].

To drop the group:


bash$ su - postgres
bash$ psql <database_name>
..... in psql press up/down arrow keys for history line editing or \s

psql=> delete from pg_group where groname = 'posthackers';

7.4 Create, Edit, Drop a table

You can use user friendly GUI tool 'pgaccess' or command line tool 'psql' to create, edit or drop a table in a database.


bash$ man pgaccess
bash$ pgaccess <database_name>

Click on Table | New | Design buttons.
bash$ man psql
bash$ psql <database_name>
..... in psql press up/down arrow keys for history line editing or \s

At psql prompt, give standard SQL statements like 'create table', 'alter table' or 'drop table' to manipulate the tables.

7.5 Create, Edit, Drop records in a table

You can use user friendly GUI tool 'pgaccess' or command line tool 'psql' to create, edit or drop records in a database table.


bash$ man pgaccess
bash$ pgaccess <database_name>

Click on Table | < pick a table > | Open buttons.
bash$ man psql
bash$ psql <database_name>
..... in psql press up/down arrow keys for history line editing or \s

At psql prompt, give standard SQL statements like 'insert into table_name', 'update table_name' or 'delete from table_name' to manipulate the tables.

7.6 Switch active Database

You can use user friendly GUI tool 'pgaccess' or command line tool 'psql' to switch active database.


bash$ man pgaccess
bash$ pgaccess <database_name>

Click on Database | Open buttons.
bash$ man psql
bash$ psql <database_name>
..... in psql press up/down arrow keys for history line editing or \s

psql=> connect <database_name> <user>

7.7 Backup and Restore database

PostgreSQL provides two utilities to back up your system: pg_dump to backup individual databases, and pg_dumpall to back up all the databases in just one step.


bash$ su - postgres
bash$ man pd_dump
bash$ pd_dump <database_name> > database_name.pgdump

and can be restored using:
bash$ cat database_name.pgdump | psql <database_name>

This technique can be used to move databases to new locations, and to rename existing databases.

WARNING: Every database should be backed up on a regular basis. Since PostgreSQL manages its own files in the file sysetem, it is not advisable to rely on system backups of your file system for your database backups; there is no guarantee that the files will be in a usable, consistent state after restoration.

BACKUP LARGE DATABASES: Since Postgres allows tables larger than the maximum file size on your system, it can be problematic to dump the table to a file, because the resulting file likely will be larger than the maximum size allowed by your system. As pg_dump writes to stdout, you can just use standard unix tools to work around this possible problem:

Use compressed dumps:


bash$ pg_dump <database_name> | gzip > filename.dump.gz

reload with:
bash$ createdb <database_name>
bash$ gunzip -c filename.dump.gz | psql <database_name>

or
bash$ cat filename.dump.gz | gunzip | psql <database_name>

Use split:


bash$ pg_dump <database_name> | 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 <database_name>
bash$ cat filename.dump.* | pgsql <database_name>

Of course, the name of the file (filename) and the content of the pg_dump output need not match the name of the database. Also, the restored database can have an arbitrary new name, so this mechanism is also suitable for renaming databases.

To dump all the databases in PostgreSQL use pg_dumpall


bash$ man pg_dumpall
bash$ pg_dumpall -o > db.out
To reload:
bash$ psql -e template1 < db.out

7.8 Security of database

See the chapter on PostgreSQL Security.

7.9 Online help

It is very important that you should know how to use online help facilities of PostgreSQL, since it will save you lot of time and provides very quick access to information.

See the online man pages on various commands like createdb, createuser, etc..


bash$ man createdb

See also online help of psql, by typing \h at psql prompt


bash$ psql mydatabase
psql> \h

Tip: In psql press up/down arrow keys for history line editing or \s

7.10 PostgreSQL Documentation

More questions, read the fine manuals of PostgreSQL which are very extensive. PostgreSQL documentation is distributed with package. See the 'User's Guide', 'Programmer's Guide', 'Administrator's Guide' and other manuals.


Next Previous Contents