[GENERAL] unable to avoid a deadlock at the end of a long transaction

[Ivan Sergio Borgonovo]

I've been having this:

psql:include/custom/import_update.custom.sql:63: ERROR:  deadlock
detected DETAIL:  Process 13349 waits for AccessExclusiveLock on
relation 250510 of database 248569; blocked by process 14153.
Process 14153 waits for ShareLock on transaction 59160779; blocked
by process 13349. CONTEXT:  SQL statement "drop trigger if exists
FT1IDX_catalog_brands_update_trigger on catalog_brands" PL/pgSQL
function "ft1idx_trigger_drop" line 2 at SQL statement

I reshuffled the update process and I started to have other lock
problems.
The only process that I'm aware of that should be *writing* to the
tables involved is the update process. I'd expect other process are
reading but just the update should be writing.

The lock problem happens nearly at the end of the overall update
process that is one big transaction, reshuffling a bit the steps
doesn't make it go away... it just locks on other statements but
still at the *end* of the process after a bunch of update and insert
have been made on a bunch of other tables the largest of whom is an
update of roughly 85834 rows on a table containing 1M rows.

The only thing that look constant is: I get a deadlock at the end of
a long process on a random statement.

Where am I going to start from to solve this?

DETAIL:  Process 3662 waits for ShareLock on transaction 59301028;
blocked by process 4303. Process 4303 waits for ShareLock on
transaction 59299342; blocked by process 3662. CONTEXT:  SQL
statement "update catalog_items set Authors= $1  where ItemID= $2 "
PL/pgSQL function "updateauthorsall" line 19 at SQL statement

create or replace function UpdateAuthorsAll()
  returns void
  as
  $$
  declare
_row record;
_ItemID bigint;
_Authors varchar(1024);
_AuthorsOLD varchar(1024);
_Name varchar(50);
  begin
_Authors := '';
_ItemID := null;
for _row in select a.Name, ia.ItemID from   catalog_itemauthor
  ia join   catalog_author   a on a.AuthorID=ia.AuthorID
  order by ia.ItemID
  loop
  if(_row.ItemID<>_ItemID) then
if(length(_Authors)>2) then
  _Authors := substring(_Authors from 3);
  select into _AuthorsOLD Authors from   catalog_items
where ItemID=_ItemID;
  if(coalesce(_Authors, '')<>coalesce(_AuthorsOLD, '')) then
update   catalog_items   set Authors=_Authors where
  ItemID=_ItemID;
  end if;
end if;
_Authors := '';
  end if;
  _ItemID := _row.ItemID;
  _Name := trim(E' \t' from _row.Name);
  if(length(_Name)>0) then
_Authors := _Authors || ', ' || _Name;
  end if;
end loop;
  return;
  end;
$$ language plpgsql volatile;

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


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

[GENERAL] Getting pgsnmpd using rsync/sup

[Sandeep Thakkar]

I checkout the pgsnmpd using command 
"cvs -d pserver:anonym...@cvs.pgfoundry.org:/cvsroot/pgsnmpd checkout -r REL1_0 
pgsnmpd"

Now, I want to setup the local mirror and wants to checkout the sources 
from there. I use the following command:

