Обсуждение: psql, pg_dumpall, remote server questions...
i've been having a dandy day reading the pgsql docs... and i think i learned some things, but i want to bounce them off of ya'll to see if i'm right or out to lunch. here are the facts: 1. current production server: pgsql 7.3.x on webhost. 2. current dev server: 7.4.6 on laptop (w/ net access). 3. future production server: 8.0.3 on on intranet server (i can vpn in, however, i don't think it has access to the net - i coul dbe wrong and i'll talk to the admin next time i see him). i think my first step is going to be like so... clear bogus data in current dev server (easy enough), backup data in current prod server (using pgsql from newer db version) and restore data to current dev server. i've been reviewing psql and pg_dump and i'm not able to understand how this works just yet. i think i log log into pgsql like so... psql dbname -h [full url to db on webhost here - is this possible?] -U [username goes here] -p [port goes here] will this work? can i access my webhost in this manner (putting url in as host option)? tia... __________________________________ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
On Sep 12, 2005, at 7:46 PM, <operationsengineer1@yahoo.com> <operationsengineer1@yahoo.com> wrote: > i think i log log into pgsql like so... > > psql dbname -h [full url to db on webhost here - is > this possible?] -U [username goes here] -p [port goes > here] > > will this work? can i access my webhost in this > manner (putting url in as host option)? The -h parameter is not a URL, it is just a host name (or IP address). Yes, you can run psql (and other PostgreSQL utilities) this way, but it assumes the proper permissions have been setup in the pg_hba.conf file to allow it (see http://www.postgresql.org/docs/8.0/ interactive/client-authentication.html). SSH access may be an alternative for you. I put a tip here about setting up SSH tunneling for PostgreSQL: http://pgedit.com/tip/postgresql/ssh_tunneling John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
John, nice concise article! i will give it a shot (hopefully, this feature is enabled on my webhost). i use pgadmin3 to admin my dev db. if i use pgadmin3 to connect to the remote db, how can i be sure that the following migration rule of thumb is followed: 1. always use the most up to date database's psql program b/c it may have increased functionality. tia... --- John DeSoi <desoi@pgedit.com> wrote: > > On Sep 12, 2005, at 7:46 PM, > <operationsengineer1@yahoo.com> > <operationsengineer1@yahoo.com> wrote: > > > i think i log log into pgsql like so... > > > > psql dbname -h [full url to db on webhost here - > is > > this possible?] -U [username goes here] -p [port > goes > > here] > > > > will this work? can i access my webhost in this > > manner (putting url in as host option)? > > > The -h parameter is not a URL, it is just a host > name (or IP > address). Yes, you can run psql (and other > PostgreSQL utilities) this > way, but it assumes the proper permissions have been > setup in the > pg_hba.conf file to allow it (see > http://www.postgresql.org/docs/8.0/ > interactive/client-authentication.html). > > SSH access may be an alternative for you. I put a > tip here about > setting up SSH tunneling for PostgreSQL: > > http://pgedit.com/tip/postgresql/ssh_tunneling > > > > John DeSoi, Ph.D. > http://pgedit.com/ > Power Tools for PostgreSQL > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Sep 13, 2005, at 11:15 AM, <operationsengineer1@yahoo.com> <operationsengineer1@yahoo.com> wrote: > i use pgadmin3 to admin my dev db. if i use pgadmin3 > to connect to the remote db, how can i be sure that > the following migration rule of thumb is followed: > > 1. always use the most up to date database's psql > program b/c it may have increased functionality. I'm not sure what connection you are making between pgadmin3 and psql -- those are two different programs. If you connect using the SSH tunneling method I outlined, you are always using whatever client program is on the local machine. Perhaps the point of confusion is what configurations are possible using SSH. You'll note that if you start SSH tunneling as I have specified you also have a remote shell connection. If your host has all of the PostgreSQL command line utilities installed, you can run them directly from the shell you just opened. But note the important distinction. If you run psql this way, it is running on remote host and using the remote host file system. If you instead run psql on your local machine and connect to the tunnel port, you are of course using the local file system and executing the process on your local machine. Both ways are useful depending on what you want to do. If you are using a GUI program like pgAdmin3 or pgEdit, they are always running locally using whatever version you have installed. pgEdit uses psql, but it will only use the one stored on your local drive. I think pgAdmin3 uses pg_dump and maybe other utilities -- it will always be local versions that the GUI program can execute directly. If this does not help, try clarify your question with more detail. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL