Re: [GENERAL] could not open process token: error code 5

2009-10-15 Thread Richard Huxton
el dorado wrote:
> Hello.
> I had such an error but rather long ago. Unfortunately I don't remember all 
> the details but you could try to do the followig:
> - check if the directory 'data' has read/write rights for your OS account 
> (account under which you try to start postgres).
> - check if your OS account has the right to log on as service (Administrative 
> Tools/Local Security Settings/User Rights Assignment)
> - check in Computer Management/Local Users and Groups/Users if your OS 
> account is NOT the member of any group of users.

All good advice - if you're trying to copy a file-level backup into
place you should check the permissions/ownership of the files after
restoring them.

Also - I seem to remember you could get this if the shared-memory
settings were too high in postgresql.conf

-- 
  Richard Huxton
  Archonet Ltd

-- 
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] could not open process token: error code 5

2009-10-15 Thread Grzegorz Jaśkiewicz
On Wed, Oct 14, 2009 at 2:42 PM, Andale  wrote:

>
> Hi
>
> We have an Postgresql 8.2.5 installation on a Windows server 2003 that have
> worked perfectly for our Mediawiki until we tried to update to 8.4. Before
> the update we took a backup, stopped the service and took a copy of the
> entire database catalog. We could not make the 8.4 (installed in a
> different
> directory) work so we decided to go back to the initial installation which
> remained intact.
>
> Then when we try to start the service it fails and we get the message
> "could
> not open process token: error code 5" in the event viewer, nothing else.


wasn't error 5 on windows meaning, crash with SIGSEGV ?



-- 
GJ


[GENERAL] Too easy to log in as the "postgres" user?

2009-10-15 Thread Thom Brown
I've noticed that if I just log in to my server, I don't su to root,
or become the postgres user, I can get straight into the database as
the postgres user merely with "psql -U postgres -h localhost".  My
user account isn't a member of the postgres group.

It appears I've not applied my security settings correctly.  What can
I do to prevent access this way?  I'd still want to be able to su to
the postgres user and log in that way, but not with the -U parameter
allowing access.

The pg_hba.conf is probably relevant here, so this is the setup:

# TYPE  DATABASEUSERCIDR-ADDRESS  METHOD

# "local" is for Unix domain socket connections only
local   all all   trust
# IPv4 local connections:
hostall all 127.0.0.1/32  trust
# IPv6 local connections:
hostall all ::1/128   trust

Thanks

Thom Brown
Crawley, UK

-- 
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] how to Export ALL plpgsql functions/triggers to file

2009-10-15 Thread Albe Laurenz
Naoko Reeves wrote:
> Could you tell me how to Export ALL plpgsql 
> functions/triggers to file?

I'd do it as follows:

- Perform a pg_dump of the database object definitions:
  pg_dump -F c -s -f database.dmp database

- Create a listing, delete everything except triggers and functions:
  pg_restore -l database.dmp | awk '/[0-9]*; [0-9]* [0-9]* (FUNCTION|TRIGGER)/ 
{ print }' > database.list

- Create an SQL script with only these objects:
  pg_restore -L database.list -f database.sql database.dmp

The only shortcoming is that it does not make a distinction
between PL/pgSQL and other functions; you could filter again with awk
or something if you need that.

Yours,
Laurenz Albe

-- 
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] Too easy to log in as the "postgres" user?

2009-10-15 Thread A. Kretschmer
In response to Thom Brown :
> I've noticed that if I just log in to my server, I don't su to root,
> or become the postgres user, I can get straight into the database as
> the postgres user merely with "psql -U postgres -h localhost".  My
> user account isn't a member of the postgres group.
> 
> It appears I've not applied my security settings correctly.  What can
> I do to prevent access this way?  I'd still want to be able to su to
> the postgres user and log in that way, but not with the -U parameter
> allowing access.
> 
> The pg_hba.conf is probably relevant here, so this is the setup:
> 
> # TYPE  DATABASEUSERCIDR-ADDRESS  METHOD
> 
> # "local" is for Unix domain socket connections only
> local   all all   trust
> # IPv4 local connections:
> hostall all 127.0.0.1/32  trust
> # IPv6 local connections:
> hostall all ::1/128   trust

Try to change trust to sameuser.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)

-- 
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] Too easy to log in as the "postgres" user?

