Deployment on Server

I have developed a web application on my MacBook Pro and am about to deploy it on a remote Mac Mini. Since I haven't done this before, I wonder what is the best way to proceed.

Kind regards,

Annet

On the database side, you'll want to dump the database using pg_dump:

pg_dump -h localhost -d databasename > db.sql

And you'll want to get all the database users and passwords with:

pg_dumpall -h localhost -g > users.sql

Then, import those on your mac-mini with the following (note: users first)

psql -f users.sql
createdb databasename
psql -d databasename -f db.sql

That should deploy the database. The webapp is the trick and that all depends on how you built your app.

--Scott

On my MacBook Pro I run Postgres Plus 8.3 with pgadmin III. When I open the
Interactive SQL Terminal I end up in a Terminal window asking me the password
for user postgres, the superuser. The database I want to back up is owned bij
another user.

When I go with the flow, and enter the password for user postgres, this is what happens.

imac:~ imac$ /Library/PostgresPlus/8.3/bin/psql -p 5432 -h localhost -U postgres postgres && exit || sleep 5 && exit
Password for user postgres:

Welcome to psql 8.3.3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=#

When I then do:

pg_dump -h localhost -d fitwise_nl > db.sql
postgres-#

Nothing happens. When I alter the command to read like:

/Library/PostgresPlus/8.3/bin/psql -p 5432 -h localhost -U myuser

this is what happens:

Password for user myuser:
psql: FATAL: password authentication failed for user "myuser"
mymac:~ mymac$

How do I proceed to dump the database.

Kind regards,

Annet

imac:~ imac$ /Library/PostgresPlus/8.3/bin/psql -p 5432 -h localhost -U postgres postgres && exit || sleep 5 && exit
Password for user postgres:

Welcome to psql 8.3.3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=#

When I then do:

pg_dump -h localhost -d fitwise_nl > db.sql
postgres-#

You're typing pg_dump in the wrong place. You do it from the OS command line, don't login with psql first.

Hi Scott,

When I open the Terminal and do

imac:~ imac$ pg_dump -h localhost - fitwise_nl > db.sql
-bash: pg_dump: command not found
imac:~ imac$

Is that because I have Postgres Plus 8.3 installed?

Annet_V wrote:Hi Scott,

When I open the Terminal and do

imac:~ imac$ pg_dump -h localhost - fitwise_nl > db.sql
-bash: pg_dump: command not found
imac:~ imac$

Is that because I have Postgres Plus 8.3 installed?

/Library/PostgresPlus/8.3/bin/pg_dump

Hi Scott,

That worked I now have a db.sql and a users.sql file. Running them using phppgadmin resulted in an enormous amount of errors, so I guess I will have to try my luck using SSH.

Have you used SHH to execute the commands you provided me with?

What errors did you get? Sometimes those errors just means that you already had the objects created. Can you post a text file with the errors in it?

I attached a text file with the errors.

postgres is the superuser, created when I installed postgres plus 8.3. I then created a role myuser in pgadmin which own the database I dumped.

I dumped the database using: imac:~ imac$ /Library/PostgresPlus/8.3/bin/pg_dump -U postgres -h localhost -d fitwise_nl > db.sql

The database created on the server through phppgadmin is owned by myuser, maybe that is one of the causes of the many errors.

Kind regards, I very much appreciate your help.

Sorry, forgot the attachment.

The database created on the server through phppgadmin is owned by myuser, maybe that is one of the causes of the many errors.

Correct, you have a bunch of lines in there:

This means that you don't have superuser privs. Run the scripts as superuser and you should be better.

I will give it a try first thing in the morning. It is getting late here.
I hope that when I have some more questions on this topic you will
be there tomorrow.

One more question right now.

On my mac mini the ISP has root access because he manages the server.
Then there is a user postgres both as unix user and as posgres superuser.
To enter phppgadmin I had to create a new user, because root or posgres as a
username is not allowed. I then created pgadmin as a superuser to log
in to phppgadmin.

When I create a database in phppgadmin the owner is the user that
is logged in. In pgadmin III the database is also owned by one user.

The database I dumped is owned by myuser (which is also the username in the
connection string in the web application I built). When I drop the database
and create a new database owned by superuser postgres, how do I give user
myuser access to the database too? In both pgadmin and phppgadmin I couldn't
find an option to do this. I presume a database is owned by one user, and more
user can have access to the database?

Kind regards,

Annet

This message was edited 1 time. Last update was at 16 Oct 2009 11:49:48

Hi Scott,

Making postgres the owner of all database object solved part of the problem.

I attached a text file with the latest errors. I hope you can point me in the right
direction to solve problem. Do I have to solve them for the database to run
properly, or are they related to peripheral issues?

Kind regards,

Annet.

I think you attached your database dump acceidentally. Can you send the log?

Sorry, must be the annoyance.

Here's the correct file.

Hi Scott,

I think the fact that on my local computer I run PostgresPlus and my Hosting Provider has installed Postgres on the Server is the cause of all the error messages. I will ask my ISP to uninstall Postgres and install PostgresPlus.

Thanks for the time you spent helping me solve the problem.

Kind regards,

Annet

Annet,

Looks to me like you are correct. The errors you're getting are due to features that exist in PostgresPlus by default but are add-ons to Open-Source PG.

Thanks!

Let me know if you need anymore help.

Scott,

> Looks to me like you are correct. The errors you're getting are due to features that exist in PostgresPlus by default but are add-ons to Open-Source PG.

It took me some time to figure that out, but at least I did find out and learned a lot while doing so.

>Let me know if you need anymore help.

My ISP installed PostgresPlus today, and I uploaded the dump to the server and ran it. I am still getting error messages but they all read something like:

already exists with same argument types

... so I assume I am alright now. To be sure I attached the latest text file with the error messages, so you can have a look at them

Thanks for your help,
Kind regards,

Annet.

Annet_V wrote:

... so I assume I am alright now. To be sure I attached the latest text file with the error messages, so you can have a look at them

You're right, they're just things that are built-ins. You're all set!

Hi Scott,

Thanks, I very much appreciated your help.

Annet.