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$ 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 destroyuser 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
bash$ cat database_name.pgdump | psql <database_name>
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
bash$ createdb <database_name> bash$ gunzip -c filename.dump.gz | psql <database_name>
bash$ cat filename.dump.gz | gunzip | psql <database_name>
Use split:
bash$ pg_dump <database_name> | split -b 1m - filename.dump.
bash$ man createdb bash$ createdb <database_name> bash$ cat filename.dump.* | pgsql <database_name>
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
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
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.