2009-10-15 Thread Glyn Astill
> From: Thom Brown 
> Subject: [GENERAL] Too easy to log in as the "postgres" user?
> To: "PGSQL Mailing List" 
> Date: Thursday, 15 October, 2009, 11:38 AM
> I've noticed that if I just log in to
> my server, I don't su to root,
> or become the postgres user, I can get straight into the
> database as
> the postgres user merely with "psql -U postgres -h
> localhost".  My
> user account isn't a member of the postgres group.
> 
> It appears I've not applied my security settings
> correctly.  What can
> I do to prevent access this way?  I'd still want to be
> able to su to
> the postgres user and log in that way, but not with the -U
> parameter
> allowing access.

You just need to change the local connections to any authentication method 
other than trust.

http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html

Glyn




-- 
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] Too easy to log in as the "postgres" user?

2009-10-15 Thread Thom Brown
2009/10/15 A. Kretschmer :
>>
>> The pg_hba.conf is probably relevant here, so this is the setup:
>>
>> # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
>>
>> # "local" is for Unix domain socket connections only
>> local   all         all                               trust
>> # IPv4 local connections:
>> host    all         all         127.0.0.1/32          trust
>> # IPv6 local connections:
>> host    all         all         ::1/128               trust
>
> Try to change trust to sameuser.
>

I've made that change, but now PostgreSQL won't start, and outputs the
following error in the log:

2009-10-15 11:52:41 BST [18720]: [2-1] CONTEXT:  line 74 of
configuration file "/var/lib/postgresql/8.4/data/pg_hba.conf"
2009-10-15 11:52:41 BST [18720]: [3-1] LOG:  invalid authentication
method "sameuser

-- 
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] Too easy to log in as the "postgres" user?

2009-10-15 Thread Stephen Frost
* Thom Brown (thombr...@gmail.com) wrote:
> 2009/10/15 A. Kretschmer :
> >>
> >> The pg_hba.conf is probably relevant here, so this is the setup:
> >>
> >> # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
> >>
> >> # "local" is for Unix domain socket connections only
> >> local   all         all                               trust
> >> # IPv4 local connections:
> >> host    all         all         127.0.0.1/32          trust
> >> # IPv6 local connections:
> >> host    all         all         ::1/128               trust
> >
> > Try to change trust to sameuser.
> >
> 
> I've made that change, but now PostgreSQL won't start, and outputs the
> following error in the log:
> 
> 2009-10-15 11:52:41 BST [18720]: [2-1] CONTEXT:  line 74 of
> configuration file "/var/lib/postgresql/8.4/data/pg_hba.conf"
> 2009-10-15 11:52:41 BST [18720]: [3-1] LOG:  invalid authentication
> method "sameuser

You need it to be 'ident sameuser', not just 'sameuser'.  Also, only do
that for the 'local' line.  Comment out the host lines if you don't need
them.  If you do need them, change them to something else (eg: md5 if
you want password-based, gssapi if you have a Kerberos or MS/Active
Directory infrastructure, ldap is also an option, etc...).

'local' is used when connecting over a unix socket, eg: psql -d blah
'host' is used when connecting over a network: psql -d blah -h myhost

Stephen

Stephen


signature.asc
Description: Digital signature


Re: [GENERAL] Too easy to log in as the "postgres" user?

2009-10-15 Thread Thom Brown
2009/10/15 Stephen Frost :
> * Thom Brown (thombr...@gmail.com) wrote:
>>
>> I've made that change, but now PostgreSQL won't start, and outputs the
>> following error in the log:
>>
>> 2009-10-15 11:52:41 BST [18720]: [2-1] CONTEXT:  line 74 of
>> configuration file "/var/lib/postgresql/8.4/data/pg_hba.conf"
>> 2009-10-15 11:52:41 BST [18720]: [3-1] LOG:  invalid authentication
>> method "sameuser
>
> You need it to be 'ident sameuser', not just 'sameuser'.  Also, only do
> that for the 'local' line.  Comment out the host lines if you don't need
> them.  If you do need them, change them to something else (eg: md5 if
> you want password-based, gssapi if you have a Kerberos or MS/Active
> Directory infrastructure, ldap is also an option, etc...).
>
> 'local' is used when connecting over a unix socket, eg: psql -d blah
> 'host' is used when connecting over a network: psql -d blah -h myhost
>
>        Stephen
>

Okay, I've just ended up commenting out the host lines and it's
effective enough as far as logging in is concerned.  However, the
websites which use the database are no longer able to connect.  I
should point out that they are connecting to pgbouncer through a
specific port number.  I haven't been successful in getting the sites
to connect through a unix socket.

Thom

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


