[postgis-users] Moving to AWS RDS PostgreSQL

[Paul & Caroline Lewis]

Hi,

   I'm running a postgres 9.1/postgis 1.5 platform on an AWS EC2 machine. I want to move to the new AWS RDS postgresql set-up so I need a couple of steps cleared up on transferring as it involves upgrading to postgis 2 at the same time.

So far I've dumped the existing DB using:

 pg_dump -Fc -b -v -f "db.backup" database -h localhost -p 5432 -U user

But to upgrade and dump onto the RDS instance is not clear and doesn't seem to work for me. I tried the following:

 perl utils/postgis_restore.pl /some_location/db.backup | psql -h aws-auth-stuff.aws-region.rds.amazonaws.com -p 5432 -U user database 2> errors.txt

All this does is ask for the DB password and once entered just returns to the terminal prompt. No error messages.

Can anyone advise on what I may be doing wrong.

Thanks

Paul

[Paul Ramsey]

You might have to put the password into the environment to avoid the
prompt screwing up the pipe...

export PGPASSWORD=yourpassword

(Im' assuming you've already tested psql connectivity and ensured you
can in fact connect via psql)

P.

> _______________________________________________
> postgis-users mailing list
> postgi...@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

[Sandro Santilli]

On Tue, Dec 10, 2013 at 05:05:16PM +0000, Paul & Caroline Lewis wrote:

> perl utils/postgis_restore.pl /some_location/db.backup | psql -h aws-auth-stuff.aws-region.rds.amazonaws.com -p 5432 -U user database 2> errors.txt
> All this does is ask for the DB password and once entered just returns to the terminal prompt. No error messages.
> Can anyone advise on what I may be doing wrong.

The error message would be in "errors.txt", as per your
redirection request.

Try:

cat errors.txt

Or:

more errors.txt

--strk;

[Paul & Caroline Lewis]

I can connect fine using psql and a select version(); responds with

--------------------------------------------------------------------------------------------------------------

 PostgreSQL 9.3.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.6.3 20120306 (Red Hat 4.6.3-2), 64-bit

I have also created the PostGIS extension on the DB I've created in the RDS version through PGADMIN.

Tried exporting PGPASSWORD but still no joy. Having it exported stops the password prompt and the psql connection is still fine.

The errors.txt file is empty based on the commands line in previous mail.

If I remove the target DB from the command line the errors.txt has a FATAL db does not exist error.

If I remove the target DB from postgresql through PGADMIN and run the command line, errors.txt contains the FATAL db does not exist error.

If I change the user the errors.txt file populates with a user error.

So the RDS platform is getting connected to but it would seem that the pipe dies because when the PGPASSWORD is set and users are correct and DB's in place the process starts but dies immediately with no error messages.

Will continue to search for what next.

Thanks again,

Paul

[Sylvain Racine [iGreffe Géomatique]]

Did you install PostGIS and topology extensions in your new DB BEFORE doing your command?

CREATE EXTENSION postgis;

CREATE EXTENSION topology;

Run also legacy.sql in your new DB to ensure compliance with the old PostGIS functions if you use PostGIS with an old application. The script should be near the directory where you can find 'psql' command (ex.: /usr/bin/psql ==> /usr/bin/share/contrib/postgis-2.0) in Ubuntu when you install EnterpriseDB package.

 

Regards

 

--

Sylvain Racine
iGreffe Géomatique
50, St-Hubert, n° 5
Granby, Québec
J2G 5L9
Tél: 450-770-9974

[Paul & Caroline Lewis]

Thanks for the feedback,

PostGIS is installed as well as topology based on following AWS docs:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.PostGIS

AFAIK you cannot terminal/SSH into the RDS platform to go routing around for directories and files, you can only log in through psql or the likes of PGADMIN, phppgadmin.

Anyway, the first problem is probably that I've being using the previously emailed commands on the original server, which is installed with pg9.1/postgis1.5, so calling perl utils/postgis_restore.pl /some_location/db.backup is requesting an old script relevant to postgis 1.5 I presume.

What I'll try is setting up a new server with pg9.3/postgis2.1 and converting the 1.5 DB dump of the original email to 2.1 through the typical methods.

Then I can just use the following docs to port the new temporary server DB dump into RDS.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html

I'll update if this works.

Thanks,

Paul

>Did you install PostGIS and topology extensions in your new DB BEFORE
doing your command? 
>
>CREATE EXTENSION postgis; 
>
>CREATE EXTENSION topology; 
>
>Run also legacy.sql in your new DB to ensure compliance with the old
PostGIS functions if you use PostGIS with an old application. The script
should be near the directory where you can find 'psql' command (ex.:
/usr/bin/psql ==> /usr/bin/share/contrib/postgis-2.0) in Ubuntu when you
install EnterpriseDB package. 
>
>Regards 
>
>-- 
>
>Sylvain Racine
>iGreffe Géomatique
>50, St-Hubert, n° 5
>Granby, Québec
>J2G 5L9
>Tél: 450-770-9974
>
>Le 2013-12-10 16:27, Paul & Caroline Lewis a écrit : 
>
>> I can connect fine using psql and a select version(); responds with 
>> -------------------------------------------------------------------------------------------------------------- 
>> 

>>  POSTGRESQL 9.3.1 ON X86_64-UNKNOWN-LINUX-GNU, COMPILED BY GCC (GCC) 4.6.3 20120306 (RED HAT 4.6.3-2), 64-BIT

>> 
>> I have also created the PostGIS extension on the DB I've created in the RDS version through PGADMIN. 
>> 
>> Tried exporting PGPASSWORD but still no joy. Having it exported stops the password prompt and the psql connection is still fine. 
>> 
>> The errors.txt file is empty based on the commands line in previous mail. 
>> 
>> If I remove the target DB from the command line the errors.txt has a FATAL db does not exist error. 
>> If I remove the target DB from postgresql through PGADMIN and run the command line, errors.txt contains the FATAL db does not exist error. 
>> If I change the user the errors.txt file populates with a user error. 
>> 
>> So the RDS platform is getting connected to but it would seem that the pipe dies because when the PGPASSWORD is set and users are correct and DB's in place the process starts but dies immediately with no error messages. 
>> 
>> Will continue to search for what next. 
>> 
>> Thanks again, 
>> 
>> Paul 
>> 
>> _______________________________________________
>> postgis-users mailing list

>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users [1]