Skip site navigation (1) Skip section navigation (2)

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

From: "Hari Bhanujan" <hbhanujan(at)sbcglobal(dot)net>
To: "'Andreas'" <maps(dot)on(at)gmx(dot)net>,"'Daniel Lerch'" <daniel(at)mountainmeasurement(dot)com>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: possible to access a Linux box db from a Windows box
Date: 2004-10-14 03:40:06
Message-ID: 008501c4b19f$8e0d4fd0$0600a8c0@Bhanujan.local (view raw or flat)
Thread:
Lists: pgsql-novice
pgAdmin to connect to Postgresql (client install on Windows).

Start postgresql with -I option.

Hari

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Andreas
Sent: Wednesday, October 13, 2004 9:37 AM
To: Daniel Lerch
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] 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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings


In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2004-10-14 04:09:16
Subject: Re: VIEWs with aggregate functions
Previous:From: David B.Date: 2004-10-14 02:10:03
Subject: FW: [ pginstaller-Bugs-1000120 ] Install fails on NT4.0 Sp6

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group