[GENERAL] Can't find documentation for ~=~ operator

2009-10-15 Thread Viktor Rosenfeld
Hi,

I can't find the documentation of the ~=~ operator anywhere on the
PostgreSQL homepage.  I'm quite certain that I saw it there a while ago,
though.

FYI, I use ~=~ for equality checks, so I can use the same index for
equality and regexp pattern matching on a varchar field.  The index is
built with varchar_pattern_ops.

Cheers,
Viktor

-- 
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] Cannot start the postgres service

2009-10-15 Thread Mitesh51

Let me clarify bit more so I can do thing in proper manner...

I take base backup(full).

at fixed interval I need to take incremental backup and for that...

I use following commands.

psql -U postgres -c "select pg_switch_xlog()"

psql -U postgres -c "SELECT pg_stop_backup()"

after that the WAL file(s) is/are archived & I consider that file as a file
which will be used for backup along with all files exist in the pg_xlog dir.

same approach is mentioned in the 
http://www.mkyong.com/database/postgresql-point-in-time-recovery-incremental-backup/

Am I doing things properly??

I know how to restore data from the full backup but havn't done anything to
restore data from the WAL files...Hopefully I will find the way...If u can
emphasize somewhere then it will be a gr8 help :)

Please correct me if I am wrong somewhere :)


Alvaro Herrera-7 wrote:
> 
> Mitesh51 wrote:
> 
>> I had 2 approach in my mind...to sync up transaction log files with
>> specific
>> full backup
>> 
>> 1) to keep only time relavent files in pg_xlog dir and move other files
>> to
>> archive dir with code which is not a good idea as u suggest
> 
> Postgres is prepared to (and assumes it can) reuse and delete files in
> pg_xlog.  If you need a copy you can use for your own purposes, you MUST
> get it through an archive_command.  You MUST NOT fiddle with the files
> in pg_xlog directly.
> 
> Also note that your archive_command needs to create a separate copy of
> the file.  Hardlinks are not allowed, because the file might get
> rewritten by Postgres later.  Moving (mv) the original files is not
> allowed either for the same reason.  Postgres will leave the file alone
> until it has been archived, and assumes it can do whatever it pleases
> with it as soon as the archiver has returned success (exit code 0).
> 
> -- 
> Alvaro Herrera   
> http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
> 
> -- 
> 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://www.nabble.com/Cannot-start-the-postgres-service-tp25867194p25904095.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] SFPUG: Video from "Statistics and Postgres -- How the Planner Sees Your Data" Now on Vimeo

2009-10-15 Thread Alban Hertroys

On 15 Oct 2009, at 24:53, Christophe Pettus wrote:


Hi,

The video from "Statistics and Postgres — How the Planner Sees Your  
Data," the September 8, 2009 meeting of the SFPUG, is now available  
on Vimeo:


http://vimeo.com/7051082


I watched this with interest. There is reference to slides with some  
frequency though, are they available somewhere?


Thanks for putting this up.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4ad7106611681113316355!



--
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] SFPUG: Video from "Statistics and Postgres -- How the Planner Sees Your Data" Now on Vimeo

2009-10-15 Thread Thom Brown
2009/10/15 Alban Hertroys :
> On 15 Oct 2009, at 24:53, Christophe Pettus wrote:
>
>> Hi,
>>
>> The video from "Statistics and Postgres — How the Planner Sees Your Data,"
>> the September 8, 2009 meeting of the SFPUG, is now available on Vimeo:
>>
>>        http://vimeo.com/7051082
>
> I watched this with interest. There is reference to slides with some
> frequency though, are they available somewhere?
>
sql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

I found it interesting too, although the interruptions were quite offputting.

Thom

-- 
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] Too easy to log in as the "postgres" user?

2009-10-15 Thread Stephen Frost
* Thom Brown (thombr...@gmail.com) wrote:
> Okay, I've just ended up commenting out the host lines and it's
> effective enough as far as logging in is concerned.  However, the
> websites which use the database are no longer able to connect.  I
> should point out that they are connecting to pgbouncer through a
> specific port number.  I haven't been successful in getting the sites
> to connect through a unix socket.

If you want access controls on network-based connections, you'll have to
switch to using one of the other auth methods I mentioned.  Probably the
simplest is to use 'md5' and then set passwords for the users who log
into the database.

Unix sockets are only possible if the application (pgbouncer, in your
case) and the database are on the same system.  If this is the case, you
may need to set the unix_socket parameter in pgbouncer, and make sure
that you do not have 'host' set in pgbouncer.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [GENERAL] Too easy to log in as the "postgres" user?

