sql-info.de
DeutschEnglish

3. Administration

3.1. How does PostgreSQL structure its data files? What are all the numbered files and directories in my $PG_DATA/base/ directory?

The numbers refer to the oid of the respective object in the system catalogues.

The directory names correspond to databases, more precisely to the column oid in the table pg_database. Use this query:

  SELECT oid, datname 
    FROM pg_database
ORDER BY oid

to show a complete list.

Within each directory file names correspond to the column relfilenode in the table pg_class:

  SELECT relfilenode, oid, relname 
    FROM pg_class
ORDER BY oid

Note that relfilenode and oid will usually be identical. Some internal operations (REINDEX and CLUSTER) may change an object's filename (as recorded by relfilenode); this column is therefore necessary to maintain the connection to the actual oid.

3.2. How can I view the definition of stored VIEWs, procedures etc.?

Trigger:

Using psql, if you know which table the trigger is defined on:

\dt (name of table)

A basic SQL statement would be:

SELECT pg_catalog.pg_get_triggerdef(t.oid)
  FROM pg_catalog.pg_trigger t
 WHERE t.tgname = 'name_of_trigger'

View:

In psql:

\d name_of_view

Using an SQL statement, there is a useful catalogue view pg_catalog.pg_view, e.g.

SELECT definition
  FROM pg_catalog.pg_views
 WHERE viewname = 'name_of_view'

(Note this query does not take the view's schema name into account).

Function:

In psql:

\df+ name_of_function

Using an SQL statement:

SELECT prosrc 
  FROM pg_catalog.pg_proc 
 WHERE proname = 'name_of_function'

(Note this statement does not take into tablespaces (schemas) into account).

The above queries will work in PostgreSQL 7.3 and later. They should also function in earlier (non-schema aware) versions if the qualifying schema name pg_catalog. is omitted.

3.3. How can I get PostgreSQL to automatically start on boot / stop on shutdown?

This depends on your system and how PostgreSQL was installed. Pre-packaged or "vendor supplied" installations (.rpm, .deb., FreeBSD ports etc.) will usually automatically insert the appropriate startup and shutdown scripts. With manual installations, usually from the source, you will need to do this manually.

Scripts for some systems can be found in contrib/start-scripts.

3.4. How can I assign database ownership to a different user?

From PostgreSQL 8.0 there is an ALTER DATABASE name OWNER TO new_owner command.

Prior to 8.0 it is necessary to directly manipulate the system tables with a query like this:

       UPDATE pg_catalog.pg_database 
          SET datdba=(SELECT usesysid 
                       FROM pg_shadow 
                      WHERE usename='user_name')
        WHERE datname='db_name'
      

(For pre-7.3 installations leave out pg_catalog.)

3.5. How can I see which users are currently connected to a database?

The system catalog view pg_catalog.pg_stat_activity contains information on current connections.

Note that collection of this kind of statistical information can be turned off any may not be available on all installations.

See http://www.postgresql.org/docs/current/static/monitoring-stats.html for further details.

3.6. PostgreSQL causes my Intel Linux system to crash

PostgreSQL runs as an unprivileged program. It does and will not run with root and cannot directly cause any system to "crash".

PostgreSQL - and any other application for that matter - may cause intensive disk or network I/O which may trigger system crashes on "bad" systems or hardware.

If experiencing system crashes / hangs while using PostgreSQL please try and replicate them on a different system to localize the cause of the problem.

For example, the author of this document has experienced massive and repeatable crashes on a Linux system occuring during large data transfers (involving SAMBA, not PostgreSQL); although the crashes always occured during SAMBA operations, the fault was traceable to either the motherboard or the network card.

3.7. How do I restore template1?

This article: http://techdocs.postgresql.org/techdocs/pgsqladventuresep1.php gives a nice explanation. The documentation link referred to in the text should is incorrect, it should be: http://www.postgresql.org/docs/current/static/managing-databases.html.