[postgis-users] more effective way?
I have a postgis table that contains radar. My problem is every 3 minutes I update the radar table and sometimes the end user has no radar showing due to the new data loading is there a more effective way than doing it like I am with the following code, shp2pgsql -D -a -s 4269 last.shp base05 > base05.sql psql -c "TRUNCATE TABLE base05" -d wxserver psql -d wxserver -f base05.sql I was dropping the table but that would cause my wms server to freakout. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] more effective way?
You can run ddl in a transaction context, so the best way is to . Upload data to mynewtable . Begin . Alter table mytable rename to myoldtable . Alter table mynewtable rename to mytable . Commit . Drop table myoldtable P. On 2012-06-03, at 7:02 AM, chris brisendine wrote: > I have a postgis table that contains radar. My problem is every 3 > minutes I update the radar table and sometimes the end user has no > radar showing due to the new data loading > is there a more effective way than doing it like I am with the following code, > > shp2pgsql -D -a -s 4269 last.shp base05 > base05.sql > psql -c "TRUNCATE TABLE base05" -d wxserver > psql -d wxserver -f base05.sql > > I was dropping the table but that would cause my wms server to freakout. > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Loading a shapefile into a postgres database remotely
Hi List, I have been using the following approach to load a shapefile into my database locally: shp2pgsql -s 2831 -i -I -D D:/test.shp public.test_shapefile | psql.exe -U postgres -d test_db -h localhost -p 5432 I now want to try to load spatial data into a database in a remote location. For the second part of my command, I tried the following: psql.exe -U postgres -P password -d test_db_remote -h 75.75.75.75 -p 5432 I've tried a few variations of this without success. Is there anything that I am missing? If it is relevant, psql.exe is on a windows 7 computer, and I am uploading the data to a postgres database on an ubuntu installation. Thanks, David ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Loading a shapefile into a postgres database remotely
On 6/3/2012 12:33 PM, David Quinn wrote: Hi List, I have been using the following approach to load a shapefile into my database locally: shp2pgsql -s 2831 -i -I -D D:/test.shp public.test_shapefile | psql.exe -U postgres -d test_db -h localhost -p 5432 I now want to try to load spatial data into a database in a remote location. For the second part of my command, I tried the following: psql.exe -U postgres -P password -d test_db_remote -h 75.75.75.75 -p 5432 I've tried a few variations of this without success. Is there anything that I am missing? If it is relevant, psql.exe is on a windows 7 computer, and I am uploading the data to a postgres database on an ubuntu installation. The server has to be configured to listen on 75.75.75.75 and to allow connection over the network device. Look at your pg_hba.conf and postgresql.conf files. and if you make changes to these you have to restart the server before the changes take effect. -Steve ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Loading a shapefile into a postgres database remotely
On Sunday, June 3, 2012 10:05:45 AM UTC-7, Stephen Woodbridge wrote: > > The server has to be configured to listen on 75.75.75.75 and to allow > connection over the network device. > > Look at your pg_hba.conf and postgresql.conf files. and if you make > changes to these you have to restart the server before the changes take > effect. > > -Steve > Another trick that I use all the time is to tunnel the SQL connection from the remote server to my machine: ssh -L5432:localhost:5432 @ Then I can connect to the remote database as if it was running locally and there is no need to mess with the pg_hba.conf file and figure out how to secure the open port, route communication through firewalls, etc. -Charlie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Loading a shapefile into a postgres database remotely
I have already edited the pg_hba.conf and postgresql.conf files and can connect remotely using pgadmin so I think that is not a problem. Charlie, should I include this code in the command that I described earlier, or is it something that I should run first? -David On Sun, Jun 3, 2012 at 1:12 PM, Charlie Sharpsteen < charles.sharpst...@gmail.com> wrote: > On Sunday, June 3, 2012 10:05:45 AM UTC-7, Stephen Woodbridge wrote: >> >> The server has to be configured to listen on 75.75.75.75 and to allow >> connection over the network device. >> >> Look at your pg_hba.conf and postgresql.conf files. and if you make >> changes to these you have to restart the server before the changes take >> effect. >> >> -Steve >> > > Another trick that I use all the time is to tunnel the SQL connection from > the remote server to my machine: > > ssh -L5432:localhost:5432 @ > > Then I can connect to the remote database as if it was running locally and > there is no need to mess with the pg_hba.conf file and figure out how to > secure the open port, route communication through firewalls, etc. > > -Charlie > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Loading a shapefile into a postgres database remotely
On Sunday, June 3, 2012 10:22:03 AM UTC-7, David Quinn wrote: > > I have already edited the pg_hba.conf and postgresql.conf files and can > connect remotely using pgadmin so I think that is not a problem. > > Charlie, should I include this code in the command that I described > earlier, or is it something that I should run first? > It is something that you should run first, in a separate terminal. Note that since you are using Windows you may not have an `ssh` command unless you have installed a suite of UNIX tools such as MSYS. You may also be able to set up a SSH tunnel using PuTTY. -Charlie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Loading a shapefile into a postgres database remotely
I think the problem may have been due to a windows 7 firewall setting. I enabled psql.exe under 'Control Panel\All Control Panel Items\Windows Firewall\Allowed Programs' and this seemed to solve the problem. -David On Sun, Jun 3, 2012 at 1:35 PM, Charlie Sharpsteen < charles.sharpst...@gmail.com> wrote: > On Sunday, June 3, 2012 10:22:03 AM UTC-7, David Quinn wrote: >> >> I have already edited the pg_hba.conf and postgresql.conf files and can >> connect remotely using pgadmin so I think that is not a problem. >> >> Charlie, should I include this code in the command that I described >> earlier, or is it something that I should run first? >> > > It is something that you should run first, in a separate terminal. Note > that since you are using Windows you may not have an `ssh` command unless > you have installed a suite of UNIX tools such as MSYS. You may also be able > to set up a SSH tunnel using PuTTY. > > > -Charlie > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
