[GENERAL] postmaster.pid does not exist. cannot start postgres server on ubuntu

[Jennifer Trey]

I am getting the following error when I go to Application -> PostgreSQL ->
Reload Configuration

"
Please enter your password if requested.
pg_ctl: PID file "/home/Jen/Installed/PostgreSQL/data/postmaster.pid" does
not exist
Is server running?

Press  to continue...
"

I have installed Postgresql from the Enterprise DB installer for Ubuntu.

It has worked before. I just restarted my computer and now face this issue.

Off topic:
I have also had a different kind of issue, where a script added to
/etc/profile.d  called Startup.sh
causes postgre to fail to startup. This script is used on login to set
global environment variables, but I get the error that Postgre cannot
execute the script.
I tried chmod 777 on the script but still no luck. If I remove it, postgre
will start up so its not a biggy..
but is not a bug ?

Cheers, Jen

Re: [GENERAL] Create table if not exists ... how ??

[Jennifer Trey]

Thanks guys.

Joe, I tried ( and learned! ) from your syntax. I didn't have pgsql language
installed but I googled it and figured that part out.

There was an issue with using your way though, you see the constraints
relation also needs to be considered, as if a constraint key already exist,
for any other table, not neccessary for hte table we are creating, then we
are going to get an error, which won't be covered by the count.

So I finally tried Scott's way because it will catch an exception, and I
believe the constraint key exception is included in there. Although I am not
sure, because he is catching a duplicate_table exception ? What is the most
generic exception in postgres ? Throwable in Java ?
http://www.postgresql.org/docs/8.1/interactive/errcodes-appendix.html

create or replace function create_table_if_not_exists (create_sql text)
returns bool as $$
BEGIN
BEGIN
EXECUTE create_sql;

Exception when duplicate_table THEN
RETURN false;
END;
RETURN true;

END;
$$
Language plpgsql;

SELECT create_table_if_not_exists ('CREATE TABLE post_codes
(
  area character varying(10) NOT NULL,
  district character varying(10) NOT NULL,
  sector character varying(10) NOT NULL,
  CONSTRAINT post_codes_pkey PRIMARY KEY (area, district, sector)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE post_codes OWNER TO postgres;')


Thank you all, Jen

Re: [GENERAL] Create table if not exists ... how ??

[Jennifer Trey]

No I don't want to drop it ... there is valuable data in there! I only
want to create it if it doesn't already exist... likely going to happen
first time the application will run. I want to create the table then and
populate. But not the next time.

Should I just let Java throw and exception and catch it ? Write a function
for this would be optimal, although I have no idea what the correct syntax
is.

Cheers, Jen



On Mon, Jul 19, 2010 at 5:58 PM, Joe Conway  wrote:

> On 07/19/2010 09:33 AM, Jennifer Trey wrote:
> > I can't figure out the correct syntax...
> >
> > I have this, but it just keeps complaining about the IF
> >
> > IF NOT EXISTS (SELECT table_name FROM information_schema.tables where
> > table_name = 'post_codes')
> >
> > THEN
> >
> > CREATE TABLE post_codes
>
> Probably better to do:
>
> DROP TABLE IF EXISTS post_codes;
> CREATE TABLE post_codes(...);
>
> See:
>  http://www.postgresql.org/docs/8.4/interactive/sql-droptable.html
>
> HTH,
>
> Joe
>
> --
> Joe Conway
> credativ LLC: http://www.credativ.us
> Linux, PostgreSQL, and general Open Source
> Training, Service, Consulting, & 24x7 Support
>
>

Re: [GENERAL] Create table if not exists ... how ??

[Jennifer Trey]

You all make it sound so easy :)

How do I write the above using a function?

Cheers, Jen

[GENERAL] Create table if not exists ... how ??

[Jennifer Trey]

I can't figure out the correct syntax...

I have this, but it just keeps complaining about the IF

IF NOT EXISTS (SELECT table_name FROM information_schema.tables where
table_name = 'post_codes')

THEN

CREATE TABLE post_codes
(
  area character varying(10) NOT NULL,
  district character varying(10) NOT NULL,
  sector character varying(10) NOT NULL,
  CONSTRAINT post_codes_pkey PRIMARY KEY (area, district, sector)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE post_codes OWNER TO postgres;

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - -

ERROR:  syntax error at or near "IF"
LINE 1: IF NOT EXISTS (SELECT table_name FROM information_schema.tab...
^

** Error **

ERROR: syntax error at or near "IF"
SQL state: 42601
Character: 1


How should this be written ?

Thanks, Jen

Re: [GENERAL] Can I have a look at your TuningWizard generated config file?

[Jennifer Trey]

For 1024MB = 1024*1024*1024 bytes
kernel.shmmax=1073741824

and for ceil(1073741824)/4096) = 262144 which is actually much smaller than
the setting that is the default ( 2097152 )

But setting these two to the values above still gives similar error and the
pg-server still doesn't start.

2009-08-01 17:58:19 CESTFATAL: could not create shared memory segment:
Invalid argument 2009-08-01 17:58:19 CESTDETAIL: Failed system call was
shmget(key=5432001, size=1106247680, 03600). 2009-08-01 17:58:19 CESTHINT:
This error usually means that PostgreSQL's request for a shared memory
segment exceeded your kernel's SHMMAX parameter. You can either reduce the
request size or reconfigure the kernel with larger SHMMAX. To reduce the
request size (currently 1106247680 bytes), reduce PostgreSQL's
shared_buffers parameter (currently 131072) and/or its max_connections
parameter (currently 153). If the request size is already small, it's
possible that it is less than your kernel's SHMMIN parameter, in which case
raising the request size or reconfiguring SHMMIN is called for. The
PostgreSQL documentation contains more information about shared memory
configuration.

Increasing to

kernel.shmmax=1106247680 kernel.shmall=29250

doesn't seem to help either :

2009-08-01 18:09:55 CESTFATAL: could not create shared memory segment: No
space left on device 2009-08-01 18:09:55 CESTDETAIL: Failed system call was
shmget(key=5432001, size=1106247680, 03600). 2009-08-01 18:09:55 CESTHINT:
This error does *not* mean that you have run out of disk space. It occurs
either if all available shared memory IDs have been taken, in which case you
need to raise the SHMMNI parameter in your kernel, or because the system's
overall limit for shared memory has been reached. If you cannot increase the
shared memory limit, reduce PostgreSQL's shared memory request (currently
1106247680 bytes), by reducing its shared_buffers parameter (currently
131072) and/or its max_connections parameter (currently 153). The PostgreSQL
documentation contains more information about shared memory configuration.


but this does work :

kernel.shmmax=1106247680
kernel.shmall=2097152 (default value)

So I am guessing some manuals needs an update :P

/Jennifer

Re: [GENERAL] Can I have a look at your TuningWizard generated config file?

[Jennifer Trey]

Hmm.. not done quite yet it seems :(

I tried initially to set shared_buffers to 1024MB but I got this message :

2009-08-01 15:04:46 CESTFATAL:  could not create shared memory segment:
Invalid argument
2009-08-01 15:04:46 CESTDETAIL:  Failed system call was shmget(key=5432001,
size=1106247680, 03600).
2009-08-01 15:04:46 CESTHINT:  This error usually means that PostgreSQL's
request for a shared memory segment exceeded your kernel's SHMMAX parameter.
 You can either reduce the request size or reconfigure the kernel with
larger SHMMAX.  To reduce the request size (currently 1106247680 bytes),
reduce PostgreSQL's shared_buffers parameter (currently 131072) and/or its
max_connections parameter (currently 153).
If the request size is already small, it's possible that it is less
than your kernel's SHMMIN parameter, in which case raising the request size
or reconfiguring SHMMIN is called for.

Lowered it to 64MB and same thing happen. Changed to 128kB and the server
started.

Reading on shared_buffers thing on
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
I
can see that this seems to be something expected, but I am confused on how
to fix this ...

sysctl -a | grep -i shm
error: permission denied on key 'net.ipv4.route.flush'
error: permission denied on key 'net.ipv6.route.flush'
kernel.shmmax = 33554432
kernel.shmall = 2097152
kernel.shmmni = 4096
vm.hugetlb_shm_group = 0

I am running Ubuntu Server 9.04 so I am guessing I should be looking under
Linux on
http://www.postgresql.org/docs/current/static/kernel-resources.html

I am confused about this part :

$ sysctl -w kernel.shmmax=134217728$ sysctl -w kernel.shmall=2097152

In addition these settings can be saved between reboots in /etc/sysctl.conf.

Should I just append that file with two lines :

kernel.shmmax=10486808576

kernel.shmmall=?? What should be here? Leave it alone? I am thinking I
shouldn't include this, only the above one... right?


Thanks in advance / Jen

Re: [GENERAL] Can I have a look at your TuningWizard generated config file?

[Jennifer Trey]

I think I got everything now. Most things where on by default it seems.


#--
# RUNTIME STATISTICS
#--

# - Query/Index Statistics Collector -

track_activities = off  *#I turned this off
*#track_counts = on
#track_functions = none# none, pl, all
track_activity_query_size = 16kB
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'


# - Statistics Monitoring -

#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off


#--
# AUTOVACUUM PARAMETERS
#--

#autovacuum = on# Enable autovacuum subprocess?  'on'
# requires track_counts to also be on.
#log_autovacuum_min_duration = -1# -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
#autovacuum_max_workers = 3# max number of autovacuum subprocesses
*autovacuum_naptime* = 20min# time between autovacuum runs
*autovacuum_vacuum_threshold* = 250# min number of row updates before
# vacuum
*autovacuum_analyze_threshold* = 125# min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2# fraction of table size before
vacuum
#autovacuum_analyze_scale_factor = 0.1# fraction of table size before
analyze
#autovacuum_freeze_max_age = 2# maximum XID age before forced
vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms# default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1# default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit


I am not going for the TuningWizard anymore since I set everything up
manually that I believe it normally touches.

Thanks all (on the other threads as well :) ), you have been extremely
helpful. I must say that I love this mailing list. And PostgreSQL :)

Sincerely / Jennifer

Re: [GENERAL] Can I have a look at your TuningWizard generated config file?

[Jennifer Trey]

Is autovacuum on by default?
or do I uncomment

#autovacuum = on

and

#track_counts = on

to enable it? Anything else I should do?

Thanks / Jennifer

On Fri, Jul 31, 2009 at 1:10 PM, Jennifer Trey wrote:

