[Nottingham] PostqreSQL Access help needed

[Duncan John Fyfe]

On Thu, 30 Dec 2004, Tony Shaw wrote:

> Hi to one and all,
> Can anyone tell me how to create/setup a database using 'PostgreSQL Access' 
> via MDK 9.2 as a root user. I would also appreciate any other help/info that 
> you can give on this matter. I hope there is now need to create the databases 
> as a non-root user, as it would mean that I would have to create one as I 
> only use a 'root' user on my system. I like to be able to do what I like on 
> my machine with out messing around first  ;-)

Doing everything as root is silly but your choice.

For pgaccess help try: http://www.pgaccess.org/
For Postgresql help start with
 	http://www.postgresql.org/docs/manuals/
and	http://www.commandprompt.com/ppbook/

What are you trying to do and where are you stimmied with pgaccess ?

You may be able to run the following commands directly as root otherwise
wrap them with "su -c '<command>' postgres".

To create a non-postgres user whom may manage the database:
 	createuser -a -d <username>

To create a database:
 	createdb <dbname>

My prefered route is:

Write your sql statements in one or more files (I usually write
one per table (+audit table if I need one) named after the table with
a .sql on the end eg foo.sql, bar.sql ...) using your favourite editor.

Now write/copy a script into the directory where you put all those
files:
--- CUT HERE ---
#!/bin/sh

db=<dbname>
tmpfile=_tmp.sql
logfile=_psql.log
dropdb $db
# createdb fails if it follows dropdb too closely
sleep 2
createdb $db

order="space separated list of all the sql files in the order they must
be created foo.sql bar.sql ..."

rm -f $logfile
for f in $order; do
         echo "++Processing $f"
 	rm -f $tmpfile
 	[ ! -f "$f" ] && echo "$f off." && break;

 	# Must remove tabs from input becasue
 	# psql chokes on them
 	# I use a temporary file so
 	# errors include line numbers

 	cat $f | tr -s '\t' ' ' > $tmpfile
 	psql -f $tmpfile $db >> $logfile
 	[ $? -gt 0 ] && echo "ABEND" && break;
 	rm -f $tmpfile
done
--- CUT HERE ---


Now you have some nice files which help document your db and it probably
took you half the time to write because you were using your favorite
editor.

Also, get hold of PostgreSQL Autodoc <http://www.rbt.ca/autodoc/>,
it is fandabidozy.

Have fun,
Duncan