headers
Ibrahim Harrani | 1 Mar 2010 11:24
Picon

Re: vacuum deadlock

Hi,

Yes,  I am trying to full vacuum.

I think, I  found the problem. There were more then 10 million records which my scripts trying to delete at night from a table while other processes were adding new entries to same table.
Delete can't finish in the day, and script tries to delete the table again in next night via crontab
then this cause to dead lock?

SQL query: "delete from mytable where createtime < now - '10 days';


We added 1K limit option to delete query in a for loop. Now I can delete all entries but slowly 

DELETE FROM mytable WHERE id = any (array(SELECT id FROM mytable WHERE createtime < NOW() - INTERVAL '10 day'  LIMIT 1000))

I am planning to partion(day based) this large table, then I will simply drop the tables after certain days.

Do you have any other suggestion?

Thanks.

On Sat, Feb 6, 2010 at 8:11 AM, Tom Lane <tgl <at> sss.pgh.pa.us> wrote:
Ibrahim Harrani <ibrahim.harrani <at> gmail.com> writes:
> I got  a deadlock while vacuuming all databases with vacuumdb command.

> vacuumdb: vacuuming of database "mydb" failed: ERROR:  deadlock detected
> DETAIL:  Process 1294 waits for AccessExclusiveLock on relation 2662 of
> database     ; blocked by process 1807.
> Process 1807 waits for AccessShareLock on relation 1259 of database 16389;
> blocked by process 1294.

vacuumdb -f you mean?  An ordinary vacuum wouldn't be trying to take
AccessExclusiveLock.

It might be that you have an instance of a failure that was identified
just a couple weeks ago:
http://archives.postgresql.org/pgsql-committers/2010-01/msg00199.php

                       regards, tom lane

Permalink | Reply | 0 comments |
headers
Jan-Peter Seifert | 1 Mar 2010 14:49
Picon
Picon

Re: LATIN2 encoding and Polish_Poland locale

Hello Andrzej,

try this:

Identify the code page Windows is using for LATIN2 with the PostgreSQL tool pg_controldata (8.2 server's
version). Look for the number following the dot in LC_COLLATE / LC_TYPE. For LATIN1 it's 28591.

Try creating the database for 8.4 with the PostgreSQL tool createdb (v8.4.x) (example for LATIN1 and
German locale, locale = LC_COLLATE / LC_TYPE in 8.2):

createdb -U postgres -h <Host> -p <Port> --owner=name_of_owner --locale=German_Germany.28591
--encoding=LATIN1 --template=template0 database_name

Good luck,

Peter

-------- Original-Nachricht --------
> Datum: Sun, 28 Feb 2010 22:44:49 +0100
> Von: Andrzej Angowski <andrzej.angowski <at> gmail.com>
> An: pgsql-admin <at> postgresql.org
> Betreff: [ADMIN] LATIN2 encoding and Polish_Poland locale

> All,
> 
> I am running PostgreSQL database on Windows XP with SP3 machine.
> Recently I have tried to upgrade from 8.2.15 to 8.4.2 but I encounter
> the following issue: one of my databases is LATIN2 encoded
> (ISO-8859-2). It works fine on 8.2.15 (LATIN2 encoding and
> Polish_Poland locale - I have Polish characters and sorting works
> fine), but on 8.4.2 default encoding for Polish_Poland is UTF-8. When
> I try to set up LATIN2 encoding with Polish_Poland locale I get the
> following error:
> 
> >createdb -E LATIN2 -l Polish_Poland test
> createdb: database creation failed: ERROR:  encoding LATIN2 does not
> match locale Polish_Poland
> DETAIL:  The chosen LC_CTYPE setting requires encoding WIN1250.
> 
> In 8.4 manual -> Section 22.2 Character Set Support one can read:
> An important restriction, however, is that each database's character
> set must be compatible with the database's LC_CTYPE and LC_COLLATE
> locale settings. For C or POSIX locale, any character set is allowed,
> but for other locales there is only one character set that will work
> correctly. (On Windows, however, UTF-8 encoding can be used with any
> locale.)
> 
> So I manage to establish a DB with LATIN2 encoding with C locale:
> >createdb -E LATIN2 -l C -T template0 test
> 
> I have Polish characters but sorting is not correct.
> 
> How can I set up Polish_Poland locale with LATIN2 encoding in PostgreSQL
> 8.4.2?
> 
> 
> Thanks,
> -- 
> Andrzej Angowski
> 
> -- 
> Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