> Ok. I will.. when it comes to the other settings, like autovacuum and such.
> Could I have a look at yours ? :)
> I will list the ones that I find important and that i already have figured
> out good values for, please fill in if there is more I should look at:
>
> max_connections
> effective_cache
> random_page_cost
> maintenance_work_mem
> shared_buffers
> work_mem
> max_fsm_pages(scratched)
> max_fsm_relations(scratched)
> wal_buffers
>
> But then there is the log and autovacuum stuff that I have no idea about
> and that believe that I could just take from someone else and not really
> machine dependant..
>
> Thanks in advance / Jennifer
>
>
>
> On Fri, Jul 31, 2009 at 12:48 PM, Magnus Hagander wrote:
>
>> On Fri, Jul 31, 2009 at 12:35, Jennifer Trey
>> wrote:
>> > On Fri, Jul 31, 2009 at 12:29 PM, Thomas Kellerer 
>> > wrote:
>> >>
>> >> Jennifer Trey, 31.07.2009 12:17:
>> >>>
>> >>> Hi,
>> >>>
>> >>> I wanted to start with the TuningWizard and then configure the one
>> they
>> >>> suggested, thinking that I am starting from good default values.
>> >>>
>> >>> But I am not able to run TuningWizard for some strange reason, but I
>> am
>> >>> actually mostly interested in some few settings that it usually
>> provides.
>> >>> Especially max_fsm_pages
>> >>> max_fsm_relations
>> >>>
>> >> Which Postgres version are you using?
>> >> IIRC those settings are not longer valid for 8.4
>> >>
>> >> THomas
>> >
>> > Aha, ok. I am using 8.4. They are still to be set, confusing.
>>
>> AFAICS, the edb tuning wizard has not (yet) been updated to support
>> PostgreSQL 8.4. The hints it gives are for 8.3 and earlier. It would
>> probably be a good idea if they gave a warning for that, but I take it
>> they don't :-)
>>
>> There may be other issues as well, so read up on the documentation for
>> anything else it recommends to change.
>>
>> As for these two values, you can just ignore them - they are tuned
>> dynamically on 8.4.
>>
>>
>> --
>>  Magnus Hagander
>>  Self: http://www.hagander.net/
>>  Work: http://www.redpill-linpro.com/
>>
>
>

Re: [GENERAL] Can I have a look at your TuningWizard generated config file?

[Jennifer Trey]

Ok. I will.. when it comes to the other settings, like autovacuum and such.
Could I have a look at yours ? :)
I will list the ones that I find important and that i already have figured
out good values for, please fill in if there is more I should look at:

max_connections
effective_cache
random_page_cost
maintenance_work_mem
shared_buffers
work_mem
max_fsm_pages(scratched)
max_fsm_relations(scratched)
wal_buffers

But then there is the log and autovacuum stuff that I have no idea about and
that believe that I could just take from someone else and not really machine
dependant..

Thanks in advance / Jennifer



On Fri, Jul 31, 2009 at 12:48 PM, Magnus Hagander wrote:

> On Fri, Jul 31, 2009 at 12:35, Jennifer Trey
> wrote:
> > On Fri, Jul 31, 2009 at 12:29 PM, Thomas Kellerer 
> > wrote:
> >>
> >> Jennifer Trey, 31.07.2009 12:17:
> >>>
> >>> Hi,
> >>>
> >>> I wanted to start with the TuningWizard and then configure the one they
> >>> suggested, thinking that I am starting from good default values.
> >>>
> >>> But I am not able to run TuningWizard for some strange reason, but I am
> >>> actually mostly interested in some few settings that it usually
> provides.
> >>> Especially max_fsm_pages
> >>> max_fsm_relations
> >>>
> >> Which Postgres version are you using?
> >> IIRC those settings are not longer valid for 8.4
> >>
> >> THomas
> >
> > Aha, ok. I am using 8.4. They are still to be set, confusing.
>
> AFAICS, the edb tuning wizard has not (yet) been updated to support
> PostgreSQL 8.4. The hints it gives are for 8.3 and earlier. It would
> probably be a good idea if they gave a warning for that, but I take it
> they don't :-)
>
> There may be other issues as well, so read up on the documentation for
> anything else it recommends to change.
>
> As for these two values, you can just ignore them - they are tuned
> dynamically on 8.4.
>
>
> --
>  Magnus Hagander
>  Self: http://www.hagander.net/
>  Work: http://www.redpill-linpro.com/
>

Re: [GENERAL] Can I have a look at your TuningWizard generated config file?

[Jennifer Trey]

On Fri, Jul 31, 2009 at 12:29 PM, Thomas Kellerer wrote:

> Jennifer Trey, 31.07.2009 12:17:
>
>> Hi,
>>
>> I wanted to start with the TuningWizard and then configure the one they
>> suggested, thinking that I am starting from good default values.
>>
>> But I am not able to run TuningWizard for some strange reason, but I am
>> actually mostly interested in some few settings that it usually provides.
>> Especially max_fsm_pages
>> max_fsm_relations
>>
>>  Which Postgres version are you using?
> IIRC those settings are not longer valid for 8.4
>
> THomas



Aha, ok. I am using 8.4. They are still to be set, confusing.



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

Thanks / Jen

[GENERAL] Can I have a look at your TuningWizard generated config file?

[Jennifer Trey]

Hi,
I wanted to start with the TuningWizard and then configure the one they
suggested, thinking that I am starting from good default values.

But I am not able to run TuningWizard for some strange reason, but I am
actually mostly interested in some few settings that it usually provides.
Especially
max_fsm_pages
max_fsm_relations

and also the Vacuum settings, track_activities and such.. I don't know how
to set those things myself but I have figured out some of the other things.

The computer is a Quad Core with 8GB memory and running on Mixed mode on
Ubuntu Server 9.04

I use pg_admin to configure the config file because it provides a good
overview of the things that are set and values.
So this is a special request, if you could provide a screen shot with
perhaps three images, then I could go through it myself one by one and do
similar things on mine.

Thank you and I hope I am not asking to much :)

- Jennifer

Re: [GENERAL] pg_config --sharedir points to the wrong folder.. is this a problem?

[Jennifer Trey]

On Fri, Jul 31, 2009 at 3:02 AM, Daniel Verite wrote:

>    Jennifer Trey wrote:
>
> > >> locate pg_config
> > /opt/PostgreSQL/8.4/bin/pg_config
> > /opt/PostgreSQL/8.4/include/ecpg_config.h
> > /opt/PostgreSQL/8.4/include/pg_config.h
> > /opt/PostgreSQL/8.4/include/pg_config_manual.h
> > /opt/PostgreSQL/8.4/include/pg_config_os.h
> > /opt/PostgreSQL/8.4/include/postgresql/server/pg_config.h
> > /opt/PostgreSQL/8.4/include/postgresql/server/pg_config_manual.h
> > /opt/PostgreSQL/8.4/include/postgresql/server/pg_config_os.h
> > /opt/PostgreSQL/8.4/share/man/man1/pg_config.1
> > /usr/bin/pg_config
> > /usr/share/man/man1/pg_config.1.gz
>
> > >> echo $PATH
> > /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games
> >
> > I am guessing I should delete the pg_config (or else there would be a
> > conflict, right?) in the /usr/bin
>
> Actually you should restrain from doing that, because it would break the
> package that installed that file, and then your system would become harder
> to
> manage in the long run. It's better to act at the package level with the
> dpkg
> command.
>
> /usr/bin/pg_config is part of the libpq-dev package (this can be found with
> dpkg -S /usr/bin/pg_config)
>
> Probably you haven't uninstalled this package, which you can check by
> running:
> dpkg -s libpq-dev (look at the Status field). In this case, remove it
> (apt-get remove libpq-dev)
>



Thanks.Yes, it was still there. I un-installed it.
Should I still add the pg_config to the $PATH ?
>> export PATH=/opt/PostgreSQL/8.4/bin/:$PATH




> You can also remove the postgresql-common and postgresql-client-8.3
> packages
> if they're still there and you want to avoid any possible conflict with
> your
> other postgresql installation.
> dpkg -l 'postgresql*' also displays the list of most PG-related packages
> and
> whether they're installed or not.
>


r...@ubuntu-904-jaunty-64-minimal:~# dpkg -l 'postgresql*'
Desired=Unknown/Install/Remove/Purge/Hold
|
Status=Not/Inst/Cfg-files/Unpacked/Failed-cfg/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Hold/Reinst-required/X=both-problems (Status,Err:
uppercase=bad)
||/ Name   VersionDescription
+++-==-==-
pn  postgresql  (no description
available)
un  postgresql-7.4  (no description
available)
un  postgresql-8.0  (no description
available)
rc  postgresql-8.3 8.3.7-1object-relational
SQL database, version 8.3 server
pn  postgresql-client   (no description
available)
pn  postgresql-client-8.3   (no description
available)
pn  postgresql-client-common(no description
available)
rc  postgresql-common  97 PostgreSQL
database-cluster manager
un  postgresql-dev  (no description
available)
un  postgresql-doc-8.3  (no description
available)

Looks fine?



> > Is there other things that could have been destroyed by the bad
> > un-installment other than pg_config or was that it ?
>
> It doesn't look like the uninstallation of the server itself has gone bad
> in
> any way. You mentioned removing the package without stopping the server
> beforehand, but personally I doubt that the package uninstall process
> doesn't
> do it automatically.
>


Ok, that's good to hear. I must have missed un-installing the package
mentioned above.
What I did was to uninstall the postgresql-common which then removed some
other depending packages.I am not sure how this package got installed.



> Best regards,
> --
> Daniel
> PostgreSQL-powered mail user agent and storage:
> http://www.manitou-mail.org
>


Sincerely / Jennifer

Re: [GENERAL] pg_config --sharedir points to the wrong folder.. is this a problem?

[Jennifer Trey]

Yes, you are right. I didn't stop the server before un-installing. Not good.
I should have thought about that. Not sure how to repair that though.

running pg_config :

r...@ubuntu-904-jaunty-64-minimal:/opt/PostgreSQL/EnterpriseDB-TuningWizard#
pg_config
BINDIR = /usr/lib/postgresql/8.3/bin
DOCDIR = /usr/share/doc/postgresql-doc-8.3
INCLUDEDIR = /usr/include/postgresql
PKGINCLUDEDIR = /usr/include/postgresql
INCLUDEDIR-SERVER = /usr/include/postgresql/8.3/server
LIBDIR = /usr/lib
PKGLIBDIR = /usr/lib/postgresql/8.3/lib
LOCALEDIR = /usr/share/locale
MANDIR = /usr/share/postgresql/8.3/man
SHAREDIR = /usr/share/postgresql/8.3
SYSCONFDIR = /etc/postgresql-common
PGXS = /usr/lib/postgresql/8.3/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--build=x86_64-linux-gnu' '--prefix=/usr'
'--includedir=/usr/include' '--mandir=/usr/share/man'
'--infodir=/usr/share/info' '--sysconfdir=/etc' '--localstatedir=/var'
'--libexecdir=/usr/lib/postgresql-8.3' '--disable-maintainer-mode'
'--disable-dependency-tracking' '--srcdir=.'
'--mandir=/usr/share/postgresql/8.3/man'
'--with-docdir=/usr/share/doc/postgresql-doc-8.3'
'--sysconfdir=/etc/postgresql-common' '--datadir=/usr/share/postgresql/8.3'
'--bindir=/usr/lib/postgresql/8.3/bin'
'--includedir=/usr/include/postgresql/' '--enable-nls'
'--enable-integer-datetimes' '--enable-thread-safety' '--enable-debug'
'--disable-rpath' '--with-tcl' '--with-perl' '--with-python' '--with-pam'
'--with-krb5' '--with-gssapi' '--with-openssl' '--with-libxml'
'--with-libxslt' '--with-ldap' '--with-ossp-uuid' '--with-gnu-ld'
'--with-tclconfig=/usr/lib/tcl8.5' '--with-tkconfig=/usr/lib/tk8.5'
'--with-includes=/usr/include/tcl8.5'
'--with-system-tzdata=/usr/share/zoneinfo' '--with-pgport=5432' 'CFLAGS=-g
-O2 -g -Wall -O2 -fPIC' 'LDFLAGS=-Wl,-Bsymbolic-functions -Wl,--as-needed'
'CC=cc' 'CPPFLAGS=' 'build_alias=x86_64-linux-gnu'
CC = cc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/tcl8.5
CFLAGS = -g -O2 -g -Wall -O2 -fPIC -Wall -Wmissing-prototypes
-Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels
-fno-strict-aliasing -fwrapv -g
CFLAGS_SL = -fpic
LDFLAGS = -Wl,-Bsymbolic-functions -Wl,--as-needed
LDFLAGS_SL =
LIBS = -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lkrb5 -lcom_err
-lgssapi_krb5 -lz -lreadline -lcrypt -ldl -lm
VERSION = PostgreSQL 8.3.7

It definetly points to the old version (8.3 .. why is 8.3 still in the
ubuntu package manager btw...?) ..

>> locate pg_config
/opt/PostgreSQL/8.4/bin/pg_config
/opt/PostgreSQL/8.4/include/ecpg_config.h
/opt/PostgreSQL/8.4/include/pg_config.h
/opt/PostgreSQL/8.4/include/pg_config_manual.h
/opt/PostgreSQL/8.4/include/pg_config_os.h
/opt/PostgreSQL/8.4/include/postgresql/server/pg_config.h
/opt/PostgreSQL/8.4/include/postgresql/server/pg_config_manual.h
/opt/PostgreSQL/8.4/include/postgresql/server/pg_config_os.h
/opt/PostgreSQL/8.4/share/man/man1/pg_config.1
/usr/bin/pg_config
/usr/share/man/man1/pg_config.1.gz

>> echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games

I am guessing I should delete the pg_config (or else there would be a
conflict, right?) in the /usr/bin and run :

>> export PATH=/opt/PostgreSQL/8.4/bin/:$PATH

is that correct? ( I learned from you other message :) )

Is there other things that could have been destroyed by the bad
un-installment other than pg_config or was that it ?

Thanks allot / Jennifer

Re: [GENERAL] How do I run PG Tuning Wizard on Linux?

[Jennifer Trey]

Thank you for your answer.
I did export the LD_LIBRARY_PATH and now the complain is something else..
progress :)

Error: Unable to initialize gtk, is DISPLAY set properly?

I have also launched the TuningWizard from an icon and I got to the step
(development, mixed, dedicated) as before and then the same message
(connecting to webserver) and then it disappears.

Thanks / Jen

Re: [GENERAL] How do I run PG Tuning Wizard on Linux?

[Jennifer Trey]

Just another try on TuningWizard.
I tried running it from console :

r...@ubuntu-904-jaunty-64-minimal:/opt/PostgreSQL/EnterpriseDB-TuningWizard#
./TuningWizard
./TuningWizard: error while loading shared libraries: libexpat.so.0: cannot
open shared object file: No such file or directory

And :

r...@ubuntu-904-jaunty-64-minimal:/opt/PostgreSQL/EnterpriseDB-TuningWizard#
locate *libexpat*

/opt/PostgreSQL/8.4/pgAdmin3/lib/libexpat.so
/opt/PostgreSQL/8.4/pgAdmin3/lib/libexpat.so.0
/opt/PostgreSQL/8.4/pgAdmin3/lib/libexpat.so.0.5.0
/opt/PostgreSQL/EnterpriseDB-TuningWizard/lib/libexpat.so
/opt/PostgreSQL/EnterpriseDB-TuningWizard/lib/libexpat.so.0
/opt/PostgreSQL/EnterpriseDB-TuningWizard/lib/libexpat.so.0.5.0
/usr/lib/libexpat.so.1
/usr/lib/libexpat.so.1.5.2
/usr/lib/libexpatw.so.1
/usr/lib/libexpatw.so.1.5.2
/usr/share/doc/libexpat1
/usr/share/doc/libexpat1/changelog.Debian.gz
/usr/share/doc/libexpat1/changelog.gz
/usr/share/doc/libexpat1/copyright
/var/lib/dpkg/info/libexpat1.list
/var/lib/dpkg/info/libexpat1.md5sums
/var/lib/dpkg/info/libexpat1.postinst
/var/lib/dpkg/info/libexpat1.postrm
/var/lib/dpkg/info/libexpat1.shlibs


Any ideas?

[GENERAL] pg_config --sharedir points to the wrong folder.. is this a problem?

[Jennifer Trey]

Hi,
First I installed postgres using the apt-get utility on ubuntu, and then
later uninstalled it to install postgres though the one-click installer.

Trying to find the share folder i ran pg_config and it shows the old path
: /usr/share/postgresql/8.3

I have a pg_config under my new installation as well
: /opt/PostgreSQL/8.4/bin/pg_config
but apperantly this is not being used in the global scope.

Am I missing something? Did I un-install incorrectly perhaps? Is this a
problem, could other files, pg_programs possibly have the same issue?

Sincerely / Jennifer

Re: [GENERAL] How do I run PG Tuning Wizard on Linux?

[Jennifer Trey]

Unfortunately I used the apt-get utility. So I might have missed out on
those packages.  But I un-installed (I hope I got everything) and deleted
some folders, got the one click installer and re-installed. I got everything
now and  set up postgre and it is working fine. However (something I ran
into), running pg_config --sharedir gives a folder that doesn't exist
anymore( from previous installation I would think (i might have deleted that
folder))

Running the TuningWizard seems to not be working though. After I select
"mixed", it says: "Initializing Webservice" and then it disappears and
nothing ever happens... Looking at the postgresql.conf file , it seems not
have been touched...

Just a thought, do I need the Apache Server thingy that one can select on
the StackBuilder?

I am running on Ubuntu Server 9.04

Thanks / Jen

Re: [GENERAL] How do I run PG Tuning Wizard on Linux?

[Jennifer Trey]

Is this it?
http://pgfoundry.org/projects/pgtune

I thought it was EnterpriseDB. Perhaps that's only for windows?

Thanks / Jen

[GENERAL] How do I run PG Tuning Wizard on Linux?

[Jennifer Trey]

I have been using Windows before and that was just an installer. How can I
install and run something similar on Ubuntu ?
Thanks / Jen

Re: [GENERAL] Moving from Windows to Ubuntu - Have a couple of questions

[Jennifer Trey]

Thanks, I got help from a friend and got it to work. I think postgre was
running already but not sure what else he did.. i think that he altered the
password among other things.
How should one port the config file from windows to linux? I am guessing
replacing the .conf file is not an option :O .. the hard ware is the same,
should I just go through them one after one?

- Jen

[GENERAL] Moving from Windows to Ubuntu - Have a couple of questions

[Jennifer Trey]

Hi,
I have been running PostgreSQL 8.3 for a while now and was installed through
the standard Windows installer and everything has been working great :)

I am in the process of switching to Ubuntu, and I have a couple of question
to get started. I installed pgadmin and postgre through the Synaptic Package
Manager. I am unfamiliar with the console so I prefer to use pgAdmin to set
things up.

Opening pgAdmin I see Servers(0) .. I am trying to add a server and filled
out things to match the windows properties but can't get it to work.
On the Service part, windows says postgresql-8.3 but on Ubuntu it is empty.
I tried to put the same thing there as well, but also with no success. I am
guessing I need to do something before that.. the ideas is that I will
create a DB later and use my windows backup and restore.

Please help me figure this out,

Sincerely / Jennifer

Re: [GENERAL] Group by on %like%

[Jennifer Trey]

Sorry for taking so long to respond. The prefix thingy is definetly
attractive for future development and I had already discovered them to be a
challenge. However, i have noticed all kinds of ways people tend to write
their number, including omitting the + or 00 .. so at this time, for this
task, I found Guys to be working pretty well.
Thanks all / Jen

[GENERAL] Group by on %like%

[Jennifer Trey]

Hi,

I would like to run a query and group several rows based on a phone number.

However, the same phone number might have a prefix on occasion, example :

name | phone_number
--
james | 123456
james | 00441234556
james | 555666
sarah | 567890
sarah | 567890

as you can see, the first 2 James seems to belong together.

running

select name, phone_number from relation group by name, phone_number

would not reflect this.

I don't think there is a way to run something similar to this :

select name, phone_number from relation group by name, %phone_number%
// or similar

However, I believe there is a way, so I would like to here it from you :)

Functions, sums .. please let me know..

Thank you in advance / Jennifer

-- 
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] Re: High I/O writes activity on disks causing images on browser to lag and not load

[Jennifer Trey]

Reporting back.. no.. I tested the track_count and autovacuum and the writes
where back.
/ Jennifer

On Thu, Jun 4, 2009 at 6:54 PM, Jennifer Trey wrote:

>
>
> On Thu, Jun 4, 2009 at 4:58 PM, Bill Moran wrote:
>
>> In response to Jennifer Trey :
>> > Bill, you wrote earlier :
>> >
>> > "
>> > Additionally, this convinces me further that you're chasing the wrong
>> > problem.  The stats collector writes tiny bits of information to disk
>> > every time you execute a command.  If your system is slow because of
>> this
>> > tiny bit of I/O, then something else is wrong.  Either your system is
>> > already near its max capacity and this is pushing it over the edge, or
>> > you're fixing the wrong problem.
>> > "
>> >
>> > If this was true, that is that only small bits should be written, why is
>> the
>> > total write size each time around 57kB (for 15 write ops)? Thats also
>> the
>> > size of the file pgstat.tmp. At this time, there is for that posgres
>> process
>> > 33,330 I/O Writes, with a total size of 129,221,526 Bytes.
>>
>> In a previous message you posted a snippet of your postgresql.conf file
>> that showed you still had a lot of the stats logging turned on.  As noted
>> in the docs, the default values for many of those settings is on, so the
>> fact that they're commented out means they're taking their default values.
>> I'm guessing that you haven't actually turned them off yet.
>>
>
> Thank you, I apologize for being a little slow :)
>
> Here is a new snippet of my file,
>
>
> #--
> # RUNTIME STATISTICS
>
> #--
>
> # - Query/Index Statistics Collector -
>
> track_activities = off
> track_counts = off
> update_process_title = off
>
>
> # - Statistics Monitoring -
>
> log_parser_stats = off
> log_planner_stats = off
> log_executor_stats = off
> log_statement_stats = off
>
>
>
> #--
> # AUTOVACUUM PARAMETERS
> #--
>
> #autovacuum = on # Enable autovacuum subprocess?  'on'
> # NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
> 2009/04/08 13:33:58
> autovacuum = off # Enable autovacuum subprocess?  'on'
>  # requires track_counts to also be on.
> #log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
>  # their durations, > 0 logs only
> # actions running at least that time.
> #autovacuum_max_workers = 3 # max number of autovacuum subprocesses
> #autovacuum_naptime = 1min # time between autovacuum runs
> # NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
> 2009/04/08 13:33:58
> autovacuum_naptime = 60 # time between autovacuum runs
> #autovacuum_vacuum_threshold = 50 # min number of row updates before
> # NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
> 2009/04/08 13:33:58
> autovacuum_vacuum_threshold = 1000 # min number of row updates before
> # vacuum
> #autovacuum_analyze_threshold = 50 # min number of row updates before
> # NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
> 2009/04/08 13:33:58
> autovacuum_analyze_threshold = 250 # min number of row updates before
> # analyze
> #autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before
> vacuum
> # NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
> 2009/04/08 13:33:58
> autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before
> vacuum
> #autovacuum_analyze_scale_factor = 0.1 # fraction of table size before
> analyze
> # NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
> 2009/04/08 13:33:58
> autovacuum_analyze_scale_factor = 0.1 # fraction of table size before
> analyze
> #autovacuum_freeze_max_age = 2 # maximum XID age before forced
> vacuum
> # (change requires restart)
> #autovacuum_vacuum_cost_delay = 20 # default vacuum cost delay for
> # autovacuum, -1 means use
>  # vacuum_cost_delay
> #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
>  # autovacuum, -1 means use
> # vacuum_cost_limit
>
>
> I restarded the app and now I have gotten rid of most of the writes. Great
> :)
> It is still writing, but this time its only doing it once per query. It
> seems like its not repeating it self. If I run a query thats no

Re: [GENERAL] Re: High I/O writes activity on disks causing images on browser to lag and not load

[Jennifer Trey]

On Thu, Jun 4, 2009 at 4:58 PM, Bill Moran  wrote:

> In response to Jennifer Trey :
> > Bill, you wrote earlier :
> >
> > "
> > Additionally, this convinces me further that you're chasing the wrong
> > problem.  The stats collector writes tiny bits of information to disk
> > every time you execute a command.  If your system is slow because of this
> > tiny bit of I/O, then something else is wrong.  Either your system is
> > already near its max capacity and this is pushing it over the edge, or
> > you're fixing the wrong problem.
> > "
> >
> > If this was true, that is that only small bits should be written, why is
> the
> > total write size each time around 57kB (for 15 write ops)? Thats also the
> > size of the file pgstat.tmp. At this time, there is for that posgres
> process
> > 33,330 I/O Writes, with a total size of 129,221,526 Bytes.
>
> In a previous message you posted a snippet of your postgresql.conf file
> that showed you still had a lot of the stats logging turned on.  As noted
> in the docs, the default values for many of those settings is on, so the
> fact that they're commented out means they're taking their default values.
> I'm guessing that you haven't actually turned them off yet.
>

Thank you, I apologize for being a little slow :)

Here is a new snippet of my file,

#--
# RUNTIME STATISTICS
#--

# - Query/Index Statistics Collector -

track_activities = off
track_counts = off
update_process_title = off


# - Statistics Monitoring -

log_parser_stats = off
log_planner_stats = off
log_executor_stats = off
log_statement_stats = off


#--
# AUTOVACUUM PARAMETERS
#--

#autovacuum = on # Enable autovacuum subprocess?  'on'
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2009/04/08 13:33:58
autovacuum = off # Enable autovacuum subprocess?  'on'
 # requires track_counts to also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
 # their durations, > 0 logs only
# actions running at least that time.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
#autovacuum_naptime = 1min # time between autovacuum runs
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2009/04/08 13:33:58
autovacuum_naptime = 60 # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2009/04/08 13:33:58
autovacuum_vacuum_threshold = 1000 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2009/04/08 13:33:58
autovacuum_analyze_threshold = 250 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2009/04/08 13:33:58
autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before
analyze
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2009/04/08 13:33:58
autovacuum_analyze_scale_factor = 0.1 # fraction of table size before
analyze
#autovacuum_freeze_max_age = 2 # maximum XID age before forced
vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20 # default vacuum cost delay for
# autovacuum, -1 means use
 # vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
 # autovacuum, -1 means use
# vacuum_cost_limit


I restarded the app and now I have gotten rid of most of the writes. Great
:)
It is still writing, but this time its only doing it once per query. It
seems like its not repeating it self. If I run a query thats not been run
before it seems to do the writes, but only the first time..
I don't mind this at all and reading the Run-Time statistics I am guessing
that it was one of those parameters that was causing this. Possibly the
first one on your link Bill. Could I possibly turn on AutoVacuum and turn on
only track_counts? I am going to try it out..
Looks fine otherwise?



>
> > I turned off AutoVacuum, and restarted PG but this was still going on.
>
> That's not going to change the behaviour of this problem if you've still
> got the stats monitoring turned on.
>
> > I would like to move the PGdata to the pictures disk.
> >
> > "
&

Re: [GENERAL] Move PGdata to a different drive

[Jennifer Trey]

Ook.. thats what I wanted to know. I figured there has to be a place to
change the pointer to the new location of the data.
Thank you / Jennifer

On Thu, Jun 4, 2009 at 6:11 PM, Bruce Momjian  wrote:

> Jennifer Trey wrote:
> > Hi,
> > What file should I be working with?
>
> Just shut down the server and move the directory whever you want and
> restart the server.  There are no file contents that need changing.  Of
> course should adjust your scripts or PGDATA environment variable.
>
> --
>  Bruce Momjian  http://momjian.us
>  EnterpriseDB http://enterprisedb.com
>
>  + If your life is a hard drive, Christ can be your backup. +
>

[GENERAL] Move PGdata to a different drive

[Jennifer Trey]

Hi,
What file should I be working with?

Thanks in advance / Jennifer

[GENERAL] Re: High I/O writes activity on disks causing images on browser to lag and not load

[Jennifer Trey]

No, I don't have any virus programs installed.
Bill, you wrote earlier :

"

Additionally, this convinces me further that you're chasing the wrong
problem.  The stats collector writes tiny bits of information to disk
every time you execute a command.  If your system is slow because of this
tiny bit of I/O, then something else is wrong.  Either your system is
already near its max capacity and this is pushing it over the edge, or
you're fixing the wrong problem.


"

If this was true, that is that only small bits should be written, why is the
total write size each time around 57kB (for 15 write ops)? Thats also the
size of the file pgstat.tmp. At this time, there is for that posgres process
33,330 I/O Writes, with a total size of 129,221,526 Bytes.

Filtering for that PID, the only action seems to be WriteFile, CreateFile,
CloseFile, SetRenameInformationFile and 2-3 other types with WriteFile being
the dominant one.
The files touched is pgstat.tmp and pgstat.stat, pgstat.tmp being the
dominant one.

As I understand it, writes occur frequently on pgstat.tmp which in the next
step gets renamed and replaces pgstat.stat with a setRenameInformationFile
with information on Detail : ReplaceIfExists: True, FileName:
\global\pgstat.stat

I turned off AutoVacuum, and restarted PG but this was still going on.

I would like to move the PGdata to the pictures disk.

"

You can just pick up the data directory and relocate it, then config
PostgreSQL to look for the data directory in the new location, or create
a symlink.

"

Where can I find that file? I found out that its the pgdata variable but
couldn't find out what file it was.


thanks / Jennifer

Re: [GENERAL] High I/O writes activity on disks causing images on browser to lag and not load

[Jennifer Trey]

Sorry, went to bed yesterday :D
I have installed postgresql-8.3.7-1-windows.exe

Bill, you are right. The app does do tons of small queries, and its possible
that the two drives are misconfigured. I have checked into that a little and
can't rule it out completely.

Yes, my images hang indefinitly, until the browser gives up. Note that, if I
reload the page it will normally reload fine, or possibly that some other
image hangs instead. The images are on the other 1TB disk drive, and there
is almost no activity on that drive(seen from perfmon), but it seems to be
going through the C:drive so if there is a queue on C: then there will be a
problem fetching from I:Image as well. I would prefer if the I:Image drive
just flushed out the images (not sure how I can accomplish that) it self but
the overwhelming disk activity seems to be the disk writes on C... thats
what I noticed on perfmon. I/O transfers and I/O writes graphs where almost
identical. Would you say that these I/O writes are normal?

I could move the pgdata to the image drive and have that one busy with the
writing instead. Not sure that will help though.

 When it come to turning of the Statistics, I saw your link and looked into
my config file.. those settings where all off or commented :
*
*
#--
# RUNTIME STATISTICS
#--

# - Query/Index Statistics Collector -

#track_activities = on
#track_counts = on
#update_process_title = on

# - Statistics Monitoring -

#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off


#--
# AUTOVACUUM PARAMETERS
#--

#autovacuum = on # Enable autovacuum subprocess?  'on'
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2009/04/08 13:33:58
autovacuum = true # Enable autovacuum subprocess?  'on'
# requires track_counts to also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least that time.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
#autovacuum_naptime = 1min # time between autovacuum runs
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2009/04/08 13:33:58
autovacuum_naptime = 60 # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2009/04/08 13:33:58
autovacuum_vacuum_threshold = 1000 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2009/04/08 13:33:58
autovacuum_analyze_threshold = 250 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2009/04/08 13:33:58
autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before
analyze
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2009/04/08 13:33:58
autovacuum_analyze_scale_factor = 0.1 # fraction of table size before
analyze
#autovacuum_freeze_max_age = 2 # maximum XID age before forced
vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20 # default vacuum cost delay for
# autovacuum, -1 means use
# vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit

*
*
Just turn off autovacuum ?
*
*
/ Jennifer

Re: [GENERAL] High I/O writes activity on disks causing images on browser to lag and not load

[Jennifer Trey]

The research is based on hypotheses and assumptions which in the end can
be discarded or not. I do not know these things, I am taking one step at
time, learning and trying.
I don't understand what kind of research you want me to do. I don't have any
performance problem other than that images seems to get stuck on occasion,
with a certain amount of users.This is what I've got, and I have spent
several weeks reaching this stage. I started by removing a router and
arranging another internet, benchmarking the application, study the network
card, switching to Apache Server and I have ruled all of them out. I have
now come to the disk, and here its postgre thats most active and its
starting to feel right, but I could be wrong but what other leads do I have?

Since even a select count(*) outside my application is causing this I do not
understand what conclusion I should take. After all, you said it yourself,
you do not get any writes, why do I ? With perfmon under heavy load I
registered 4000 I/0 transefers / sec and they where all writes so I think I
am getting closer.

Here is a similar one but on linux
http://archives.postgresql.org/pgsql-admin/2005-12/msg00266.php

I am will try turn the stats off, but I am not sure how that might affect
the db's internal stats on how to best run a query, what indices is good or
not.. ?

Thanks / Jennifer

Re: [GENERAL] High I/O writes activity on disks causing images on browser to lag and not load

[Jennifer Trey]

Hmm.. I doesn't look it made it to the mailing list. I guess you can't
attach a file then.
Finally!
I did use the programs suggested by Michael, but I actually found it a
little before you wrote :P

Filtering out with the pid showed that it was the file
pgdata/global/pgstat.tmp