2009-10-15 Thread Andrew Bailey
Thom,

You appear to be trusting all connections what I think you want is the
following:

local all all ident sameuser
# IPv4 local connections:
host all all 127.0.0.1/32 ident sameuser
# IPv6 local connections:
host all all ::1/128 ident sameuser

Remember that you need to get postgres to reread the file after
changing it by using pg_ctl reload or kill -HUP {pid}

Andy Bailey


On Thu, Oct 15, 2009 at 5:38 AM, Thom Brown  wrote:
> I've noticed that if I just log in to my server, I don't su to root,
> or become the postgres user, I can get straight into the database as
> the postgres user merely with "psql -U postgres -h localhost".  My
> user account isn't a member of the postgres group.
>
> It appears I've not applied my security settings correctly.  What can
> I do to prevent access this way?  I'd still want to be able to su to
> the postgres user and log in that way, but not with the -U parameter
> allowing access.
>
> The pg_hba.conf is probably relevant here, so this is the setup:
>
> # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
>
> # "local" is for Unix domain socket connections only
> local   all         all                               trust
> # IPv4 local connections:
> host    all         all         127.0.0.1/32          trust
> # IPv6 local connections:
> host    all         all         ::1/128               trust
>
> Thanks
>
> Thom Brown
> Crawley, UK
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Andrew Bailey

(312) 866 9556

NOTA DE CONFIDENCIALIDAD Y DE NO DIVULGACIÓN:
La información contenida en este E-mail y sus archivos adjuntos es
confidencial y sólo puede ser utilizada por el individuo
o la empresa a la cual está dirigido. Si no es el receptor autorizado,
cualquier retención, difusión,
distribución o copia de este mensaje queda prohibida y sancionada por
la ley. Si por error recibe este
mensaje, favor devolverlo y borrarlo inmediatamente.

-- 
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] Too easy to log in as the "postgres" user?

2009-10-15 Thread Stephen Frost
* Andrew Bailey (hazloreali...@gmail.com) wrote:
> You appear to be trusting all connections what I think you want is the
> following:
> 
> local all all ident sameuser
> # IPv4 local connections:
> host all all 127.0.0.1/32 ident sameuser
> # IPv6 local connections:
> host all all ::1/128 ident sameuser
> 
> Remember that you need to get postgres to reread the file after
> changing it by using pg_ctl reload or kill -HUP {pid}

ident sameuser for host connections really isn't recommend nor is
terribly secure, in general.  Over localhost is better, but using local
is infinitely better, imo.

Stephen


signature.asc
Description: Digital signature


Re: [GENERAL] Can't find documentation for ~=~ operator

2009-10-15 Thread Tom Lane
Viktor Rosenfeld  writes:
> I can't find the documentation of the ~=~ operator anywhere on the
> PostgreSQL homepage.

Which version's documentation are you reading?  It's gone as of 8.4.

> FYI, I use ~=~ for equality checks, so I can use the same index for
> equality and regexp pattern matching on a varchar field.  The index is
> built with varchar_pattern_ops.

Use plain old '=' as of 8.4.

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] SFPUG: Video from "Statistics and Postgres -- How the Planner Sees Your Data" Now on Vimeo

2009-10-15 Thread Nathan Boley
>
> There is reference to slides with some
> frequency though, are they available somewhere?
>

Ya.

http://encodestatistics.org/publications/statistics_and_postgres.pdf

Is there a better place for this?

-Nathan

-- 
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] SFPUG: Video from "Statistics and Postgres -- How the Planner Sees Your Data" Now on Vimeo

2009-10-15 Thread Alvaro Herrera
Nathan Boley escribió:
> >
> > There is reference to slides with some
> > frequency though, are they available somewhere?
> >
> 
> Ya.
> 
> http://encodestatistics.org/publications/statistics_and_postgres.pdf
> 
> Is there a better place for this?

I don't know how they do it but the pgcon 2009 page has links to videos
of the presentations that have the slides changing in coordination.
It's pretty good.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
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] SFPUG: Video from "Statistics and Postgres -- How the Planner Sees Your Data" Now on Vimeo

2009-10-15 Thread Tom Lane
Alvaro Herrera  writes:
> Nathan Boley escribió:
>> http://encodestatistics.org/publications/statistics_and_postgres.pdf
>> Is there a better place for this?

> I don't know how they do it but the pgcon 2009 page has links to videos
> of the presentations that have the slides changing in coordination.
> It's pretty good.