$rsync -av cvs.pgfoundry.org:/cvsroot/pgsnmpd/* .
Password:

But, it asks me for password. What password should I enter?

$ rsync -av cvs.pgfoundry.org::
pgfoundry  Mirror: ftp.postresql.org
pgfoundry_all  Backup: tribble.postresql.org
pgfoundry-cvs  pgFoundry CVS
pgfoundry-filespgFoundry files

Please help. Thanks.

Sandeep.



  

Re: [GENERAL] missing chunk number 0 for toast value 25693266 in pg_toast_25497233

[Magnus Hagander]

On Fri, May 7, 2010 at 01:24, Bryan Murphy  wrote:
> I'm running into this issue again:
>
> psql --version
> psql (PostgreSQL) 8.3.7
>
> COPY items_extended TO '/dev/null';
> ERROR:  missing chunk number 0 for toast value 25693266 in pg_toast_25497233
>
> Unfortunately, I do not know where these are coming from and I cannot
> replicate the data in at least one of my tables (which has 20 million
> records) because of this.  I've already found 10 bad records.  There
> are more.
>
> I have four tables with 20 million records (and a fifth which has even
> more) that may have this problem.  Right now, checking every record
> one by one is going to take *DAYS* to complete, and I'm in the middle
> of adding additional nodes to our cluster because we are already
> stretched to the limit.
>
> I have a few options, such as trying to check batches of records and
> spinning up multiple checkers in parallel looking at different subsets
> of the data on wal shipped spares (assuming the wal shipped spares
> would suffer the same problem, which is a big assumption), but this is
> a lot of effort to get going.

Try doing a binary search with LIMIT. E.g., if you have 20M reecords,
do a SELECT * FROM ... LIMIT 10M. (throw away the results) If that
broke, check the upper half, if not, check the lower one (with
OFFSET).

If you have a serial primary key or something, you can use WHERE on it
which will likely be a lot faster than using LIMIT, but the same idea
applies - do a binary search. Should take a lot less than days, and is
reasonably easy to script.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

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

Re: [GENERAL] unable to avoid a deadlock at the end of a long transaction

[Tom Lane]

Ivan Sergio Borgonovo  writes:
> I've been having this:
> psql:include/custom/import_update.custom.sql:63: ERROR:  deadlock
> detected DETAIL:  Process 13349 waits for AccessExclusiveLock on
> relation 250510 of database 248569; blocked by process 14153.
> Process 14153 waits for ShareLock on transaction 59160779; blocked
> by process 13349. CONTEXT:  SQL statement "drop trigger if exists
> FT1IDX_catalog_brands_update_trigger on catalog_brands" PL/pgSQL
> function "ft1idx_trigger_drop" line 2 at SQL statement

I'd suggest not using DROP TRIGGER in operations that need to run
concurrently with other accesses to the same table.  Consider fixing
things so the trigger is always there but knows enough to not do
anything when it doesn't need to.

The particular case here seems to be that the transaction doing DROP
has already modified some rows in the same table, and there are now
other transactions blocked waiting to modify those same rows.  Perhaps
you could avoid combining the DROP with the data modifications.  Or
if you must do it like that, take exclusive lock via LOCK TABLE at the
start of the whole transaction, so you aren't trying to do a lock
upgrade partway through.

regards, tom lane

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

Re: [GENERAL] missing chunk number 0 for toast value 25693266 in pg_toast_25497233

[Bryan Murphy]

On Fri, May 7, 2010 at 9:02 AM, Magnus Hagander  wrote:
> Try doing a binary search with LIMIT. E.g., if you have 20M reecords,
> do a SELECT * FROM ... LIMIT 10M. (throw away the results) If that
> broke, check the upper half, if not, check the lower one (with
> OFFSET).
>
> If you have a serial primary key or something, you can use WHERE on it
> which will likely be a lot faster than using LIMIT, but the same idea
> applies - do a binary search. Should take a lot less than days, and is
> reasonably easy to script.

That's my next step if I can't find a quicker/simpler method.  My math
tells me that my current script is going to take 24 days to test every
record.  Obviously, there are ways I can speed that up if I have no
choice but I'm hoping for a simpler solution.

I'd prefer to run a COPY TABLE like command and have it skip the bad records.

Thanks,
Bryan

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

[GENERAL] Formatted reports

[Sorin Schwimmer]

Hi All,

Is there in PostgreSQL an equivalent of Oracle's BREAK/COMPUTE reporting 
feature?

Thanks,
SxN




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

Re: [GENERAL] unable to avoid a deadlock at the end of a long transaction

[Ivan Sergio Borgonovo]

On Fri, 07 May 2010 10:29:20 -0400
Tom Lane  wrote:

> Ivan Sergio Borgonovo  writes:
> > I've been having this:
> > psql:include/custom/import_update.custom.sql:63: ERROR:  deadlock
> > detected DETAIL:  Process 13349 waits for AccessExclusiveLock on
> > relation 250510 of database 248569; blocked by process 14153.
> > Process 14153 waits for ShareLock on transaction 59160779;
> > blocked by process 13349. CONTEXT:  SQL statement "drop trigger
> > if exists FT1IDX_catalog_brands_update_trigger on
> > catalog_brands" PL/pgSQL function "ft1idx_trigger_drop" line 2
> > at SQL statement

> I'd suggest not using DROP TRIGGER in operations that need to run
> concurrently with other accesses to the same table.  Consider
> fixing things so the trigger is always there but knows enough to
> not do anything when it doesn't need to.

That's nice to know... but even skipping the whole drop/create
trigger thing the lock problem is still there and still happens near
the end of a long transaction that makes a lot of other stuff on
mainly one table.

The statement that cause the lock is not always the same, what is
"constant" across several modification of the overall transaction
is: the lock happens near the end of the transaction.

I'd say that that *should* be the only one transaction *writing* to
the few tables that are involved in the transaction, some of which
are very small (hundreds of record).

I expect (that doesn't mean I know) that from a writing point of
view the overall transaction doesn't involve any write concurrency.
So I thought I wouldn't be involved in stable locking problems on
*random* statement whose only fault is being near the end of the
whole transaction.

I need some help on how to learn how to track down this kind of
problem.

thanks

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


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

Re: [GENERAL] Formatted reports

[Filip Rembiałkowski]

2010/5/7 Sorin Schwimmer :
> Hi All,
>
> Is there in PostgreSQL an equivalent of Oracle's BREAK/COMPUTE reporting 
> feature?

No, there isn't.

You need some procedural language function, or use external tool to
accomplish this.
There are free reporting engines. jaspersoft, pentaho, BIRT, ...

[[ note: BREAK and COMPUTE are features of Oracle's SQL*Plus (client)
not their SQL dialect. ]]



cheers,
Filip

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

Re: [GENERAL] password management

[Christophe Dore]

Hi

 

IMHO, you should never store password in clear

 

If you store the last 5 crypted passwords, then you can make it  comparing the 
new password, crypted, to those 5 strings.

 

Regards

 

-- 

Christophe Doré 
Implementation Product Manager 

3 rue Marcel Allegot 
92190 Meudon, France 
+33 1 46 90 21 00 office 
+33 6 1379 2910 mobile 
CAST, Leader in Automated Application Intelligence 
Achieve Insight. Deliver Excellence. 

www.castsoftware.com   | Gain visibility into 
application quality to proactively manage risk and improve team performance.

From: akp geek [mailto:akpg...@gmail.com] 
Sent: jeudi 6 mai 2010 20:31
To: pgsql-general
Subject: password management

 

Dear all -

 

   I am writing function to handle the passwords. Currently the 
crypt is being used to store the password in the database. what I need to do 
is, when the user wants to change the password, I need to check if that 
password is not being used before up to 5 times, If not then then records 
should be inserted to the database.

 

  The problem where i am running into, when I capture the password 
that user entered, I can't compare to the one in database , because each time 
the function crypt gives different one. Is there any way that I can achieve 
this?

 

  Appreciate your help

 

Regards

[GENERAL] initdb fails on Centos 5.4 x64

[Valentin Hocher]

Hi,

i've tried to install 8.4.3 for about two days, but i don't get it.

8.1.x has been installed before and was runnig but never used. I've removed it 
with yum and wiped /var/lib/pgsl.

-=>> service postgresql initdb
Initializing database: [FAILED]


-=>> cat /var/lib/pgsql/pgstartup.log
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "english".

fixing permissions on existing directory /var/lib/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 24MB
creating configuration files ... ok
creating template1 database in /var/lib/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... FATAL:  could not load library 
"/usr/lib64/pgsql/utf8_and_shift_jis_2004.so": 
/usr/lib64/pgsql/utf8_and_shift_jis_2004.so: failed to map segment from shared 
object: Cannot allocate memory
STATEMENT:  CREATE OR REPLACE FUNCTION shift_jis_2004_to_utf8 (INTEGER, 
INTEGER, CSTRING, INTERNAL, INTEGER) RETURNS VOID AS 
'$libdir/utf8_and_shift_jis_2004', 'shift_jis_2004_to_utf8' LANGUAGE C STRICT;

child process exited with exit code 1
initdb: removing contents of data directory "/var/lib/pgsql/data"


-=>> rpm -qa | grep postgre
postgresql-libs-8.1.18-2.el5_4.1
postgresql-libs-8.4.3-2PGDG.el5
postgresql-devel-8.4.3-2PGDG.el5
compat-postgresql-libs-4-1PGDG.rhel5
postgresql-server-8.4.3-2PGDG.el5
postgresql-plperl-8.4.3-2PGDG.el5
postgresql-8.4.3-2PGDG.el5
postgresql-contrib-8.4.3-2PGDG.el5


-=>> ldd /usr/lib64/pgsql/utf8_and_shift_jis_2004.so
libc.so.6 => /lib64/libc.so.6 (0x2aad6e656000)
/lib64/ld-linux-x86-64.so.2 (0x003777a0)

The server is running with 8 gigs ram, so i think there should be enough mem?

Thanks,

Valentin


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

[GENERAL] postgreSQL not working after upgrade to Ubuntu 10.4

[AllieH]

Yesterday I upgraded from Ubuntu 9.10 to 10.4. Unfortunately this has caused
me serious problems with everything related to postgreSQL. It seems as
though the new version of ubuntu is requiring me to use PostgreSQL 8.4
instead of 8.3 which I was previously using. I tried installing 8.4 but I
can't seem to get the database to start. Has anyone had a similar problems?
-- 
View this message in context: 
http://old.nabble.com/postgreSQL-not-working-after-upgrade-to-Ubuntu-10.4-tp28488130p28488130.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

[GENERAL] what's the relation between pathkey and path in optimize phase?

[sunpeng]

what's the relation between pathkey and path in optimize phase?
thanks
peng

[GENERAL] when to update pg_statistic relation?

[sunpeng]

when to update pg_statistic relation? is it when inserting a new tuple of
any user's relations? and the relation pg_stats at document 8.4 chapter
44.55.pg_stats hasn't been used anymore ?
another question is:
I noticed when i send the sql :"select catcode from pois goup by catcode",
the function :double estimate_num_groups(PlannerInfo *root, List
*groupExprs, double input_rows)  uses pg_statistic.staattnum to set
Agg.numGroups, then if the pg_statistic.staattnum is incorrect ,does
postgresql still use this infomation to set Agg.numGroups ?

Re: [GENERAL] postgreSQL not working after upgrade to Ubuntu 10.4

[Alan Hodgson]

On Friday 07 May 2010, AllieH  wrote:
> Yesterday I upgraded from Ubuntu 9.10 to 10.4. Unfortunately this has
>  caused me serious problems with everything related to postgreSQL. It
>  seems as though the new version of ubuntu is requiring me to use
>  PostgreSQL 8.4 instead of 8.3 which I was previously using. I tried
>  installing 8.4 but I can't seem to get the database to start. Has anyone
>  had a similar problems?
> 

Major OS version upgrades don't transparently do database upgrades. You have 
to take care of things like that yourself.

In particular, for PostgreSQL, you would need to dump all your databases 
using 8.3, then upgrade, then create a new 8.4 installation and restore the 
databases into it. Sadly PostgreSQL does not do in-place major version 
upgrades.

In your case, you need to stop and make a couple good filesystem-level 
backups of your database. Then you need to find a way to either get 8.3 
working on that machine, or move the database to another machine where you 
can get it working, so you can do the PostgreSQL backup. Then you can think 
about getting 8.4 setup on your upgraded machine and restore that backup 
into it. You should probably also read through the 8.4 release notes to see 
if you will require changes in any applications using those databases.


-- 
"No animals were harmed in the recording of this episode. We tried but that 
damn monkey was just too fast."

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

[GENERAL] list of databases in C ? libpq ?

[Joao Ferreira gmail]

Hello all,

I need to write an application in C to read the list of databases
currently in the server. very much like a "psql -l"...

but I need it in C ! I never used C before to access PG.

the libpq API seems a bit scary ! Is there anything, shipped with
postgresql, other than libpq that would make my life simpler ?

thanks a lot

Joao



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

Re: [GENERAL] initdb fails on Centos 5.4 x64

[Tom Lane]

valentin.hoc...@kabelbw.de (Valentin Hocher) writes:
> creating conversions ... FATAL:  could not load library 
> "/usr/lib64/pgsql/utf8_and_shift_jis_2004.so": 
> /usr/lib64/pgsql/utf8_and_shift_jis_2004.so: failed to map segment from 
> shared object: Cannot allocate memory

Hmm, there was something similar reported last month, but that person
was completely unhelpful at tracking down the problem.  Could we see
the output of "ldd /usr/lib64/pgsql/utf8_and_shift_jis_2004.so"
and "readelf -d /usr/lib64/pgsql/utf8_and_shift_jis_2004.so" ?

regards, tom lane

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

Re: [GENERAL] what's the relation between pathkey and path in optimize phase?

[Tom Lane]

sunpeng  writes:
> what's the relation between pathkey and path in optimize phase?

pathkey is a data structure representing the ordering of a path.
Have you read src/backend/optimizer/README ?

regards, tom lane

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

Re: [GENERAL] postgreSQL not working after upgrade to Ubuntu 10.4

[Steve Crawford]

AllieH wrote:

Yesterday I upgraded from Ubuntu 9.10 to 10.4. Unfortunately this has caused
me serious problems with everything related to postgreSQL. It seems as
though the new version of ubuntu is requiring me to use PostgreSQL 8.4
instead of 8.3 which I was previously using. I tried installing 8.4 but I
can't seem to get the database to start. Has anyone had a similar problems?
  

Probably, depending on method of upgrade.

Try looking at the output of:
ls /etc/postgresql
ls /etc/init.d/postgresql*
ls /var/lib/postgresql

If you did a basic upgrade you may find that both versions are installed 
and you just have to start the old one (then do your dumps so you can 
upgrade). Alternately, you may want to use the 
/usr/bin/pg_upgradecluster script that is typically available on Ubuntu 
and (usually, in reasonably vanilla cases) does the upgrade for you. In 
all cases, do your best to get a backup, first.


Cheers,
Steve


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

Re: [GENERAL] postgreSQL not working after upgrade to Ubuntu 10.4

[Steve Clark]

On 05/07/2010 12:10 PM, AllieH wrote:


Yesterday I upgraded from Ubuntu 9.10 to 10.4. Unfortunately this has caused
me serious problems with everything related to postgreSQL. It seems as
though the new version of ubuntu is requiring me to use PostgreSQL 8.4
instead of 8.3 which I was previously using. I tried installing 8.4 but I
can't seem to get the database to start. Has anyone had a similar problems?

You are probably going to have to do an initdb. 8.4 db is not compatible
 8.3 db.
I ran into this with Fedora when they went from 8.3 to 8.4 in the middle of
the life of Fedora 12 - luckily I had been doing daily pg_dumps so all I had
to do was initdb under 8.4 and psql < my backup.
--
Stephen Clark
NetWolves
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.cl...@netwolves.com
www.netwolves.com

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

Re: [GENERAL] list of databases in C ? libpq ?

[Alex Hunsaker]

On Fri, May 7, 2010 at 11:18, Joao Ferreira gmail
 wrote:
> Hello all,
>
> I need to write an application in C to read the list of databases
> currently in the server. very much like a "psql -l"...

select datname as database from pg_database;

> but I need it in C ! I never used C before to access PG.
>
> the libpq API seems a bit scary ! Is there anything, shipped with
> postgresql, other than libpq that would make my life simpler ?

Maybe ecpg http://www.postgresql.org/docs/current/static/ecpg.html ? I
also hear good things about libpqtypes making some things easier
http://pgfoundry.org/projects/libpqtypes/.

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

Re: [GENERAL] postgreSQL not working after upgrade to Ubuntu 10.4

[AllieH]

Thanks for all the help - I needed to an initdb and then I also had to do:

sudo dpkg -i /var/cache/pat/archives/postgresql-client-8.4_8.4.3-1_i386.deb

but it's finally working!! Now I just have to get all my data back but after
this headache I think that will be comparatively easy!

Thanks for all the help!

Allie


Steve Crawford wrote:
> 
> AllieH wrote:
>> Yesterday I upgraded from Ubuntu 9.10 to 10.4. Unfortunately this has
>> caused
>> me serious problems with everything related to postgreSQL. It seems as
>> though the new version of ubuntu is requiring me to use PostgreSQL 8.4
>> instead of 8.3 which I was previously using. I tried installing 8.4 but I
>> can't seem to get the database to start. Has anyone had a similar
>> problems?
>>   
> Probably, depending on method of upgrade.
> 
> Try looking at the output of:
> ls /etc/postgresql
> ls /etc/init.d/postgresql*
> ls /var/lib/postgresql
> 
> If you did a basic upgrade you may find that both versions are installed 
> and you just have to start the old one (then do your dumps so you can 
> upgrade). Alternately, you may want to use the 
> /usr/bin/pg_upgradecluster script that is typically available on Ubuntu 
> and (usually, in reasonably vanilla cases) does the upgrade for you. In 
> all cases, do your best to get a backup, first.
> 
> Cheers,
> Steve
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 
> 

-- 
View this message in context: 
http://old.nabble.com/postgreSQL-not-working-after-upgrade-to-Ubuntu-10.4-tp28488130p28489311.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] postgreSQL not working after upgrade to Ubuntu 10.4

[Steve Clark]

On 05/07/2010 01:22 PM, Alan Hodgson wrote:

On Friday 07 May 2010, AllieH  wrote:

Yesterday I upgraded from Ubuntu 9.10 to 10.4. Unfortunately this has
  caused me serious problems with everything related to postgreSQL. It
  seems as though the new version of ubuntu is requiring me to use
  PostgreSQL 8.4 instead of 8.3 which I was previously using. I tried
  installing 8.4 but I can't seem to get the database to start. Has anyone
  had a similar problems?



Major OS version upgrades don't transparently do database upgrades. You have
to take care of things like that yourself.

Yes but the install procedure should be smart enough to look and see if you are 
using a previous
version of Postgres and prompt you if you want to continue installing the
new database.



In particular, for PostgreSQL, you would need to dump all your databases
using 8.3, then upgrade, then create a new 8.4 installation and restore the
databases into it. Sadly PostgreSQL does not do in-place major version
upgrades.

In your case, you need to stop and make a couple good filesystem-level
backups of your database. Then you need to find a way to either get 8.3
working on that machine, or move the database to another machine where you
can get it working, so you can do the PostgreSQL backup. Then you can think
about getting 8.4 setup on your upgraded machine and restore that backup
into it. You should probably also read through the 8.4 release notes to see
if you will require changes in any applications using those databases.





--
Stephen Clark
NetWolves
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.cl...@netwolves.com
www.netwolves.com

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

Re: [GENERAL] list of databases in C ? libpq ?

[Steve Atkins]

On May 7, 2010, at 10:18 AM, Joao Ferreira gmail wrote:

> Hello all,
> 
> I need to write an application in C to read the list of databases
> currently in the server. very much like a "psql -l"...
> 
> but I need it in C ! I never used C before to access PG.
> 
> the libpq API seems a bit scary !

It's easier than it looks, really:

PGconn *db;
PGresult *result;
int i;

db = PQconnectdb("host=127.0.0.1 dbname=template1 user=joao password=foo");
if(0 == db || PQstatus(db) != CONNECTION_OK) {
  fprintf(stderr, "Failed to connect to db: %s", db ? PQerrorMessage(db) : 
"unknown error\n");
  exit(1);
}
result = PQexec(db, "select datname from pg_catalog.pg_database");
if(0 == result || PQresultStatus(result) != PGRES_TUPLES_OK) {
  fprintf(stderr, "Failed to run query: %s", result ? 
PQresultErrorMessage(result) : "unknown error\n");
  exit(1);
}
for(i=0; i < PQntuples(result); ++i) {
  printf("%s\n", PQgetvalue(result, i, 0));
}
PQclear(result);
PQfinish(db);

(Untested)

> Is there anything, shipped with
> postgresql, other than libpq that would make my life simpler ?

Not for C, I don't think. Other languages (C++, perl ...) have bindings that 
simplify some things.

Cheers,
  Steve



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

Re: [GENERAL] postgreSQL not working after upgrade to Ubuntu 10.4

[Yeb Havinga]

AllieH wrote:

Yesterday I upgraded from Ubuntu 9.10 to 10.4. Unfortunately this has caused
me serious problems with everything related to postgreSQL. It seems as
though the new version of ubuntu is requiring me to use PostgreSQL 8.4
instead of 8.3 which I was previously using. I tried installing 8.4 but I
can't seem to get the database to start. Has anyone had a similar problems?
  
Maybe this could have been prevented by 'holding' (in aptitude press '=' 
on the packages to hold at current level) the postgresql package at the 
current level.


Btw: it is possible to mix packages from multiple versions - you might 
be able to install the 8.3 again by adding "jaunty" back to 
/etc/apt/sources.list (not replace lucid but just add jaunty), run 
"apt-get update", then find the package "postgresql" in the aptitude 
tool, and press 'v': you then get all the versions available. Select the 
8.3 version, press '+' and 'g'. I just verified this on a test fresh 
10.4 installation and after 'g' I get no conflicts at all, however, 9.10 
(karmic) does not have postgres 8.4, but 9.04 (jaunty) does.


regards,
Yeb Havinga



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

[GENERAL] Question about joins, left outer and others

[Jeff Ross]

This is going to be simple, I'm sure, but I'm stumped.  It's a little long but 
I thought as I wrote it out I'd get the aha! moment and since that didn't 
happen, here goes.


I have a table of education categories:

jr...@wykids localhost# \d education_categories
  Table "public.education_categories"
  Column   |  Type   |Modifiers

---+-+--
 ed_cat_id | integer | not null default 
nextval('education_categories_ed_cat_id_seq'::regclass)

 ed_cat_name   | text| not null
 ed_cat_short_name | text| not null
Indexes:
"education_categories_pkey" PRIMARY KEY, btree (ed_cat_id)
Referenced by:
TABLE "people_education_categories" CONSTRAINT 
"people_education_categories_pp_ed_cat_id_fkey" FOREIGN KEY (pp_ed_cat_id) 
REFERENCES education_categories(ed_cat_id)


filled with these values:

jr...@wykids localhost# select * from education_categories order by ed_cat_id;
 ed_cat_id |ed_cat_name| ed_cat_short_name
---+---+---
 1 | Some High School  | some_high_school
 2 | High School or GED| high_school
 3 | Some College  | some_college
 4 | Trade or Technical School | trade
 5 | Certificate or License| cert
 6 | Associates| associates
 7 | Bachelors | bachelors
 8 | Masters   | masters
 9 | Doctorate | doctorate
(9 rows)

I have another table that contains information about the education level 
someone might have.  A person can have more than one entry in the table--the 
idea is to track their education status over time.


jr...@wykids localhost# \d people_education_categories
 Table "public.people_education_categories"
 Column  |Type |  Modifiers

-+-+
 pp_ed_cat_id| integer | not null
 pp_ed_cat_pp_id | integer | not null
 pp_ed_cat_subject   | text|
 pp_ed_cat_institution   | text|
 pp_ed_cat_date_achieved | date|
 pp_ed_cat_date_expires  | date|
 pp_ed_cat_date_recorded | timestamp without time zone | default 
('now'::text)::date

Foreign-key constraints:
"people_education_categories_pp_ed_cat_id_fkey" FOREIGN KEY 
(pp_ed_cat_id) REFERENCES education_categories(ed_cat_id)
"people_education_categories_pp_ed_cat_pp_id_fkey" FOREIGN KEY 
(pp_ed_cat_pp_id) REFERENCES people(pp_id)


I'm trying see all of the possible education categories for any person.  If 
they have no entry in the people education categories table for the 
corresponding category I'd want the null  values to display, so for someone 
with a bachelors degree I'd like to see something like this:


   Level   |  Subject|Institution
---+-+--
 Some High School  | |
 High School or GED| |
 Some College  | |
 Trade or Technical School | |
 Certificate or License| |
 Associates| |
 Bachelors |Elementary Education | University of Wyoming
 Masters   | |
 Doctorate | |


Here's a sample entry of a person with a bachelor's degree--this is the only 
entry for this person in the people_education_categories table:


jr...@wykids localhost# select * from people_education_categories where 
pp_ed_cat_pp_id = 1796;

-[ RECORD 1 ]---+
pp_ed_cat_id| 7
pp_ed_cat_pp_id | 1796
pp_ed_cat_subject   | Elementary Education/Fine Arts
pp_ed_cat_institution   |
pp_ed_cat_date_achieved |
pp_ed_cat_date_expires  |
pp_ed_cat_date_recorded | 2009-12-17 00:00:00

To get the output I want above, I'd think I'd need to do a left outer join 
like this:


jr...@wykids localhost# select ed_cat_name as "Level", pp_ed_cat_subject as 
"Subject", pp_ed_cat_institution as "Institution" from

education_categories left outer join people_education_categories on
(ed_cat_id = pp_ed_cat_id) where pp_ed_cat_pp_id = 1796;

but that only gives me this:

   Level   | Subject| Institution
---++-
 Bachelors | Elementary Education/Fine Arts |
(1 row)

In fact, every join combination (left|right|full|inner|outer) I've tried gives 
me the same result.


Clearly I'm missing something obvious and will welcome all hints, 
clue-by-fo

Re: [GENERAL] Formatted reports

[Sorin Schwimmer]

Definitely SQL*PLUS, you're right.

I managed to create a report as a UNION select between data and SUMs of data 
queries, with relevant fields for GROUPing and ORDERing. I would have liked to 
be able to get these "relevant fields" shown only once, so I started to check 
what is available, and stumbled over Oracle's approach.

I'll have a look at the suggested reporting tools.

Thanks,
SxN




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

Re: [GENERAL] Question about joins, left outer and others

[Andy Colson]

On 5/7/2010 2:38 PM, Jeff Ross wrote:

This is going to be simple, I'm sure, but I'm stumped.  It's a little
long but I thought as I wrote it out I'd get the aha! moment and since
that didn't happen, here goes.



snip


To get the output I want above, I'd think I'd need to do a left outer
join like this:

jr...@wykids localhost# select ed_cat_name as "Level", pp_ed_cat_subject
as "Subject", pp_ed_cat_institution as "Institution" from
education_categories left outer join people_education_categories on
(ed_cat_id = pp_ed_cat_id) where pp_ed_cat_pp_id = 1796;

but that only gives me this:

Level | Subject | Institution
---++-
Bachelors | Elementary Education/Fine Arts |
(1 row)



Its the "where pp_ed_cat_pp_id = 1796" that is causing it, not the join.

try adding "or pp_ed_cat_pp_id is null"

-Andy

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

Re: [GENERAL] Question about joins, left outer and others

[Tom Lane]

Jeff Ross  writes:
> To get the output I want above, I'd think I'd need to do a left outer join 
> like this:

> jr...@wykids localhost# select ed_cat_name as "Level", pp_ed_cat_subject as 
> "Subject", pp_ed_cat_institution as "Institution" from
> education_categories left outer join people_education_categories on
> (ed_cat_id = pp_ed_cat_id) where pp_ed_cat_pp_id = 1796;

This query is actually equivalent to a plain join.  The reason is that
no null-extended row can possibly satisfy the WHERE clause, since the
WHERE is constraining a field from the inner side of the join.  Remember
WHERE filters the output of the join.

Possibly what you want is

select ... from
education_categories left outer join people_education_categories on
(ed_cat_id = pp_ed_cat_id and pp_ed_cat_pp_id = 1796);

regards, tom lane

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

Re: [GENERAL] Question about joins, left outer and others

[Jeff Ross]

Tom Lane wrote:

Jeff Ross  writes:
To get the output I want above, I'd think I'd need to do a left outer join 
like this:


jr...@wykids localhost# select ed_cat_name as "Level", pp_ed_cat_subject as 
"Subject", pp_ed_cat_institution as "Institution" from

education_categories left outer join people_education_categories on
(ed_cat_id = pp_ed_cat_id) where pp_ed_cat_pp_id = 1796;


This query is actually equivalent to a plain join.  The reason is that
no null-extended row can possibly satisfy the WHERE clause, since the
WHERE is constraining a field from the inner side of the join.  Remember
WHERE filters the output of the join.

Possibly what you want is

select ... from
education_categories left outer join people_education_categories on
(ed_cat_id = pp_ed_cat_id and pp_ed_cat_pp_id = 1796);

regards, tom lane



Thanks, Tom and Andy.  This is exactly what I was missing.

Jeff Ross

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

[GENERAL] psql weird behaviour with charset encodings

[hernan gonzalez]

(Disclaimer: I've been using Postgresql for quite a long time, I
usually deal with non-ascii LATIN-9 characters ,
but that has never been a problem, until now)

My issue summarized: when psql is invoked from a user who has a locale
different from that of the database, the tabular output
is wrong for some text fields. The weird thing is that those text
fields are not just garbled, but empty. And more weird:
this does not happen in the expanded output format (\x). Apparently
it's not a terminal problem (I see all right after \x),
nor a client_encoding issue (idem). So...?

Details follow.
My scenario: Fedora 12, Postgresql 8.4.3 compiled from source.

Database encoding (global) LATIN9.
User postgres locale: LANG=en_US.iso885915,
User root locale LANG=en_US.UTF-8

When I connect from postgres user, all is right.
When I connect from root, it's not right... except with \x
Example (here last_name field has one non ascii character, N WITH TILDE) :



[r...@myserver ~]# su - postgres
[postg...@myserver ~]$ psql db
psql (8.4.3)
db=# \set
...
ENCODING = 'LATIN9'
db=# select first_name,last_name,birth_date from persons where rid= 143;
 first_name  | last_name | birth_date
--+---+
Guillermo    | Calcaño   | 1996-09-30
db=# \x
db=# select first_name,last_name,birth_date from persons where rid= 143;
-[ RECORD 1 ]
first_name | Guillermo
last_name  | Calcaño
birth_date | 1996-09-30


[r...@myserver ~]# /usr/local/pgsql/bin/psql -U postgres db
psql (8.4.3)
db=# \set
...
ENCODING = 'LATIN9'
db=# select first_name,last_name,birth_date from persons where rid= 143;
 first_name  | last_name | birth_date
--+---+
Guillermo    |    | 1996-09-30
(1 row)
db=# \x
db=# select first_name,last_name,birth_date from persons where rid= 143;
-[ RECORD 1 ]
first_name | Guillermo
last_name  | Calcaño
birth_date | 1996-09-30

==

It looks as it psql, in tabular form, needs to compute the lenght of
the field to output, and for this uses the user locale (not
the client_encoding, mind you, but the locale of the user that is
running the psql process). In case of mismatch,
it cannot decode the string and compute the lenght, so... it assumes
length=0 (?)
Is this the expect behaviour ? Has this behaviour changed recently?

--
Hernán J. González
http://hjg.com.ar/

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

Re: [GENERAL] psql weird behaviour with charset encodings

[Tom Lane]

hernan gonzalez  writes:
> My scenario: Fedora 12, Postgresql 8.4.3 compiled from source.

> Database encoding (global) LATIN9.
> User postgres locale: LANG=en_US.iso885915,
> User root locale LANG=en_US.UTF-8

> When I connect from postgres user, all is right.
> When I connect from root, it's not right... except with \x

What's client_encoding set to in the two cases?  If it's not utf8,
does changing it to that improve matters?  Alternatively, see what
xterm (or whatever terminal window you're using) thinks the encoding
is, and change it to match psql's client_encoding.

regards, tom lane

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

Re: [GENERAL] psql weird behaviour with charset encodings

[hernan gonzalez]

It's surely not a xterm problem, I see the characters ok with just the
\x formatting. I can check also the output redirecting to a file.

My original client_encoding seems to be LATIN9 in both cases,
accorging to the \set ouput.

If I change it (for the root user) to UTF8 with " SET CLIENT_ENCODING
TO 'UTF8';  " the conversion from LATIN9 to UTF8 indeed takes place
(and I see the characters ok, by switching my terminal to UTF8).

(BTW: I understood from the  docs that  " \set ENCODING 'UTF8'; " is
equivalent but this does nothing in my case)

But I actually dont want that. I want psql to not try any charset
conversion, just give me the raw text as is stored in the db. He seems
to do this when \x is set. But it seems that he need to compute lenght
of the strings and for this he needs to use the string routines from
his own locale.

I'm uncertain yet if this is the expected behaviour.


> What's client_encoding set to in the two cases?  If it's not utf8,
> does changing it to that improve matters?  Alternatively, see what
> xterm (or whatever terminal window you're using) thinks the encoding
> is, and change it to match psql's client_encoding.
>
>                        regards, tom lane
>



-- 
Hernán J. González
http://hjg.com.ar/

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

Re: [GENERAL] psql weird behaviour with charset encodings

[Tom Lane]

hernan gonzalez  writes:
> But I actually dont want that. I want psql to not try any charset
> conversion, just give me the raw text as is stored in the db.

Well, that's what it's doing (given the default setting with
client_encoding equal to server_encoding), and then xterm is
misdisplaying the text because xterm thinks it's utf8.  I'm
not very clear on why the \x case seems to display correctly
anyway, but you really need the terminal's encoding to agree
with client_encoding in order to get non-ASCII characters to
work well.

regards, tom lane

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

Re: [GENERAL] psql weird behaviour with charset encodings

[hernan gonzalez]

Mmm no:  \x displays correctly for me because it sends
 the raw text (in LATIN9) and I have set my terminal in LATIN9 (or ISO-8859-15)

And it's not that "xterm is misdisplaying" the text, it just that psql
is ouputting
an EMPTY (zero lenght) string for that field.
(I can even send the output to a file with \o, and check it in hexadecimal,
to re-verify  that it's not a terminal problem - it's not).

The issue is that psql tries (apparently) to convert to UTF8
(even when he plans to output the raw text -LATIN9 in this case)
just for computing the lenght of the field, to build the table.
And because for this computation he (apparently) rely on the string
routines with it's own locale, instead of the DB or client encoding.

That's why no problem arises with the \x switch.

I believe this is wrong, because when the client does not specify
an encoding, no conversion should be attempted.

Hernán

>
> Well, that's what it's doing (given the default setting with
> client_encoding equal to server_encoding), and then xterm is
> misdisplaying the text because xterm thinks it's utf8.  I'm
> not very clear on why the \x case seems to display correctly
> anyway, but you really need the terminal's encoding to agree
> with client_encoding in order to get non-ASCII characters to
> work well.
>
>                        regards, tom lane
>



-- 
Hernán J. González
http://hjg.com.ar/

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

Re: [GENERAL] initdb fails on Centos 5.4 x64

[Tom Lane]

valentin.hoc...@kabelbw.de (Valentin Hocher) writes:
> creating conversions ... FATAL:  could not load library 
> "/usr/lib64/pgsql/utf8_and_shift_jis_2004.so": 
> /usr/lib64/pgsql/utf8_and_shift_jis_2004.so: failed to map segment from 
> shared object: Cannot allocate memory

FWIW, I tried to duplicate this on a RHEL5-U4 installation, without success:
initdb runs fine for me.  I assume you're using the RPMs from
http://yum.pgsqlrpms.org/8.4/redhat/rhel-5-x86_64/
?  If so, it seems like this must be some problem in CentOS' version of
the distro.  What I find installed on Red Hat's machine is
kernel-2.6.18-164.el5
glibc-2.5-42
(and a boatload of other stuff of course, but those seem like the
packages most likely to be involved).  Maybe CentOS 5.4 is a bit
different?

regards, tom lane

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

Re: [GENERAL] psql weird behaviour with charset encodings

[Tom Lane]

hernan gonzalez  writes:
> The issue is that psql tries (apparently) to convert to UTF8
> (even when he plans to output the raw text -LATIN9 in this case)
> just for computing the lenght of the field, to build the table.
> And because for this computation he (apparently) rely on the string
> routines with it's own locale, instead of the DB or client encoding.

I didn't believe this, since I know perfectly well that the formatting
code doesn't rely on any OS-supplied width calculations.  But when I
tested it out, I found I could reproduce Hernan's problem on Fedora 11.
Some tracing showed that the problem is here:

fprintf(fout, "%.*s", bytes_to_output,
this_line->ptr + bytes_output[j]);

As the variable name indicates, psql has carefully calculated the number
of *bytes* it wants to print.  However, it appears that glibc's printf
code interprets the parameter as the number of *characters* to print,
and to determine what's a character it assumes the string is in the
environment LC_CTYPE's encoding.  I haven't dug into the glibc code to
check, but it's presumably barfing because the string isn't valid
according to UTF8 encoding, and then failing to print anything.

It appears to me that this behavior violates the Single Unix Spec,
which says very clearly that the count is a count of bytes:
http://www.opengroup.org/onlinepubs/007908799/xsh/fprintf.html
However, I'm quite sure that our chances of persuading the glibc boys
that this is a bad idea are zero.  I think we're going to have to
change the code to not rely on %.*s here.  Even without the charset
mismatch in Hernan's example, we'd be printing the wrong amount of
data anytime the LC_CTYPE charset is multibyte.  (IOW, the code should
do the wrong thing with forced-line-wrap cases if LC_CTYPE is UTF8,
even if client_encoding is too; anybody want to check?)

The above coding is new in 8.4, but it's probably not the only use of
%.*s --- we had better go looking for other trouble spots, too.

regards, tom lane

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

Re: [GENERAL] psql weird behaviour with charset encodings

[hgonzalez]

However, it appears that glibc's printf

code interprets the parameter as the number of *characters* to print,
and to determine what's a character it assumes the string is in the
environment LC_CTYPE's encoding.

Well, I myself have problems to believe that :-)
This would be nasty... Are you sure?

I couldn reproduce that.
I made a quick test, passing a utf-8 encoded string
(5 bytes correspoding to 4 unicode chars: "niño")
And my glib (same Fedora 12) seems to count bytes,
as it should.

#include
main () {
char s[] = "ni\xc3\xb1o";
printf("|%.*s|\n",5,s);
}

This, compiled with gcc 4.4.3, run with my root locale (utf8)
did not padded a blank. ie it worked as expected.

Hernán

Re: [GENERAL] psql weird behaviour with charset encodings

[hernan gonzalez]

Sorry about a error in my previous example (mixed width and precision).
But the conclusion is the same - it works on bytes:

#include
main () {
char s[] = "ni\xc3\xb1o"; /* 5 bytes , 4 utf8 chars */
printf("|%*s|\n",6,s); /* this should pad a black */
printf("|%.*s|\n",4,s); /* this should eat a char */
}

[r...@myserv tmp]#  ./a.out | od -t cx1
000   |   n   i 303 261   o   |  \n   |   n   i 303 261   |  \n
 7c  20  6e  69  c3  b1  6f  7c  0a  7c  6e  69  c3  b1  7c  0a


Hernán



On Fri, May 7, 2010 at 10:48 PM,   wrote:
>> However, it appears that glibc's printf
> code interprets the parameter as the number of *characters* to print,
> and to determine what's a character it assumes the string is in the
> environment LC_CTYPE's encoding.
>
> Well, I myself have problems to believe that :-)
> This would be nasty... Are you sure?
>
> I couldn reproduce that.
> I made a quick test, passing a utf-8 encoded string
> (5 bytes correspoding to 4 unicode chars: "niño")
> And my glib (same Fedora 12) seems to count bytes,
> as it should.
>
> #include
> main () {
> char s[] = "ni\xc3\xb1o";
> printf("|%.*s|\n",5,s);
> }
>
> This, compiled with gcc 4.4.3, run with my root locale (utf8)
> did not padded a blank. i.e. it worked as expected.
>
> Hernán

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

[GENERAL] peer-to-peer replication with Postgres

[Mike Christensen]

I'm considering using a cloud hosting solution for my website.  It
will probably be either Amazon, Rackspace or Hosting.com.  I'm still
comparing.  Either way, my site will consist of multiple virtual
server instances that I can create and destroy as needed.  Each
virtual machine instance will be self contained, meaning it'll run the
website and its own instance of postgres.  The website will only talk
to the local DB instance.  However, if I'm running several machine
instances, I want all the databases to keep in sync preferably with as
little lag as possible.

This is not a master/slave replication issue where there's one big DB
that's always up and everything syncs to, this is basically total
peer-to-peer replication where any time data is updated on one server,
an update command gets sent to all the other servers.  I would also
have to address the issue when I provision a new virtual server, I'd
have to import the current data into the DB seamlessly.

What's the best way to do this?  Looks like something like pgPool
might be what I want, but I haven't looked into it deeply yet.
Thanks!!

Mike

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