Commonly Asked DataBase Questions

The answets provided below apply to PostgreSQL. Other databases we've used (Ingres, Oracle and Sybase) are similar, but the names of the specific procedures and syntax vary, so please check the vender's documentation for changes.

How Do I Create a New Database?

There are potentially three parts to this: 1) Creating the database, 2) loading the database with stored data, and 3) Creating an ODBC link

The example below assumes you're creating a database named db1. Change db1 to your database name appropriately.

To create the database, log in as the postgres user and type:

createdb db1

To load the database from an archive file type:

psql db1 < filename > /dev/null

The output is directed to /dev/null because otherwise the simple monitoring messages overwhelm the output and you won't see any actual errors. Typically there are none, though you will be notified about the creation of indices on objectdefn, platformchar and some other tables. These messages are OK; the indicies are in place to preserve data intrity and improve performance. The only common error we've seen occurs when the dumped database contains a database user that the new database doesn't recognize. The resulting error reads as follows:

FATAL 1: user "forcesa" does not exist

In this case you can do one of two things:

1) Create a user by this name. To do this type:

      psql db1

      create user forcesa;

      \q

Now destroy, recreate and reload the data by typing:

      dropdb db1; createdb db1; psql db1 < filename > /dev/null

2) Remove references to the unknown user from the input file. Edit the file and remove all lines that look like:

    \connect - username

    Then reload the database by typing:

      dropdb db1; createdb db1; psql db1 < filename > /dev/null

Finally, put an entry in the ODBC configuration file for the new database. For PostgreSQL on linux this file is named ~/.odbc.ini. The entry for each new database should look like this:

[db1]

Driver=/usr/local/pgsql/lib/libpsqlodbc.so

Database=db1

Servername=localhost

Username=postgres

ReadOnly=0

Change the driver as appropriate for your machine.



How Do I Delete an Old Database?

Log in as the DBA (usually postgres) and type:

dropdb db1

Then remove the odbc references in the .odbc.ini files for each user to prevent larer confusion.


How Do I Archive (or Restore) A Database?

To archive a database log in as the DBA (usually postgres) and type:

pg_dump dba > filename

This will put the schema and data for the db1 database into a file that can be compressed, transferred to another machine or kept as a backup. To restore the database from this file, first delete it (if it exists), create it, and reload from the file as follows:

dropdb db1

createdb db1

psql db1 < filename > /dev/null

As before, the output is directed to /dev/null because otherwise the simple monitoring messages overwhelm the output and you won't see any actual errors. Some common errors are discussed in the section on Creating a New Database, above.



How Can I Transfer ALL Databases Between Two Installations?

Often it's desirable to transfer the contents of all databases between two computers. While the above methods will work to transfer the databases one at a time, there's a more convenient way to effect this transfer. But this procedure is only appropriate when you don't want to preserve any databases on the target computer.

Steps (I'll assume you want to use a transfer file named /tmp/databases, if not just change the name):

  1. (On the source computer) Dump all databases in one step using the command:

"pg_dumpall -U postgres > /tmp/databases; gzip /tmp/databases"
Note that no database is specified on the command line.
  1. Move the "/tmp/databases.gz" file to the target computer. Unzip it.

(e.g. "gunzip /tmp/databases.gz" on the target computer)
  1. Remove all existing databases on the target computer EXCEPT databases starting with "postgres*" or "template*". These databases are system databases and should not be dropped. To find a list of all databases currently on the target computer type:

psql -U postgres
\l    (This means type a backslash followed by the letter "l" at the psql prompt).
This will generate a list of databases currently hosted on this computer. The databases that you intend to replace with this transfer need to be individually deleted. To do this type the following at a terminal prompt (NOT in psql):
dropdb -U postgres (database name)
  1. Now you're ready reload the databases. This is a one-liner:

psql -U postgres < /tmp/databases > /dev/null
Again, note that no database is specified on the command line.



How Do I Compare Database Schemas?

It's often important to compare the structure (schema) of databases to verify integrity. There are two methods for this. The first uses the db_tables_and_columns described in the scripts overview to list the tables and names and datatypes of columns alphabetically. The results of this script against each database is stored in two files and then these files are visually compared. This is quick and catches most problems, but does not catch tmany possible database schema discrepancies, including:

To identify these errors rely on the DBA tools provided with the DBMS, such as pg_dump for postgresql.

To use the quick check, log in as the forces user (NOT the postgres user) and type:

db_tables_and_columns db1 > /tmp/db1.schema

db_tables_and_columns db2 > /tmp/db2.schema

tkdiff /tmp/db1.schema /tmp/db2.schema

For the more detailed check, try:

db_tables_and_columns -full db1 > /tmp/db1.schema

db_tables_and_columns -full db2 > /tmp/db2.schema

tkdiff /tmp/db1.schema /tmp/db2.schema