Search Postgresql Archives

Massively Parallel transactioning?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Is there a way to update a number of databases hosted on a single server 
without opening a separate psql connection to each database? 

We have a cluster of servers hosting an application on Postgres. Right now, we 
have dozens of databases per server, enough that we're starting to have 
problems with our update process. 

When we release updates, we have a migrate script within our update process 
that runs all the database schema updates for all our clients. The way that it 
works is to open a transaction on all the databases concurrently, run the 
commands in sequence on the databases within the transactions, and then commit 
them all (or rollback if there was a problem) 

This way we can be sure that either all the databases are in synch, or that we 
need to rollback the program patch/update. 

So far, it's been a dream, but now, as we continue to grow, we're starting to 
reach connection limits per server. Short of raising the number of 
simultaneous connections, is there a way to run all the transactions for a 
single server for all databases within it on a single (or small number) of 
connections? 

I've tried the following: 

# ATTEMPT 1
$psql -U postgres template1 -h server1; 
template1=# begin transaction; create table testtable (name varchar); 
BEGIN
CREATE TABLE
\c somedatabase; ri
psql (8.4.4, server 8.4.0)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
You are now connected to database "somedatabase".
somedatabase=# rollback; 
NOTICE:  there is no transaction in progress
ROLLBACK
somedatabase=# \c template1; 
template1=# rollback; 
NOTICE:  there is no transaction in progress
ROLLBACK
template1=# 

# ATTEMPT 2
$psql -U postgres template1 -h server1; 
template1=# alter table somedatabase.testtable add address varchar; 
ERROR:  cross-database references are not implemented: 
"somedatabase.public.students"
template1=#

Is there a better way?

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux