Re: [GENERAL] SYSCONFDIR, initdb and postgresql.conf
On 2010-11-21, Tom Lane t...@sss.pgh.pa.us wrote: SYSCONFDIR is only used for global configuration files, like the default psqlrc or pg_service.conf. OK, so it doesn't regard postgresql.conf and friends as conf files in that sense. It would be pretty inappropriate to put postgresql.conf there because postgresql.conf is a per-cluster configuration file. Debian does it with a hierarchy under /etc/postgres that reflects the versions and clusters installed. E.g. /etc/postgres/8.4/main holds the cluster-conf files for the 'main' cluster running 8.4. Having said that, you don't have to put postgresql.conf in the data directory if you don't want to. Just move it to where you do want it (along with the other cluster config files) and add an entry to it to point to the actual data directory. Beware that this arrangement isn't supported as fully as the default --- in particular, I think pg_ctl will have some trouble with it. It wants a '-o' to tell postgres where its config is. Debian uses a system of ingenious wrapper scripts that automatically set it. My poor man's version seems to be working in my /etc/rc.local, su -l _postgresql -c nohup /usr/local/bin/pg_ctl start \ -D /var/postgresql/9.0/main -l /var/postgresql/logfile \ -o '-D /var/postgresql/9.0/main' \ -o '-c config_file=/etc/postgresql/9.0/main/postgresql.conf' \ /dev/null regards, tom lane Thanks -- KM -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SYSCONFDIR, initdb and postgresql.conf
On an OpenBSD machine I just compiled and installed 9.0.1. The ./configure arguments included '--sysconfdir=/etc'. Running 'pg_config --sysconfdir' returns '/etc/postgresql'. The cluster is running and I can create a database and connect to it. However, initdb put the config files in the directory named by the --pgdata option. There is no directory /etc/postgresql. Should I expect this? How do I persuade postgres to put its config under /etc? Thanks -- KM -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Listen and do something daemon
Does this program already exist? $ listen-and-do --database=foo --listen-for=somenotification \ --then-do=some-script It will daemonize itself, issue a 'LISTEN somenotification' on the database foo, and on each NOTIFY will run some-script. Thanks -- KM -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Timestamp with time zone: why not?
Would I be right in thinking that, in general, a column to hold timestamp values is best created with type 'TIMESTAMP WITH TIME ZONE' and not 'TIMESTAMP' nor 'TIMESTAMP WITHOUT TIME ZONE'? To put it another way, for what reasons might the 'TIMESTAMP' type be preferred to 'TIMESTAMP WITH TIME ZONE'? -- KM ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PG compilation
On Fri, Jan 12, 2007 at 11:50:19AM +0100, Albe Laurenz wrote: gmake[3]: Entering directory `/home/km/postgresql8.2.1/postgresql-8.2.1/src/pl/plpython' gcc -fPIC -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g -fpic -shared -Wl,-soname,libplpython.so.0 plpython.o -L/usr/local/lib/python2.5/config -L../../../src/port -lpython2.5 -lpthread -ldl -lutil -lm -Wl,-rpath,'/usr/local/lib/python2.5/config' -o libplpython.so.0.0 /usr/bin/ld: /usr/local/lib/python2.5/config/libpython2.5.a(abstract.o): relocation R_X86_64_32 against `a local symbol' can not be used when making a shared object; recompile with -fPIC /usr/local/lib/python2.5/config/libpython2.5.a: could not read symbols: Bad value collect2: ld returned 1 exit status gmake[3]: *** [libplpython.so.0.0] Error 1 gmake[3]: Leaving directory `/home/km/postgresql8.2.1/postgresql-8.2.1/src/pl/plpython' I am on a x86_64 platform. any ideas whats going on here ? Not really. You should CC the list, because somebody there may know. You can try the following: ar -t /usr/local/lib/python2.5/config/libpython2.5.a abstract.o file abstract.o ya extracted the object file. What does the file command tell you? ya it says : abstract.o: ELF 64-bit LSB relocatable, AMD x86-64, version 1 (SYSV), not stripped Did you build Python yourself or did you install a package? I had installed python2.5 myself from source package. regards, KM ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PG compilation
I would like to know if there is a way to pass an argument to ./configure to consider compiling with a specific python version ? coz i have many python versions in the system .I presume that configure would check for the /usr/bin/python alone, but what if i want /usr/bin/python2.5 to be compiled in ? Have a look at ./configure --help ya had looked at '--with-python' option but that is where my question arises - it looks at default python (/usr/bin/python) and not /usr/local/bin/python2.5 which i need for PL/Python functionality in PG 8.2.1 one solution is to soft link the /usr/bin/python to /usr/local/bin/python2.5 but i donot want to change the default python on the system as some other programs depend on it. so i am looking for a flexibility in configuring PG that it created python modules with python2.5 only. any ideas ? regards, KM ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgres Replication
On Tue, Jan 09, 2007 at 12:17:20PM -0600, Scott Marlowe wrote: Has anybody researched on this that can point me in the right direction? You could use possibly use pgpool as long as its caveats aren't a show stopper (can't insert with random, individual inserts with things like now() might be a little different, insert order might not be the same on both machines, etc... I haven't used daffodil, but have heard of it. There's also c-jdbc and a few others. what abt pgcluster ? how does it fare with SlonyI ? regards, KM ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] PG compilation
Hi, I would like to know if there is a way to pass an argument to ./configure to consider compiling with a specific python version ? coz i have many python versions in the system .I presume that configure would check for the /usr/bin/python alone, but what if i want /usr/bin/python2.5 to be compiled in ? regards , KM ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] dynamic SQL - variable substitution in plpgsql
Hi all, i could not do variable substitution in plpgsql procedure. The variable names are taken as it is but not substituted in the SQL query. what could be the problem ? code looks like this: -- CREATE OR REPLACE FUNCTION test(a text) RETURNS SETOF RECORD AS $$ DECLARE a text; b text; BEGIN IF a = 'odd' THEN b := 10; c := 30; ELIF a = 'even' THEN b := 20; c := 40; END IF; FOR result IN SELECT x,y,z FROM mydata WHERE x = a AND y b AND z c LOOP RETURN NEXT result; END LOOP; END; $$ language 'pgplsql'; - tia KM ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] running external programs
Hi all, Is it possible in a PLSQL function to call an external program/script residing at /usr/bin and return the result ? are there any workarounds for this sort of a problem ? regards, KM ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] running external programs
Is it possible in a PLSQL function to call an external program/script residing at /usr/bin and return the result ? No, because plsql is a trusted language. You can't run external commands from such a language. Is that a deciding criteria for a language to be flagged trusted or not ? KM ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] postgresql and reiserfs
Hi all, Is anyone up with database features in reiserfs (reiser4) with postgresql 8.x? regards, KM ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] plpython
Hi all, Can someone hint on resources for using plpython for writing stored procedures ? I have gone thru official docs for 8.1.5 for plpythonu but its not in detail/with examples. When is plpython going to be considered safe ? any targeted version ? regards, KM ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] plpython
Sure. But it depends a lot on what you're willing to do. The docs have the details. This one I did just to learn it: CREATE FUNCTION f_v_fechamento(p_cliente_id integer, p_data date, p_pago boolean, OUT retorno record) RETURNS record AS $$ p_cliente_id = args[0] p_data = args[1] p_pago = args[2] w_total = 0 w_amostra = 0 w_final_do_mes = plpy.execute(SELECT f_v_final_do_mes(%s::date) % p_data) w_inicio_do_mes = plpy.execute(SELECT f_v_inicio_mes(%s::date) % p_data) retorno = dict() retorno['w_inicio_do_mes'] = w_inicio_do_mes retorno['w_final_do_mes'] = w_final_do_mes return retorno $$ LANGUAGE plpythonu STABLE; It can be rewritten in a better way but was the handier example I had here that had queries and used a bit more of PostgreSQL 8.1 :-) Thanks for that snippet. why is that 'STABLE' at the end of the function ? i am stuck at createlang for plpythonu! with postgres user error reads: $createlang plpythonu template1; createlang: language installation failed: ERROR: could not load library /usr/local/pgsql/lib/plpython.so: /usr/local/pgsql/lib/plpython.so: undefined symbol: Py_InitModule4_64 i am on a x86_64 linux box. couldnt comprehend the error. plpython.so is in /usr/local/pgsql/lib whats wrong? regards, KM ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] schema 8.1.5
Hi all, Have a general doubt abt default schema public in postgresql 8.1.5: i would like to know if for every database a valid user creates, postgreSQL by default creates a public schema which is optional ? Also is it possible to know which schema i am currently in ? how do i set a user account to default to a predefined schema ? i have tried : SET search_path TO myschema; but thats temprary setting i suppose. so that when the user logis in and accesses a database via psql he should be able to land into his schema. how do i do that ? any tips ? regards, KM ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] postgresql /var fill
Hi all, I have installed postgresql(8.1.4) data dir on a partition (/data) which rests on a separate disk from OS disk. The install dir is default (usr/local/pgsql). Now when i use use pgbench with scaling factor of 1000 it creates a whooping 15 GB database. but i see /var partition used space increases considerably - why is this happening ? i expected more free disk space to be used in /data ! does it mean that the actual database created lies in /var instead of /data ? how do i fix it ? regards, KM ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresql /var fill
On Fri, Oct 06, 2006 at 09:38:46AM -0400, Ray Stell wrote: ls -l / maybe /data is a symlink? no /data is the label for separate SCSI disk. no symlinks !!! /var resides on /dev/sda, and /data in /dev/sdb regards, KM ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresql /var fill
On Fri, Oct 06, 2006 at 03:57:47PM +0200, Csaba Nagy wrote: /var resides on /dev/sda, and /data in /dev/sdb I bet you're running a default installation of postgres which has it's data in /var. Check your real data directory by running 'ps auxww|grep post', and see what's after the '-D' parameter... and then when you figure out that the startup script is using the system default and not what you've initdb-ed, fix your start script ;-) nope! i have purposefully deselected postgres 7.4 installation at OS install. then downloaded postgresql sources of 8.1.4 and installed it in /usr/local/pgsql with data dir as /data/pgdata. later , i have set PGDATA to /data/pgdata in startup script from contrib/scripts of sources and placed it in /etc/init.d to be sure, i re-checked with rpm -qa|postgres too which didnt give me any package listing. ps auxww |grep postgresql gives /data/pgdata as the data dir. whats happening ? regards, KM ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql /var fill
Have you tried looking in /var to see what's there? find /var -type f -msize +k ya looking for files bigger than KB showed only: /var/lib/rpm/RpmPackages /var/lib/rpm/Filemd5s regards, KM ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] shared_buffer setting
Hi all, Iam using postgresql 8.1.4 with 8GB physical RAM. OS kernel max shared memory usage is set to( /proc/sys/kernel/shmmax) 33554432 dont know if this number is in bytes or bits now how do i set my shared_buffer setting in postgresql.conf such that ican use max shared memory setting of the kernel. also default shared_buffer setting is 1000 - its not clear if i have to multiply this number with 8KB to be = max shared memory value or it is 1000 bytes or bits. can i set the max shared memory value to use atleast half of my physicl RAM available ? any idea ? also i would like to know how is the performance hit when we change shared_buffer value in general ? regards, KM ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] postgresql.conf shared buffers
Hi all, - What does the shared_buffers setting do ? - Does it mean that that the postgres cannot access most of the physical RAM but limited to the memory setting (shared_buffers) specified ? - How do i relate and set max_connections and shared_buffers? - Is there a thumb rule to determine shared_buffers from max connections ? - I see , by default max_connections set to 100 and shared_buffers to 1000 - does 1000 mean 1000 bytes or KB ? - Also postgres will not start if the shared_buffers value exceeds the kernel setting of SHMMAX. do i need to recompile the kernel to increase this value ? or is there any workaround ? tia, regards, KM ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] 8.1.4 compile problem
Hi all, i am compiling postgresql 8.1.4 on AMB x86_64 platform. configure runs fine but shows output (snippet shown) as follows: ... checking for int8... no checking for uint8... no checking for int64... no checking for uint64... no ... my gcc -v gives me: Reading specs from /usr/lib/gcc/x86_64-redhat-linux/3.4.4/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-java-awt=gtk --host=x86_64-redhat-linux Thread model: posix gcc version 3.4.4 20050721 (Red Hat 3.4.4-2) how can i add int8,uint8,int64 and uint64 functionality into postgres ? are there any special flags that i am missing ? tia, regards, KM ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 8.1.4 compile problem
On Fri, Sep 29, 2006 at 11:48:09AM +0200, Thomas Pundt wrote: I'd say, this is expected output from configure. Configure is just a mechanism to help constructing an abstraction for different types of OS (i.e. things like can we use this C type? Do we need to use another type instead?). [...] | how can i add int8,uint8,int64 and uint64 functionality into postgres ? are | there any special flags that i am missing ? The smallint, integer and bigint types are available regardless of that output from configure. I was in a doubt if int64,int8,uint64 and uint8 are not supported after setting up the db. thanks for clarifying my doubt. will proceed with gmake :) regards, KM -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] benchmark suite
Hi all, Is there any good benchmark suite for testing postgresql performance? i tried to work with pgbench but found pgbench source (v 8.0.x and 7.4.x) but couldnt compile with gcc ($gcc -o pgbench pgbench.8.0.x.c) postgres 8.1.4 is on AMDx86_64 platform. regards, KM ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] benchmark suite
On Fri, Sep 29, 2006 at 07:27:49PM +0530, km wrote: Is there any good benchmark suite for testing postgresql performance? I suggest looking at the excellent software provided by OSDL. http://www.osdl.org/lab_activities/kernel_testing/osdl_database_test_suite/ ya i have tried the dbt1 (database test 1 - which is what i require) from the ODSL site but couldnt compile as i got the error: cache.c: In function `main': cache.c:134: error: `sname2' undeclared (first use in this function) cache.c:134: error: (Each undeclared identifier is reported only once cache.c:134: error: for each function it appears in.) cache.c:146: error: `dbname2' undeclared (first use in this function) cache.c:150: error: `uname2' undeclared (first use in this function) cache.c:154: error: `auth2' undeclared (first use in this function) cache.c: In function `warm_up_cache': cache.c:421: error: storage size of 'dbc' isn't known cache.c:421: warning: unused variable `dbc' cache.c: In function `usage': cache.c:730: error: `uname2' undeclared (first use in this function) cache.c:730: error: `auth2' undeclared (first use in this function) make[1]: *** [cache.so] Error 1 make[1]: Leaving directory `/root/osdl/dbt1-v2.1/cache' make: *** [cache_exe] Error 2 any ideas how to circumvent the problem? regards, KM -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 8.1.4 compile problem
I'd say, this is expected output from configure. Configure is just a mechanism to help constructing an abstraction for different types of OS (i.e. things like can we use this C type? Do we need to use another type instead?). | how can i add int8,uint8,int64 and uint64 functionality into postgres ? are | there any special flags that i am missing ? The smallint, integer and bigint types are available regardless of that output from configure. I was in a doubt if int64,int8,uint64 and uint8 are not supported after setting up the db. thanks for clarifying my doubt. will proceed with gmake :) regards, KM let me add this too: i have compiled postgres 8.1.4 even if configure didnt detect int8,uint8,int64 and uint64. i have tried to create a test table with datattype as int64 , but it says no such datatype, same is with uint8 an uint64 datatypes. of the four mentioned above, int8 only is recognised as a datatype. which means i have compiled postgresql without int64/uint64 support ??? any gotchas ? tia regards, KM ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.1.4 compile problem
At the SQL level, these datatypes are named after byte sizes not bit sizes, ie, int2, int4, int8. Or you might prefer smallint, int, bigint. There are no unsigned types. regards, tom lane oh!! that makes it clear :) thanks! regards, KM ---(end of broadcast)--- TIP 6: explain analyze is your friend