See the attached image. ( see this link instead :
http://tinypic.com/view.php?pic=52e73n&s=5 )

The file is being written to / replaced and closed. The size of that file is
around 57kb which was reported earlier as well.

The file contains just allot of stange tokens and letters. What is used for?

/ Jennifer

Re: [GENERAL] High I/O writes activity on disks causing images on browser to lag and not load

[Jennifer Trey]

Bill, did you see my last message on the mailing list? I have tracked down
the file. Is this some statistics file? Could this be a bug caused by auto
vacuum being on?

Re: [GENERAL] High I/O writes activity on disks causing images on browser to lag and not load

[Jennifer Trey]

On Wed, Jun 3, 2009 at 10:59 PM, Bill Moran wrote:

> In response to Jennifer Trey :
>
> > Hmm, I just noticed the same write behavior on my Windows Xp laptop but
> the
> > values was a little less.
> > I even created an DB with one table and column and this still happened
> > when querying it.
>
> By "created", you mean you created a table and populated it with data?
> Once you do that, do a "SELECT count(*)" on that table, then wait for
> the I/O to calm down.  That select statement will force all the hint
> bits to be updated.  See if subsequent selects still cause disk
> activity.
>

No, I created a new DB, created a table, and did not even populate any
data.

Running select count(*) from test

just now, still caused the 10-20 I/O-writes.


>
> > Are you sure that moving to Linux will solve this?
>
> I never advocated that Linux would fix this, and I still don't.  I
> recommended a short list of methods to investigate the issue, most of
> which you ignored.  You _still_ don't know what's being written, and
> I _highly_ recommend that you isolate that before doing something
> radical like switching operating systems.


I didn't ignore all of them.
When it comes to the logging I am still not sure. What file should I be
looking at ? The standard log file currently has 5 lines in it, and its only
errors.
When it comes to things set as wrong, it might be true. However, on the
laptop I've only installed and ran Tuning Wizard and haven't touched it
afterwards.

No, I still don't know whats being written. I have tried to isolate it, and
checked several folders, but can't find the path.

The statement i made earlier about how there was no reads was false. There
is reads and they are done mostly by another thread. I was checking the same
process at that time. However, the combined sum of I/O shows that there are
more writes than reads with postgresql. Currently on the server by 2.25


>
>
> If you've got the DB configured in such a way that it's causing a lot of
> write ops, it's going to do it in Linux or any other Posix systems, or
> on CP/M for that matter.
>
> Posix systems have a laundry list of tools to identify what programs are
> doing.  It's been a while since I've worked with Windows, but I seem to
> remember MS having tools to audit disk activity.  Turn them on and see
> which files are actually being written to.
>

I will try to find such a tool.


>
> > Could you please check if
> > you notice the same write behavior?
>
> My BSD-based systems to no do this.  Doing a select count(*) on a table
> with 750,000 rows produces no write activity.
>

Thats good to know.


>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>


Grzegorz, i have considered the hosting solutions. Problem is money. I am
still a student. I might take you up on the other offer though :)

Scott, how much would such a controller cost me?

Tim, yes, I am using the tool "ProcessExplorer" from the windows site. It
shows all the activity but can't see to where those writes are being done
with that tool. Any ideas?


Thanks all, appreciate all your help and effort.

Sincerely / Jennifer

Re: [GENERAL] High I/O writes activity on disks causing images on browser to lag and not load

[Jennifer Trey]

Hmm, I just noticed the same write behavior on my Windows Xp laptop but the
values was a little less.
I even created an DB with one table and column and this still happened
when queering it.

The problem here is the number of I/O writes. From 15 to 30... with lots of
users this is becoming an issue.

Are you sure that moving to Linux will solve this? Could you please check if
you notice the same write behavior? Any other windows users that noticed
this?

Thank you in advance / Jennifer


On Wed, Jun 3, 2009 at 8:32 PM, Jennifer Trey wrote:

> Sorry.. the formatting got screwed.. here it is again :
> -
> lol, if I had a nickel for every one that said they had a dollar I'd buy a
> Ferrari F430 :P
> but I do.. most of my memory isn't being used.. and I have once had a deep
> discussion on tuning postgres on this mailing list. Feel free to search on
> my name and you will see it :)
>
> But I have gotten even closer now to understanding this and changing the
> effective_cache thingy didn't help :(
>
> I have noticed that for every query that is ran on the DB the write
> happens, no matter if it is from my application or from some other place,
> 15 I/O writes always occurs and has a size of approximatly 57kb each. The
> size differiates a little but it is always 15 writes! It doesn't matter if
> it is 2 queries (from the page) or 1 query or more. It seems to be on a
> connection basis. I opened up pgadmin and ran an sql query there and same
> thing happened. I don't understand how why the reads aren't increasing
> either, even if there is writes, there should be reads as well, right!?
>
> select * from ad where id >= 2 limi x ... where x could be anything and the
> nbr of writes was still constant and id is a primary key.
>
> It leaves me to believe that its either some logging like you mentioned
> Bill, but I have turned off it (at least I believe so) or that there is some
> PostgreSQL on Windows issues. I forgot to mention that I am running Postgre
> 32bit on Windows Server 2008 64bit. ??
>
> Also, the data that Postgre uses is on the same disk but on a different
> partition. Could it be that the writing is from one partion to another, but
> then there is the reading that should still occur. But perhaps its from the
> buffer or memory :O haha.. just guessing..
>
> It is possible that Windows permission system is forcing postgre to this
> behaviour. I have had numerous permission issues, where the OS after ( in
> postgreSql's case, was when I installed Tuning Wizard) took permission over
> files and therefore was no longer accisseble to Posgre (that hassle took me
> some time aswell)
>
> If you have a good suggestion or theories please let me know. Otherwise, I
> think I will move over to Ubuntu Server real soon! I can't stand this
> anymore! I always have to consider that its Windows screwing things up for
> me :) I think with linux I will at least be free of that :)
>
> Thank you  / Jennifer
>
>

Re: [GENERAL] High I/O writes activity on disks causing images on browser to lag and not load

[Jennifer Trey]

Sorry.. the formatting got screwed.. here it is again :
-
lol, if I had a nickel for every one that said they had a dollar I'd buy a
Ferrari F430 :P
but I do.. most of my memory isn't being used.. and I have once had a deep
discussion on tuning postgres on this mailing list. Feel free to search on
my name and you will see it :)

But I have gotten even closer now to understanding this and changing the
effective_cache thingy didn't help :(

I have noticed that for every query that is ran on the DB the write happens,
no matter if it is from my application or from some other place,
15 I/O writes always occurs and has a size of approximatly 57kb each. The
size differiates a little but it is always 15 writes! It doesn't matter if
it is 2 queries (from the page) or 1 query or more. It seems to be on a
connection basis. I opened up pgadmin and ran an sql query there and same
thing happened. I don't understand how why the reads aren't increasing
either, even if there is writes, there should be reads as well, right!?

select * from ad where id >= 2 limi x ... where x could be anything and the
nbr of writes was still constant and id is a primary key.

It leaves me to believe that its either some logging like you mentioned
Bill, but I have turned off it (at least I believe so) or that there is some
PostgreSQL on Windows issues. I forgot to mention that I am running Postgre
32bit on Windows Server 2008 64bit. ??

Also, the data that Postgre uses is on the same disk but on a different
partition. Could it be that the writing is from one partion to another, but
then there is the reading that should still occur. But perhaps its from the
buffer or memory :O haha.. just guessing..

It is possible that Windows permission system is forcing postgre to this
behaviour. I have had numerous permission issues, where the OS after ( in
postgreSql's case, was when I installed Tuning Wizard) took permission over
files and therefore was no longer accisseble to Posgre (that hassle took me
some time aswell)

If you have a good suggestion or theories please let me know. Otherwise, I
think I will move over to Ubuntu Server real soon! I can't stand this
anymore! I always have to consider that its Windows screwing things up for
me :) I think with linux I will at least be free of that :)

Thank you  / Jennifer

Re: [GENERAL] High I/O writes activity on disks causing images on browser to lag and not load

[Jennifer Trey]

lol, if I had a nickel for every one that said they had a dollar I'd buy a
Ferrari F430 :P
but I do.. most of my memory isn't being used.. and I have once had a deep
discussion on tuning postgres on this mailing list. Feel free to search on
my name and you will see it :)
But I have gotten even closer now to understanding this and changing the
effective_cache thingy didn't help :(
I have noticed that for every query that is ran on the DB the write happens,
no matter if it is from my application or from some other place,
15 I/O writes always occurs and has a size of approximatly 57kb each. The
size differiates a little but it is always 15 writes! It doesn't matter if
it is 2 queries (from the page) or 1 query or more. It seems to be on a
connection basis. I opened up pgadmin and ran an sql query there and same
thing happened. I don't understand how why the reads aren't increasing
either, even if there is writes, there should be reads as well, right!?
select * from ad where id >= 2 limi x ... where x could be anything and the
nbr of writes was still constant and id is a primary key.
It leaves me to believe that its either some logging like you mentioned
Bill, but I have turned off it (at least I believe so) or that there is some
PostgreSQL on Windows issues. I forgot to mention that I am running Postgre
32bit on Windows Server 2008 64bit. ??
Also, the data that Postgre uses is on the same disk but on a different
partition. Could it be that the writing is from one partion to another, but
then there is the reading that should still occur. But perhaps its from the
buffer or memory :O haha.. just guessing..
It is possible that Windows permission system is forcing postgre to this
behaviour. I have had numerous permission issues, where the OS after ( in
postgreSql's case, was when I installed Tuning Wizard) took permission over
files and therefore was no longer accisseble to Posgre (that hassle took me
some time aswell)
If you have a good suggestion or theories please let me know. Otherwise, I
think I will move over to Ubuntu Server real soon! I can't stand this
anymore! I always have to consider that its Windows screwing things up for
me :) I think with linux I will at least be free of that :)
Thank you  / Jennifer

Re: [GENERAL] High I/O writes activity on disks causing images on browser to lag and not load

[Jennifer Trey]

On Wed, Jun 3, 2009 at 5:13 PM, Bill Moran  wrote:

> In response to Jennifer Trey :
> >
> > I have finally found out why I have had images that lag on the website.
> It
> > seems to be that postgre is doing allot of I/O activity and the images is
> > somehow suffering because of this.
> > The strange part about this is that it seems to be allot more disk writes
> > than disk reads. Even though 99.9% of my application traffic is DB read.
> > Looking up in tables and presenting.
> > Why is postgre doing all this writing? Should it not be I/O reads?
>
> Possible causes:
> * Table reads sometimes result in the updating of hint-bits, which equate
>  to disk writes.  This should not happen often, however, and not
> continually.
>  http://wiki.postgresql.org/wiki/Hint_Bits

Ok. But as you say those should not happen so often and the size is not that
great. The combined write size is 3,254,048,805 bytes which is around 3GB
..


>
> * If you are doing complex queries with sorting and don't have enough RAM,
>  PostgreSQL will have to create temporary files.  See the config variable
>  log_temp_files:
>  http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html

I have enough RAM :)
Java has been awarded 3GB and postgreSQL (Just went to see the value and
somehow it was set to 438MB! what!?... just changed it to 2800MB.. could
this be the reason??? 438 should be enough though.. (it was an error) I have
not that many tuples..)


>
> * You have other logging settings set too high and PostgreSQL is tying
>  up disk I/O writing log data.

Doesn't seem to be it.. just just checked a couple of things..


>
> * Your application is more write-intensive than you realize.
>
> > I would now like to move the DB activity to another disk if possible?
>
> That's only going to help if the machine has enough I/O capability to
> process both disks.  Adding more RAM might be a better (and cheaper)
> solution.

Enough I/O capability? Not sure what you mean... how can I find out?


>
>
> > Would
> > I have to re-install PostgreSQL from scratch?
>
> No.  You can just pick up the data directory and relocate it, then config
> PostgreSQL to look for the data directory in the new location, or create
> a symlink.

Yes.. I would like to try that(question is how I can do that, without stupid
Windows changes permission on my files!) if it turns out that it wasn't the
effective cache.. (good that I noticed at least :) )


>
>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>
 Grzegorz, yes, you are right. I would not recommend Windows Server to
anyone either. I am to weak on linux and thought that this was going to make
my life easier. Have had lots of issues. I might be trying Ubuntu Server out
real soon :)
I am going to try to restart and look at the read, writes for a while.
Perhaps it was the effective cache that was causing this (lets hope so :) )
Thanks for the tips and ideas. It feels like I am moving closer to resolving
this issue once and for all.
Sincerley / Jennifer