-- 
Sicherer, schneller und einfacher. Die aktuellen Internet-Browser -
jetzt kostenlos herunterladen! http://portal.gmx.net/de/go/chbrowser

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Permalink | Reply | 1 comment |
headers
Achilleas Mantzios | 1 Mar 2010 16:31

severely wierd problem & PgSQL log analyzer

Hi,
i am facing a very wierd situation, a piece of java code seems correct, however *some* times a connection
instead of being rollbacked gets commited.
(which has driven me nuts). So what i did, is replicate the problem with full logging enabled from java, in
order to know at any point
in the code against which postgres pid backend i am running against.
At the same time i had full logging enabled in postgresql 8.3 and i tried to trace the exact backend which
resulted in commit.
(i try to replicate the problem by hitting the reload button in firefox).
However that gives me about over 160 instances of the program, making difficult to put together all those
sessions from the logs,
and it seems i cannot see whats happening unless i have a handy tool that can extract info from the logs and
present it in a human way.

Any suggestions for a good pgsql log analyzer? i am currently struggling with http://pgfouine.projects.postgresql.org/tutorial.html
(having failed with http://bucardo.org/wiki/Pgsi which wants only syslog)
-- 
Achilleas Mantzios

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Permalink | Reply | 15 comments |
headers
Andrzej Angowski | 1 Mar 2010 19:09
Picon

Re: LATIN2 encoding and Polish_Poland locale

Peter,

The following actually works:
>createdb -E LATIN2 -l Polish_Poland.28592 test

Thanks a lot!

2010/3/1 Jan-Peter Seifert <Jan-Peter.Seifert <at> gmx.de>:
> Hello Andrzej,
>
> try this:
>
> Identify the code page Windows is using for LATIN2 with the PostgreSQL tool pg_controldata (8.2 server's
version). Look for the number following the dot in LC_COLLATE / LC_TYPE. For LATIN1 it's 28591.
>
> Try creating the database for 8.4 with the PostgreSQL tool createdb (v8.4.x) (example for LATIN1 and
German locale, locale = LC_COLLATE / LC_TYPE in 8.2):
>
> createdb -U postgres -h <Host> -p <Port> --owner=name_of_owner --locale=German_Germany.28591
--encoding=LATIN1 --template=template0 database_name
>
> Good luck,
>
> Peter
>
> -------- Original-Nachricht --------
>> Datum: Sun, 28 Feb 2010 22:44:49 +0100
>> Von: Andrzej Angowski <andrzej.angowski <at> gmail.com>
>> An: pgsql-admin <at> postgresql.org
>> Betreff: [ADMIN] LATIN2 encoding and Polish_Poland locale
>
>> All,
>>
>> I am running PostgreSQL database on Windows XP with SP3 machine.
>> Recently I have tried to upgrade from 8.2.15 to 8.4.2 but I encounter
>> the following issue: one of my databases is LATIN2 encoded
>> (ISO-8859-2). It works fine on 8.2.15 (LATIN2 encoding and
>> Polish_Poland locale - I have Polish characters and sorting works
>> fine), but on 8.4.2 default encoding for Polish_Poland is UTF-8. When
>> I try to set up LATIN2 encoding with Polish_Poland locale I get the
>> following error:
>>
>> >createdb -E LATIN2 -l Polish_Poland test
>> createdb: database creation failed: ERROR:  encoding LATIN2 does not
>> match locale Polish_Poland
>> DETAIL:  The chosen LC_CTYPE setting requires encoding WIN1250.
>>
>> In 8.4 manual -> Section 22.2 Character Set Support one can read:
>> An important restriction, however, is that each database's character
>> set must be compatible with the database's LC_CTYPE and LC_COLLATE
>> locale settings. For C or POSIX locale, any character set is allowed,
>> but for other locales there is only one character set that will work
>> correctly. (On Windows, however, UTF-8 encoding can be used with any
>> locale.)
>>
>> So I manage to establish a DB with LATIN2 encoding with C locale:
>> >createdb -E LATIN2 -l C -T template0 test
>>
>> I have Polish characters but sorting is not correct.
>>
>> How can I set up Polish_Poland locale with LATIN2 encoding in PostgreSQL
>> 8.4.2?
>>
>>
>> Thanks,
>> --
>> Andrzej Angowski
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>
> --
> Sicherer, schneller und einfacher. Die aktuellen Internet-Browser -
> jetzt kostenlos herunterladen! http://portal.gmx.net/de/go/chbrowser
>

-- 
Andrzej Angowski

e-mail : Andrzej.Angowski <at> gmail.com

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Permalink | Reply | 0 comments |
headers
Major Services | 2 Mar 2010 09:43
Picon

password authentication failed for user postgres

Hi,

1. I have added  listen_addresses = '*' ; in  postgresql.conf

2. And have made the following changes to pg_hba.conf

local   DATABASE    USER   METHOD
local    samegroup      all      password
host    DATABASE    USER    CIDR-ADDRESS  METHOD  [OPTION]
host          all                 all       0.0.0.0/0             md5
hostssl      all                 all       0.0.0.0/0            md5

3. Have restarted the server

4. I can login to the server as user postgres.

But  while trying to createuser I get the following message  :
'createuser: could not connect to database postgres:  FATAL :
password authentication failed for user "postgres"

This is on Redhat running Plesk also.

I am new to Linux as well as PostgreSQL. Help would be much appreciated.

Thanks
Viv

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Permalink | Reply | 8 comments |
headers
Iñigo Martinez Lasala | 2 Mar 2010 10:14
Favicon

Re: password authentication failed for user postgres

In order to lauch createuser you don't need to modify listen_address since you can use unix sockets. But anyway it's a requirement if you are goint to access remotely.

Add this line in first place to your pg_hba.conf
local   all         postgres                          ident sameuser


Reload postgresql and launch createuser as postgres user.


-----Original Message-----
From: Major Services <services.major <at> gmail.com>
To: pgsql-admin <at> postgresql.org
Subject: [ADMIN] password authentication failed for user postgres
Date: Tue, 2 Mar 2010 14:13:10 +0530

Hi, 1. I have added listen_addresses = '*' ; in postgresql.conf 2. And have made the following changes to pg_hba.conf local DATABASE USER METHOD local samegroup all password host DATABASE USER CIDR-ADDRESS METHOD [OPTION] host all all 0.0.0.0/0 md5 hostssl all all 0.0.0.0/0 md5 3. Have restarted the server 4. I can login to the server as user postgres. But while trying to createuser I get the following message : 'createuser: could not connect to database postgres: FATAL : password authentication failed for user "postgres" This is on Redhat running Plesk also. I am new to Linux as well as PostgreSQL. Help would be much appreciated. Thanks Viv
Permalink | Reply | 7 comments |
headers
Major Services | 2 Mar 2010 12:00
Picon

Fwd: password authentication failed for user postgres

Thank you for your message. But am in another problem now.
In between I also tried to restart the server with 'service postgresql restart'
It stopped the server, but then server start failed.
Had restarted earlier also, without a problem.

What should I do?


On Tue, Mar 2, 2010 at 2:44 PM, Iñigo Martinez Lasala <imartinez <at> vectorsf.com> wrote:
> In order to lauch createuser you don't need to modify listen_address since
> you can use unix sockets. But anyway it's a requirement if you are goint to
> access remotely.
>
> Add this line in first place to your pg_hba.conf
> local   all         postgres                          ident sameuser
>
>
> Reload postgresql and launch createuser as postgres user.
>
>
> -----Original Message-----
> From: Major Services <services.major <at> gmail.com>
> To: pgsql-admin <at> postgresql.org
> Subject: [ADMIN] password authentication failed for user postgres
> Date: Tue, 2 Mar 2010 14:13:10 +0530
>
> Hi,
>
> 1. I have added  listen_addresses = '*' ; in  postgresql.conf
>
> 2. And have made the following changes to pg_hba.conf
>
> local   DATABASE    USER   METHOD
> local    samegroup      all      password
> host    DATABASE    USER    CIDR-ADDRESS  METHOD  [OPTION]
> host          all                 all       0.0.0.0/0             md5
> hostssl      all                 all       0.0.0.0/0            md5
>
> 3. Have restarted the server
>
> 4. I can login to the server as user postgres.
>
> But  while trying to createuser I get the following message  :
> 'createuser: could not connect to database postgres:  FATAL :
> password authentication failed for user "postgres"
>
> This is on Redhat running Plesk also.
>
> I am new to Linux as well as PostgreSQL. Help would be much appreciated.
>
> Thanks
> Viv
>
>
>


Permalink | Reply | 0 comments |
headers
Major Services | 2 Mar 2010 12:10
Picon

Re: password authentication failed for user postgres

But the server restart has failed; I am yet to make the changes suggested by you in the first instance.




On Tue, Mar 2, 2010 at 4:32 PM, Iñigo Martinez Lasala <imartinez <at> vectorsf.com> wrote:
Try with these parameters in pg_hba.conf. Perhaps your postgres-redhat compilation does not have ident enabled...

local all postgres trust



-----Original Message-----
From: Major Services <services.major <at> gmail.com>
To: Iñigo Martinez Lasala <imartinez <at> vectorsf.com>
Subject: Re: [ADMIN] password authentication failed for user postgres
Date: Tue, 2 Mar 2010 16:03:59 +0530

Thank you for your message. But am in another problem now.
In between I also tried to restart the server with 'service postgresql restart'
It stopped the server, but then server start failed.
Had restarted earlier also, without a problem.

What should I do?

On Tue, Mar 2, 2010 at 2:44 PM, Iñigo Martinez Lasala <imartinez <at> vectorsf.com> wrote:
> In order to lauch createuser you don't need to modify listen_address since
> you can use unix sockets. But anyway it's a requirement if you are goint to
> access remotely.
>
> Add this line in first place to your pg_hba.conf
> local   all         postgres                          ident sameuser
>
>
> Reload postgresql and launch createuser as postgres user.
>
>
> -----Original Message-----
> From: Major Services <services.major <at> gmail.com>
> To: pgsql-admin <at> postgresql.org
> Subject: [ADMIN] password authentication failed for user postgres
> Date: Tue, 2 Mar 2010 14:13:10 +0530
>
> Hi,
>
> 1. I have added  listen_addresses = '*' ; in  postgresql.conf
>
> 2. And have made the following changes to pg_hba.conf
>
> local   DATABASE    USER   METHOD
> local    samegroup      all      password
> host    DATABASE    USER    CIDR-ADDRESS  METHOD  [OPTION]
> host          all                 all       0.0.0.0/0             md5
> hostssl      all                 all       0.0.0.0/0            md5
>
> 3. Have restarted the server
>
> 4. I can login to the server as user postgres.
>
> But  while trying to createuser I get the following message  :
> 'createuser: could not connect to database postgres:  FATAL :
> password authentication failed for user "postgres"
>
> This is on Redhat running Plesk also.
>
> I am new to Linux as well as PostgreSQL. Help would be much appreciated.
>
> Thanks
> Viv
>
>
>



Permalink | Reply | 4 comments |
headers
Iñigo Martinez Lasala | 2 Mar 2010 13:25
Favicon

Re: password authentication failed for user postgres

Ah... ok.
In /var/lib/pgsql you will find a file called pgstartup.log
Could you post here last lines of this file ?


-----Original Message-----
From: Major Services <services.major <at> gmail.com>
To: Iñigo Martinez Lasala <imartinez <at> vectorsf.com>
Cc: pgsql-admin <at> postgresql.org
Subject: Re: [ADMIN] password authentication failed for user postgres
Date: Tue, 2 Mar 2010 16:40:47 +0530

But the server restart has failed; I am yet to make the changes suggested by you in the first instance.




On Tue, Mar 2, 2010 at 4:32 PM, Iñigo Martinez Lasala <imartinez <at> vectorsf.com> wrote:
Try with these parameters in pg_hba.conf. Perhaps your postgres-redhat compilation does not have ident enabled...

local all postgres trust



-----Original Message-----
From: Major Services <services.major <at> gmail.com>


To: Iñigo Martinez Lasala <imartinez <at> vectorsf.com>
Subject: Re: [ADMIN] password authentication failed for user postgres
Date: Tue, 2 Mar 2010 16:03:59 +0530

Thank you for your message. But am in another problem now.
In between I also tried to restart the server with 'service postgresql restart'
It stopped the server, but then server start failed.
Had restarted earlier also, without a problem.

What should I do?

On Tue, Mar 2, 2010 at 2:44 PM, Iñigo Martinez Lasala <imartinez <at> vectorsf.com> wrote:
> In order to lauch createuser you don't need to modify listen_address since
> you can use unix sockets. But anyway it's a requirement if you are goint to
> access remotely.
>
> Add this line in first place to your pg_hba.conf
> local   all         postgres                          ident sameuser
>
>
> Reload postgresql and launch createuser as postgres user.
>
>
> -----Original Message-----
> From: Major Services <services.major <at> gmail.com>
> To: pgsql-admin <at> postgresql.org
> Subject: [ADMIN] password authentication failed for user postgres
> Date: Tue, 2 Mar 2010 14:13:10 +0530
>
> Hi,
>
> 1. I have added  listen_addresses = '*' ; in  postgresql.conf
>
> 2. And have made the following changes to pg_hba.conf
>
> local   DATABASE    USER   METHOD
> local    samegroup      all      password
> host    DATABASE    USER    CIDR-ADDRESS  METHOD  [OPTION]
> host          all                 all       0.0.0.0/0             md5
> hostssl      all                 all       0.0.0.0/0            md5
>
> 3. Have restarted the server
>
> 4. I can login to the server as user postgres.
>
> But  while trying to createuser I get the following message  :
> 'createuser: could not connect to database postgres:  FATAL :
> password authentication failed for user "postgres"
>
> This is on Redhat running Plesk also.
>
> I am new to Linux as well as PostgreSQL. Help would be much appreciated.
>
> Thanks
> Viv
>
>
>





Permalink | Reply | 3 comments |
headers
Major Services | 2 Mar 2010 14:49
Picon

Re: password authentication failed for user postgres

pgstartup.log:

The files belonging to this database system will be owned by user "postgres".
....
....

Success. You can now start the database server using:
/usr/bin/postmaster -D /var/lib/pgsql/data
or
/usr/bin/pg_ctl -D /var/lib/pgsql/data -1 logfile start
FATAL: lock file "postmaster.pid"already exists
HINT:Is another postmaster (PID 3300) running in data directory"b/pgsql/data"?
LOG:  logger shutting down
runuser: cannot set groups: Operation not permitted
LOG:  logger shutting down
FATAL: syntax error in file "/var/lib/pgsql/data/postgresql.conf", near token ","


Now, in the "/var/lib/pgsql/data/postgresql.conf" the only change I have made is in listen_addresses; viz
listen_addresses = 'localhost', '*'

Let me know how to move ahead.
Thanks.

On Tue, Mar 2, 2010 at 5:55 PM, Iñigo Martinez Lasala <imartinez <at> vectorsf.com> wrote:
Ah... ok.
In /var/lib/pgsql you will find a file called pgstartup.log
Could you post here last lines of this file ?



-----Original Message-----
From: Major Services <services.major <at> gmail.com>
To: Iñigo Martinez Lasala <imartinez <at> vectorsf.com>
Cc: pgsql-admin <at> postgresql.org
Subject: Re: [ADMIN] password authentication failed for user postgres
Date: Tue, 2 Mar 2010 16:40:47 +0530

But the server restart has failed; I am yet to make the changes suggested by you in the first instance.




On Tue, Mar 2, 2010 at 4:32 PM, Iñigo Martinez Lasala <imartinez <at> vectorsf.com> wrote:
Try with these parameters in pg_hba.conf. Perhaps your postgres-redhat compilation does not have ident enabled...

local all postgres trust



-----Original Message-----
From: Major Services <services.major <at> gmail.com>


To: Iñigo Martinez Lasala <imartinez <at> vectorsf.com>
Subject: Re: [ADMIN] password authentication failed for user postgres
Date: Tue, 2 Mar 2010 16:03:59 +0530

Thank you for your message. But am in another problem now.
In between I also tried to restart the server with 'service postgresql restart'
It stopped the server, but then server start failed.
Had restarted earlier also, without a problem.

What should I do?

On Tue, Mar 2, 2010 at 2:44 PM, Iñigo Martinez Lasala <imartinez <at> vectorsf.com> wrote:
> In order to lauch createuser you don't need to modify listen_address since
> you can use unix sockets. But anyway it's a requirement if you are goint to
> access remotely.
>
> Add this line in first place to your pg_hba.conf
> local   all         postgres                          ident sameuser
>
>
> Reload postgresql and launch createuser as postgres user.
>
>
> -----Original Message-----
> From: Major Services <services.major <at> gmail.com>
> To: pgsql-admin <at> postgresql.org
> Subject: [ADMIN] password authentication failed for user postgres
> Date: Tue, 2 Mar 2010 14:13:10 +0530
>
> Hi,
>
> 1. I have added  listen_addresses = '*' ; in  postgresql.conf
>
> 2. And have made the following changes to pg_hba.conf
>
> local   DATABASE    USER   METHOD
> local    samegroup      all      password
> host    DATABASE    USER    CIDR-ADDRESS  METHOD  [OPTION]
> host          all                 all       0.0.0.0/0             md5
> hostssl      all                 all       0.0.0.0/0            md5
>
> 3. Have restarted the server
>
> 4. I can login to the server as user postgres.
>
> But  while trying to createuser I get the following message  :
> 'createuser: could not connect to database postgres:  FATAL :
> password authentication failed for user "postgres"
>
> This is on Redhat running Plesk also.
>
> I am new to Linux as well as PostgreSQL. Help would be much appreciated.
>
> Thanks
> Viv
>
>
>






Permalink | Reply | 2 comments |

Gmane