Please read the databases page and PostgreSQL introduction first.

How can I copy a large number of records into my database?

There are two methods:

• Prepare a file of data in some character-separated format, for instance where each line represents a whole record and fields are separated using the '|' character. Then add the line to the top:

  COPY tablename FROM stdin USING DELIMITERS '|';
  

  and at the bottom, add the line:

  \.
  

  Then do:

  psql databasename < file_name
  

• Change the delimiter to Tab, eg using:

  tr \| "\t" < my_file > new_file
  

  Then, in psql, do:

  \copy table_name from 'new_file'
  

Remember to convert any DOS/Windows files to UNIX format by doing:

dos2unix file_name

I don't have permission to create database?

Users only have permission to create tables, not databases. This is why the database was created by us when your account was setup.

How do I copy text files on web to postgreSQL database table?

This answer includes a fictional user name abc99, Postgress password wibble, filename myfile, and table mytable.

For this, you will have to use the Linux command line. Change to the directory containing your text file. If you don't already have the file, download it with your favourite browser.

In order for postgres to read the file, you need to convert it to a format the database will understand. At the moment, it looks like you have padding spaces between the fields. The best format to use is tab separation. You can easily convert the file with:

% grep -v ^# tab:myfile | perl -p -e 's/ +/\t/g' > myfile.tsv

Check your postgres environment variables are set correctly. If you've followed our standard advice, you should have two standard files: ~/.pg containing:

setenv PGHOST db
setenv PGPORT 5432
setenv PGUSER abc99 
setenv PGDATBASE abc99 
unsetenv PGPASSWORD

and ~/.pgpass with the contents:

db:*:*:abc99:wibble

With these files, source ~/.pg to start using the database. If you want this to happen automatically on login, add source ~/.pg to the end of your ~/.cshrc file.

Having set up the Postgres environment variables and ~/.pgpass file, connect to the database simply by typing:

% psql
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66]

   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: abc99 

abc99=>

abc99=>\copy mytable from myfile.tsv
Successfully copied.

Can I transfer the database I developed at home to DoC?

A pg_dump from your home system would install ok. A tar of the postgres files would probably not. If you can do a pg_dump and then bring the resulting file in to DoC, you would be able to restore it yourself. eg

home % pg_dump my_db > my_db
home % gzip my_db
home % cp my_db.gz /mnt/floppy/


doc % setenv PGUSER user {and so on}
doc % zcat /mnt/floppy/my_db.gz | psql my_db

On accessing my table I get permission denied?

Your problem might be some environment variables left over from a lab session.

Try (tcsh):

setenv | grep PG

unsetenv PGPASSWORD

On accessing my table I get permission denied?

This is a common problem, where people have used the standard lab setup for postgres instead of using the account that was created for them, and then created a table. This can be checked by entering psql and using the \d describe command to list your tables. If the table you're selecting from is owned by lab, that's the problem.

The simplest fix is to drop the offending table and create it again. You did keep the SQL create statement, didn't you? If however the data is hard to recreate, try setting your postgres settings to use the lab user, pg_dump the database to a file and then drop the table, then edit the dump file to delete everything except the SQL create table and copy data sections, then set your postgres settings to use your username and then use psql < dump_file. Unless there are syntax errors, this should recreate the table.

Alternatively, the following query (run by a DBA, i.e. a postgres wizard in CSG) fixes the problem. User foobar. Connect to database foobar:

update pg_class set relowner = (select usesysid from pg_shadow where usename = 'foobar') where relowner = '99';

I got an error message from "PostgreSQL backend".

The full error message was:

NOTICE: Message from PostgreSQL backend:
        The Postmaster has informed me that some other backend
        died abnormally and possibly corrupted shared memory.
        I have rolled back the current transaction and am going
        to terminate your database system connection and exit.
        Please reconnect to the database system and repeat your query.

It's not something we can prevent, and it does not result in data corruption. It's simply the result of a large number of inexperienced users sending unexpectedly large queries to the database, and the database doing it's best to maintain everyone's data in a consistent state.

As the message says, all you have to do is reconnect and try again. If the message persists for a long term, please email help@doc and report the problem.

How can find all the tables that exist in my database?

The following SQL query will retrieve the list of tables:

SELECT relname FROM pg_class WHERE relname NOT LIKE 'pg_%' AND relkind = 'r';

Alternatively, inside psql the \d command with no arguments lists all tables, or with a table name as argument describes that table.

Can a script running on my home PC access a DoC db?

db.doc is available from offsite, but only via SSL. So make sure that your Postgres client is SSL capable and has this turned on.

Alternatively ssh into any DoC machine (for example, one of the vertexes) and run your script there.

Can I make the data in my tables viewable by others?

You can grant whatever access privileges on your data that you want, so other people can see the data if you let them.

The following SQL query will give read access to everyone:

GRANT select on [table] to PUBLIC;

Is the C++ library called libpq++ installed?

All the postgresql client-side libraries (including libpq++) are installed on all Linux machines (in the standard place: /usr/lib). The header files you will need are also installed as standard in /usr/include/pgsql/, so you can #include <pgsql/libpq++.h> for example.

Documentation: /usr/share/doc/postgresql-docs-7.3.4/index.html

How can I make a copy of my database?

Try using the pg_dump command. With your postgres settings set up, i.e. so that you can already access your database via psql without being prompted for information:

pg_dump > sql.dump.filename

© CSG / 2005