Well, if you saw the video, the whole problem was that Nathan was
contending with lack of a projector, so there weren't any displayed
slides to see in the video :-(.  It would have been a lot easier to
follow with slides, for sure.

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] SFPUG: Video from "Statistics and Postgres -- How the Planner Sees Your Data" Now on Vimeo

2009-10-15 Thread Christophe Pettus


On Oct 15, 2009, at 8:20 AM, Nathan Boley wrote:

http://encodestatistics.org/publications/statistics_and_postgres.pdf

Is there a better place for this?


For now, I'll add it to the Vimeo page.

--
-- Christophe Pettus
   x...@thebuild.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] timestamp without time zone and datetime

2009-10-15 Thread danclemson

Hi,

I have a stored procedure in db that takes a 'timestamp without time zone'
as its parameter.
The application uses c# and npgsql to access database.

When I call the stored procedure from c#, I got an exception says that the
stored procedure with 'timestamp with time zone' is unknown. It seems the
driver somehow maps the datatime to 'timestamp with time zone' in this case.

The code I used in c#;

DbCommand command = conn.CreateCommand();
command.CommandText = "getInfo";
command.CommandType = CommandType.StoredProcedure;

DbParameter param1 = command.CreateParameter();
param1.DbType = DbType.DateTime;
param1.Value = mission.StartTime //datetime value retrieved from
a database table 'timestamp without time zone' column
command.Parameters.Add(param1);

IDataReader dr = command.ExecuteReader();

Form npgsql user menu, both timestamp with/without time zone are mapped to
DbType.DateTime.
What is the issue here?

Thanks /dan
-- 
View this message in context: 
http://www.nabble.com/timestamp-without-time-zone-and-datetime-tp25910519p25910519.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] Many instances of postgres.exe

2009-10-15 Thread Bob Pawley
Hi

While looking at an error message for iexplore.exe, I noticed, on Task Manager, 
that there are 5 or more instances of postgres.exe running. Each instance is 
consuming between 7 to 10 megs of resources, for a total of almost 50 megs.

Is this normal behavour?

If so, could someone enlighten me as to the purpose?

Bob

Re: [GENERAL] Many instances of postgres.exe

2009-10-15 Thread Brian Modra
2009/10/15 Bob Pawley :
> Hi
>
> While looking at an error message for iexplore.exe, I noticed, on Task
> Manager, that there are 5 or more instances of postgres.exe running. Each
> instance is consuming between 7 to 10 megs of resources, for a total of
> almost 50 megs.
>
> Is this normal behavour?

yes

> If so, could someone enlighten me as to the purpose?

Its good that it uses more than one process, because each task then is
separated from other tasks by the operating system's memory
management. I.e. if one crashes, the others stay up.
This is good design as opposed to hugely multi-threaded apps where one
little bug can bring everything down.

The memory used is configurable, you can set up the amount of caching,
but actually I am not sure exactly how much memory is used for what
purpose. However, I set up my server with larger cache than standard.
It has a load of memory though... 50Mb is very little memory
considering you are talking about a professional database system

> Bob



-- 
Brian Modra   Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.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] Many instances of postgres.exe

2009-10-15 Thread John R Pierce

Bob Pawley wrote:

Hi
 
While looking at an error message for iexplore.exe, I noticed, on Task 
Manager, that there are 5 or more instances of postgres.exe running. 
Each instance is consuming between 7 to 10 megs of resources, for a 
total of almost 50 megs.
 
Is this normal behavour?
 
If so, could someone enlighten me as to the purpose?


A) much of that memory is shared by them.   task manager doesn't account 
for shared memory



B) on a Linux or Unix system, we might see something like

$ ps uww -U postgres
USER   PID %CPU %MEMVSZ   RSS TTY  STAT START   TIME COMMAND
postgres  3279  0.0  0.5 152036  6000 ?SSep29   2:59 
/usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
postgres  3587  0.0  0.0  13056   704 ?Ss   Sep29   0:04 
postgres: logger process 
postgres  3630  0.0 10.4 152192 108508 ?   Ss   Sep29   0:20 
postgres: writer process 
postgres  3631  0.0  0.0 152192   980 ?Ss   Sep29   0:01 
postgres: wal writer process 
postgres  3632  0.0  0.2 153348  2072 ?Ss   Sep29  13:08 
postgres: autovacuum launcher process
postgres  3633  0.1  0.1  14196  1500 ?Ss   Sep29  36:59 
postgres: stats collector process
postgres 27452  0.0  0.1 152740  2028 ?Ss   09:04   0:00 
postgres: squeals squeals [local] idle   

