Thread: Can I run two Postgres sessions at the same time?

I have a small business I am helping with their Postgresql server. They
may need to move one of two databases onto a seperate disk system. They
don't want another physical server. Can I run two postmasters pointing
to different disk areas
(i.e. /usr/local/db1/data , /usr/local/db2/data)? Can they be on the
same port?

Thanks.

Steve Poe

Steve Poe wrote:

>I have a small business I am helping with their Postgresql server. They
>may need to move one of two databases onto a seperate disk system. They
>don't want another physical server. Can I run two postmasters pointing
>to different disk areas
>(i.e. /usr/local/db1/data , /usr/local/db2/data)? Can they be on the
>same port?
>
>
You can run muliptle postmasters on the same server, but they have to be
on different ports.

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

At 12:36 PM 6/27/05, Steve Poe wrote:

>I have a small business I am helping with their Postgresql server. They
>may need to move one of two databases onto a seperate disk system. They
>don't want another physical server. Can I run two postmasters pointing
>to different disk areas
>(i.e. /usr/local/db1/data , /usr/local/db2/data)? Can they be on the
>same port?


Your example shows two data directories (which contain subdirectories for
databases), but your question was about moving a database, not a data
directory.  If you want to move one database to another disk, you can do
that without much trouble...

         - stop postmaster
         - move database directory to new disk system
         - create link from old db2 location to new disk system
         - restart postmaster

Frank

i have the following two queries i would like to run.


$sql_product = 'SELECT product_id, product_number FROM
t_product ' .
'ORDER BY product_number ASC';

$sql_employee = 'SELECT employee_id, employee_name
FROM t_employee ORDER BY LOWER(employee_name) ASC';

$rs_product = $db->Execute($sql_product);
$rs_employee = $db->Execute($sql_employee);

i read somewhere that a transaction will speed up the
process b/c only one db
connection/hit/request/whatever is made (i may have
said it wrong, but hopefully you get the meaning)
instead of two.

how do i implement the BEGIN and COMMIT in php?  i saw
an example on php.net, but it didn't apply to adodb.
in the example, begin and commit were transmitted to
the db via the the pg_query function.

tia...



__________________________________
Do you Yahoo!?
Make Yahoo! your home page
http://www.yahoo.com/r/hs

Hi,

On 6/27/05, operationsengineer1@yahoo.com <operationsengineer1@yahoo.com> wrote:
> i have the following two queries i would like to run.
>
> $sql_product = 'SELECT product_id, product_number FROM
> t_product ' .
> 'ORDER BY product_number ASC';
>
> $sql_employee = 'SELECT employee_id, employee_name
> FROM t_employee ORDER BY LOWER(employee_name) ASC';
>
> $rs_product = $db->Execute($sql_product);
> $rs_employee = $db->Execute($sql_employee);
>
> i read somewhere that a transaction will speed up the
> process b/c only one db
> connection/hit/request/whatever is made (i may have
> said it wrong, but hopefully you get the meaning)
> instead of two.

If you don't specify any transactions, PostgreSQL will execute each
sent query in a single transaction block. For instance

  $conn->Execute("SELECT 1");
  $conn->Execute("SELECT 2");

will be executed by PostgreSQL as

  BEGIN; SELECT 1; COMMIT;
  BEGIN; SELECT 2; COMMIT;

But if you'd specify your own transaction blocks:

  $conn->StartTrans();
  $conn->Execute("SELECT 1");
  $conn->Execute("SELECT 2");
  $conn->CompleteTrans();

This time it'll be executed as:

  BEGIN;
    SELECT 1;
    SELECT 2;
  END;

As you can realize, we removed the repeatation of transactions and
combined them as one. Therefore, last one will be executed faster when
compared to first example.

For more information, you can take a look at ADOdb's transactions
documentation [1]. Furthermore, I strongly encourage you to read ADOdb
documentation [2] before starting to use ADOdb. You'll be introduced
lots of cool stuff for your own applications.

[1] http://phplens.com/adodb/tutorial.smart.transactions.html
[2] http://phplens.com/adodb/

> how do i implement the BEGIN and COMMIT in php?  i saw
> an example on php.net, but it didn't apply to adodb.
> in the example, begin and commit were transmitted to
> the db via the the pg_query function.

You can achieve above $conn->StartTrans(); and $conn->CompleteTrans();
functionality by using $conn->Execute("BEGIN"); and
$conn->Execute("COMMIT"); too. But gor portability and ADOdb'ish way,
you should prefer the first one.

Furthermore, if your queries are used more than once in a single
connection session, you can use PREPARE [3] to reduce query execution
times.

[3] http://phplens.com/adodb/reference.functions.prepare.html

Regards.