Thread: Uploading and loading

So far I've been relegated to phpPgAdmin to upload my data, but it has
size limits, which I am exceeding.  So I was told to ftp the backup
files, and use PuTTY, which I've been trying to do.  I'm getting
connected, but don't know what to do next.  None of the postgreSQL
commands are available.  Is there something I'm missing?  Any insight
would be most appreciated.

Sean

"Sean" <scene@themountainscene.com> wrote in message
news:42F69C21.3030900@themountainscene.com...
> So far I've been relegated to phpPgAdmin to upload my data, but it has
> size limits, which I am exceeding.  So I was told to ftp the backup
> files, and use PuTTY, which I've been trying to do.  I'm getting
> connected, but don't know what to do next.  None of the postgreSQL

I assume that you have a shell account on the server, and that you have a
command prompt?  Check out the manual, Reference -> PostgreSQL Client
Applications.  The primary application that you are interested in will be
"psql", which can be used interactively, or invoked from the OS command
line.  Perhaps interactive is alround most useful, because the other client
applications have equivalents in SQL.  But the ability to invoke these
applications from the command line makes them capable of being scripted,
which will enable you to automate routine functions, especially those to do
with data loading.

> commands are available.  Is there something I'm missing?  Any insight
> would be most appreciated.
>
> Sean
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

On Monday 08 August 2005 07:41, Sean wrote:
> So far I've been relegated to phpPgAdmin to upload my data, but it has
> size limits, which I am exceeding.  So I was told to ftp the backup
> files, and use PuTTY, which I've been trying to do.  I'm getting
> connected, but don't know what to do next.  None of the postgreSQL
> commands are available.  Is there something I'm missing?  Any insight
> would be most appreciated.

I would suggest that you look into using SSH tunneling. This allows you to
use local clients to access the remote server in a secure manner, and be
able to make use of local files. It eliminates the need to separately
transfer data to the server (say via ftp).

To configure PuTTY:

1) edit or create a new session and fill in whatever you usually fill in
to connect to the remote host
2) go to section Connection > SSH > Tunnels
3) In "Source Port" enter any valid port number (I use 15432)
4) In "Destination" enter "127.0.0.1:5432". NB if your postgresql server
listens on a different port adjust accordingly. And obviously your
postgresql server must be configured to accept connections on 127.0.0.1
(see pg_hba.conf).
5) Make sure that "Local" is selected (as opposed to "Remote") then click
"Add".
6) Save your session settings

NB above procedure is from memory so refer to PuTTY manual where
necessary.

To use:

1) connect using the new session, when the session is established you can
ignore the PuTTY window.

2) If you're using psql, start it using something like:

  psql -h 127.0.0.1 -p 15432 -U username [etc]

A more specific example:

  psql -h 127.0.0.1 -p 15432 -U username databasename -f
fileofcommands.sql

Note you should be using the psql on the *local* machine, ie don't use the
PuTTY window!

3) If you're using pgadmin3 (or any other clients) then simply configure
it use host=127.0.0.1 and port=15432.

--
Jason Wong -> Gremlins Associates -> www.gremlins.biz
Open Source Software Systems Integrators
* Web Design & Hosting * Internet & Intranet Applications Development *