Re: [GENERAL] High I/O writes activity on disks causing images on browser to lag and not load

[Jennifer Trey]

I accidentally replied only to Grzegorz before so I don't think it turned up
on the mailing list. Here it is again. (Bill, I will try to answer your
email in a minute) ---
Thank you for you answer :)
2009/6/3 Grzegorz Jaśkiewicz 

> move postgresql to another disc if possible.


I have 2 disks thats the best Samsung has to offer :
http://www.samsung.com/global/business/hdd/productType.do?group=72#(HE103UJ,
at the bottom of page)
First splitted on
C:100GB ( Windows Server drive with postgreSQL installation )
E: 800GB with PostgreSQL data.
I: Pictures
Do you suggest that I move the  PostgreSQL data to another disk or all of
it? Including the installation?

> You have really slow
> discs if that happen,


By that you mean, that writes occur if one has to slow disks?


> or perhaps it does loads of seq scans, because
> of lack of indices.


Its possible that its the lack of indices, but they are pretty many. Most of
the fetching thats being done is through a primary key. However when using
search, there is a1-2 JOINS in each search. Does perhaps a JOIN force a
writing to the disk?

>
> All in all, it seems like your server is to low spec for what you are
> trying to do.


Not sure about that Grzegorz, its a pretty powerful computer :) and I don't
have that much data and traffic yet..
Normally, should the writes really be more than the reads?
Intel Quad Core 2.4 GHz
8GB Memory
Asus P5B Motherboard (Standard Version)
Windows Web Server 2008 x64 (trial)
PostgreSQL 32bit
Application running Java x64 and on Jetty Server 6
2 Samung Disks...
Thanks / Jennifer

[GENERAL] High I/O writes activity on disks causing images on browser to lag and not load

[Jennifer Trey]

Hi,
I have finally found out why I have had images that lag on the website. It
seems to be that postgre is doing allot of I/O activity and the images is
somehow suffering because of this.
The strange part about this is that it seems to be allot more disk writes
than disk reads. Even though 99.9% of my application traffic is DB read.
Looking up in tables and presenting.
Why is postgre doing all this writing? Should it not be I/O reads?
Is there something that I should know? Please enlighten me :)
I would now like to move the DB activity to another disk if possible? Would
I have to re-install PostgreSQL from scratch?
Thank you in advance / Jennifer

Re: [GENERAL] Now I am back, next thing. Final PGS tuning.

[Jennifer Trey]

Well, no.. I don't know that. But in a worst case scenario, where everything
is using max, there won't be 3.5 GB for the OS. But for the OS + Postgre
(combined) there will be  2.5 + 2.75 .. But it seems that there is no
greater danger in the effective cache, but a good setting would be nice :)
Is the effective cache only the one for the OS ? not for them combined ?

Sincerely / Jen


On Wed, Apr 8, 2009 at 7:44 PM, David Wilson wrote:

> On Wed, Apr 8, 2009 at 12:24 PM, Jennifer Trey 
> wrote:
>
> > I think I might have misunderstood the effective cache size. Its measured
> in
> > 8kB blocks. So the old number 449697 equals 3.5 GB, which is quite much.
> > Should I lower this? I had plans to use 2.75GB max. Can I put 2.75GB
> there?
> > Should I leave it?
>
> The effective cache size setting is merely letting postgres know how
> much caching it can expect the OS to be doing. If you know that the OS
> isn't going to have more than 2.75 GB available for caching DB files,
> then by all means reduce it. The setting by itself doesn't affect
> postgres memory usage at all, though.
>
> --
> - David T. Wilson
> david.t.wil...@gmail.com
>

Re: [GENERAL] Now I am back, next thing. Final PGS tuning.

[Jennifer Trey]

Scott, thank you.

I think I might have misunderstood the effective cache size. Its measured in
8kB blocks. So the old number 449697 equals 3.5 GB, which is quite much.
Should I lower this? I had plans to use 2.75GB max. Can I put 2.75GB there?
Should I leave it?


Also, Greg. Since I use Java, prepared statements are quite natural. And I
read this part on the guide which I understand you are part of :

http://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAX-PREPARED-TRANSACTIONS

Should I change this value? Not sure... :S

Worried about the locks... whats your though on this? Should I just leave it
alone?


Sincerely / Jennifer

Re: [GENERAL] Now I am back, next thing. Final PGS tuning.

[Jennifer Trey]

max_connections = 150 # A comprimise :)

effective_cache_size = 2048MB # Old value 439MB --> Even older : 128MB
#Is this too high?

maintenance_work_mem = 96MB # Old 16MB. Would 64MB be better? Updates
and therefore re-indexing of tuples happens quite frequently.

work_mem = 3MB
# Old was 1MB!? That is too low.
# Scott you mentioned an example with 1 GB. I guess this is the work
memory to work on per user query to sort, join and so on. I will be
doing those things quite often.
# After all, if I understand the concept correctly, it will only use
it if needs too, otherwise performance will take a hit.
# Scott, you say that I might need to change this later on when I have
several gigs of data. But will it hurt when I don't?
# I think 4-8MB should be enough and relativly safe to start with. I
am scared of going higher. But 1MB is low.

shared_buffer = 1024MB # Kept it

random_page_cost = 3 # I have pretty fast disks.

wal_buffers = 1024KB

Scott, you mentioned :

You can also use the pg_stat_all_indexes table to look at index scans
vs. tuples being read, this can sometimes hint at index 'bloat'. I
would also recommend pg_stattuple which has a pg_statindex function
for looking at index fragmentation.

>From where can I see these stats ? Is there any graphic tool?

Thanks all / Jennifer

Re: [GENERAL] Now I am back, next thing. Final PGS tuning.

[Jennifer Trey]

On Wed, Apr 8, 2009 at 5:38 PM, Massa, Harald Armin  wrote:

> Bill, Jennifer,
>
> > *shared_buffers = 1024 # min 128kB or max_connections*16kB  ## Also to
>> low.
>> > Right? I've got 3GB to work with!*
>>
>> Assuming that's equating to 1G, then the value is about right.  Common
>> best practice is to set this value to 1/4 - 1/3 of the memory available
>> for PostgreSQL.  You're saying you'll have ~3G for PG, so 1G is about
>> right to start with.
>>
>>
> "documenting" that for the wiki is still on my backlog; so, here:
>
> shared_buffers of PostgreSQL on Windows != shared_buffers of PostgreSQL on
> Unix
>
> My experience is that raising shared_memory on Windows above minimum+~20%
> is not helping performance; it's more effective to have that memory at
> Windows for caching. (at least up to server 2003)

I forgot to comment on this on Bill so its good you brought it up again.
This guide : http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
says under shared_buffers
**
*"If you have a system with 1GB or more of RAM, a reasonable starting value
for shared_buffers is 1/4 of the memory in your system."*
**
*in your system* ... that means I should count from 8GB right? Bill
mentioned countring from the 3GB. What would you say Harald, is perhaps 1.5
GB more suitable, a comprise for my giga byte greed :P haha!


>
>
> Harald
>
> --
> GHUM Harald Massa
> persuadere et programmare
> Harald Armin Massa
> Spielberger Straße 49
> 70435 Stuttgart
> 0173/9409607
> no fx, no carrier pigeon
> -
> LASIK good, steroids bad?
>
When it comes to the effective_cache I think this might be of great
importance for me since similar tuples will be fetched quite often by
different users. So caching could become quite important here. 439 MB is not
so much. The same guide as mentioned seconds ago says this :
*Setting effective_cache_size to 1/2 of total memory would be a normal
conservative setting, and 3/4 of memory is a more aggressive but still
reasonable amount.*
**
3/4 of total memory!? Its on 439 MB now. Could someone give me a better
offer?
Other things to consider ?
Sincerely / Jennifer

Re: [GENERAL] Now I am back, next thing. Final PGS tuning.

[Jennifer Trey]

On Wed, Apr 8, 2009 at 5:23 PM, Bill Moran  wrote:

> In response to Jennifer Trey :
> >
> > I have 8GB memory, Intel Quad Core 2.4Ghz with 8MB L2 cache. I am running
> > Windows Web Server 2008 x64 and will be running a Java (64 bit version)
> > application.
> >
> > I want to give the java app room for working on 2-3GB. The operating
> system
> > is currently consuming around 1GB but lets give it a little more room.
> Lets
> > give it a total of 2GB.
> >
> > That leaves 8-3-2 = 3 GB for PostgreSQL alone. Sounds good?
> >
> > Here is my config file :
> >
> > http://85.235.31.35/resources/postgresql.conf
> >
> > I see there is a setting
> >
> > *max_connections = 100*
> >
> > What does this do? Should I be looking at this as max similtaneous
> queries ?
> > is 100 really enough? I think I want to max this more.
> >
> > I am looking for a worst scenario around like 50-100 similitaneous user
> > clicks (per second?). But the querying might be around like 200 queries
> per
> > seocond, not really, but I want to be prepared. :)
>
> Depends on how long your "clicks" take to process.  If you're doing 100
> page views (clicks) /second and each view takes 2 seconds to process,
> you're
> tying up 200 connections on a continual basis.
>
> Unless you're using some sort of connection pooling ... I'm no Java expert,
> but doesn't Java have connection pooling built in?  If so, it becomes
> more difficult to estimate the # of simultaneous connections because each
> instance of a running script might share a connection with other scripts.
>
> In that case, you'll probably have to test to see what a good max is, as
> it's going to be difficult or impossible to estimate.
>
> In any event, 100 is probably a good starting point (based on my
> experience).  Note that if you find that you have to raise that value too
> high, (much over a few hundred) then you probably want to investigate some
> form of connection pooling, such as pgpool.
>
**
*Yes.* I think java uses these things. Looking at jConsole I can see that
there is these things (pools) going on.
I think I will increase this to 175. Just to be on the safe side...


> > Here is some other settings I am thinking about :
> >
> > *effective_cache_size = 449697*
>
> What version of Postgres?  In modern versions, you can specify MB, GB, etc.
>
I use 8.3.7 for windows.
I think this is kb since no MB is specified afterwards, which makes it 439
MB. The old value before tuning wizard was 128 MB.


>
>
> This value should be the memory that's left unused when everything is
> running (including Postgres).  It helps the planner estimate how much of
> the filesystem is cached in memory.  Based on the other numbers you've
> mentioned, this should probably be set to about 2G.
>
> > *maintenance_work_mem = 16384 *
> >
> > *work_mem = 1024  # I think this is kb. Way to low, right? What is a
> better
> > value?*
>
> I haven't noticed much value in tweaking this.  It only affects a few
> commands, such as vacuum and analyze.  Test to see if tweaking it speeds
> up vacuum without pushing the system into swap.

Yes, I will leave those as is then. But is it possible to set a time on when
the auto vacuum should kick in? Perhpas late at night would be better than
in the day.


>
>
> > *shared_buffers = 1024 # min 128kB or max_connections*16kB  ## Also to
> low.
> > Right? I've got 3GB to work with!*
>
> Assuming that's equating to 1G, then the value is about right.  Common
> best practice is to set this value to 1/4 - 1/3 of the memory available
> for PostgreSQL.  You're saying you'll have ~3G for PG, so 1G is about
> right to start with.

Yes, about 3GB but now I started to think about the OS cache aswell, which I
believe will be involved so perhpas put his at 2.75 GB with at java 2.75GB
and 2.5GB on the OS.


