Hi Rommel,

I thought I'd throw a little more input in, as an alternative to psql.
I use Ruby on Rails with Postgres, and for testing, it drops the
testing database, recreates it and loads the structures dumped from the
development database, before running tests - to ensure a clean test
every time (it also has a tool for re-building the development database
programatically, which one can run before all this).

So, Rails has to solve the same problem you have, and it doesn't use
psql to do it. My investigation into it's method is as so:

1) Initiate a connection to Pg server via Pg network port.
1.a) Dump dev database structures to file
2) Drop database 'test'
3) Create database 'test' (with correct parameters)
4) Initiate a new connection to Pg server via port, request connection
to newly created 'test' database
5) Load dev database structures from file to 'test' database.

The key obviously is reconnecting over the port connection and
specifying the database you wish to connect to - I think this is how
PGAdmin does the job too (when you click on "SQL" you're making a port
level connection to the server, specifying which database you want to
talk with - from what I can tell, Pg doesn't let you operate across
databases, as MS SQL does - one connection = one database).

I'm not nearly as expert as others on the list, so any corrections to
the above analysis would be welcome.

Sincerely,

Steve


At 04:49 AM 2/27/2007, pgsql-sql-owner@postgresql.org wrote:
>Date: Mon, 26 Feb 2007 18:02:38 -0400
>From: "Rommel the iCeMAn" <icecrew@gmail.com>
>To: <pgsql-sql@postgresql.org>
>Subject: Re: Change Default Database
>Message-ID: <00d301c759f1$d61c2e70$b364a8c0@theport.com>
>
>I seem to be blundering a lot today! I thought I was replying to the
>entire
>list, didn't realize I replied to one person :-)
>
>Nothing was wrong with my script, I assumed that since it was
>generated by
>pgAdmin that I could run it inside pgAdmin. It works perfectly when I
>run it
>using psql. It is very much like SQL Server's osql command line tool.
>Thanks
>to everyone that helped ...
>
>So I've learnt three valuable lessons:
>
>1. It's better to execute scripts using the command line tool 'psql'.
>
>2. Make sure I'm replying to the list and not to an individual.
>
>3. Do not post HTML messages!!! :-))
>
>
>Thanks again everyone.
>
>Rommel Edwards
>Software Developer,
>Barbados, Caribbean.


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Hi Steve,

Thanks for the alternative method, I will give that a try as well. It's nice
to know there is more than one road to reach a destination.

Thanks again,
Rommel Edwards
Software Developer,
Barbados, Caribbean.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match