Patch: Add support for execution of jobs on a remote database - Mailing list pgadmin-hackers
| From | Ashesh Vashi |
|---|---|
| Subject | Patch: Add support for execution of jobs on a remote database |
| Date | |
| Msg-id | 494A4132.20602@enterprisedb.com Whole thread Raw |
| Responses |
Re: Patch: Add support for execution of jobs on a remote database
("Dave Page" <dpage@pgadmin.org>)
|
| List | pgadmin-hackers |
Hi All,
As per my discussion with Dave Page:
- Add support for execution of jobs on a remote database in pgAgent.
(primarily for hot standby support). This essentially means allowing a
full connection string to be specified in place of the current database
name option.
Please find the patches for changes in pgAgent (pgagent_remotedb_cnstr.patch)
& pgAdminIII (pgadmin_agent_remotedb.patch)
Changes made in pgAgent:
* Added new column jstrconnstr in pga_jobstep table. (Made changes in the
pgagent.sql for that. Also, created a pgagent_update.sql for existing
users upgrade purpose.)
* Made changes in the pgagent code such that, it can take database or
connection string to connect it to remote database. If connection string
is provided, than database will be ignored altogether.
* For existing users, if connection string (jstconnstr) not found in the
pgagent.pga_jobstep table, then it will use the dbname and basic connection
string (provided at starting of the pgagent) for step execution.
For selection of the connection string in the dlgStep (UI):
* A pop up window with a tree structure contains servers (registered with the
pgAdmin) and the database list as the children for the respective server.
Actual control looks like this:
|```````````````````````````````````````````````````````````````|`````|
|hostAddr=192.168.23.131 port=5432 user=postgres database=test | ... | <--- On this button, a pop up window will come up as below.
|_______________________________________________________________|_____|
(POPUP DIALOG)
------------------------------------------------
| SELECT DATABASE |
------------------------------------------------
| Server1 (192.168.23.131:5432 user:postgres) |
| | |
| |--postgres |
| |--test |
| Server2 (10.2.32.45:5444 user:enterprisedb) |
| | |
| |--edb |
| |--dev |
| ... |
------------------------------------------------
In this case, jstconnstr will be 'hostAddr=192.168.23.131 port=5432 user=postgres' and jstdatabase='test'
* Currently, connection string must be in this format property=value.
White-spaces are allowed in between. Valid properties are user, host,
hostAddr, port, dbname, password & connection_timeout. At least dbname must
exist for proceed ahead.
* The text field (which holds the connection string) is editable for
user-specific values.
Changes made in pgAdminIII:
* Introduced a new class dlgSelectDatabase for the above pop-up dialog. It
contains the tree structure for selecting the databases from all the servers
registered under the pgAdmin.
* Earlier, we had database to select from the combobox.
Now, we have introduced a radio button for selection for the connection
type (local/Remote).
1. Local Connection will allow to select the databases from the combo-box.
2. Remote Connection will allow to select the connection string using
dlgSelectDatabase.
* In case, the pga_jobstep table does not have the column jstconnstr (for the
existing users, the remote connection option will be disabled, but not hidden)
* Introduced a new function HasColumn in db/pgSet for that the result set has
data for particular column or not.
* Introduced a new static function HasColumn in schema/pgTable (takes four
arguments - pgConnection object, schema name, table name & column name).
This checks if that table does have column with this name or not.
Thanks & Regards,
Ashesh Vashi
As per my discussion with Dave Page:
- Add support for execution of jobs on a remote database in pgAgent.
(primarily for hot standby support). This essentially means allowing a
full connection string to be specified in place of the current database
name option.
Please find the patches for changes in pgAgent (pgagent_remotedb_cnstr.patch)
& pgAdminIII (pgadmin_agent_remotedb.patch)
Changes made in pgAgent:
* Added new column jstrconnstr in pga_jobstep table. (Made changes in the
pgagent.sql for that. Also, created a pgagent_update.sql for existing
users upgrade purpose.)
* Made changes in the pgagent code such that, it can take database or
connection string to connect it to remote database. If connection string
is provided, than database will be ignored altogether.
* For existing users, if connection string (jstconnstr) not found in the
pgagent.pga_jobstep table, then it will use the dbname and basic connection
string (provided at starting of the pgagent) for step execution.
For selection of the connection string in the dlgStep (UI):
* A pop up window with a tree structure contains servers (registered with the
pgAdmin) and the database list as the children for the respective server.
Actual control looks like this:
|```````````````````````````````````````````````````````````````|`````|
|hostAddr=192.168.23.131 port=5432 user=postgres database=test | ... | <--- On this button, a pop up window will come up as below.
|_______________________________________________________________|_____|
(POPUP DIALOG)
------------------------------------------------
| SELECT DATABASE |
------------------------------------------------
| Server1 (192.168.23.131:5432 user:postgres) |
| | |
| |--postgres |
| |--test |
| Server2 (10.2.32.45:5444 user:enterprisedb) |
| | |
| |--edb |
| |--dev |
| ... |
------------------------------------------------
In this case, jstconnstr will be 'hostAddr=192.168.23.131 port=5432 user=postgres' and jstdatabase='test'
* Currently, connection string must be in this format property=value.
White-spaces are allowed in between. Valid properties are user, host,
hostAddr, port, dbname, password & connection_timeout. At least dbname must
exist for proceed ahead.
* The text field (which holds the connection string) is editable for
user-specific values.
Changes made in pgAdminIII:
* Introduced a new class dlgSelectDatabase for the above pop-up dialog. It
contains the tree structure for selecting the databases from all the servers
registered under the pgAdmin.
* Earlier, we had database to select from the combobox.
Now, we have introduced a radio button for selection for the connection
type (local/Remote).
1. Local Connection will allow to select the databases from the combo-box.
2. Remote Connection will allow to select the connection string using
dlgSelectDatabase.
* In case, the pga_jobstep table does not have the column jstconnstr (for the
existing users, the remote connection option will be disabled, but not hidden)
* Introduced a new function HasColumn in db/pgSet for that the result set has
data for particular column or not.
* Introduced a new static function HasColumn in schema/pgTable (takes four
arguments - pgConnection object, schema name, table name & column name).
This checks if that table does have column with this name or not.
Thanks & Regards,
Ashesh Vashi
EnterpriseDB INDIA: http://www.enterprisedb.com
pgadmin-hackers by date: