PostgreSQL

Administration

The database system is managed through the special postgres user. Before you maintain the database on a Debian GNU/Linux system you need to become that user with su - postgres.

New users with regard to PostgreSQL are added using the createuser command. They are removed with destroyuser. New databases are added with createdb while they're removed with destroydb.

Daily Use

To actually work with the database you can either use the SQL monitor program that is shipped. Please call it with

psql [-h
  host] [-p port] database

. You can enter regular SQL commands within this program. Type \h to get a short help at the commands.

Please keep in mind that SQL commands need to be terminated with \g or ; to be executed.

In contrary to most other programs running under Unix comments are prefixed with two dashes --. A hash sign # will result in an error.

Every update or insert is a separate transaction, unless you declare transactions yourself. So use BEGIN TRANSACTION ... COMMIT ... END TRANSACTION to enclose related updates and you should get a speed improvement.

Maintenance

You can dump the whole database or a single table. Use the pg_dump program for this task.

Every once in a while you need to do a "vacuum" of the tables. This is especially needed after a lot of INSERT/UPDATE queries. Vacuum is the Postgres vacuum cleaner. It opens every class in the database, cleans out records from rolled back transactions, and updates statistics in the system cata- logs. The statistics maintained include the number of tuples and number of pages stored in all classes. Running vacuum periodically will increase Postgres's speed in pro- cessing user queries.

Edit /etc/postgresql/postmaster.init and change the settings as indicated by the comments. The relevant variables are POSTGRES_LOG, PGDEBUG and PGECHO.

To allow another user access to your database / tables you have to create the user with the createuser command first. The second stage has to be issued as master of the specific database. You have to allow access. This is done with a special SQL query like the following. Sie man grant for further details.

  GRANT ALL ON table1, table2, table3 TO new-user;

Be warned, you might lose your own permission to access to the database. In that case you have to issue the same command with new-user set to your own username.

Removal/Adding of columns

PostgreSQL does not support builtin ways to add or remove columns at a later time, after the first creation. However you can use the builtin copy mechanism to achieve this.

  SELECT ...  -- select all columns but the one you want to remove
  INTO TABLE new_table
  FROM old_table;
  DROP TABLE old_table;
  ALTER TABLE new_table RENAME TO old_table;

To add a column you could use the same mechanism or make use of the special ADD feature of the ALTER command.

  Syntax:
     alter table  [*] add column  ;
     alter table  [*] rename [column]  to ;
     alter table  rename to 

     ALTER TABLE foo ADD COLUMN bar VARCHAR(100);

From the postgrsql documentation:

ALTER TABLE table DROP [ COLUMN ] column { RESTRICT | CASCADE }

Removes a column from a table. Currently, to remove an existing column the table must be recreated and reloaded:

CREATE TABLE temp AS SELECT did, city FROM distributors;
DROP TABLE distributors;
CREATE TABLE distributors (
    did      DECIMAL(3)  DEFAULT 1,
    name     VARCHAR(40) NOT NULL
);
INSERT INTO distributors SELECT * FROM temp;
DROP TABLE temp;

Changing the database owner

--  Find out the user id (aka usesysid)
SELECT * FROM pg_user;
-- Find out the current settings
SELECT * FROM pg_database;
-- Now update the owner of the respective database
UPDATE pg_database SET datdba=YourIDnumber WHERE datname= 'YourDatabase';