Re: [GENERAL] could not open process token: error code 5
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. Regards, Marina. 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. Now after googling for some hours and days I am stuck. the Postgres user are to start the service and so nothing is changed there either. Even though the database files were not changed, we have also copied the entire original database back. The installation has been done with the postgresql-8.2-int.msi package and it has been reapplied with the following command, msiexec /i postgresql-8.2-int.msi REINSTALLMODE=vamus REINSTALL=ALL / Still no progress. What to do? /Anders -- 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
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
On Wed, Oct 14, 2009 at 2:42 PM, Andale and...@pagiad.se 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?
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
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?
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?
From: Thom Brown thombr...@gmail.com Subject: [GENERAL] Too easy to log in as the postgres user? To: PGSQL Mailing List pgsql-general@postgresql.org 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 A. Kretschmer andreas.kretsch...@schollglas.com: 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?
* Thom Brown (thombr...@gmail.com) wrote: 2009/10/15 A. Kretschmer andreas.kretsch...@schollglas.com: 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 Stephen Frost sfr...@snowman.net: * 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
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
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
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 Alban Hertroys dal...@solfertje.student.utwente.nl: 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?
* 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?
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 thombr...@gmail.com 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?
* 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
Viktor Rosenfeld listuse...@googlemail.com 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
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
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
Alvaro Herrera alvhe...@commandprompt.com 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
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
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
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 Bob Pawley rjpaw...@shaw.ca: 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
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
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
On Thu, Oct 15, 2009 at 12:07 PM, Brian Modra br...@zwartberg.com wrote: 2009/10/15 Bob Pawley rjpaw...@shaw.ca: 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
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
On Thu, Oct 15, 2009 at 12:31 PM, danclemson danclem...@gmail.com 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 Merlin Moncure mmonc...@gmail.com:
On Thu, Oct 15, 2009 at 12:31 PM, danclemson danclem...@gmail.com 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
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
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
On Thu, Oct 15, 2009 at 4:14 PM, danclemson danclem...@gmail.com 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
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
Peter Hunsberger peter.hunsber...@gmail.com 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
Peter Hunsberger peter.hunsber...@gmail.com 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
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
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