the first one is the postmaster process, then there is the 'logger', the 
'writer', the 'write ahead log writer', the 'autovacuum launcher' and 
hte 'stats collector'.   Finally, the last process 27452 is a user 
connection to database squeals by local user squeals, which is idle.


the first 6 of these will always be running, then one additional process 
for each active database connection.   While the VIRTUAL size (VSZ) of 
most of them is 150MB 'each' on my system, you'll note the RESIDENT SET 
SIZE (RSS) is 6M, 700k, 100M, 1M, 2M, 1,5M, 2M.   This is much more 
representive of the actual memory usage.






--
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] Many instances of postgres.exe

2009-10-15 Thread Massa, Harald Armin
Bob,
>
>
> While looking at an error message for iexplore.exe, I noticed, on Task
> Manager, that there are 5 or more instances of postgres.exe running. Each
> instance is consuming between 7 to 10 megs of resources, for a total of
> almost 50 megs.
>
> Is this normal behavour?
>
> 5 instances is the default when nothing has connected.

Reason: statwriter, autovacuum, ... are all separate processes. For every
additional connection there is another process, also giving a postgres.exe

What most likely is "wrong" are those "50 megs", as I guess you are looking
at the default task manager.

Within those 50MB the PostgreSQL shared memory is counted (number of
processes) time. That is: If there are 8MB of shared memory configured, you
will see them as 40MB with 5 running processes.

Look within "View", "Select other columns" to find other memory columns.

best wishes,

Harald




-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
%s is too gigantic of an industry to bend to the whims of reality


Re: [GENERAL] Many instances of postgres.exe

2009-10-15 Thread Scott Mead
On Thu, Oct 15, 2009 at 12:07 PM, Brian Modra  wrote:

> 2009/10/15 Bob Pawley :
> > Hi
> >
> > While looking at an error message for iexplore.exe, I noticed, on Task
> > Manager, that there are 5 or more instances of postgres.exe running. Each
> > instance is consuming between 7 to 10 megs of resources, for a total of
> > almost 50 megs.
>

It's not actually using up that much memory.  Windows (and linux for that
matter) think Postgres is using up that much per process, but in actuality,
most of that is shared memory between them.  So if you have 5 @ 10 MB a
piece, you may only have 12 - 20 MB in use.



> >
> > Is this normal behavour?
>
> yes
>
>
 +1

> > If so, could someone enlighten me as to the purpose?
>
>
>
  Every time you connect to postgres, your connection gets a new
postgres.exe (plus there are a few system ones).  So 5 connections = 5
postgres.exe + a few (3 or 4) system processes.  It's normal, it allows the
OS to schedule who does what work.  If you have multiple processors, you get
nice parallelism without postgres having to be threaded internally.

--Scott


[GENERAL] npgsql and postgres enum type

2009-10-15 Thread danclemson

Hi, 

As postgres now has enum type, does npgsql driver support the enum type?

I use c# and npgsql as databse driver.  One of the database stored procedure
takes enum as its parameter.

What will be the DbType for postgres enum type?

Thanks /dan
-- 
View this message in context: 
http://www.nabble.com/npgsql-and-postgres-enum-type-tp25911871p25911871.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] npgsql and postgres enum type

2009-10-15 Thread Merlin Moncure
On Thu, Oct 15, 2009 at 12:31 PM, danclemson  wrote:
>
> Hi,
>
> As postgres now has enum type, does npgsql driver support the enum type?
>
> I use c# and npgsql as databse driver.  One of the database stored procedure
> takes enum as its parameter.
>
> What will be the DbType for postgres enum type?

that's really a npgsql question, but as long as you have access to the
sql being used, you should be able to work around it by altering the
sql like this:

select some_function('abc'::the_enum);

merlin

-- 
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] npgsql and postgres enum type

2009-10-15 Thread William Temperley
2009/10/15 Merlin Moncure :
> On Thu, Oct 15, 2009 at 12:31 PM, danclemson  wrote:
>>
>> Hi,
>>
>> As postgres now has enum type, does npgsql driver support the enum type?
>>
>> I use c# and npgsql as databse driver.  One of the database stored procedure
>> takes enum as its parameter.
>>
>> What will be the DbType for postgres enum type?
>
> that's really a npgsql question, but as long as you have access to the
> sql being used, you should be able to work around it by altering the
> sql like this:
>
> select some_function('abc'::the_enum);
>
> merlin
>