>
>
> Once the system is up and running, you can install pg_buffercache to
> monitor usage and help tune it.
>
> > *wal_buffers = 256 # Also kB...*
> >
> > Please give your thoughts. I was also wondering about the Vacuum, force
> > reindex and stuff. Are those things good to run once in a while? Force
> > sounds a little brutal though!
>
> Turn on autovacuum.  I've found it's the best way to go in 99% of installs
> (the corner cases being servers that have _very_ predictable workloads ...
> in which case explicit, scheduled vacuums are better).

I will :) But as I mentioned earlier. Is there a way to set a more suited
time for this happen (autovacuum)?


>
>
> REINDEXing is an occasional top

[GENERAL] Now I am back, next thing. Final PGS tuning.

[Jennifer Trey]

Ok,

I have left the previous thread. After changing the last permissions, even
though it said Access Denied, suddenly PostgreSQL started to work again. I
will not dig any further to the strangeness.

I copied the content of the.conf from tuning wizard and restarted. Still
working!

I want to say thanks to several people on that thread :) Thank you!

I would like to further tune the tuning wizards recommendations though. I
think it put itself on the lower scale.

I have 8GB memory, Intel Quad Core 2.4Ghz with 8MB L2 cache. I am running
Windows Web Server 2008 x64 and will be running a Java (64 bit version)
application.

I want to give the java app room for working on 2-3GB. The operating system
is currently consuming around 1GB but lets give it a little more room. Lets
give it a total of 2GB.

That leaves 8-3-2 = 3 GB for PostgreSQL alone. Sounds good?

Here is my config file :

http://85.235.31.35/resources/postgresql.conf

I see there is a setting

*max_connections = 100*

What does this do? Should I be looking at this as max similtaneous queries ?
is 100 really enough? I think I want to max this more.

I am looking for a worst scenario around like 50-100 similitaneous user
clicks (per second?). But the querying might be around like 200 queries per
seocond, not really, but I want to be prepared. :)

I would appreciate if could have a discussion on these topics. On whats
important and whats not.

Here is some other settings I am thinking about :

*effective_cache_size = 449697*

is this kilo bytes ? Is this a good value?

*maintenance_work_mem = 16384 *

*work_mem = 1024  # I think this is kb. Way to low, right? What is a better
value?*

*shared_buffers = 1024 # min 128kB or max_connections*16kB  ## Also to low.
Right? I've got 3GB to work with!*

*wal_buffers = 256 # Also kB...*

Please give your thoughts. I was also wondering about the Vacuum, force
reindex and stuff. Are those things good to run once in a while? Force
sounds a little brutal though!

Something else I should consider?

/ Jennifer

Re: [GENERAL] PGS Tuning Wizard destroys my login

[Jennifer Trey]

After doing allot of clicks with the permissions. It automagically fixed
itself. Fantastic!

The root problem was Windows itself!

I would recommend the EnterpriseDB folks to consider this though, because in
someway its affecting your software.

Thank you all. Now join my new thread for real discussions, instead of
problem solving :)


On Wed, Apr 8, 2009 at 4:31 PM, Massa, Harald Armin  wrote:

> Jennifer,
>
> 1.) you should state the exact version of your operating system
>>
>> *Windows Web Server 2008 x64*
>>
>>>
> 2.) you should state the exact PostgreSQL Version
>>
>> *The latest available, installed this : postgresql-8.3.7-1-windows.exe*
>>
>>
>>> 3.) exactly describe your Windows Authorization. Is there some Domain /
>>> ActiveDirectory going on? Do you really have MasterChiefAdministrator
>>> rights?
>>
>> *As far as I know, *I have one user and that is the Administrator. Not
>> sure if that is the commander in chief but obviously not, because he is
>> screwing with me allot.
>>
>
> You have digged down to the root of the problem:
>
> FATAL: could not open configuration file
>> "E:/PostgreSQLDATA/postgresql.conf": Permission denied
>>
>
> so to sum it up: you ran the Tuning Wizard, which gave you a new
> postgresql.conf. Tuning wizard, running as your user, copied this
> postgresql.conf OR you put back your back-upped postgresql.conf, also as
> "Administrator"
>
> The permissions of that directory seem to be very screwed :)
>
>>
>> 5.) Check for the logfiles of PostgreSQL within the pg_log directory.
>> *I cannot enter that folder! *Crazy! I command you to let me in!
>> No, I try to change the settings and permissions (there is like tousand
>> entries to those things), i give full access but no difference! I don't get
>> it! Make me the super chief damn it!
>>
>
> A quick google for NTFS permissions points to a lot of second source
> manuals; that is a hint that it is complicated enough for people to make
> money to write books about it. As I am always working WITH the system
> adminstrators when implementing PostgreSQL on Win32, I have no MCP or MSC or
> whatever that title for knowing about Windows-Permissions is.
>
> So my hints to solve your problem:
>
> a) check if there is some higher authority - as "this computer is member of
> a domain / Active Directory", and rights for those file systems are managed
> elsewhere out of your control
>
> b) read up on Windows NTFS File Permissions on Server 2008. Do you have to
> use another account? Do you have to elevate privileges in any way?
>
> c) after learning enough about windows File Permissions, make sure that the
> user "Postgres" -> PostgreSQL Service Account has the appropriate
> permissions to access all the files within the PostgreSQL Data directory.
>
> *You seem to be on something here. Keep going! :)*
>>
>
> Now it's you and windows. Good luck!
>
> Harald
>
> --
> GHUM Harald Massa
> persuadere et programmare
> Harald Armin Massa
> Spielberger Straße 49
> 70435 Stuttgart
> 0173/9409607
> no fx, no carrier pigeon
> -
> LASIK good, steroids bad?
>

Re: [GENERAL] PGS Tuning Wizard destroys my login

[Jennifer Trey]

It seems like my long message didnt make it :S Here it is again. (Perhaps it
was because of the attached pic, I will leave it out this time)--

On Wed, Apr 8, 2009 at 3:17 PM, Massa, Harald Armin  wrote:

> Jennifer,
>
> obviously you are on windows. And the problem you are describing seems to
> be:
>
> "after running the tuning wizzard, the PostgreSQL Service does not start
> any more"
>
> AT the same time you are describing some problem of
>
> "you are not able to delete some Files / Directories connected with
> PostgreSQL-Databases, and you are not able to change ownership / take
> ownership of those files."
>
> THAT gives reason to the assumption that you are using something Vistaish
> or Windows-7ish.
>
> AND that the right structure of your file system around PostgreSQLs Files
> is rather screwed; not clear, by what or who.
>
> SO, to help you to get to some solution:
>
> 1.) you should state the exact version of your operating system

**

*Windows Web Server 2008 x64*
**

>
> 2.) you should state the exact PostgreSQL Version

**

*The latest available, installed this : postgresql-8.3.7-1-windows.exe*


>
> 3.) exactly describe your Windows Authorization. Is there some Domain /
> ActiveDirectory going on? Do you really have MasterChiefAdministrator
> rights?

**

*As far as I know, *I have one user and that is the Administrator. Not sure
if that is the commander in chief but obviously not, because he is screwing
with me allot.

running *lusrmgr.msc * I can see under user that there is a username :

postgre

with description : PostgreSQL service account

There is allot of changes that can be made on the properties here.


>
>
> and to continue your diagnostics:
>
> 4.) Check the application event log of windows. "eventvwr" from the Command
> line, or dig through the Administration Panels

**

*Ok, I did that. *The export generated uglyness so I will be pasting those I
found relevant and there was a few!

By Date ASC (Oldest first, and only errors)

FATAL: could not open configuration file
"E:/PostgreSQLDATA/postgresql.conf": Permission denied
FATAL: could not open configuration file
"E:/PostgreSQLDATA/postgresql.conf": Permission denied
FATAL: could not open configuration file
"E:/PostgreSQLDATA/postgresql.conf": Permission denied  and some
more of these 
Timed out waiting for server startup
pg_ctl: PID file "E:/PostgreSQLDATA/postmaster.pid" does not exist
Is server running?
pg_ctl: PID file "E:/PostgreSQLDATA/postmaster.pid" does not exist

5.) Check for the logfiles of PostgreSQL within the pg_log directory.
**

*I cannot enter that folder! *Crazy! I command you to let me in!
No, I try to change the settings and permissions (there is like tousand
entries to those things), i give full access but no difference! I don't get
it! Make me the super chief damn it! Freaking Windows! Just crap and I am
stuck with it.
Most likely reason is that postgres.exe is not allowed to open some file or
port; to write to a log file or read it's databases or even read its
configuration file postgresql.conf
**

*I enter the properties.* postgres is among the users. The only checked (for
the entire folder) is 'Special Permissions', nothing else. (Oh yeah, postgre
is the owner). Changing to full access says that Access denied. Please look
at the JPEG I attached to this email, its marked with the steps.

So, next of this: check the file-ownerships and rights of your PostgreSQL
Data directory. Especially, is the user owning the postgresql service
allowed to read an change all the files within the PostgreSQL Data
directory? Especially the postgresql.conf?

*You seem to be on something here. Keep going! :)*

Best luck,

Harald

Sincerely / Jennifer

Re: [GENERAL] PGS Tuning Wizard destroys my login

[Jennifer Trey]

I just tried to change the permission on all users, admin to user to
postgres. Access Denied for all! The machines have taken over!

Re: [GENERAL] PGS Tuning Wizard destroys my login

[Jennifer Trey]

Hehe, I am not saying that you are untruthful, but obviously there is more
to this issue than just the .conf file.

Because when returning to the old conf the problem is still there and I am
also certain that this problem was not there before I just installed the
tuning wizard.

Would you at least agree that there is something fishy going on, and that
its related to the tuning wizard? Things seldom work as expected

I just un-installed the tuning-wizard (I still have the generated config
file, so I dont have to repeat this step once more), restarted, the problem
is still there. The problem does not seem to be login related. But more to
that the server doesn't start up.

Running the program, 'Start Server', it just sits there and does nothing.
After long time it says:

The service did not report an error.

More help is available by typing NET HELPMSG 3534

Not sure what is going on. Server refuses to start it seems. Please help me
debug this problem :)

Sincerely / Jen

 On Wed, Apr 8, 2009 at 2:31 PM, Dave Page  wrote:
>
> On Wed, Apr 8, 2009 at 12:16 PM, Jennifer Trey 
> wrote: > Look, I am telling now what I did. > > I don't want to hear
> claims about how its not the tuning wizards fault. It > is! > > And it
> does more than the postgresql.conf because thats not true.
> I'm sorry to hear you think I'm being untruthful. The source code for the
> tuning wizard is at
> http://www.enterprisedb.com/openDownloads.do?productId=417&redirectReason=true&productVersion=otherDownload-
>  you can see exactly what it modifies there. --Dave PageEnterpriseDB UK:
> http://www.enterprisedb.com
>

Re: [GENERAL] PGS Tuning Wizard destroys my login

[Jennifer Trey]

Look, I am telling now what I did.

I don't want to hear claims about how its not the tuning wizards fault. It
is!

And it does more than the postgresql.conf because thats not true.
Just until today I have been able to login and logout, using pgAdmin to my
DB.

Now, after considering what you said, I tried once more! And I am getting
tired of this whole process.

I installed the tuning wizard. I got myself a new postgresql.conf and it
renamed the old one which I noted.

