Refer also to Quick Installation chapter.
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
To drop the database do :
bash$ man dropdb bash$ man destroydb (for older versions of pgsql) bash$ dropdb <dbname>
> drop database <dbname>
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>
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
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=>
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';
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>
bash$ man psql bash$ psql <database_name> ..... in psql press up/down arrow keys for history line editing or \s
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>
bash$ man psql bash$ psql <database_name> ..... in psql press up/down arrow keys for history line editing or \s
You can use user friendly GUI tool 'pgaccess' or command line tool 'psql' to switch active database.
bash$ man pgaccess bash$ pgaccess <database_name>
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>
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
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>
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.
See the chapter on PostgreSQL Security.
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
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
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();
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
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.