Re: possible to access a Linux box db from a Windows box

Hi Daniel,
I'm a novice too but allready for some time ;)
Sorry for the long rant.

Daniel Lerch wrote:

> It occured to me that I might have an easier time learning PostgreSQL
> if I start off with a GUI instead of just command line.
> But I access the database over our LAN -- I'm on a Windows 2000 box,
> and the psql database is on a Linux box. I've been accessing it by
> running PuTTY.
>
> So, my question is - is there a GUI program I can run off my Windows
> 2000 box that will allow me to access myt psql database on the Linux
> box? Or do I have no choice but run the GUI on the Linux box?

First you've got to understand what PostgreSQL does.
It stores data in a sensible way and provides a query inteface. This
interface can't be accessed directly by mere humans.
Compare it to the JET database engine that MSAccess uses. JET is there
in the background and you probaply never heared of it though it is
actually a separate piece of software.

There are 2 type of actions that you need a client for.
1) database maintenance
* creating/dropping databases, tables, users
* making backups
* vacuuming the database
2) data maintenance
* editing, deleting records
* select and combine records for analysis or export to other programs

The clients are as a rule only good at one type of task.

We need client-tools like psql or PGAdmin that actually know how to talk
in the DB server's language. Next to allways this talking happens over a
LAN or at least over the LAN mechanisms in your local computer as if the
server were on a remote box.
So don't worry about the LAN. You just have to tell Postgres to listen
to the LAN and allow remote connections.
To do this you need to be the DB administrator.

Another possibility is to use ODBC to access Postgres by a standardized
interface. You will then need an ODBC driver for Postgres on your
Windows computer. With it Windows can connect many programs to PG
without them actually knowing what database they are talking to. They
just need to know ODBC.
Here we get MS-Access, Excel, and others back into the game. Access can
link to tables that are stored in PG and you can edit them in Access
with some restrictions.
You can't create or alter tables in a visual way. Though it is possible
with SQL commands that you send the server from withing Access which
shouldn't be your problem at first.
Access is nice if your tables are all created in the way you need them
and you'll just search, edit and delete records.

Someone mentioned phpPGAdmin. It resembles PGAdmin but needs a running
webserver with installed PHP. That'd add a lott more complexity to your
learning, I'd say. phpPGAdmin is surely nice if the PG is on a secure
server that doesn't allow connections from your box but the provider let
a www-server connect where you can put own PHP scripts.

Learning SQL is the way to go and it can be rather easy, too.
Often it is even easier than using a GUI for the managing stuff like
creating tables.
I suppose you used Access to store data in so you probaply know how to
create a table in Access. It's a good number of chosing combo-box values
and checking check-boxes.
In SQL you do:
CREATE TABLE tab1 (
tab1_id int4 not null,
name varchar(100),
adress varchar(200,
PRIMARY KEY (tab1_id)
);

You could type this directly into psql's command line but that isn't
convenient.
Use your favourite editor and store the command in the file "tab1.sql".
Then run it on the putty command line through psql.
psql daniels_database < tab1.sql

If you are allowed to access the servers drive from your computer by a
networkdrive then you could use any windows texteditor you like.

To learn the syntax you can look it up in postres' manual or you can try
to mouseclick what you want in PGAdmin3. PGAdmin3 shows what SQL
command it used. There you can watch a "professional" at work, but it
does all in the most formalistic way without omitting defaults and other
decorations.

I'd advise to
0) Get your PG server up and allow LAN connections
1) start with PGAdmin3 and the PG manual that is included into
PGAdmin3, too.
2) Rather sooner than later, I'd move to the editor + psql way.
3) After creating the db tables you can settle back to MS-Access over
ODBC to search and edit data if Access is available in your production
environment.
4) before you put important stuff into the db learn how to
automatically make regular backups with pg_dump & cron
5) in a production environment you'll need indices on foreign key
collumns to speed things up.

after you get a grip on that, you can procede to the aspects that lift
PostgreSQL over Access like
* stored procedures in PG's own language
* triggers
* object inheritance

Books on database theory are important in some way but they tend to
demotivate by utter dullness.
It's often like starting to learn how to ride a car by studying how to
mine for iron ore.
Btw. database theory, SQL and managing a PostgreSQL server are 3
different topics that only overlap a bit.