I opened my pgAdmin, logged in, worked fine (I guess the changes require
some kind of restart). I restarted my computer and we are back with the old
pattern where I cannot login, not with pgAdmin, not with my java-app and not
with sql shell :
Username [postgres]:
psql: could not connect to server: Connection refused (0x274D/10061)
  Is the server running on host "localhost" and accepting
  TCP/IP connections on port 5432?

Press any key to continue . . .

looking at the pgadmin.log i see this :
2009-04-08 02:03:01 ERROR : server closed the connection unexpectedly
 This probably means the server terminated abnormally
 before or while processing the request.

I replaced the new postgresql.conf with the old one. Restarted my computer
again. And still the problems are there. Last time I had to uninstall
postgre (the time before I reformatted the computer) .. so I would like this
to work this time around.

About taking ownership, its not working for me either, (I am trying to
delete the data folder that was left from the previous installation) but it
refuses to let me delete two folders (base, pg_log) but I guess thats more a
windows server issue.
Lets concentrate on the first problem.

How can I fix it ?
/ Jennifer

[GENERAL] PGS Tuning Wizard destroys my login

[Jennifer Trey]

Hi,
What does the Tuning Wizard do? The one you can run on PGS ? Does it just
touch up the postgresql.conf ? Does it play with other stuff as well?

I am asking because, every time I run the wizard I cannot login with the
password I choose when I installed PGS. What is going on? Replacing the
generated postgresql.conf with the old to "go back" does not help either.

First time I reformatted my whole disk, and tried install PGS and ran Tuning
Wizard again. Same thing happened!

This time I just uninstalled, and installed PGS again without the tuning
wizard and I haven't had any problems since, but I need the tuning!

The un-installation left some of the Data files behind, which i cannot
delete, I have not access to them, but I am logged in as Administrator.

What is going on!?

Jennifer

Re: [GENERAL] Connect to server PG from laptop java app

[Jennifer Trey]

no pg_hba entry for host 85.235 user postgres, database xxx .. ssl off
...
?? I guess that means that I need to set up pg then..

how is the question now :)

/ Jen

On Sun, Apr 5, 2009 at 8:35 PM, Raymond O'Donnell  wrote:

> On 05/04/2009 19:28, Jennifer Trey wrote:
> > Hi, I am trying to create my DB schema on the server through my
> development
> > laptop.
> > I have installed Web Server 2008 and PostgreSQL. They are both running.
> For
> > some reason its not working. Do I need to open some firewall ports?
> > PostgreSQL runs on 5432. ( I think I did that though). Do I need to
> > configure PG somehow as well?
>
> What does it say in the postgres logs?
>
> Ray.
>
> --
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> r...@iol.ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> --
>

[GENERAL] Connect to server PG from laptop java app

[Jennifer Trey]

Hi, I am trying to create my DB schema on the server through my development
laptop.
I have installed Web Server 2008 and PostgreSQL. They are both running. For
some reason its not working. Do I need to open some firewall ports?
PostgreSQL runs on 5432. ( I think I did that though). Do I need to
configure PG somehow as well?

Thanks / Jennifer

Re: [GENERAL] Installed PG with pgAdmin, some days later, now my password don't work!

[Jennifer Trey]

By the way, yesterday I used the TuningWizard too, could it have changed
some of these things? It does create a new config file.. is it possible?



On Wed, Apr 1, 2009 at 10:50 PM, Jennifer Trey wrote:

>
> Sorry, for the confusion.. just want to make something I wrote more clear:
>
> I tried to add a server and it required the use of a password and not to
> add a password.. upon the creation and connection, it says that it failed.
> The Server gets created but I cannot log in to it, even though its new...
>
> and also, I found it (the password) but I cannot log in with it anyway...
>
> / Jennifer
> -- Forwarded message --
> From: Jennifer Trey 
> Date: Wed, Apr 1, 2009 at 10:47 PM
> Subject: [GENERAL] Installed PG with pgAdmin, some days later, now my
> password don't work!
> To: pgsql-general@postgresql.org
>
>
> Yes, I found it.. but I cannot log in?
>
> Is there any simple way just to scratch the server and add a new one? thru
> pgAdmin please?
>
> I tried to "Add Server" but it requires a password too!? and thats not
> working either.. why does a new server require a new password?
>
> To Raymonds last,
>
> I am using pgAdmin, and that file I accidentally attached instead of an
> image I was planning to attach..
>
> On Wed, Apr 1, 2009 at 10:40 PM, Raymond O'Donnell  wrote:
>
>> On 01/04/2009 20:16, Jennifer Trey wrote:
>>
>> > Most importantly, where can I find the password if it was stored?
>>
>> It's in a file called pgpass.conf - on Windows, this is stored in the
>> Application Data\postgresql directory under your profile.
>>
>> Ray.
>>
>> --
>> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
>> r...@iol.ie
>> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
>> --
>>
>
>
>
>

Fwd: [GENERAL] Installed PG with pgAdmin, some days later, now my password don't work!

[Jennifer Trey]

Sorry, for the confusion.. just want to make something I wrote more clear:

I tried to add a server and it required the use of a password and not to add
a password.. upon the creation and connection, it says that it failed. The
Server gets created but I cannot log in to it, even though its new...

and also, I found it (the password) but I cannot log in with it anyway...

/ Jennifer

-- Forwarded message --
From: Jennifer Trey 
Date: Wed, Apr 1, 2009 at 10:47 PM
Subject: [GENERAL] Installed PG with pgAdmin, some days later, now my
password don't work!
To: pgsql-general@postgresql.org


Yes, I found it.. but I cannot log in?

Is there any simple way just to scratch the server and add a new one? thru
pgAdmin please?

I tried to "Add Server" but it requires a password too!? and thats not
working either.. why does a new server require a new password?

To Raymonds last,

I am using pgAdmin, and that file I accidentally attached instead of an
image I was planning to attach..

On Wed, Apr 1, 2009 at 10:40 PM, Raymond O'Donnell  wrote:

> On 01/04/2009 20:16, Jennifer Trey wrote:
>
> > Most importantly, where can I find the password if it was stored?
>
> It's in a file called pgpass.conf - on Windows, this is stored in the
> Application Data\postgresql directory under your profile.
>
> Ray.
>
> --
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> r...@iol.ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> --
>

[GENERAL] Installed PG with pgAdmin, some days later, now my password don't work!

[Jennifer Trey]

Yes, I found it.. but I cannot log in?

Is there any simple way just to scratch the server and add a new one? thru
pgAdmin please?

I tried to "Add Server" but it requires a password too!? and thats not
working either.. why does a new server require a new password?

To Raymonds last,

I am using pgAdmin, and that file I accidentally attached instead of an
image I was planning to attach..


On Wed, Apr 1, 2009 at 10:40 PM, Raymond O'Donnell  wrote:

> On 01/04/2009 20:16, Jennifer Trey wrote:
>
> > Most importantly, where can I find the password if it was stored?
>
> It's in a file called pgpass.conf - on Windows, this is stored in the
> Application Data\postgresql directory under your profile.
>
> Ray.
>
> --
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> r...@iol.ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> --
>

[GENERAL] Re: Installed PG with pgAdmin, some days later, now my password don't work!

[Jennifer Trey]

It is still stored..and I got hold of it.. I used it and it is not workin!?

What could be the problem? How should I log in?

See the picture I attached...

On Wed, Apr 1, 2009 at 10:16 PM, Jennifer Trey wrote:

> I even wrote down the password when I installed the DB and now it doesn't
> work!
>
> I have logged in once to the DB through pgAdmin, and choose to store the
> password and it said that it was stored in plain text.. where can I find it?
> in what file?? I even created a DB that I haven't used yet so I am certain I
> have been in there.. what has happend?
>
> Most importantly, where can I find the password if it was stored?
>
> / Jennifer
>


pgadmin.log
Description: Binary data

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

[GENERAL] Installed PG with pgAdmin, some days later, now my password don't work!

[Jennifer Trey]

I even wrote down the password when I installed the DB and now it doesn't
work!

I have logged in once to the DB through pgAdmin, and choose to store the
password and it said that it was stored in plain text.. where can I find it?
in what file?? I even created a DB that I haven't used yet so I am certain I
have been in there.. what has happend?

Most importantly, where can I find the password if it was stored?

/ Jennifer

[GENERAL] Optimization Wizard

[Jennifer Trey]

Hi,
During the development of my application I once, somehow through an
installed postgresql software got me to a page where I could find
information on how to tune my db.
I can't seem to find to that link again. I think it was to a page within the
EnterpriseDB but that page seems to have significantly changed.

Could someone please help me with this issue?

/ Jennifer

[GENERAL] Point me to latest JDBC for pgSQL (plus) standard server?

[Jennifer Trey]

Point me to latest JDBC for pgSQL (plus) standard server?

Re: [GENERAL] PostgreSql with or without Plus?

[Jennifer Trey]

Hmm, cant find it.. but.. if I install the regular (not plus), could I later
upgrade easily to other versions ? Is that possible? In that case I could
just start with the one I already are familiar with.
Jen

On Mon, Mar 30, 2009 at 9:03 PM, Dave Page  wrote:

> On Mon, Mar 30, 2009 at 7:36 PM, Jennifer Trey 
> wrote:
> > I am sorry, but it seems that I replied to only one of you instead of to
> the
> > mailing list.
> > I am just about to install a version on my "Server computer" (Windows web
> > server 2008 x64) and I am stuck on what I should go with.
> > I have been developing on the standard postgreSQL but like that the
> > EnterpriseDB webpage claims that "Standard Plus" is faster.
>
> Where does it claim that? Standard Server is pure community
> PostgreSQL, with a bunch of extra stuff in the installer.
>
>
> --
> Dave Page
> EnterpriseDB UK:   http://www.enterprisedb.com
>

Re: [GENERAL] PostgreSql with or without Plus?

[Jennifer Trey]

I am sorry, but it seems that I replied to only one of you instead of to the
mailing list.
I am just about to install a version on my "Server computer" (Windows web
server 2008 x64) and I am stuck on what I should go with.

I have been developing on the standard postgreSQL but like that the
EnterpriseDB webpage claims that "Standard Plus" is faster.

Is there something that will work differently, more complex with that
version? Not free, or similar? Something I should know? Why would they give
out a better version, if both are free??? There is something fishy going on
:P

Thanks / Jennifer

On Wed, Mar 18, 2009 at 4:49 PM, Joshua D. Drake wrote:

> On Wed, 2009-03-18 at 11:19 +0530, Ashish Karalkar wrote:
>
> > Postgres Plus does support  Linux and it includes all the PostgreSQL
> > features as well as have many other additional useful features.One of
> > which is its dyna tune module.
>
> Yes it does, but it doesn't use native packages (as far as I know).
> Meaning, it doesn't use RPM or DEB.
>
> Joshua D. Drake
>
> --
> PostgreSQL - XMPP: jdr...@jabber.postgresql.org
>   Consulting, Development, Support, Training
>   503-667-4564 - http://www.commandprompt.com/
>   The PostgreSQL Company, serving since 1997
>
>

[GENERAL] PostgreSql with or without Plus?

[Jennifer Trey]

Which one should one install? Is there any difference?

They are both free right? And both has fulltext support, right?

Jen

[GENERAL] How do I turn on query logger?

[Jennifer Trey]

I am new to postgreSQL and I am running queries from JAVA and would like to
see what queries are being run.

How can I achieve that ? I am running PostgreSQL 8.3 on Windows and like
using pgAdmin to do most things.

Jennifer Trey