United States Asia Pacific United Kingdom France Germany Hong Kong India Italy Japan Korea

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$ man destroydb (for older versions of pgsql) 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 dropuser bash$ man destroyuser (older versions of pgsql) 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 To dump all databases - bash$ man pg_dumpall bash$ pg_dumpall -o > db_all.out To reload (restore) a database dumped with pg_dump: bash$ cat database_name.pgdump | psql <database_name> To reload (restore) all databases dumped with pg_dumpall: bash$ psql -e template1 < db_all.out 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 an 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. Backup LARGE Objects: Large objects are not handled by pg_dump. The directory contrib/pg_dumplo of the Postgres source tree contains a program that can do that. FILESYSTEM BACKUP : You can use the linux OS tools and commands to backup the entire database. But you must completely shutdown the postgresql database server before doing backup or restore with this method. The filesystem backup or restore may be 2 to 3 times faster than the pg_dump command, but only disadvantage is that you must completely shutdown the database server. It is very highly recommended that you use backup and restore tools like Arkeia, Bru which are given in Mic-Lin analogy list sub-heading "Backup and Restore Utility" at http://aldev.8m.com and mirror sites are at webjump, angelfire, geocities, virtualnet, bizland, theglobe, spree, infoseek, bcity, 50megs .The OS commands to use are - bash$ man tar bash$ tar -cvf backup.tar /usr/local/pgsql/data or using compression bash$ tar -zcvf backup.tgz /usr/local/pgsql/data INCREMENTAL BACKUP : This is in todo list and will appear in future release of PostgreSQL. 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 Creating Triggers and Stored Procedures To create triggers or stored procedures, First run 'createlang' script to install 'plpgsql' in the particular database you are using. If you want by default then install it in 'template1' and subsequent created databases will be clones of template1. See 'createlang' web page in User guide at /usr/doc/postgresql-7.0.2/user/index.html. bash$ man createlang bash$ createdb mydb bash$ export PGLIB=/usr/lib/pgsql bash$ createlang plpgsql mydb bash$ createlang plpgsql template1 See also the trigger, stored procedures examples in Examples RPM . One sample code from examples RPM: create function tg_pfield_au() returns opaque as ' begin if new.name != old.name then update PSlot set pfname = new.name where pfname = old.name; end if; return new; end; ' language 'plpgsql'; create trigger tg_pfield_au after update on PField for each row execute procedure tg_pfield_au(); Another trigger example sample code: create trigger check_fkeys_pkey_exist before insert or update on fkeys for each row execute procedure check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2'); You must also install the TEST package - postgresql-test-7.0.2-2.rpm and read the example sql scripts at - /usr/lib/pgsql/test/regress/sql To see the list of triggers in database do - bash$ psql mydb psql=> \? psql=> \dS psql=> \d pg_trigger psql=> select tgname from pg_trigger order by tgname; To see the list of functions and stored procedures in database do - bash$ psql mydb psql=> \? psql=> \dS psql=> \d pg_proc psql=> select proname, prosrc from pg_proc order by proname; psql=> \df 7.11 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. The release docs are at http://www.postgresql.org/users-lounge/docs. Next Previous Contents