I cannot find any documentation and discriptions about how to set up Servoy 5.2 application server for running PostgreSQL databases.
When I take a look into the database folder in the application_server directory, I can see the differences in organizing the database stuff between sybase and postgres.
But how do I configure the postgres databases I want to run on startup?
If I run sybase only one db_server is running. Starting Servoy with PostgreSQL several db servers are running as “postgres”!?
What is the default db server name when staring Servoy and PostgreSQL? If necessary, how can I change the db server name?
How can I manage several postgres db servers on different installation locations by only one pgadmin installation?
How can I shut down the postgres db servers? Quit the services in the activity monitor won’t work.
Tips, hints and links for more information are welcome. Thanks in advance
Thomas
Unlike Sybase PostgreSQL doesn’t have 1 .db file (+ log) but has many files spread over many directories (just like MySQL, Oracle, etc).
In fact that postgres directory is a database cluster and can contain many databases.
And those postgres processes you see are in fact connections (and some other processes). Each connection gets it’s own process.
Why do you want to change the name of the db server ? Servoy Developer 5.2 will start AND stop PostgreSQL for you when launching/quitting Servoy Developer (see preferences).
If you want to run multiple installations at the same time then you need to change the PostgreSQL port number in the postgresql.conf file. Of course you need to adjust your servoy connections accordingly as well.
for managing several sybase db’s on several servers by only one Sybase Central installation, I have to rename the default server name (servoy_repository) to an unique one (e.g. db_server_1, db_server_2,…), to connect to at the same time.
I also would like to do that by pgadmin and postgres databases.
How can I shut down the postgres server(s) on a servoy application_server machine without running servoy developer? I checked out the “stop” property in the servoy developer settings and will try to quit the server by taking the command in the terminal/console.
How do I set up launching the postgresql databases at servoy_server.sh startup? Is there a config file for entering the database path or anything else?
I would like to read more about it in the servoy wiki (as for servoy and sybase), but nothing to find .
You mean you have to rename the database name, not the server name.
Renaming a database is possible using PgAdmin by right-clicking on the database node and then select Properties. Make sure the database is not being accessed by any other app (like Servoy).
Or you can use the plain SQL in an SQL editor (like psql): ALTER DATABASE myDatabase RENAME TO myNewDBName;
Controlling the Postgres cluster you use the commandline tool pg_ctl (‘pg_ctl --help’ will show all the possible arguments).
In short you use pg_ctl start -D /path/to/your/postgresqldbcluster/
Ditto for stop, reload and restart.
Also if you want to use only 1 instance of PostgreSQL (with multiple Servoy (developer) instances) I recommend to install PostgreSQL via one of the installers available on the web.
For example the PostgreSQL installers from EnterpriseDB (registration required). They have 3 versions (PostgreSQL, Postgres Plus and Postgres Plus Advanced. The one you want to download is the PostgreSQL installer (it’s what I use).
The installer will set PostgreSQL up as a service and after you close the installer you are in fact ready to go.
It then also comes with handy apps to Start, Stop, Reload and Restart the PostgreSQL server that you can simply launch from the GUI.
Another benefit of this installer is that when there are new (security) updates you simply run the installer and less than 1 minute later you are up to date and ready to go again.
Of course when you use this one instance you don’t have to install Servoy with a database (uncheck in the installer).
ROCLASI:
You mean you have to rename the database name, not the server name.
Sybase Central cannot open concurrent several database servers with the same name on different locations. For that I remane the sybase servers in the servoy_server.sh file like:
cd /Applications/Servoy5/application_server; export DYLD_LIBRARY_PATH=/Applications/Servoy5/application_server/sybase_db; /Applications/Servoy5/application_server/sybase_db/dbsrv11 -n dbsrv11_<customer_name> @/Applications/Servoy5/application_server/sybase_db/sybase.config &
In PostgreSQL you don’t have to name a server. You also don’t have to bring your database server down to add (or remove) a new database either.
Any database created in the PostgreSQL cluster is available to connect to via Servoy or PgAdmin (or any other client).
Now if you really want to use different clusters you can but you have to give each cluster a unique port number (see postgresql.conf).
You just need to add the port number to your connection settings in Servoy (and PgAdmin) to differentiate between the clusters.
I have now installed PostgreSQL 8.4 as one stand alone instance on the Mac server for teamsupport. But I’m struggeling to connect to the postgres server from an other pc in the network. I always get the dialog message: “FATAL:no pg_hba.conf entry for host…”.
When I try to edit and/or save a pg_hba.conf file in the /Library/PostgreSQL/8.4 directory I get an error message about wrong user rights. If I am changing the user rights for “Administrators” to read/write, the postgres server doesn’t restart because “Permissions should be u=rwx”?!
This is very confusing for me…
PostgreSQL security by default only allows local connections, so either you have to add the IP of the computer you want to connect from to pghba.conf file or ssh tunnel into your server.
You need to edit the pghba.conf file as a superuser, I use vi to edit such files:
sudo vi pghba.conf
And you will be prompted for the superuser password.
If you don’t like editing on the command line, you could use TextMate
After running this command you can access the postgresql database on the computer with IP 192.168.1.200 as if it was on 127.0.0.1:5433, in other words running on your own computer. We use port 5433, 5434 or some other port not to clash with any local postgreSQL server which usually runs on 5432.
These days I always use ssh, I never edit the pghba.conf file.
could you please explain it a little bit more detailed?
The PostgreSQL server is running on Server1. This should be the teamsupport server. How must I setup now the postgres server permanently to establish tcp/ip connections from other workstations?
Do I have to establish a ssh connection for the server1 as remote every time I would like to connect to the postgres server?
I have reinstalled the PostgreSQL once again and set the default Port to 5434. But when I execute the command “ssh -Nfl 5434/127.0.0.1/5432 @192.168.155.100” I get the return:
<<bind: Address already in use
channel_setup_fwd_listener: cannot listen to port: 5434
Could not request local forwarding.>>
When I take an other Port (e.g. 5433) than 5434 in the commandline the connection seems to be established, but I cannot connect from the workstation!?
If I get this work some day, does the Database Server URL in Eclipse looks like: jdbc:postgresql://192.168.155.100:5434/servoy_repository ?
The pg_hba.conf file as the alternative way exists only as .sample and has to be renamed or new created. Both is not so easy to do because of the permissions.
tgs:
The PostgreSQL server is running on Server1. This should be the teamsupport server. How must I setup now the postgres server permanently to establish tcp/ip connections from other workstations?
Do I have to establish a ssh connection for the server1 as remote every time I would like to connect to the postgres server?
you do have to do it every time, so in this case I would edit the pg_hba.conf file.
Try adding a line like
Ok, I have read the PostgreSQL Documentation for the pg_hba.conf file, Authendification and so on and created the pg_hba.conf file in the /Library/PostgreSQL/8.4 direction. I have also tried different settings of the .conf file and made a postgres server restart after every change. But no success! I always get the boring error message while connecting from a workingplace to the server: FATAL: no pg_hba.conf entry for host…
I don’t know what’s wrong and/or is the postgres server even reading the .conf file.
My PostgresSQL adventure takes to much time and that is frustrating me. I’m also displeased about Servoy because they are changing the default SQL db without any documentation and/or online help. Not all the Servoy developers are hardcore programmers and deep informatik specialists. The time to get that db change running get lost for gaining customers and selling licenses…
You don’t have to create any config files, they are already there.
I assume you used the EnterpriseDB installer so then the config files live in /Library/PostgreSQL/8.4/data/ .
But as you already experienced this directory is owned (and only accesible) by the system user ‘postgres’ (which has no password just like ‘www’). This is for security reasons, don’t go changing the permissions on that directory.
What you do is the following:
Open terminal.app and type the following
sudo su - postgres
It will ask for the root password (make sure you have that enabled)
Now you are working under the postgres user
cd /Library/PostgreSQL/8.4/data/
pico pg_hba.conf
Pico is a very easy editor, you can page down with CTRL-V and up with CTRL-Y.
At the bottom you see other CTRL commands.
Now at at the bottom of the file in the IPV4 section you put the following line
host all all 192.168.155.100/32 md5
The /32 means ONLY this IP can connect and the md5 method means no password will go in plain text over the network.
Save the file (CTRL-O) and quit Pico (CTRL-X).
To get back to your own user just type:
exit
Now launch the Reload Configuration.app (applescript) in the /Applications/PostgreSQL 8.4/ directory and you are in business.