I recently stopped using enums after reading this:
http://www.commandprompt.com/blogs/joshua_drake/2009/01/fk_check_enum_or_domain_that_is_the_question/
Using a foreign key to a single column table is pretty much as fast as
an enum, is supported by most (all?) third party libraries, and avoids
all the problems associated with enums.
I guess the downside is the foreign key will take up more disk space,
but that isn't an issue for me.

Cheers, Will Temperley.

-- 
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] npgsql and postgres enum type

2009-10-15 Thread Merlin Moncure
On Thu, Oct 15, 2009 at 2:52 PM, William Temperley > I recently
stopped using enums after reading this:
> http://www.commandprompt.com/blogs/joshua_drake/2009/01/fk_check_enum_or_domain_that_is_the_question/
> Using a foreign key to a single column table is pretty much as fast as
> an enum, is supported by most (all?) third party libraries, and avoids
> all the problems associated with enums.
> I guess the downside is the foreign key will take up more disk space,
> but that isn't an issue for me.

enums are a bit faster in the general case: you have a oid's worth of
storage.  where enums have the chance to pay big dividends is indexes
_espeically_ if the enum is part of more complex ordering.  This can
be worked around using the classic approach but the enum is simpler
and cleaner.

For example, suppose you have a requirement you have to pulling up
orders by account#/status

select *  from order where .. order by account_id, status ;

if the status is an enum, you can take advantage of the enum's natural
ordering without the performance killing join for the natural ordering
or using function tricks in the create index statement to get good it
working properly.

This case comes often enough to justify enum's existence IMO.

merlin

-- 
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] npgsql and postgres enum type

2009-10-15 Thread danclemson

Thanks for the information.

I did try the approach, but it failed due to any other issue with npgsql.

The stored procedure returns a setof refcursor.
If I use "select * from test('e1':testEnum)", the command.ExecuteReader does
not return the datareader properly. The code errored out when I use the
datareader to get the data in the refcursor.

The stored procedure (return setof refcursor) works if I use prepared
statement, but in this case I am unable to do the explict type cast.
-- 
View this message in context: 
http://www.nabble.com/npgsql-and-postgres-enum-type-tp25911871p25915268.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] npgsql and postgres enum type

2009-10-15 Thread Merlin Moncure
On Thu, Oct 15, 2009 at 4:14 PM, danclemson  wrote:
>
> Thanks for the information.
>
> I did try the approach, but it failed due to any other issue with npgsql.
>
> The stored procedure returns a setof refcursor.
> If I use "select * from test('e1':testEnum)", the command.ExecuteReader does
> not return the datareader properly. The code errored out when I use the
> datareader to get the data in the refcursor.
>

if you can't figure out any other solution (there probably is one),
wrap your procedure in sql function that takes text and do the casting
there.

merlin

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


[GENERAL] Craeteing sparse arrays

2009-10-15 Thread Peter Hunsberger
Is there any easy way to create sparse arrays with Postres?
Specifically, when I construct, add or aggregate data to an array, I
want to be able to specify the position within the array where the
data is placed and have any intervening positions that have not yet
been populated just marked as nulls. eg, something like

insert into foo (bar[3],[7]) values ( 'a', 'b')

would build an array

bar = { null, null, 'a', null, null, null, 'b' }

or some such thing.  I suspect I'm going to have to write a function
to just find the length and append nulls until I reach the desired
position?  Given that some of the arrays I will be dealing with could
potentially be 1000s of elements long that seems a bit perverse.

I'm currently using 8.3 but 8.4 solutions are also welcome.  C code
not considered out of the question if it isn't a lot of work and will
make the rest of the process close to trivial...

-- 
Peter Hunsberger

-- 
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] Craeteing sparse arrays

2009-10-15 Thread Tom Lane
Peter Hunsberger  writes:
> Is there any easy way to create sparse arrays with Postres?

Have you tried it?

regression=# create table foo (bar text[]);
CREATE TABLE
regression=# insert into foo (bar[3],bar[7]) values ( 'a', 'b');
INSERT 0 1
regression=# select * from foo;
bar 

 [3:7]={a,NULL,NULL,NULL,b}
(1 row)

In the last couple of releases, assigning to a nonexistent subscript
will fill nulls into positions between that and the existent ones,
so something like UPDATE foo SET bar[7] = 'b' will clearly do what
you want.  The above syntax is less obvious but IIRC it's treated
as an assignment to bar[3] followed by an assignment to bar[7].

