Re: [ADMIN] what happend to my database
On Tue, Jun 10, 2008 at 12:49 PM, Scott Marlowe [EMAIL PROTECTED] wrote: On Wed, May 28, 2008 at 11:14 PM, Medi Montaseri [EMAIL PROTECTED] wrote: Yes, this type of presumptuous behavior to wipe out a production database based on a few checks is too risky... Behavior one: First out-of-box time, pg_ctl does not find any database files, it tells the user that sorry I did not find any database to startsee initdb Result: we have a semi-unhappy user/admin that says... what is initdb Behavior two: In order to enhance the out-of-box experience, we have wiped out a production environment, leading to many unhappy staff and customers PG developers...I am not impressed at all... In defense of the pg developers, the behaviour you describe was removed long ago BECAUSE of the issues you mention. The fact is that pg developers can't police every distro out there to make sure they've removed such hinky behaviour from their startup scripts. So, the persons to NOT be impressed with at all are the folks who maintain your OS's postgresql packaging, not the pg developers. stand corrected Course, you can always switch to MySQL, or Oracle, or MSSQL where nothing like that ever happens. uh huh. Never...I rather stay and fix it...than run away to a different country Thanks
Re: [ADMIN] How add db users from shell script with their passwords?
You can write a program in say Perl and use create user bob with .options encrypted password x valid until xxx which is pretty much what createuser(1) does. Cheers Medi On Tue, Jun 3, 2008 at 12:54 AM, Илья Скорик [EMAIL PROTECTED] wrote: Dear community, help please. I need to add some standard users to database together with their standard passwords from a shell script. That the script would not ask to enter passwords manually for each user. How it can be made? How I have understood, createuser comand does not allow to make it? In advance thanks. -- -- С уважением, Илья Скорик специалист Inprint - автоматизация вашего издательства Yours faithfully, Ilya Skorik the expert Inprint - automation of your publishing house e-mail: [EMAIL PROTECTED] web: http://www.inprint.ru/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] postgres, syslog and freeBSD
did you kill -hup syslogd after you changed syslog.conf On Thu, May 29, 2008 at 11:04 PM, Achilleas Mantzios [EMAIL PROTECTED] wrote: Στις Friday 30 May 2008 07:35:19 ο/η kevin kempter έγραψε: Hi list; I'm trying to get postgres syslog to get along with no luck. I'm running freeBSD7 and postgres 8.3.1 Here's my postgres setup log_destination = 'syslog' #logging_collector = off syslog_facility = 'LOCAL0' syslog_ident = 'postgres' silent_mode = on log_min_duration_statement = 0 Here's my /etc/syslog.conf file: security.*/var/log/security auth.info;authpriv.info /var/log/auth.log mail.info /var/log/maillog lpr.info /var/log/lpd-errs ftp.info /var/log/xferlog cron.*/var/log/cron *.=debug /var/log/debug.log *.emerg * # uncomment this to log all writes to /dev/console to /var/log/ console.log #console.info /var/log/console.log # uncomment this to enable logging of all log messages to /var/log/ all.log # touch /var/log/all.log and chmod it to mode 600 before it will work #*.* /var/log/all.log # uncomment this to enable logging to a remote loghost named loghost #*.* @loghost # uncomment these if you're running inn # news.crit /var/log/news/news.crit # news.err/var/log/news/news.err # news.notice /var/log/news/news.notice !startslip *.* /var/log/slip.log !ppp *.* /var/log/ppp.log LOCAL0.* /var/log/pgsql change that to local0.*/var/log/pgsql.log (lowercase, also a .log suffix is more standard) make sure the /var/log/pgsql.log file exists (create it with vi), and then change its permissions to smth like -rw--- 1 root wheel 109274 30 Μαϊ 09:00 /var/log/pgsql.log This works in my FreeBSD system, although with 7.4.*, i dont have any 8.3.1 handy at the moment. I dont see any files show up in /var/log what am I doing wrong? Thanks in advance.. /Kevin -- Achilleas Mantzios -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] what happend to my database
Hi, I am faced with a database disapperance and seeking some explanations outside of gremlins. I had a database running at cat /etc/sysconfig/pgsl/postmaster PGDATA=/qmsvol/pg_8.1.9/data PGLOG=/var/log/pgsql/pgstartup.log Where /qmsvol is an iSCSI block device A couple of days ago, my server was rebooted and by the time I got to it my database was deleted, gone, zapped, not there any more. I looked at my pgstartup.log where I see the following postmaster cannot access the server configuration file /qmsvol/pg_8.1.9/postgresql.conf: Permission denied over 17 times and then following by... The database cluster will be initialized with locale en_US.UTF-8. I think the following happend... Since my PGDATA was on an iSCSI device, by the time /etc/rc3.d/S64postgresql was executed, the device below it was not available.question...why the error says permission denied vs file not found. In the meantime, pg_ctl kept trying and finally concluded that the data directory is blank, and hence this must be a out-of-box case and he is good to initdb the PGDATA and as it called initdb to do the job... the iSCSI volume below it came online and by then the bomb had already been dropped. Now I need to find some facts to support this... Where else can I look for forensics Thanks Medi
Re: [ADMIN] what happend to my database
Yes, this type of presumptuous behavior to wipe out a production database based on a few checks is too risky... Behavior one: First out-of-box time, pg_ctl does not find any database files, it tells the user that sorry I did not find any database to startsee initdb Result: we have a semi-unhappy user/admin that says... what is initdb Behavior two: In order to enhance the out-of-box experience, we have wiped out a production environment, leading to many unhappy staff and customers PG developers...I am not impressed at all... Medi On Wed, May 28, 2008 at 7:51 PM, Tom Lane [EMAIL PROTECTED] wrote: Steve Holdoway [EMAIL PROTECTED] writes: Medi Montaseri [EMAIL PROTECTED] wrote: I think the following happend... Since my PGDATA was on an iSCSI device, by the time /etc/rc3.d/S64postgresql was executed, the device below it was not available.question...why the error says permission denied vs file not found. In the meantime, pg_ctl kept trying and finally concluded that the data directory is blank, and hence this must be a out-of-box case and he is good to initdb the PGDATA and as it called initdb to do the job... the iSCSI volume below it came online and by then the bomb had already been dropped. Now I need to find some facts to support this... When you mount a partition on linux, it does this by overlaying it's root directory with the existing one on the parent volume. Ownerships and permissions are also replaced. I expect that the /qmsvol directory will be owned by root, with fairly restrictive access rights. This will not be the case the root ( . ) directory on the external device, which will be postgres-friendly. Where else can I look for forensics I don't think you need any more! To fix this, I'd do 2 things. First, start postgres much later in the boot sequence: cd /etc/rc3.d ; mv S64postgresql S99postgresql ( and the same in rc5.d if you're using a gui at all ). The other thing to do is remove the auto-initdb behavior in your startup script. We've done that in recent releases because of prior reports of this type of problem. The OP's script is evidently still old-school, though. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Database update problem from crontab on ubuntu server
The usual trap in cron usage is the fact that crontab commands are executed
in a cleanroom environment, ie no environment variable is used/inherited, so
PATH, HOME, PGDATA, etc are not set/available when the command is launched.
You can set vars or be very explicit in your script including DB names, DB
Users, etc
Cheers
Medi
On Sun, Apr 20, 2008 at 5:08 PM, Phillip Smith
[EMAIL PROTECTED] wrote:
same result when it running trough cronjob. Manually everything is
fine. Even I put this commands (without su -l ...) in postgres user
crontab, but same result.
Server is ubuntu 64bit. Does it makes any difference from 32bit in
terms of crontab functionality?
System architecture shouldn't affect crontab. Can you give us the
full output from cron? Also, just for debugging's sake, try putting
it in a script and call the script from cron.
If it still fails, then it might help identify exactly where the
error is. If it doesn't fail, then you can start shrinking it all
back down in to one line again and see where the error comes in.
#!/bin/bash
BACKUP_FILE_GZ='/backup/rms.gz'
BACKUP_FILE='/tmp/rms.sql'
echo ---
echo Unzipping backup...
/bin/gunzip -c ${BACKUP_FILE_GZ} ${BACKUP_FILE}
echo ---
echo Attempting restore...
/usr/bin/psql rms ${BACKUP_FILE}
echo ---
echo Done
THINK BEFORE YOU PRINT - Save paper if you don't really need to print this
***Confidentiality and Privilege Notice***
The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.
Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by
Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of
this
message or any attachments
e-mail.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Dev Process
Hi, I was wondering if you could critique the following DB application development process. Given a production DB and a Dev DB: 1- Develope schema and code on Dev including test data which will impact sequences, etc 2- Dump the schema only (not the data) from Dev and restore it into the production DB 3- Populate DB with real data, usage begins 4- Enhancement to schema on Dev DB including unit testing again with impact on sequences 5- Dump schema and move to production 6- Goto 4 Thanks Medi
Re: [ADMIN] synonym
PostgreSQL does not support SYNOSYM or ALIAS. Synonym is a non SQL 2003 feature implemented by Microsoft SQL 2005 (I think). While it does provide an interesting abstraction, but due to lack of relational integrity, it can be considered a risk. That is, you can create a synonym, advertise it to you programmers, the code is written around it, including stored procedures, then one day the backend of this synonym (or link or pointer) is changed/deleted/etc leading to a run time error. I don't even think a prepare would catch that. It is the same trap as the symbolic links in unix and null pointers in C/C++. Cheers Medi On Dec 13, 2007 10:39 PM, DBA [EMAIL PROTECTED] wrote: Hi, Is it possible to create synonym in postgresql? I am getting error while creating synonym. ERROR: syntax error at or near synonym at character 8 Thanks Regards, Suresh
[ADMIN] WHERE clause OR vs IN
Hi, I know this is not exactly admin related, but ... it is simple enough to be even fun From a performance point of view, is it better to use OR as in SELECT expr FROM expr WHERE col=this OR col=that OR col=theOther Or to use a range of values as in SELECT expr FROM expr WHERE col in ( val1, val2, , valn) I think the IN range yields a better query plan what do you think ? Thanks Medi
Re: [ADMIN] WHERE clause OR vs IN
Thanks...is'nt the run time latency more visiable with lorge input sets (big
tables) as well as how long the OR-ed expression chain is
based on your report, run time of OR is 0.275 ms and IN is 0.314
Perhaps if we run explain verbose to see the actual query plan
medi
On Dec 12, 2007 2:36 PM, Joshua D. Drake [EMAIL PROTECTED] wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wed, 12 Dec 2007 14:25:16 -0800
Medi Montaseri [EMAIL PROTECTED] wrote:
Hi,
I know this is not exactly admin related, but ... it is simple enough
to be even fun
From a performance point of view, is it better to use OR as in
SELECT expr FROM expr WHERE col=this OR col=that OR col=theOther
Or to use a range of values as in
SELECT expr FROM expr WHERE col in ( val1, val2, , valn)
I think the IN range yields a better query plan what do you
think ? Thanks
Well a simple test:
postgres=# explain analyze select * from tellers where bid in ('1','2');
QUERY
PLAN
-
--
Seq Scan on tellers (cost=0.00..10.25 rows=20 width=352) (actual
time=0.019..0.192 rows=20 loops=1) Filter: (bid = ANY
('{1,2}'::integer[])) Total runtime: 0.314 ms (3 rows)
postgres=# explain analyze select * from tellers where bid = '1' or bid
= '2'; QUERY PLAN
-
--
Seq Scan on tellers (cost=0.00..11.50 rows=20 width=352) (actual
time=0.018..0.199 rows=20 loops=1) Filter: ((bid = 1) OR (bid = 2))
Total runtime: 0.275 ms
(3 rows)
postgres=#
Medi
- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHYGJwATb/zqfZUUQRAs32AJ9HuJVcY5gcr0hboxkI6PcRtv++JwCfXd00
nQ7Frkof0mVwqNYVxQ9Vziw=
=XzJi
-END PGP SIGNATURE-
Re: [ADMIN] WHERE clause OR vs IN
Thanks everyone, and I know I am taking too much bandwidth, but...
The reason I was thinking IN would work better is that from a parser point
of view confronted with a series of expressions chained via boolean
operators such as
expr OR expr OR expr
The back end code generated has to be generic to combat with different type
of expression such as equality, greater than, less than, or any fancy
expressions. For example:
a == b OR c != d AND e f OR g == h
Where as in the case of
a IN (1, 2, 3, 4)
is actually a subset of the above composite expression because
1- the expressions are always equality of operand a with a literal value
which means the operand can be cached (register)
2- the composite expression is always an OR chained expression where the
first TRUE-ness would return the composite as TRUE (aka short circuit
behavior)
I could be wrong...I have been wrong before...
Medi
On Dec 12, 2007 5:03 PM, Tom Lane [EMAIL PROTECTED] wrote:
Richard Broersma Jr [EMAIL PROTECTED] writes:
--- On Wed, 12/12/07, Medi Montaseri [EMAIL PROTECTED] wrote:
based on your report, run time of OR is 0.275 ms and IN is
0.314
postgres=# explain analyze select * from tellers where
bid in ('1','2');
Two other options are:
SELECT *
FROM Tellers
WHERE bin = ANY( '1', '2' );
Note that depending on which PG version you are testing, x IN (a,b,c)
is exactly equivalent to x=a OR x=b OR x=c (older versions), or to
x = ANY(ARRAYa,b,c]) (newer versions).
SELECT T.*
FROM Tellers AS T
INNER JOIN ( VALUES ( '1' ), ( '2' ) ) AS B( bin )
ON T.bin = B.bin;
I seriously doubt that one's gonna win ...
regards, tom lane
Re: [ADMIN] postmaster -D PGDATA and postmaster.pid
Thanks Tom..that did the magic Would I allow PG to start from any -D place by saying semange fcontext -a -t postgresql_db_t /qmsvol(/.*)? Thanks Medi On Nov 26, 2007 7:48 PM, Tom Lane [EMAIL PROTECTED] wrote: Medi Montaseri [EMAIL PROTECTED] writes: However if I try to start the engine with /etc/init.d/postgresql start or service postgresql start I see the following error message in my PGLOG (ie /var/log/pgsql/pgstartup.log) postmaster cannot access the server configuration file /qmsvol/pg_8.1.9/data/postgresql.conf: Permission denied This being CentOS (ie RHEL), you probably have got SELinux turned on by default. The out-of-the-box SELinux policy forbids the postmaster daemon from accessing portions of the filesystem other than /var/lib/pgsql. You can either disable SELinux or fix its policy to allow PG to access the area where you want to put PGDATA. I don't really recommend the former, at least not for a system that has any exposure at all to the open Internet. However, I don't have a recipe for fixing the latter in my hip pocket, either. (Really need to go study SELinux someday.) regards, tom lane
[ADMIN] postmaster -D PGDATA and postmaster.pid
Hi, I am configuring a PG 8.1.9 on CentOS 5. I am seeing some anomalies and would like you feedback. I have defined a new PGDATA=/qmsvol/pg_8.1.9/data, ran initdb -D /qmsvol/pg_8.1.9/data successfully and can start the engine with pg_ctl -D $PGDATA. I have also populated /etc/sysconfig/pgsql/postgresql which overrides PGDATA and PGLOG to my values. However if I try to start the engine with /etc/init.d/postgresql start or service postgresql start I see the following error message in my PGLOG (ie /var/log/pgsql/pgstartup.log) postmaster cannot access the server configuration file /qmsvol/pg_8.1.9/data/postgresql.conf: Permission denied ls -ld /qmsvol /qmsvol/pg_8.1.9 /qmsvol/pg_8.1.9/data /qmsvol/pg_8.1.9/data/postgresql.conf drwxrwxrwx 4 root root 4096 Nov 26 10:39 /qmsvol drwx-- 3 postgres postgres 4096 Nov 26 11:25 /qmsvol/pg_8.1.9 drwx-- 11 postgres postgres 4096 Nov 26 12:04 /qmsvol/pg_8.1.9/data -rw--- 1 postgres postgres 13699 Nov 26 11:25 /qmsvol/pg_8.1.9/data/postgresql.conf Any ideas ? Thanks medi
Re: [ADMIN] Migrating from 32 to 64 bit
But theoretically speaking, 32 or 64-bit ness of the application (ie the postmaster server) should not influence the data types offered by a particular DB version. That is the semantics of data types and cpu-arch (register width, big endian, little endian, sparc, mips, x86), etc ) offered by a particular DB version should be orthogonal. A practical example is when I first begin my business on a Mac, then I move the database to a Sun and then on to a mainframe Cheers Medi On Nov 25, 2007 4:59 AM, Hannes Dorbath [EMAIL PROTECTED] wrote: Laurent CARON wrote: Question: I'd like to know if it is possible (and wise) to just keep the /var/lib/postgres.. directories from the old 32Bit server to use on the 64Bit version. This is just as a personal interest since I can also just dump and restore the database in about 2.5 hrs. Dump and restore. Using the old data dirs won't work. -- Best regards, Hannes Dorbath ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] [HACKERS] grep command
Or ... ask the application not the OS psql select version() ; Cheers Medi On 10/29/07, Andrew Dunstan [EMAIL PROTECTED] wrote: Leaving aside the question of why one might want to do this, Unix 101 should show you many ways to do it. For example, sed -n -e 's/.*PG_VERSION /PG_VERSION /p' -e /PG_VERSION/q config.log Please don't cross-post questions like this, especially when it's not really a PostgreSQL question at all. cheers andrew Kuriakose, Cinu Cheriyamoozhiyil wrote: Hi All, I am giving the command cat config.log|grep -w 'PG_VERSION' Which gives the following Output: | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 #define PG_VERSION 8.3beta2 But the output that I would require is PG_VERSION 8.3beta2 that should occur only once, can anyone please tell me the command to get this output. Thanks in advance Regards Cinu Kuriakose ---(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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Postgres User Password File????
PG maintains user passwd within itself in a table called pg_shadow.
But based on the error message, it seems like you are having problem
connecting to the server.
Try accessing PG from command line with something like psql(1) as in
psql -U proot -d databaseName
and see what error message do you get...can you connect to PG as a different
user ?
On 9/7/07, smiley2211 [EMAIL PROTECTED] wrote:
Hello all,
Where do I find the PASSWORDs for users on Postgresql??? It seems
connections are failing because user PROOT's password is not correct -
where
do I find out what it's set to - do I need to change it???
DataObjects_Users : CONNECT :
a:1:{s:32:b8c77e08e3d43ac04152a2db76d9728a;O:8:DB_Error:8:{s:20:error_message_prefix;s:0:;s:4:mode;i:1;s:5:level;i:1024;s:4:code;i:-24;s:7:message;s:24:DB
Error: connect failed;s:8:userinfo;s:171: [nativecode=Unable to
connect
to PostgreSQL server: FATAL: password authentication failed for user
proot]
Thanks...Michelle
--
View this message in context:
http://www.nabble.com/Postgres-User-Password-File-tf4402207.html#a12558458
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] how to create dir using pg/plsql
I was under the impression that recent modern databases, like PG, allow you to transfer control to an out-of-system (system being the DB engine) module (shared lib, dll). That is while triggers transfer control within the system, now mechanisms exists that allow you to go outside of the system. In that context, whence the control is transferred to a C, Perl or whatever language, who is to stop that function from doing anything it wants. I appreciate a clarification. Medi On 8/29/07, Scott Marlowe [EMAIL PROTECTED] wrote: On 8/29/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: just subject, i need to create dir using pg/plsql function,,but cannt get anything after google, As noted, not possible. BUT, you can do it in untrusted languages, like pl/perlu or pl/tclu or pl/C ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] how to create dir using pg/plsql
I see...thank you for the clarification...one more question... How does PG protects itself from a run-away code (eg an endless loop) ? In other words, does PG run the untrusted code in a seperate process? Thanks Medi On 8/29/07, Tom Lane [EMAIL PROTECTED] wrote: Medi Montaseri [EMAIL PROTECTED] writes: I was under the impression that recent modern databases, like PG, allow you to transfer control to an out-of-system (system being the DB engine) module (shared lib, dll). That is while triggers transfer control within the system, now mechanisms exists that allow you to go outside of the system. In that context, whence the control is transferred to a C, Perl or whatever language, who is to stop that function from doing anything it wants. Well, sure. Postgres calls those untrusted languages, and only lets database superusers use them. regards, tom lane
Re: [ADMIN] How to monitor resources on Linux.
The 3.4G per process seems too un-realistic. Here is a simple way to isolate or narrow the scope of the problem at hand. Bring the server up, go to the run level that you run PG, but stop PG, now measure your memory consumption. This is your baseline. Now start PG, but no connection, just idle, measure your memory consumption Then bang on your PG (or wait for a busy time) and measure your memory consumption. Tools available on linux include ps(1), vmstat(1), top(1), ipcs(1), proc(5) Medi On 8/28/07, John R Allgood [EMAIL PROTECTED] wrote: Hello All I have some questions on memory resources and linux. We are currently running Dell Poweredge 2950 with dual core opeterons and 8GB RAM. Postgres version is 7.4.17 on RHEL4. Could someone explain to me how to best monitor the memory resources on this platform. Top shows a high memory usage nearly all is being used. ipcs -m shows the following output. If I am looking at this correctly each of the postgres entries represents a postmaster with the number of connections. If I calculate the first entry it comes to around 3.4GB of RAM being used is this correct. We have started running into memory issues and I think we have exhausted all the memory on the system. I think the best approach would be to add more memory unless someone can suggest other options. We have a 2 node cluster running about 10 separate postmasters divided evenly on each node. Each postmaster is a separate division is our company if we have a problems with one database not everyone is down. 0x0052ea91 163845 postgres 600133947392 26 0x00530db9 196614 postgres 60034529280 24 0x00530201 229383 postgres 60034529280 21 0x005305e9 262152 postgres 60049152003 0x005311a1 294921 postgres 60034529280 28 0x0052fe19 327690 postgres 60049152004 Thanks John Allgood - Systems Admin Turbo Logistics ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] How to import CSV file?
This might not be the quickest way, but it is a skill you'll be happy to have gained. Perl has a class (or module) called CSV.pm, you feed it a file, and it does the rest (parsing it) and gives an array for each row. You can then alter them (however you wish) and write them to a file suitable for pg copy, or just use DBI to insert it into the PG. Which is basically an ETL (Extract, Transform, Load). I know insert is slower, but the point was to show a general way that will always for. Cheers Medi Montaseri On 8/28/07, Chris Hoover [EMAIL PROTECTED] wrote: I need some help importing a csv file. I have been given a csv file full of data that is delimited by a comma, and the strings are delimited by a single quote. How do you write the copy statement to use a single quote delimiter? I have tried several things, but so far, no joy. Thanks for any help, Chris
Re: [ADMIN] PostgreSQL and virtualization
Another view is...by the time you figure out all these virtualization solutions you can buy a used box for less than $100 (no monitor needed) and focus on the real test. By the way, in any HA one issue will be the IP takeover on the surviving node. Under the IP will be the Mac address of the previous primary node which is now cached in switches and/or routers so a rebuild of the ARP cache will be needed. I am not sure how these virtualizations play the MAC game but minimally your test is tainted from that point of the view Cheers Medi On 8/27/07, Johann Spies [EMAIL PROTECTED] wrote: On Fri, Aug 24, 2007 at 07:28:43AM -0700, Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Arnau wrote: Hi all, I'd like to do some tests with replication, high availability, ... with PostgreSQL. The problem I have is I don't have enough hardware to do such tests, so I'd like to use virtualization. I'd like to do it using linux. Nowadays there several products to do it Xen, Qemu, VMware, ... and I don't have any experience with any of them. Anybody has tried anything like this? Vmware server is free (as in beer) as it by far the easiest to get running. However, it will also be slower than Xen. If you are just testing, use VMWare. You can also look at VirtualBox (www.virtualbox.org). I my experience it is as easy as VMWare and faster. Regards Johann -- Johann Spies Telefoon: 021-808 4036 Informasietegnologie, Universiteit van Stellenbosch But the end of all things is at hand; be ye therefore sober, and watch unto prayer. And above all things have fervent love among yourselves; for love shall cover the multitude of sins. I Peter 4:7,8 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] Installing Postgres
Just dont get into the habbit of moving db files around like too muchfilesystems will spead your files all over the place and your database will slow down Medi On 8/23/07, Campbell, Lance [EMAIL PROTECTED] wrote: Tom, It is nice to know that you can actually move the database by simply moving the files. That does assume you shut down the database first. Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Thursday, August 23, 2007 1:42 PM To: Campbell, Lance Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Installing Postgres Campbell, Lance [EMAIL PROTECTED] writes: The solution, based on emails I have received, is to install PostgreSQL into a subdirectory called data. I then move the contents of data back a directory. This way I am able to have the files in the directory I want them in. Even though the directory I wanted them in contains a subdirectory. This is how you get around the issue. NO, NO, NO, NO, NO. You appear to be impervious to several people telling you this is a bad idea, but I will try one more time to explain why. The reason you see lost+found in that directory is that it is a volume mount point. Volume mount point directories should *always* be owned by root, for both reliability and security reasons. The setup you are proposing would have to have the mount point directory be postgres-owned. You will live to regret that if you insist on doing it that way (in fact, I'm astonished your sysadmin agreed to it in the first place ... he must be pretty new at his job). Stick with the extra level of directory. It appears that the PostgreSQL data directory contents can be moved. Yeah, they can. The argument here is about exactly where you are proposing to move them to. If it were an ordinary directory it'd be fine. regards, tom lane ---(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
Re: [ADMIN] ROI on 7.2 to 8.2
What can I sayat least he is willing to listen now...thanks guys... This is an excellent start... Cheers everyone Medi On 8/21/07, Joshua D. Drake [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Medi Montaseri wrote: Hi, I have finally convinced my manager to consider the idea of migrating from 7.2 to 8.2. Obviously he needs an ROI (Return Of Investment) analysis (in a technical sense). So, I am wondering if anyone has worked on a similar project where you had to go thru the release notes and bug fixes to compile such a motivational speech and willing to share. Otherwise, as many links you can provide would be nice. Wow... Does your manager still run Windows 98? Not to be sarcastic but that is essentially what the argument boils down to. 1. 7.2 is *not* supported 2. 7.2 will not receive any backpatches * There are data loss and security bugs that have not been back patched 3. If you post here with a question about 7.2, the first thing you will get back is, upgrade. 4. 8.2 is much faster (not like 10%, like 100%) Now what problems are you going to run into? Several datatypes in 7.2 are allowed to be '', in 8.x they are not. Specifically number based types. You may have to turn on add_missing_from_clause Sincerely, Joshua D. Drake Cheers Medi - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGyyABATb/zqfZUUQRAk0SAJ9kQ2LvZ9MoCVwZX7gHgSB8NNgxygCfYp5a bxD4582Zw+WM2XCEy38idCM= =dP3b -END PGP SIGNATURE-
Re: [ADMIN] ROI on 7.2 to 8.2
Thanks Chris, I was actually just doing that and going down the list, picking fixes and enhancements that are relevant to our productby the time I got to 7.3...I came almost the same conclusion that I should just print (all of) them and drop them on his desk..:-) This is good, I thank everyone. Medi On 8/21/07, Chris Browne [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] (Medi Montaseri) writes: I have finally convinced my manager to consider the idea of migrating from 7.2 to 8.2. Obviously he needs an ROI (Return Of Investment) analysis (in a technical sense). So, I am wondering if anyone has worked on a similar project where you had to go thru the release notes and bug fixes to compile such a motivational speech and willing to share. Otherwise, as many links you can provide would be nice. You can simply look at the release notes for 7.3, 7.4, 8.0, 8.1, and 8.2. You can find them here: http://www.postgresql.org/docs/8.2/static/release.html More specifically, you can find them at these URLs. http://www.postgresql.org/docs/8.2/static/release-7-3.html http://www.postgresql.org/docs/8.2/static/release-7-4.html http://www.postgresql.org/docs/8.2/static/release-8-0.html http://www.postgresql.org/docs/8.2/static/release-8-1.html http://www.postgresql.org/docs/8.2/static/release-8-2.html Print off the contents of those five URLs, and you'll find somewhere between 40 and 50 pages worth of _brief listings_ of bug fixes and enhancements. Drop that pile of paper on his foot and ask if he thinks it seems heavy enough to seem significant. Add to that the other folks' arguments: - There be database eating bugs there that be fixed in later releases. - Nobody is interested in supporting problems you have with such an ancient version. -- select 'cbbrowne' || '@' || 'cbbrowne.com'; http://www3.sympatico.ca/cbbrowne/internet.html First Fact About Networks Pipes can be made wider, but not shorter -- Mark Miller ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] how to call sql code without function
Perhaps you could try to express your idea in SQL language (as much as possible, call it pseudo SQL) and then ask for optimization suggestion Cheers Medi On 8/20/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi all, I agree, but now I actually need a technical and syntactical support in order to grab all my queries to keep the php code simple and fast. This is the scenario: I send as input three parameters: x,y,z (one or two could be null) 1th query on human anagrafic table(S) regarding x,y,z - 1th temp table 2th query returns from the 1th temp table all actual data - 2th tamp table 3th and 4th queries retrieve some historical data - 3th temp table 5th query selects data regarding the 2th temp table from the 1th building tables -4 th temp table 6th query selects data about th 2th table from th 2th building tables - into 4th tamp table in output I've 1th temp table, th 2th one and th 4th table By using PHP, afaik, I have to send a query and get the results, now I can send the whole package of query and read the results, but so the sql is in php: dangerous and slow. The other way consist on using the functions but, afaik, a function can returns only a set of data a time, so I should write three functions but the syntax for the funcion has a lot of overhead code lines. Is there another way in order to store the whole set of queries and to call it once from php, sending the parameter and reading the three tables ? I'd like to reduce my php calls only to send the parameters (first call) and then reading the three table of result ( three calls). Now at my present learning I perform 9 calls. Giu - Original Message Da: Scott Marlowe [EMAIL PROTECTED] To: Medi Montaseri [EMAIL PROTECTED] Cc: pgsql-admin@postgresql.org Oggetto: Re: [ADMIN] how to call sql code without function Data: 20/08/07 20:05 On 8/20/07, Medi Montaseri [EMAIL PROTECTED] wrote: You can think of a database as a filesystem as well. That is do some processing, store the result in temp table, do some more, etc,etc then merge and process temp tables to arrive at some result. Just as in the case of filesystem, if you are operating in a concurrent evironment, you need to fence against that. That is it is possible that at a given time two sessions will arrive at the same processing point where they need to create such temp tables. Each session will get it's own temp table, even if they have the same name. The real issue is what they do with the data in that temp table to make sure that they're committing changes that make sense given the current state of data in the database. The other solution which I prefer is to write a stored procedure to solve this. Or get creative with nested and complex SQL queries. Note that nested queries still have some race conditions (such as with aggregate functions) in postgresql. ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Viaggi, voli, soggiorni...cattura l'offerta e parti con Mondolastminute Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6850d=20070821 ---(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: [ADMIN] Building source code of PostgreSQl-7.1
In other words, are you sure you want to use 7.1 which is about 5 years old ? Also note that there are binary versions for various platforms, ready to be used. Cheers Medi On 8/20/07, Joshua D. Drake [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Kuriakose, Cinu Cheriyamoozhiyil wrote: Hi, I was in the process of downloading the source code of PostgreSQL-7.1 through CVS, and I was able to do that with the command : cvs -d :pserver:[EMAIL PROTECTED]:/projects/cvsroot co -rREL7_1 -P pgsql Now this source code is downloaded is in the pgsql folder, can anyone please tell me the command to build PostgreSQL-7.1 binary from this source code. Awaiting response. Not to be unkind but, Good god, WHY!?? If you really must know I suggest this link: http://www.commandprompt.com/ppbook/c360 Sincerely, Joshua D. Drake Thanks and Regards Cinu - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGylgAATb/zqfZUUQRAgpkAKCbdRhVeAolsCjLR7SxfLtxTTBU8QCcCp+e XdoAC9GV8Zo7D7ZjPEvl754= =nfUV -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] compilation error on CentOS 5
Oh what I can share with you about employers who are not familiar with database technologies and how far they go to distance themselves from it or use it in such ill ways By the way, I have fixed the problem, and it looks like it was handcrafted makefile that caused it. It still does not explain why the same makefile works on previous OS releases, but that is another story and there many such stories in a day of software engineer. Cheers for now Medi On 8/17/07, Tom Lane [EMAIL PROTECTED] wrote: Medi Montaseri [EMAIL PROTECTED] writes: I am having difficulty compiling my postgresql 7.2.3 on a CentOS 5. Why in the world are you trying to build a five-year-old PG release on a current operating system? Do yourself a favor and move up to some recent release. regards, tom lane
[ADMIN] compilation error on CentOS 5
Hi, I am having difficulty compiling my postgresql 7.2.3 on a CentOS 5. The same code works on our build machine which is running RedHat 7.2. I have checked many of the configure(1) output with no major diff. Capturing the output of make reveals a silly problem which I am failing to find the root of. The problem is: Compilation moves forward and at one point it does a make -C libpq all. This operation builds all the object files and even ar(1) them to create libpq.a. But next step, attempting to run ranlib(1) fails because the ranlib command was misconstructed as in /opt/ide/i2.02/hardhat/devkit/x86/pentium4/bin/pentium4-ranlibCFLAGS=-I/u/medi/share/src/port/usr/include Note how ranlibCFLAGS should've been ranlib CFLAGS So it looks like in my CentOS 5 something has changes such that the variable substituions are wrong. Which points to things that are autogenerated, which points to configure(1). Any tips on the actual problem or how to get to the bottom of it is appreciated. Perhaps a few make tricks would surface the problem. Thank you Medi
Re: [ADMIN] PG engine takeover or switch over
Thank you both for your input...this is indeed the type of analysis I was looking for. Now I have to read and understand them more carefully. At this point I wanted to thank you both and hopefully I can trouble you both with some follow ups in the future. Cheers Medi On 8/16/07, Chander Ganesan [EMAIL PROTECTED] wrote: Hannes Dorbath wrote: On 15.08.2007 21:30, Medi Montaseri wrote: I am looking for some suggestions to optimize the following problem/solution. Given two nodes A and B (two computers) in a active-passive mode where A is running PG engine, and B is simply standing by, and a common storage (twin tailed) ( or from pg_ctl point of view -D /common/data ), I am looking for a faster solution during the takeover where A has crashed and B is to start PG engine and run with it. My current solution is to start PG engine which should take little time to study the configuration files and /common/data and fork a few childrens. But I am still interested in optimizing this start-up cost. For example, would I gain anything by starting PG engine on both A and B, but on B I point it to /common/dummy and during the takeover, I somehow tell it to now read from /common/data, for example have two postmaster.conf or PGDATA and then use pg_ctl reload. Starting up PostgreSQL should be very fast, given no recovery to be done and decent hardware. PostgreSQL does not fork a lot unless it is accepting new connections and if reading a config file is slow on your system, something else is broken. In a active/passive setup your should be able to switch over in under 3 seconds. If there was a lot of load on the failed node the recovery times on the new active node increase. The only thing you can do about that is getting faster disks.. Your startup time in the event of a failure will be predicated on the number of WAL files that need to be played back in order to perform auto-recovery. For example, if you've set your checkpoint_segments to some high number, PostgreSQL will need to play back those WAL files to ensure that transactions that were committed to disk are correctly represented in your on disk object data (tables, indexes, etc). Note that in the case of a graceful shutdown, you'll find that PostgreSQL doesn't need to replay WAL files (since it checkpoints prior to shutting down) and as such the startup time is pretty fast. You can decrease the amount of time it takes to recover by decreasing the number of segments per checkpoint; however, this may result in more frequent checkpointing and as as result a reduction in overall performance on your active node. Basically, you're balancing the cost of auto crash recovery with the frequency of checkpointing. An alternative (and perhaps faster) method of failover would be to have a cluster in warm standby mode (this wouldn't rely on a shared pgdata directory). In such cases you would only have to wait for the last few WAL files to be played back in order to recover. In the case of a high checkpoint_segments number this would allow you to recover quickly from failures, but would introduce PITR overhead (copying WAL files when WAL files become full), but that's a pretty small cost - and you may already have that cost if you do PITR backups. There is no way to have postgresql switch data directories to speed up startup. -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 919-463-0999/866-229-3386 http://www.otg-nc.com
[ADMIN] PG engine takeover or switch over
Hi, I am looking for some suggestions to optimize the following problem/solution. Given two nodes A and B (two computers) in a active-passive mode where A is running PG engine, and B is simply standing by, and a common storage (twin tailed) ( or from pg_ctl point of view -D /common/data ), I am looking for a faster solution during the takeover where A has crashed and B is to start PG engine and run with it. My current solution is to start PG engine which should take little time to study the configuration files and /common/data and fork a few childrens. But I am still interested in optimizing this start-up cost. For example, would I gain anything by starting PG engine on both A and B, but on B I point it to /common/dummy and during the takeover, I somehow tell it to now read from /common/data, for example have two postmaster.conf or PGDATA and then use pg_ctl reload. Thanks Medi
[ADMIN] test
testing...am I being copied to the list Medi
Re: [ADMIN] postmaster restart on a different dataDir
Actually I found the answerpg_ctl supports a -w switch which waits for the start operation to complete. Decibel...without -w, pg_ctl will return immediately before postmaster is fully operational. If you don't believe me start the engine and try to connect to it right away with pgsql(1)... Cheers for now Medi On 8/14/07, Decibel! [EMAIL PROTECTED] wrote: On Mon, Aug 13, 2007 at 04:05:37PM -0700, Medi Montaseri wrote: Hi, I am trying to measure startup time of postmaster. Say I have postmaster Why? running on -D /tmp/d1 and would like to restart it on -D /tmp/d2. If I just say time pg_ctl restart -D /tmp/d2 it will not produce an accurate result because pg_ctl will start postmaster in the background and return immediately but if you query the engine right away, you'll see that it is not ready yet as in time ( pg_ctl restart -D /tmp/d2 ; psql -l ) If I call postmaster, then the command is blocking waiting for postmaster to exit. So let it stop, start the timer, then start it again. -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
[ADMIN] postmaster restart on a different dataDir
Hi, I am trying to measure startup time of postmaster. Say I have postmaster running on -D /tmp/d1 and would like to restart it on -D /tmp/d2. If I just say time pg_ctl restart -D /tmp/d2 it will not produce an accurate result because pg_ctl will start postmaster in the background and return immediately but if you query the engine right away, you'll see that it is not ready yet as in time ( pg_ctl restart -D /tmp/d2 ; psql -l ) If I call postmaster, then the command is blocking waiting for postmaster to exit. I am sure there is a way...but I figured I ask my good friends first... Thanks Medi