I wouldn't want to try working with very large arrays in PG, mind
you --- it's not terribly efficient with them.

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] Craeteing sparse arrays

2009-10-15 Thread Scott Bailey

Peter Hunsberger  writes:

Is there any easy way to create sparse arrays with Postres?


Have you tried it?

regression=# create table foo (bar text[]);
CREATE TABLE
regression=# insert into foo (bar[3],bar[7]) values ( 'a', 'b');
INSERT 0 1
regression=# select * from foo;
bar 


 [3:7]={a,NULL,NULL,NULL,b}
(1 row)

In the last couple of releases, assigning to a nonexistent subscript
will fill nulls into positions between that and the existent ones,
so something like UPDATE foo SET bar[7] = 'b' will clearly do what
you want.  The above syntax is less obvious but IIRC it's treated
as an assignment to bar[3] followed by an assignment to bar[7].

I wouldn't want to try working with very large arrays in PG, mind
you --- it's not terribly efficient with them.

regards, tom lane


You may be better off using hstore instead of straight arrays.

http://www.postgresql.org/docs/8.4/interactive/hstore.html

Scott Bailey

--
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] adding another node to our pitr config

2009-10-15 Thread Yaroslav Tykhiy

On 06/10/2009, at 11:51 PM, Geoffrey wrote:

We are currently using WAL shipping to have a hot spare of our  
databases.  We want to add another node to this configuration.  The  
question is, what is the best way to go about this?


Currently, our script checks to see if the WAL file already exists  
on the target server, if not, then we scp the file over.  This is a  
local machine, so the scp overhead is not considered to be an issue.


So, the current approach is:

ssh $1 "test ! -f $2/$4" && scp $3 $1:$2/$4

So, should I simply duplicate that line for the second server and  
place it below this one, or should they be dependent upon each  
other?  That is:



archive_command = 'archive.sh node1 /esc/master/pitr %p %f node2'


ssh $1 "test ! -f $2/$4" && scp $3 $1:$2/$4 &&
ssh $5 "test ! -f $2/$4" && scp $3 $5:$2/$4

The second node will not be at the same location, thus the network  
reliability is less.


Thanks for any insights.


I've been interested in a similar setup, too, although I haven't  
implemented it yet.  I think there are at least 3 obvious approaches  
to consider.  They all are basically solutions/workaround to the  
following issue with multiple spare nodes: If the WAL copy operation  
to spare node 1 succeeds but that to spare node 2 fails, you have to  
handle the partial success somehow.  Your suggested archive_command  
will keep returning failure because the WAL segment already exists on  
node 1.


1. A shared WAL spool on a fault-tolerant SAN mounted via NFS or  
similar by all nodes.  Then you can use a trivial `test && cp && mv'  
archive_command on the master node and have all the spare nodes fetch  
WAL files from the spool.  (mv is there to make the copy atomic.)  An  
additional advantage is that you can use the same WAL spool for  
periodic DB backups: You run a file-level backup once in a while and  
rely on the spool to accumulate the matching WALs for you.  A problem  
with this approach is that it can be non-trivial to implement a truly  
fault-tolerant shared spool and you'll end up with a single point of  
failure on it.


2. Destructive copy.  Just let your archive_command overwrite existing  
WAL segments.  Then a failure with node 2 will result in a retry from  
scratch.


3. Delegation of failure handling to archive_command.  Instead of  
relying on the pg archiver process to retry archive_command if it  
returned failure, run the copy op to each spare node in a loop until  
success.  Then you'll be sure all the nodes received the WAL by the  
end of the script.


From a probabilistic PoV, (3) will be notably better than (2) only if  
the probability of failure for each node is high.




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


[GENERAL] PGCluster vs CyberCluster

2009-10-15 Thread Yadira Lizama Mue
Hi. 
Can anyone tell me, which are the differences between PGCluster and 
CyberCluster?
 I need a multi-master, synchronous replication solution based on PostgreSQl 
and I have founded these tools. But I think they are almost ident and I'd like 
to see your opinions about...   

Regards, 
Y. 

-- 
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] could not open process token: error code 5

2009-10-15 Thread Andale

Hi all

Thanks for all your responses. I have not yet been able to try your
suggestions as I have access to the system only on tuesday and wednesday. I
will come back and report how it goes.

/Anders
-- 
View this message in context: 
http://www.nabble.com/could-not-open-process-token%3A-error-code-5-tp25891332p25919854.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