[ADMIN] interfacing ms-access with postgresql?
Hello, I'd like to interface ms-access 2000 with postgresql 7.1.1. What my objective is is to create a database in access, then upload it on to a web server, and via an interactive form and postgresql have various data returned, or to enter new data from the web site. Is this possible? Thanks. Dave. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] Postgres not starting at boot(FreeBSD) - startup script not releasing
Try this on for size... recently during a reboot (first in about 3 months for
this particular server) our entire rc.d directory failed to start... after some
hacking of the rc file to output some helpful debuggin, it was apparent that the
010.pgsql.sh script in /usr/local/etc/rc.d was timing out and causing any
directives thereafter not to be processed.
Running the script manually as root starts the postmaster but doesn't return you
to the command prompt. ^C and checking the errlog shows
Waiting for postmaster starting up..DEBUG: Data Base System is starting up at
Sat Mar 9 17:05:45 2002
DEBUG: Data Base System was shut down at Sat Mar 9 17:05:39 2002
DEBUG: Data Base System is in production state at Sat Mar 9 17:05:45 2002
Fast Shutdown request at Sat Mar 9 17:05:48 2002
DEBUG: Data Base System shutting down at Sat Mar 9 17:05:48 2002
DEBUG: Data Base System shut down at Sat Mar 9 17:05:48 2002
Can force it to return to command prompt by adding a "&" and doublt cr
web1# /usr/local/etc/rc.d/010.pgsql.sh start &
[1] 4635
web1#
[1] + Suspended (tty output)/usr/local/etc/rc.d/010.pgsql.sh start
web1#
and postgres stays up and frees the terminal. Output in errlog for this is...
Waiting for postmaster starting up..DEBUG: Data Base System is starting up at
Sat Mar 9 17:07:21 2002
DEBUG: Data Base System was shut down at Sat Mar 9 17:05:48 2002
DEBUG: Data Base System is in production state at Sat Mar 9 17:07:21 2002
No idea what could be causing the script not to function as it is the EXACT same
script as on the other servers we are operating (did a diff just to be sure)
In the interim we removed the script from the startup dir... any ideas as to
why this is occuring?
Installed from port, left the port startup script as is... listed below.
Appreciate any feedback/comments.
Dave
# $FreeBSD: ports/databases/postgresql7/files/pgsql.sh.tmpl,v 1.9 2000/12/11
03:22:07 steve Exp $
#
# For postmaster startup options, edit $PGDATA/postmaster.opts.default
# Preinstalled options are -i -o "-F"
case $1 in
start)
[ -d /usr/local/pgsql/lib ] && /sbin/ldconfig -m /usr/local/pgsql/lib
[ -x /usr/local/pgsql/bin/pg_ctl ] && {
su -l pgsql -c \
'exec /usr/local/pgsql/bin/pg_ctl -w start > /usr/local/pgsql/errlog
2>&1'
echo -n ' pgsql'
}
;;
stop)
[ -x /usr/local/pgsql/bin/pg_ctl ] && {
exec su -l pgsql -c 'exec /usr/local/pgsql/bin/pg_ctl -w -m fast stop'
}
;;
status)
[ -x /usr/local/pgsql/bin/pg_ctl ] && {
exec su -l pgsql -c 'exec /usr/local/pgsql/bin/pg_ctl status'
}
;;
*)
echo "usage: `basename $0` {start|stop|status}" >&2
exit 64
;;
esac
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Postgres not starting at boot(FreeBSD) - startup script not releasing
Sorry, should point out that the stop is resulting from executing a ^c after running the script manually. Since the script runs... postgres starts, but from reading the startup script, it is waiting for the pid file to appear before reporting suscess... and it isn't doing this. Or at least not exiting and leaving the postmaster running. It just sits there... thus the ^c to regain the terminal. opening two terminals, I can run the start script, and while the first terminal is sitting there waiting for the script to release control, move to the second terminal and view the results... postmaster running fine, pid file there, all normal. if I execute the script with the & behind it, it allows everything through after entering another which from what I can see suspends the session which then clears normally. (making sense?) Confused still as to the cause or how to rectify. Dave >-Original Message- >From: Tom Lane [mailto:[EMAIL PROTECTED]] >Sent: Sunday, March 10, 2002 11:22 AM >To: Dave >Cc: [EMAIL PROTECTED] >Subject: Re: [ADMIN] Postgres not starting at boot(FreeBSD) - startup >script not releasing > > >"Dave" <[EMAIL PROTECTED]> writes: >> DEBUG: Data Base System is starting up at Sat Mar 9 17:05:45 2002 >> DEBUG: Data Base System was shut down at Sat Mar 9 17:05:39 2002 >> DEBUG: Data Base System is in production state at Sat Mar 9 17:05:45 2002 >> Fast Shutdown request at Sat Mar 9 17:05:48 2002 >> DEBUG: Data Base System shutting down at Sat Mar 9 17:05:48 2002 >> DEBUG: Data Base System shut down at Sat Mar 9 17:05:48 2002 > >It looks like something is hitting the postmaster with a SIGINT signal >as soon as it starts. Got any idea what might be doing that? It's >not pg_ctl, for sure (unless the "something" is firing your init >script with a 'stop' option). In any case I think you should be looking >for outside agencies, not a problem directly in this init script. > > regards, tom lane > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Postgres not starting at boot(FreeBSD) - startup script not releasing
hold the farm... >>> Try this on for size... recently during a reboot (first in about 3 >>months for >>> this particular server) our entire rc.d directory failed to start... >> after some >>> hacking of the rc file to output some helpful debuggin, it was >>apparent that the >>> 010.pgsql.sh script in /usr/local/etc/rc.d was timing out and causing any >>> directives thereafter not to be processed. >> >>have you tried manually doing "pg_ctl restart" to see if any problems >>pop-up? Maybe it is not a script error, but some other issue with the db >>server. did the following, stopped the server totally... then ran the following. web5# su -l pgsql -c 'exec /usr/local/pgsql/bin/pg_ctl start' postmaster successfully started up. web5# DEBUG: Data Base System is starting up at Sun Mar 10 14:32:46 2002 DEBUG: Data Base System was shut down at Sun Mar 10 14:32:04 2002 DEBUG: Data Base System is in production state at Sun Mar 10 14:32:46 2002 web5# web5# su -l pgsql -c 'exec /usr/local/pgsql/bin/pg_ctl restart' Smart Shutdown request at Sun Mar 10 14:33:25 2002 Waiting for postmaster shutting down..The Data Base System is shutting down ..The Data Base System is shutting down ...The Data Base System is shutting down The Data Base System is shutting down ...The Data Base System is shutting down .pg_ctl: postmaster does not shut down web5# The Data Base System is shutting down The Data Base System is shutting down The Data Base System is shutting down The Data Base System is shutting down Hmmm... check that its still running... web5# ps -aux | grep pgsql pgsql 81016 0.0 0.1 628 452 p0 I 2:32PM 0:00.00 /bin/sh /usr/loca pgsql 81018 0.0 0.3 4080 2404 p0 I 2:32PM 0:00.03 /usr/local/pgsql/ pgsql 81082 0.0 0.4 4508 3008 p0 I 2:33PM 0:00.03 /usr/local/pgsql/ pgsql 81083 0.0 0.4 4556 3364 p0 I 2:33PM 0:00.06 /usr/local/pgsql/ web5# ok, lets try and use the rc.d script... web5# /usr/local/etc/rc.d/010* stop Fast Shutdown request at Sun Mar 10 14:37:28 2002 Aborting any active transaction... Waiting for postmaster shutting down..FATAL 1: The system is shutting down FATAL 1: The system is shutting down NOTICE: AbortTransaction and not in in-progress state .NOTICE: AbortTransaction and not in in-progress state DEBUG: Data Base System shutting down at Sun Mar 10 14:37:28 2002 DEBUG: Data Base System shut down at Sun Mar 10 14:37:28 2002 done. postmaster successfully shut down. web5# Thats interesting, perhaps pg_ctl is hosed? web5# ps -aux | grep pgsql web5# Ideas? Dave ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Postgres not starting at boot(FreeBSD) - startup script not releasing < solved
Bingo! Dumb move. Dropped everything to password a few months back, never had the occasion to restart after that. Will work on tweaking the pg_hba.conf Thanks Matthew... if you are ever in Toronto, I owe you a beer. Dave >At a guess, you've set it up to not automatically trust local users, so >the default options which 'wait' for the server to come up (and "waits" >by having psql try connecting as the postgres user) waits for a long long >time for somebody to give it the password it now requires. > >I find that rather annoying, and miss it every time, until the rc script >hangs. Check the options and figure out which one it is you have to take >out, I can't recall offhand. > > > >-- >Matthew Fuller (MF4839) |[EMAIL PROTECTED] >Unix Systems Administrator |[EMAIL PROTECTED] >Specializing in FreeBSD |http://www.over-yonder.net/ > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] logic check of pg_hba.conf configuration
so rarely have problems with postgres that setup or reconfigs are few and far between. In reviewing the docs on this after a recent config error, I am asking for assistance to acid test the following config file. running version 7.0 current pg_hba.conf contains// localall trust host sameuser127.0.0.1 255.255.255.255 password host all 123.45.678.1 255.255.255.255 trust host all 0.0.0.0 0.0.0.0 reject intended results are// 1) anyone who can ssh into the box should have admin priv anyway, as such trust them 2) anyone accessing it via PHP, Perl, or others using port 5432 but from the same physical machine will get access to the database that they provide username/pw for - expect them to be logging in as their postgres username 3) 123.45.678.1 is a secure box, and needs to access database tables and such... as such it should be able to connect to and do whatever as needed... trusted 4) no one else should from any other machine should be able to access any database resources on this server mods, misconceptions, problems? thanks Dave ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] logic check of pg_hba.conf configuration
>> running version 7.0 >7.0? Not even 7.0.something? You really ought to update. Havn't seen anyting critical requiring an upgrade... call it laziness or not wanting to fix something that isn't broke and causing problems. This security issue may end up being that reason though. >But I digress... ditto... ok, let me step back and come at it this way (at the risk of a RTFM which has been done) Authentication/Security Goals Assuming I want to allow postgres to start up unattended at startup (FreeBSD) so local machine needs to be trusted or the startup script chokes. (recent failure of boot scripts was as a result of us changing everything to password). Startup does an su to user pgsql to run the pg_ctl to start/stop the database on reboot. Can I trust a single user (like pgsql) for this purpose? Assuming that I have multiple users, all with FTP access only (no shell accounts). I do have some of these users with postgres databases, and am managing postgres users with the same ftp username/password, and restricting databases within postgres etc... Requests for these databases will be via PHP or Perl scripts and they will be running as the web server (so user nobody/apache whatever). I want to require these users to place their postgres username and password in their PHP/Perl script in order to access ANY database, and when they provide those, they should only be able to access databases that that user has permissions to access from within postgres... no automatic or passwordless access. Easy to secure the username and passwords for accessing the database with unix file permissions and keeping them out of the web root. I need access to all databases from the 123.45.678.1 server... can provide a username and password since they are scripted items so it doesn't necessarily HAVE to be trust'ed, we can secure the scripts appropriately (probably better than trusting anything anyway). This server is the ONLY server currently that needs to access any database from outside the postgres server itself. If we add others in the future it would be to specific databases and we would probably use the same password as we would with the 123.45.678.1 server since these would be exceptions to the rule. The permissions just don't seem to be designed around that sort of flexibility/restrictions, or at least not the way I am looking at it. I do appreciate the response Tom. Dave ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] Sudden stop of postmaster - Looking for help
Hello, My RH 7.3 PGSQL 7.2 suddenly died last night and left me with no idea of what happened. I am looking for a logfile and not finding one with anything interesting in it. Any clues as to where the postmaster might've dumped a record of what happened? Here's the message returned from pg_ctl with the -s option which helped me to re-start the stubborn postmaster. Could someone help me deduce whether this is a serious issue or not please. TIA!!! bash-2.05a$ pg_ctl start -D /var/lib/pgsql/data -s pg_ctl: Another postmaster may be running. Trying to start postmaster anyway. Found a pre-existing shared memory block (ID 196608) still in use. If you're sure there are no old backends still running, remove the shared memory block with ipcrm(1), or just delete "/var/lib/pgsql/data/postmaster.pid". pg_ctl: cannot start postmaster Examine the log output. bash-2.05a$ rm /var/lib/pgsql/data/postmaster.pid bash-2.05a$ pg_ctl start -D /var/lib/pgsql/data -s bash-2.05a$ DEBUG: database system was interrupted at 2003-03-17 21:39:38 UTC DEBUG: checkpoint record is at 0/DC2A414 DEBUG: redo record is at 0/DC2A414; undo record is at 0/0; shutdown FALSE DEBUG: next transaction id: 1081353; next oid: 37710 DEBUG: database system was not properly shut down; automatic recovery in progress DEBUG: ReadRecord: record with zero length at 0/DC2A454 DEBUG: redo is not required DEBUG: database system is ready DAVE ---(end of broadcast)--- TIP 3: 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] Trojan Alert
Avi Schwartz wrote: > According to my virus scanner, the message from Wuttipong Suvaphrom > <[EMAIL PROTECTED]> titled "v7.0.3 on Solaris 2.7" contained the > "TR.Worm.Navidad" Trojan. Be carefull: > > - log file begin - > info: extracting attachment 1 to /var/tmp/avVBIA4R/av-0 > (encoding="quoted-printable", name="(no name)", filename="(no name)") > info: extracting attachment 2 to /var/tmp/avVBIA4R/av-1 > (encoding="base64", > name="Navidad.exe", filename="Navidad.exe") > checking file "/var/tmp/avVBIA4R/av-0" > checking file "/var/tmp/avVBIA4R/av-1" > VIRUS! the file "/var/tmp/avVBIA4R/av-1" contains code of > "TR.Worm.Navidad" > - log file end - > > Thanks, > Avi > -- > Avi Schwartz > [EMAIL PROTECTED] Just got this one too, although in one of its alternative incarnations! The attached mail has been found to contain a virus Originally /usr/sbin/scanmails -f [EMAIL PROTECTED] -Y -a -d dmill The mail has been stored as /var/virusmails/root/virus-20001124-5063 xxFri Nov 24 13:58:34 GMT 2000xxx scanmails (0.2.1) called -f [EMAIL PROTECTED] -Y -a -d dmill FROM: [EMAIL PROTECTED] TO: dmill /var/tmp/scanmails5063/unpacked/SFX: total 2 drwxr-xr-x2 root root 1024 Nov 24 13:58 . drwxr-xr-x3 root root 1024 Nov 24 13:58 .. Scanning /var/tmp/scanmails5063/unpacked/* Scanning file /var/tmp/scanmails5063/unpacked/mm.VBPik2 Scanning file /var/tmp/scanmails5063/unpacked/Navidad.exe /var/tmp/scanmails5063/unpacked/Navidad.exe Found the W32/Navidad@M trojan !!! Info on the virus can be found at: http://vil.nai.com/vil/dispVirus.asp?virus_k=98881 Looks like it could be nasty if you run Outlook on a Whinedoze PC. Watch your backs! Dave -- He was part of my dream, of course -- but then I was part of his dream too. -- Lewis Carroll email: [EMAIL PROTECTED] web1 : www.largesalad.co.uk web2 : www.p21.co.uk
[ADMIN] Installation and execution under Jail'ed virtual machine
Question probably steps beyond the general list... Have a FreeBSD 4.2 box... have created two Virtual machines with the jail process. Apache and PHp configure and execute fine, but am having some cinfiguration and execution problems with pgsql... not sure if I am missing something or if pgsql doesn't like being in the jail Attempting to manually start the postmaster produces the following: %postmaster -i IpcMemoryCreate: shmget failed (Function not implemented) key=5432010, size=144, permission=700 This type of error is usually caused by an improper shared memory or System V IPC semaphore configuration. For more information, see the FAQ and platform-specific FAQ's in the source directory pgsql/doc or on our web site at http://www.postgresql.org. IpcMemoryIdGet: shmget failed (Function not implemented) key=5432010, size=144, permission=0 IpcMemoryAttach: shmat failed (Function not implemented) id=-2 FATAL 1: AttachSLockMemory: could not attach segment % Searching for jail faq's and posts regarding this matter produces some close, but not helpful posts regarding chroot'ed environments. Any insights, requests for details, and assistance are appreciated. Dave
RE: [ADMIN] select * from pgadmin_users; causes error
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED]] > Sent: 23 February 2001 22:29 > To: Dave Page > Cc: [EMAIL PROTECTED]; 'PostgreSQL Admin News' > Subject: Re: [ADMIN] select * from pgadmin_users; causes error > > > Dave Page <[EMAIL PROTECTED]> writes: > > Basically it isn't so much as the user ID that created > > the views that is an issue, it's that fact that pgAdmin > >> then didn't issue a > >> 'GRANT ALL ON pgadmin_users TO PUBLIC'. > >> > >> The particular case being complained of here would not be > >> fixed by that. > >> > > > What exactly was the problem? > > The problem was that pgadmin was first started in a > particular database > by a non-privileged user. So it created the pgadmin_users > view as owned > by that non-privileged user. Then the view does not work, > even for the > superuser, because its attempt to access pg_shadow is checked > under the > permissions of its owner not of the invoker. Hmm yes. I've just been playing with it and that is exactly the case. > Deleting and recreating the view (to make it owned by the superuser) > will fix this problem, and I'm glad to hear that pgadmin provides a > reasonably painless way to do that. But the average Joe > isn't going to > realize what the problem is or what he has to do to fix it. It'd be > better if the problem couldn't occur in the first place. If > you really > need a view on pg_shadow, can you postpone creating it until > you are run > as superuser? That shouldn't be a major problem, I'll look into it for the next release. Regards, Dave.
RE: [ADMIN] select * from pgadmin_users; causes error
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED]] > Sent: 23 February 2001 15:23 > To: [EMAIL PROTECTED] > Cc: 'PostgreSQL Admin News'; Dave Page > Subject: Re: [ADMIN] select * from pgadmin_users; causes error > > > John Hatfield <[EMAIL PROTECTED]> writes: > > It looks as though the views are created the first time you > login to > > pgAdmin. So when I logged first as an ordinary user not as > the postgres > > (database superuser), the views were created with this user > as the owner. > > > A trap for beginners!! > > Indeed. I wonder whether pgadmin actually needs to see the password > column. If not, perhaps it could make a view of pg_user instead of > pg_shadow to avoid this problem. If it does, selecting directly from > pg_shadow with no view seems like the most reliable way... > > regards, tom lane pgAdmin uses the view for a couple of reasons: 1) It is one of a set of views that are created and upgraded as required by pgAdmin to simplify updating pgAdmin if the PostgreSQL system tables change from version to version (there are loads of queries in pgAdmin, so upgrading for a new release is far easier if I just have to update one set of views). 2) pgadmin_users includes the OID of the user tuple in pg_users which is not in pg_shadow. The problem in question has been fixed in the current dev code of pgAdmin (as it has been noted as an issue by a couple of other users), soon to be released as v7.1.0. Basically it isn't so much as the user ID that created the views that is an issue, it's that fact that pgAdmin then didn't issue a 'GRANT ALL ON pgadmin_users TO PUBLIC'. It may help to know that all pgAdmin created SSOs (we dubbed them Server Side Objects 'cos it sounded good!) can be dropped and recreated using the options on the Advanced Menu. This may be useful when preparing a finished database for release to a customer for example. Regards, Dave.
RE: [ADMIN] select * from pgadmin_users; causes error
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED]] > Sent: 23 February 2001 21:07 > To: Dave Page > Cc: [EMAIL PROTECTED]; 'PostgreSQL Admin News' > Subject: Re: [ADMIN] select * from pgadmin_users; causes error > > > Dave Page <[EMAIL PROTECTED]> writes: > > Basically it isn't so much as the user ID that created > > the views that is an issue, it's that fact that pgAdmin > then didn't issue a > > 'GRANT ALL ON pgadmin_users TO PUBLIC'. > > The particular case being complained of here would not be > fixed by that. > What exactly was the problem? I must have missed an earlier part of the thread as I don't read pgsql-admin - I barely get time to read ODBC & Interfaces :-( Regards, Dave.
Re: [HACKERS] Re: [ADMIN] v7.1b4 bad performance
On Fri, Feb 23, 2001 at 01:09:37PM +0200, Hannu Krosing wrote: > Dmitry Morozovsky wrote: > > > DM> I just done the experiment with increasing HZ to 1000 on my own machine > > DM> (PII 374). Your test program reports 2 ms instead of 20. The other side > > DM> of increasing HZ is surely more overhead to scheduler system. Anyway, it's > > DM> a bit of data to dig into, I suppose ;-) > > DM> > > DM> Results for pgbench with 7.1b4: (BTW, machine is FreeBSD 4-stable on IBM > > DM> DTLA IDE in ATA66 mode with tag queueing and soft updates turned on) > > Is this unmodified pgbench or has it Hiroshi tweaked behaviour of > connecting each client to its own database, so that locking and such > does not shade the possible benefits (was it about 15% ?) of delay>1 > > also, IIRC Tom suggested running with at least -B 1024 if you can. Just try this: explain select * from where = (Use for fieldname an indexed field). If postgres is using an sequential scan in stead of an index scan. You have to vacuum your database. This will REALLY remove deleted data from your indexes. Hope it will work, Dave Mertens System Administrator ISM, Netherlands
Re: [ADMIN] Computer with Linux OS not shown on Computers with Windows OS
If you want to see your linux box in network neighbourhood, you have to run samba on the linux box! See: http://www.samba.org regards, Dave Silky Mittal wrote: > > Dear Sir, > Thanks for your precious advice now things have > started working but now we are facing other problem. > When we give the command "nmblookup -d 2 '*' it shows > IP address of some of the computers in the network and > not all and also when we check in network > neighbourhood of other computers having Win95 OS our > Linux computer is not shown.Looking forward for the > early response . Help will be appreciated. > Thanking You, > Regards, > Silky. > --- Oliver Elphick <[EMAIL PROTECTED]> wrote: -- He was part of my dream, of course -- but then I was part of his dream too. -- Lewis Carroll email: [EMAIL PROTECTED] web1 : www.largesalad.co.uk web2 : www.p21.co.uk ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[ADMIN] Irrevocable privileges
Title: Irrevocable privileges I think it's silly that any privileges that an owner grants to himself are essentially irrevocable. It's silly because it makes changing the object owner pointless. Consider: User joe creates table foo User joe grants permission rw to himself on foo User joe decides that user bob should really be the owner of foo User joe revokes his permissions, alters foo to be owned by bob, and gives bob rw privilege User joe is annoyed to find out that his privileges are in a state of limbo The only way to fix this is to do a backup/restore. It seems to me that owner privs should be revokable in a transaction where the owner is changed to a different user. That way, joe doesn't leave behind a trail of mess when he decides to change ownership of objects. __ David B. Held Software Engineer/Array Services Group 200 14th Ave. East, Sartell, MN 56377 320.534.3637 320.253.7800 800.752.8129
Re: [ADMIN] Irrevocable privileges
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: Tuesday, May 10, 2005 6:11 PM > To: Dave Held > Cc: [email protected] > Subject: Re: [ADMIN] Irrevocable privileges > > > "Dave Held" <[EMAIL PROTECTED]> writes: > > I think it's silly that any privileges that an owner grants > to himself = > > are essentially irrevocable. > > Say again? An owner can certainly revoke his own ordinary privileges. Not completely. > [...] > Please define "state of limbo". CREATE TABLE foo (); GRANT ALL ON foo TO postgres; REVOKE ALL ON foo FROM postgres; \z foo Access privileges for database "production" Schema | Table | Access privileges +---+- public | foo | {postgres=***/postgres} (1 row) I don't know what a permission of "*" means, so that's what I call "limbo". While this isn't a very useful thing to be able to do, the following is: CREATE TABLE foo (); GRANT ALL ON foo TO postgres; BEGIN; REVOKE ALL ON foo FROM postgres; ALTER TABLE foo OWNER TO joeuser; GRANT ALL ON foo TO joeuser; COMMIT; Essentially, this is an attempt to change ownership from postgres to joeuser, without leaving any residual permissions from postgres on the table. > Also note that if user joe is able to do "ALTER OWNER" then he must be a > superuser, and hence not subject to access controls in the first place. Ah, except for this blurb in the documentation: "If a superuser chooses to issue a GRANT or REVOKE command, the command is performed as though it were issued by the owner of the affected object. In particular, privileges granted via such a command will appear to have been granted by the object owner." http://www.postgresql.org/docs/7.4/static/sql-grant.html So whether I'm joe or whether I'm postgres (which is superuser on my db), I can't revoke postgres' permissions. That's because if postgres is the owner, it can't revoke its own permissions, and if joe is the owner, it attempts to revoke *as joe*, which also fails, *even if joe is a superuser*, because joe didn't grant the permissions to begin with. > I do recall that we recently (probably in 8.0) fixed some issues with > what ALTER OWNER does with existing privileges. What version are you > testing? 7.4 __ David B. Held Software Engineer/Array Services Group 200 14th Ave. East, Sartell, MN 56377 320.534.3637 320.253.7800 800.752.8129 ---(end of broadcast)--- TIP 3: 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] how do i kill user sessions?
Or, Kill -s SIGINT Thanks, Anjan -Original Message- From: Gourish Singbal [mailto:[EMAIL PROTECTED] Sent: Friday, May 13, 2005 9:27 AM To: Ing. Jhon Carrillo Cc: [email protected] Subject: Re: [ADMIN] how do i kill user sessions? you can try. 1) ps -ef | grep "postgres" see which users are connectioned and than. 2) pkill -f 'postgres: postgres '; its more safer i suppose than kill -9 regards Gourish On 5/12/05, Ing. Jhon Carrillo <[EMAIL PROTECTED]> wrote: > I have a problem with the users administration. When I want to erase (drop) > some databases there's an error: ** database "name_db" is being accessed by > other users.** I want to kill the user sessions conected but i don't know > how to do it (Kill the user sessions). > > thanks. -- Best, Gourish Singbal ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Killing a Query
I use kill -s SIGINT Thanks, Anjan -Original Message- From: Brandon Hines [mailto:[EMAIL PROTECTED] Sent: Friday, May 13, 2005 2:32 PM To: [email protected] Subject: [ADMIN] Killing a Query Is there a method for killing a query from the server. I have a number of users that manage to run queries that busy the server preventing a number of critical queries from running within an acceptable time limit. I would like to kill off the queries that appear to be busying the server without having to take postgres down altogether. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] SQL logging (log_min_duration_statement) not working
For logging the queries taking 5+ seconds, try this:
In postgresql.conf:
syslog = 0# range 0-2; 0=stdout; 1=both; 2=syslog
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
log_min_duration_statement = 5000
Then, in your pg startup script (etc/init.d/Postgresql), you want to
have something like PGLOG variable...and change the startup command so
stuff goes into this file:
PGLOG=/var/log/pglogs
export PGLOG
$SU -l postgres -c "$PGENGINE/initdb --pgdata=$PGDATA" >> $PGLOG 2>&1
< /dev/null
$SU -l postgres -c "$PGENGINE/postmaster -p ${PGPORT} -D '${PGDATA}'
${PGOPTS} &" >> $PGLOG 2>&1 < /dev/null
Note, you need to stop|start the database for all this, and do copy your
original init.d/Postgresql and the data/Postgresql.conf files first.
Thanks,
Anjan
-Original Message-
From: Scott Marlowe [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 18, 2005 11:52 AM
To: Pallav Kalva
Cc: [email protected]
Subject: Re: [ADMIN] SQL logging (log_min_duration_statement) not
working
On Wed, 2005-05-18 at 09:49, Pallav Kalva wrote:
> Scott Marlowe wrote:
>
> >On Wed, 2005-05-18 at 08:35, Pallav Kalva wrote:
> >
> >
> >>Hi Everybody,
> >>
> >>
> >>I am having problems with logging long running SQL in the log
file.
> >>I have set log_min_duration_statement = 5000 (5secs) and it is not
> >>logging sql statements that took over 5 secs and this is only from
> >>queries generated by Hibernate . here is a sample query from
hibernate :
> >>
>
>>--
---
> >>select listingadd1_.addressid as col_0_0_
> >>from listing.listing listing0_, listing.address listingadd1_
> >>where ((select count(taxrolls2_.fkaddressid)
> >> from listing.taxroll taxrolls2_
> >> where listingadd1_.addressid=taxrolls2_.fkaddressid)=0 )
> >> and(listing0_.fkbestaddressid=listingadd1_.addressid )
> >> and(listing0_.entrydate>$1 )
> >>order by listingadd1_.addressid asc
>
>>--
--
> >>
> >> This above query take more than 5 secs to run and it wont get
logged ,
> >>where as all other queries which are not generated by Hibernate are
> >>logged. Is this expected behavior ? am I missing something else ?
> >>Please advice.
> >>
> >>
> >
> >Are you sure it's taking more than 5 seconds to run, not that it's
> >taking <5 seconds to run and >5 seconds to return the data. I'm
pretty
> >sure the time it takes to ship out the data isn't counted in the time
> >used to decide whether to log the query or not by the postmaster.
> >
> >I.e. what does explain analyze QUERY HERE say about the execution
time?
> >
> >
> >
> Hi Scott,
>
> Thanks! for the reply. The query for sure runs for more than 5
> secs, I passed the same value again and ran the query in the psql
with
> explain analyze .
>
> Here is the explain analyze output :
SNIP
>-> Nested Loop (cost=0.00..17503496.52 rows=678 width=4) (actual
> time=5.185..31575.629 rows=398459 loops=1)
SNIP
> Total runtime: 38026.606 ms
> (13 rows)
OK, two problems here, I assume. One is your logging is dodgy right
now. Is postgresql logging anything else? Are you sure your logs are
going where you think they are, etc?
Secondly, it looks from the nested loop part of your explain analyze
that there's a part where the query planner THINKS there's gonna be 678
rows, but actually gets 398459, that's a miscalculation by a factor of
about 600. Since there's no functions or anything I recognize as being
a common cause of such a mis-estimation, I'd guess that either you need
to analyze, or if you have analyzed, you may need to alter the stats
target for the column entrydate.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [ADMIN] startup subprocess hangs
Probably some kind of a hardware issue (you lost a path to the array for e.g.), or someone may have issued a kill -9 to postmaster? Open another terminal and check the system log (/var/log/messages) if you are not redirecting logs anywhere. Can you paste the console messages on the list so people can see what exactly might be going on? Thanks, Anjan From: Andrew Janian [mailto:[EMAIL PROTECTED] Sent: Friday, June 17, 2005 4:21 PM To: [email protected] Subject: [ADMIN] startup subprocess hangs I am running Postgres 7.4.5 and I have recently run into a problem. My database was running fine for days and then today in the middle of the day it seems that the startup subprocess kicked in and started recovering. I am not sure from what, and I am not sure how it started, but nothing was allowed to connect and it has been running for quite some time (30 minutes). The process said it was starting to recover, but how long am I to wait? Is there a way to get the status? Can I just skip the recovery and get it going? The environment is RH EL 3 2.4.21-4.ELsmp, with and EMC SAN for storage and QLogix cards for fibre access. Thanks, Andrew
Re: [ADMIN] startup subprocess hangs
Without any actual messages, not sure what to say in regards to whether it is still recovering, or finished recovering successfully (from the logs). There are a few ways to check if the postmaster is up. A simple check is to try a psql and see if you’re up. Thanks, Anjan From: Andrew Janian [mailto:[EMAIL PROTECTED] Sent: Friday, June 17, 2005 4:37 PM To: Anjan Dave; [email protected] Subject: RE: [ADMIN] startup subprocess hangs Do you want me to stop the recovery to get the console messages? After issuing the start command I did not capture the logs because I did not notice a problem right away. Now I cannot see them anymore. Is there anything I can do? The database is 99GB with almost all the data in one table. How long should the recovery process take? Thanks, Andrew From: Anjan Dave [mailto:[EMAIL PROTECTED] Sent: Friday, June 17, 2005 3:32 PM To: Andrew Janian; [email protected] Subject: RE: [ADMIN] startup subprocess hangs Probably some kind of a hardware issue (you lost a path to the array for e.g.), or someone may have issued a kill -9 to postmaster? Open another terminal and check the system log (/var/log/messages) if you are not redirecting logs anywhere. Can you paste the console messages on the list so people can see what exactly might be going on? Thanks, Anjan From: Andrew Janian [mailto:[EMAIL PROTECTED] Sent: Friday, June 17, 2005 4:21 PM To: [email protected] Subject: [ADMIN] startup subprocess hangs I am running Postgres 7.4.5 and I have recently run into a problem. My database was running fine for days and then today in the middle of the day it seems that the startup subprocess kicked in and started recovering. I am not sure from what, and I am not sure how it started, but nothing was allowed to connect and it has been running for quite some time (30 minutes). The process said it was starting to recover, but how long am I to wait? Is there a way to get the status? Can I just skip the recovery and get it going? The environment is RH EL 3 2.4.21-4.ELsmp, with and EMC SAN for storage and QLogix cards for fibre access. Thanks, Andrew
[ADMIN] Temporary Tables
Hi, A developer is trying to use a Temporary Table via the java based application. The problem is following the temp table creation, he wants subsequent queries to query the temp table, but they are not in the same database session as the temp table. Is this something I can setup somewhere in the config? I would appreciate any pointers/info I can forward to him. Thanks, Anjan **This e-mail and any files transmitted with it are intended for the use of the addressee(s) only and may be confidential and covered by the attorney/client and other privileges. If you received this e-mail in error, please notify the sender; do not disclose, copy, distribute, or take any action in reliance on the contents of this information; and delete it from your system. Any other use of this e-mail is prohibited.**
Re: [ADMIN] Postgresql 8.0.3, symbolic link to /var/lib/pgsql/data/base
Why don't you copy/move the entire data directory (minus pg_xlog if you are separating them) -Original Message- From: Sergiusz Jarczyk [mailto:[EMAIL PROTECTED] Sent: Wed 9/28/2005 3:34 PM To: [email protected] Cc: Subject: Re: [ADMIN] Postgresql 8.0.3, symbolic link to /var/lib/pgsql/data/base Hi Jeffrey Jeffrey Tenny wrote: > I'm trying to move my databases to another drive. > > With postmaster off, I create: > > /home/pgsql > > mv /var/lib/pgsql/data/base /home/pgsql/ > > > I make sure that /home/pgsql is owned by postgres, and only 'rwx' for > the owner. All the permissions look fine. > > from /var/lib/pgsql/data, I do > > ln -s /home/pgsql/base > > Of course the link permissions look like: > > lrwxrwxrwx 1 postgres postgres 16 Sep 28 14:21 base -> /home/pgsql/base > > > The postmaster starts, but attempts to access the database complain > that permission is denied: > > $ psql -l > psql: FATAL: could not access directory "/var/lib/pgsql/data/base/1": > Permission denied > > > Any clues about why this won't work? > I've searched the archives, but most references were to invalid > permissions on the directories and I'm pretty sure I've got those right. > > All my pgsql dirs look pretty much like: > drwx-- 3 postgres postgres 4096 Sep 28 14:21 pgsql > > Both in the original /var/lib/pgsql area, and the /home/pgsql area. > Try to issue chown -R postgres.postgres /home/pgsql Sergiusz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] [pgadmin-hackers] pgAdmin guru hints
Thanks - fix committed to svn. Regards, Dave From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bastiaan WakkieSent: 03 October 2005 17:04To: Andreas PflugCc: PostgreSQL-development; pgadmin-hackers; [email protected]: Re: [pgadmin-hackers] pgAdmin guru hints I noticed a typo in hints/conn-hba.htmlThe second internal ip adres missed a '.' You're invited to make your comments on the hints: are these correct, is there something missing or misleading? Cheers, -- ^(B(astia{2}n)?)(\s)?(W(ak{2}ie)?)$
Re: [ADMIN] Urgent!
I've been through a similar situation before (partition was full, and subsequently, PG wasn't able to access a pg_clog/. Tom Lane had referred me to another archive about zeroing out that particular file, which had worked for me and the database was up. And yes, don't shutdown the machine like that. Thanks, Anjan -Original Message- From: codeWarrior [mailto:[EMAIL PROTECTED] Sent: Thursday, October 13, 2005 11:31 AM To: [email protected] Subject: Re: [ADMIN] Urgent! Lookas as if you've managed to turn off your computer mid-transaction thereby corrupting the postgreSQL commit logs (pg_clog)... You should never just turn off a database server... always shut it down normally... Turning it off was a major mistake. I dont know if you can recover or not as the system (postgreSQL) now thinks it is in the middle of a transaction... As far as recovery goes... You might try a vacuum full and pg_resetxlog (search the postgreSQL docs for pg_resetxlog)... Other than that you might need to get Tom Lane's attention on this one... ""Nirav Parikh"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] ls.com.au... Hi, I got this error message when I tried to do pg_dump on the database. pg_dump: ERROR: invalid memory alloc request size 4294967293 pg_dump: SQL command to dump the contents of table "wordlist" failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: invalid memory alloc request size 4294967293 pg_dump: The command was: COPY public.wordlist (word_id, word, count_word) TO stdout; I went to phppgadmin and try to access the worldlist table and it also gives me following error message: ERROR: could not access status of transaction 6849340 DETAIL: could not open file "/var/lib/pgsql/data/pg_clog/0006": No such file or directory In statement: SELECT COUNT(*) AS total FROM (SELECT "oid", * FROM "public"."wordlist") AS sub CPU was heating up so I turn off the computer and to fix the problem and after that this occour. I don't know if this has anything to with database corruption. This is production server and I don't have latest backup does anyone how to resolve this. Please Help Regards, Nirav Parikh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] [webmaster] How can I join in the PostgreSql developing team ?
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Qiu LeiSent: 07 November 2005 09:12To: [EMAIL PROTECTED]; [email protected]: [webmaster] How can I join in the PostgreSql developing team ? Dear webmaster: I am glad to write to you ! I am a programmer at NEC. Recently, I have interests in PostgreSql and want to be a member of the developing team. Can I join in the team ? And how ? Hope to receive your letters ! Yours,sincerely Sam Hi Sam, Anyone is welcome to join the development team if they feel they can contribute :-). The first step is to subscribe to the [email protected] mailing list (see http://www.postgresql.org/community/lists/) and get a feel for the atmosphere and current work. We're a day away from release at the moment, so today and tomorrow might be a little different from normal! The next step is to visit http://www.postgresql.org/developer/, and have a read of the material there, particularly the developers FAQ. Next, have a search through http://archives.postgresql.org/ to see if there has been any previous discussion on the feature you'd like to work on. When you think you're ready to go, send an email to the hackers list explaining what you'd like to work on, and include any initial ideas you might have had, and where appropriate, references to anything you found in the archives of relevance. Quite possibly discussion of your proposed work will follow, and any design issues will be worked out, and you can get coding. Once you think the job is done, simply post a patch in diff -c format to [email protected] for community review. Don't be surprised or upset if the first couple get rejected - this is perfectly normal, even for seasoned hackers; just fix whatever people objected to and re-post. Hope this helps (and doesn't put you off)! Regards, Dave
Re: [ADMIN] PG 8.1.1 Cannot allocate shared_buffers memory error
Not sure if the kernel shared memory segment size is appropriate, you are allocating all your memory to the chunk. With 32GB memory, effective_cache_size can be raised, higher, and you can go down on shared_buffers. Check this: http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html -anjan -Original Message- From: Tomeh, Husam [mailto:[EMAIL PROTECTED] Sent: Wed 12/14/2005 5:08 PM To: [email protected] Cc: Tom Lane Subject: [ADMIN] PG 8.1.1 Cannot allocate shared_buffers memory error I just installed postgres 8.1.1 on a dual optron Linux box running Linux 4 advanced server, with 32GB of RAM and 2GB of swap. I'm trying to allocate 24 GB as shared_bufferes. I'm getting an error that I can not allocate memory (see below). If I change my shared_buffers to 16GB, postgres starts up with no issue. I'm trying to understand why I'm getting the shared memory error at sizes above 16GB whilst I have 32GB with appropriate (I think) settings of kernel parameters. Any help is appreciated. Thanks... My sysctl.conf has : kernel.shmmax = 34359738368 kernel.shmall = 34359738368 (32GB). And here's some of related postgresql.conf parameters: shared_buffers = 2359296 work_mem = 16384 maintenance_work_mem = 2097152 max_connections = 100 effective_cache_size = 2621440 --- Error: FATAL: could not create shared memory segment: Cannot allocate memory [2005-12-14 13:56:03 PST|||] DETAIL: Failed system call was shmget(key=5432001, size=19760111616, 03600). [2005-12-14 13:56:03 PST|||] HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 19760111616 bytes), reduce PostgreSQL's shared_buffers parameter (currently 2359296) and/or its max_connections parameter (currently 100). --- Regards, Husam Tomeh ** This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged. If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited. If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter. Thank you. FADLD Tag ** ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] startup scripts
Check in the contrib directory of the source tree. Techdocs.postgresql.org site also has some to download I believe. -anjan From: Laureen Lampe [mailto:[EMAIL PROTECTED] Sent: Friday, December 16, 2005 12:01 PM To: [email protected] Subject: [ADMIN] startup scripts From where can I download a startup script for postgresql to put in /etc/init.d? Thanks, Laureen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Laureen Lampe Database Administrator Symyx IntelliChem 755 SW Bonnett Way, Suite 2100 Bend, OR 97702 USA 541-382-7043 x140 877-673-1792 FAX [EMAIL PROTECTED] www.intellichem.com, www.symyx.com . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . === Notice: This e-mail message, together with any attachments, contains information of Symyx Technologies, Inc. that may be confidential, proprietary, copyrighted, privileged and/or protected work product, and is meant solely for the intended recipient. If you are not the intended recipient, and have received this message in error, please contact the sender immediately, permanently delete the original and any copies of this email and any attachments thereto.
[ADMIN] effects of cancelling a vacuum full
Hi, I just had to cancel one VACUUM FULL (of a 4.3m row table) that someone ran in business hours. It was after an hour or so that I realized, and it was blocking queries, running out of connections, so I cancelled it. Are there any bad side effects of cancelling an ongoing full vacuum of a table or a database? Does a partial full vacuum give back any space? Thanks,Anjan This email message and any included attachments constitute confidential and privileged information intended exclusively for the listed addressee(s). If you are not the intended recipient, please notify Vantage by immediately telephoning 215-579-8390, extension 1158. In addition, please reply to this message confirming your receipt of the same in error. A copy of your email reply can also be sent to mailto:[EMAIL PROTECTED]. Please do not disclose, copy, distribute or take any action in reliance on the contents of this information. Kindly destroy all copies of this message and any attachments. Any other use of this email is prohibited. Thank you for your cooperation. For more information about Vantage, please visit our website at http://www.vantage.com.
[ADMIN] Trouble installing on XP home
I'm trying to install postgresql on my laptop, windows XP home edition sp2. can't install as a service: I either have too much permission or not enough... Here are the steps I performed and the generated messages: 1)I ran the install program (postgresql-8.1) the data directory chosen is a folder located in a NTFS logical drive (F:\PostgreSQL\), the remainder of the drive being of FAT type. error message: "Failed to run initdb: 1!" the initdb log reads as follows: The files belonging to this database system will be owned by user "postgresusr". This user must also own the server process. The database cluster will be initialized with locale C. fixing permissions on existing directory F:/PostgreSQL ... initdb: could not change permissions of directory "F:/PostgreSQL": Permission denied 2) I ran the install again, but this time uncheck 'install as service' I opened a Command Prompt and executed the following: cd \program files\postgresql\8.1\bin initdb --locale=C --username=postgres -W -A md5 -E UNICODE -D f:\postgresql I get the following: The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale C. fixing permissions on existing directory f:/postgresql ... ok creating directory f:/postgresql/global ... ok creating directory f:/postgresql/pg_xlog ... ok creating directory f:/postgresql/pg_xlog/archive_status ... ok creating directory f:/postgresql/pg_clog ... ok creating directory f:/postgresql/pg_subtrans ... ok creating directory f:/postgresql/pg_twophase ... ok creating directory f:/postgresql/pg_multixact/members ... ok creating directory f:/postgresql/pg_multixact/offsets ... ok creating directory f:/postgresql/base ... ok creating directory f:/postgresql/base/1 ... ok creating directory f:/postgresql/pg_tblspc ... ok selecting default max_connections ... 10 selecting default shared_buffers ... 50 creating configuration files ... ok creating template1 database in f:/postgresql/base/1 ... Execution of PostgeSQL by a user with administrative permissions is not permitted. The server must be started under an unpriviledged user ID to prevent possible system security compromises. See the documentation for more information on how to properly start the server. child process was terminated by signal 1 initdb: removing contents of data directory "f:/postgresql" --- 3)so I try the following command: runas /user:postgres cmd then run initdb again as previously and get the following: --- The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale C. fixing permissions on existing directory f:/postgresql ... initdb: could not change permissions of directory "F:/PostgreSQL": Permission denied' --- I had no trouble installing on Win XP Pro on my desktop, but am stumped on XPhome. Can anyone push me in the right direction? Thanks for your time :-)Dave -- ___ Play 100s of games for FREE! http://games.mail.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[ADMIN] invalid multibyte character for locale
Hi, Recently I restored a database from 7.4.1 (RHAS3.0) to 8.0.3 (RHAS4.0), and now we are running into locale issues: [22:03] WARN SECUserDAO () - Problem retrieving user by username. org.sourceforge.jxutil.sql.I18nSQLException: sqlError[invalid multibyte character for locale] … ERROR: invalid multibyte character for locale HINT: The server's LC_CTYPE locale is probably incompatible with the database e ncoding. ERROR: invalid multibyte character for locale HINT: The server's LC_CTYPE locale is probably incompatible with the database e ncoding. The original database is in UNICODE, and so is the new database, on 8.0.3. I am reading the pg doc about locale and character set encoding, but with the source and target database having been created with same encoding, I am not sure why the LC_CTYPE may not be compatible with UNICODE. Any help is appreciated. Thanks, Anjan This email message and any included attachments constitute confidential and privileged information intended exclusively for the listed addressee(s). If you are not the intended recipient, please notify Vantage by immediately telephoning 215-579-8390, extension 1158. In addition, please reply to this message confirming your receipt of the same in error. A copy of your email reply can also be sent to mailto:[EMAIL PROTECTED]. Please do not disclose, copy, distribute or take any action in reliance on the contents of this information. Kindly destroy all copies of this message and any attachments. Any other use of this email is prohibited. Thank you for your cooperation. For more information about Vantage, please visit our website at http://www.vantage.com.
Re: [ADMIN] problems to install PostgreSQL
Can also use a startup script from the contrib directory of the source. -Original Message- From: sandhya [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 21, 2006 7:14 AM To: Paolo De Simone; [email protected] Subject: Re: [ADMIN] problems to install PostgreSQL Instead of executing the command from root ...login as postgres user and try like su - postgres - Original Message - From: "Paolo De Simone" <[EMAIL PROTECTED]> To: Sent: Tuesday, February 21, 2006 5:34 PM Subject: [ADMIN] problems to install PostgreSQL > Hello all, > > i'm trying to install postgresql 8.1.3 > on AIX 5.2 RISC 6000 > > the steps > - ./configure > - gmake > - gmake install > > are OK > > so i created /usr/local/pgsql/data > but when ran > - initdb -D /usr/local/pgsql/data > > had the following result : > > " > The files belonging to this database system will be owned by user > "postgres". > This user must also own the server process. > > The database cluster will be initialized with locale en_US. > The default database encoding has accordingly been set to LATIN1. > > fixing permissions on existing directory /usr/local/pgsql/data ... ok > creating directory /usr/local/pgsql/data/global ... ok > creating directory /usr/local/pgsql/data/pg_xlog ... ok > creating directory /usr/local/pgsql/data/pg_xlog/archive_status ... ok > creating directory /usr/local/pgsql/data/pg_clog ... ok > creating directory /usr/local/pgsql/data/pg_subtrans ... ok > creating directory /usr/local/pgsql/data/pg_twophase ... ok > creating directory /usr/local/pgsql/data/pg_multixact/members ... ok > creating directory /usr/local/pgsql/data/pg_multixact/offsets ... ok > creating directory /usr/local/pgsql/data/base ... ok > creating directory /usr/local/pgsql/data/base/1 ... ok > creating directory /usr/local/pgsql/data/pg_tblspc ... ok > selecting default max_connections ... 10 > selecting default shared_buffers ... 50 > creating configuration files ... ok > creating template1 database in /usr/local/pgsql/data/base/1 ... child > process was terminated by signal 11 > initdb: removing contents of data directory "/usr/local/pgsql/data" > " > > I have repeated the installation steps for > 8.1.0, 8.1.1, 8.1.2 versions > but I obtained the same result > > > Many thanx to any that can help. > > > > Greetings > paolo > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > ---(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 ---(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] file too large, segmentation fault
Title: Message Hi All: I am trying to restore a database on a different machine (7.2.3 on Sol9 to 7.2.4 RH8), and i get the following error, that i haven't seen before: -bash-2.05b$ pg_restore -d access -Ft -a access.dump.tarpg_restore: [tar archiver] could not open TOC file for input: File too largeSegmentation fault-bash-2.05b$ -bash-2.05b$ ls -ltotal 2339148-rw-r--r-- 1 root root 2323785216 Nov 23 22:43 access.dump.tar The only thing i did different in this restore is that i had created two groups in the template1 because the schema needed it and the pg_dump of the schema would not do it..so i used pg_dumpall. I read somewhere that there's a patch for this: http://dbforums.com/arch/173/2003/1/675653 Will this patch fix it? Where do i get it from and how do i apply it? Thanks in advance, anjan
[ADMIN] file too large, segmentation fault
Hi All: I am trying to restore a database on a different machine (7.2.3 on Sol9 to 7.2.4 RH8), and i get the following error, that i haven't seen before: -bash-2.05b$ pg_restore -d access -Ft -a access.dump.tar pg_restore: [tar archiver] could not open TOC file for input: File too large Segmentation fault -bash-2.05b$ -bash-2.05b$ ls -l total 2339148 -rw-r--r--1 root root 2323785216 Nov 23 22:43 access.dump.tar The only thing i did different in this restore is that i had created two groups in the template1 because the schema needed it and the pg_dump of the schema would not do it..so i used pg_dumpall. I read somewhere that there's a patch for this: http://dbforums.com/arch/173/2003/1/675653 Will this patch fix it? Where do i get it from and how do i apply it? Thanks in advance, anjan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] Upgrading from 7.2.4 (RH 8) to 7.4 (RH9)
Title: Upgrading from 7.2.4 (RH 8) to 7.4 (RH9) Hi, I would like to start planning on upgrading a few servers from RH8 to RH9, essentially, also upgrading Postgres from 7.3.2 to 7.4 from the OS. There's also a box with RH7.3 (postgres 7.2.1) that could be upgraded to RH9 and Postgres 7.4. I am not sure if I should backup (pg_dump) the databases, and do a clean install of the OS, and do a pg_restore…OR, just upgrade the OS..? Are there any issues that I should be aware of? Any relevant procedure that can come handy? Thanks, Anjan ** This e-mail and any files transmitted with it are intended for the use of the addressee(s) only and may be confidential and covered by the attorney/client and other privileges. If you received this e-mail in error, please notify the sender; do not disclose, copy, distribute, or take any action in reliance on the contents of this information; and delete it from your system. Any other use of this e-mail is prohibited.
[ADMIN] GRANT in pg7.2
Title: GRANT in pg7.2 Hi, I need some help for the following: Is there a way I can assign EXECUTE privilege for a user-defined function in the GRANT command in 7.2? (7.3 has it) Also, how do I list all the user-defined functions/stored procedures. Once listed, how to give UPDATE privilege to a user on all of them? Appreciate any help. Thanks, Anjan ** This e-mail and any files transmitted with it are intended for the use of the addressee(s) only and may be confidential and covered by the attorney/client and other privileges. If you received this e-mail in error, please notify the sender; do not disclose, copy, distribute, or take any action in reliance on the contents of this information; and delete it from your system. Any other use of this e-mail is prohibited.
[ADMIN] Database taking up space rapidly
Title: Database taking up space rapidly Hi All, I just moved over a db from 7.2.3 to 7.4.0 on a new RH9 server. The dump, I remember was about 2GB, 350MB compressed, when I restored it only about 3-4 days ago… Now, the slice stands at about 30GB!! /dev/sda2 50830616 30034872 18213700 63% /var/lib/pgsql The server seems a bit slow in responding, and the Postmaster is taking up a lot of CPU. Here's where I find the bulk of the large files: # pwd /var/lib/pgsql/data/base/17142 Some of the files in this directory are: -rw--- 1 postgres postgres 1073741824 Dec 23 18:57 17610 -rw--- 1 postgres postgres 1073741824 Dec 23 22:41 17610.1 -rw--- 1 postgres postgres 1073741824 Dec 25 16:21 17610.10 -rw--- 1 postgres postgres 1073741824 Dec 25 21:57 17610.11 -rw--- 1 postgres postgres 1073741824 Dec 26 03:44 17610.12 -rw--- 1 postgres postgres 1073741824 Dec 26 09:33 17610.13 -rw--- 1 postgres postgres 1073741824 Dec 26 15:38 17610.14 -rw--- 1 postgres postgres 1073741824 Dec 26 2003 17610.15 -rw--- 1 postgres postgres 1073741824 Dec 27 2003 17610.16 -rw--- 1 postgres postgres 1073741824 Dec 26 15:06 17610.17 -rw--- 1 postgres postgres 214654976 Dec 26 16:40 17610.18 -rw--- 1 postgres postgres 1073741824 Dec 24 02:44 17610.2 -rw--- 1 postgres postgres 1073741824 Dec 24 06:55 17610.3 -rw--- 1 postgres postgres 1073741824 Dec 24 11:12 17610.4 -rw--- 1 postgres postgres 1073741824 Dec 24 15:41 17610.5 -rw--- 1 postgres postgres 1073741824 Dec 24 20:14 17610.6 -rw--- 1 postgres postgres 1073741824 Dec 25 00:55 17610.7 -rw--- 1 postgres postgres 1073741824 Dec 25 05:46 17610.8 -rw--- 1 postgres postgres 1073741824 Dec 25 10:58 17610.9 Not sure what's going on here…also vacuum analyzed the db a few minutes ago. Is there a way I can find out who is connected to the db and what process is running under the postmaster? Appreciate any help. Thanks, Anjan ** This e-mail and any files transmitted with it are intended for the use of the addressee(s) only and may be confidential and covered by the attorney/client and other privileges. If you received this e-mail in error, please notify the sender; do not disclose, copy, distribute, or take any action in reliance on the contents of this information; and delete it from your system. Any other use of this e-mail is prohibited.
Re: [ADMIN] Database taking up space rapidly
Tom, I was able to locate a bad reporting program (apparently the pg update to 7.4 caused some mismatched datatypes, i believe, from the logs.) that was causing this. I will look into pg_class for the relfilenode 17610 and others to see if i can free up the space already taken up... Thanks, Anjan -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Fri 12/26/2003 6:29 PM To: Anjan Dave Cc: [EMAIL PROTECTED] Subject: Re: [ADMIN] Database taking up space rapidly "Anjan Dave" <[EMAIL PROTECTED]> writes: > Here's where I find the bulk of the large files: > -rw---1 postgres postgres 1073741824 Dec 23 18:57 17610 > -rw---1 postgres postgres 1073741824 Dec 23 22:41 17610.1 > [ etc ] So, which table or index has relfilenode 17610 (look in pg_class)? What sorts of operations are you doing on it? > Not sure what's going on here...also vacuum analyzed the db a few > minutes ago. The question is more like how often do you run vacuum. You probably need more frequent routine vacuumings, larger FSM settings, or a combination. regards, tom lane ---(end of broadcast)--- TIP 3: 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] How to use psql -c?
Chuck, Report bugs to <[EMAIL PROTECTED]>. -bash-2.05b$ psql -c 'select version();' template1 version - PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) (1 row) -bash-2.05b$ Thanks, Anjan -Original Message- From: Charles Haron [mailto:[EMAIL PROTECTED] Sent: Monday, December 29, 2003 3:49 PM To: [EMAIL PROTECTED] Subject: [ADMIN] How to use psql -c? Hello, Can someone give me an example of how to use the -c option of psql? I can't seem to get it to work. Thanks, Chuck ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] "DELETE FROM" protection
On Friday, 20.02.2004 at 10:12 +, Matt Clark wrote: > > So now with pgsql, when I am typing "DELETE FROM" until I get to > > the "WHERE" part of the statement, I get a little nervous because I > > know hitting Enter by mistake will wipe out that table. [...] How about typing the "WHERE" part of the statement first, then 'left-arrowing' back to the start of the statement and do "DELETE FROM ..." *last*? Dave. -- Dave Ewart [EMAIL PROTECTED] Computing Manager, Epidemiology Unit, Oxford Cancer Research UK PGP: CC70 1883 BD92 E665 B840 118B 6E94 2CFD 694D E370 ---(end of broadcast)--- TIP 3: 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
[ADMIN] pg_restore data block error
Title: Message Dear All: I am trying to restore a database (size is about 7GB) from a dump file created by a simple backup script..The format is data.pg. The schema restores fine, but during the data restore, i get the following error: -bash-2.05b$ pg_restore -a -d access backups/access/data.pg pg_restore: [custom archiver] could not read data block -- expected 4096, got 3608 I tried this twice now, with backups taken on different days... I am dumping from 7.2.4 (RH8 - RAID5 set) to 7.4.0 (RH9). Any ideas please? Thanks, Anjan ** This e-mail and any files transmitted with it are intended for the use of the addressee(s) only and may be confidential and covered by the attorney/client and other privileges. If you received this e-mail in error, please notify the sender; do not disclose, copy, distribute, or take any action in reliance on the contents of this information; and delete it from your system. Any other use of this e-mail is prohibited.
Re: [ADMIN] pg_restore data block error
Title: Message Same error again. What would be the other option? Dump the tables individually and restore them? Thanks,Anjan -Original Message-From: V i s h a l Kashyap @ [Sai Hertz And Control Systems] [mailto:[EMAIL PROTECTED] Sent: Thursday, February 26, 2004 1:12 PMTo: Anjan DaveCc: [EMAIL PROTECTED]Subject: Re: [ADMIN] pg_restore data block errorDear Anjan Dave , -bash-2.05b$ pg_restore -a -d access backups/access/data.pg pg_restore: [custom archiver] could not read data block -- expected 4096, got 3608To me this seems as a error in file format.Anyways try using the followingpg_restore --disable-triggers -i -a -d access backups/access/data.pg I am dumping from 7.2.4 (RH8 - RAID5 set) to 7.4.0 (RH9).use -i switch for restore of dataKindly shootback it this helps-- Best Regards, Vishal Kashyap Director / Lead Developer, Sai Hertz And Control Systems Pvt Ltd, http://saihertz.rediffblogs.com Jabber IM: [EMAIL PROTECTED] ICQ : 264360076 --- You yourself, as much as anybody in the entire universe, deserve your love and affection. - Buddha --- I am usually called by the name Vishal Kashyap and my Girlfriend calls me Vishal CASH UP. This is because everyone loves me as Vishal Kashyap and my Girlfriend loves me as CASH. ___ //\\\ ( 0_0 ) o0o-o0o-
Re: [ADMIN] pg_restore data block error
Yes, it's about 7GB. I installed it from RPMs...Do I just copy over the pg_backup_archiver.c file? Is there any easy way to dump individual tables (dealing with 73 tables)? Thanks for your response, Anjan -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Friday, February 27, 2004 5:24 PM To: Anjan Dave Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [ADMIN] pg_restore data block error "Anjan Dave" <[EMAIL PROTECTED]> writes: > Same error again. > -bash-2.05b$ pg_restore -a -d access > backups/access/data.pg > pg_restore: [custom archiver] could not read data block > -- expected 4096, got 3608 You said the dump file was over 4Gb, right? I'm wondering if you are running into some file-offset-size bug. In particular this recent bug fix might be relevant: 2004-01-03 23:02 tgl * src/bin/pg_dump/pg_backup_archiver.c (REL7_4_STABLE): Fix ReadOffset() to work correctly when off_t is wider than int. although offhand I'm not sure how that would translate into what sounds to be an unexpected-EOF failure. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Database Encryption (now required by law in Italy)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday, 05.03.2004 at 11:10 +0200, Radu-Adrian Popescu wrote: > | While I can see how to protect a PGSQL connection using SSH > | tunneling or a VPN, I cannot see any way to encrypt a PGSQL > | database stored on a hard disk, put aside the possibility to > | encrypt the hosting file system with LoopAES. What's wrong with using a LoopAES filesystem? It protects against someone walking off with the server, or at least the hard disk, and being able to see the data. Keeping encrypted data in the database itself is also perfectly possible; you just need to decide what you're going to use to decrypt it afterwards. Dave. - -- Dave Ewart [EMAIL PROTECTED] Computing Manager, Epidemiology Unit, Oxford Cancer Research UK PGP: CC70 1883 BD92 E665 B840 118B 6E94 2CFD 694D E370 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFASEZpbpQs/WlN43ARApVHAKD+ymIbp+HAFUJwaTQ7JyUL07f3GgCeN9D7 miJut0SeQ7/16ANQf1NUUWk= =lzG/ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Database Encryption (now required by law in Italy)
On Friday, 05.03.2004 at 09:21 +, [EMAIL PROTECTED] wrote: > Is it enough to encrypt some table fields only, i.e. "personnal data" > personal(social security) number, name-surname and birth data - if one > can not easily tell whom the data(e.g. bank account) belongs to then > it may be quite enough? > > Just because "personal" data is quite a loose term: it can be all or > nearly nothing. I realise the original poster is from Italy, but in the UK there is a Data Protection Act which defines personal data. It says that personal data means "data that relate to a living individual who can be identified from those data or from those data and other information in the possession of, or likely to come into the possession of, the data controller". This seems like a good definition to me, although we extend it to also apply to people who have died. There is an extension which states that "sensitive" personal data is a subset of personal data where the data is one of several categories, such as "physical or mental health" (this is most appropriate one of for us, doing medical research). I guess there may similar legislation in Italy and elsewhere. Suggestions: 1. You need to decide which fields in the database should be encrypted. 2. Perhaps keep all 'personal' data in a separate table and control access to that table more closely with database permissions. Optionally, encrypt the contents of that table too. 3. Use a loopback-encrypted filesystem. Dave. -- Dave Ewart [EMAIL PROTECTED] Computing Manager, Epidemiology Unit, Oxford Cancer Research UK PGP: CC70 1883 BD92 E665 B840 118B 6E94 2CFD 694D E370 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] Database Encryption (now required by law in Italy)
On Friday, 05.03.2004 at 11:17 +, Matt Clark wrote: > > What's wrong with using a LoopAES filesystem? It protects against > > someone walking off with the server, or at least the hard disk, and > > being able to see the data. > > Yes, but only if the password has to entered manually [1] at boot > time. And it gives zero protection against someone who gains root > access to the server. > > [...] > > [1] There are ways of avoiding having to enter the info manually, but > they're very tricky to implement securely. Not sure I follow this - there's no point AT ALL in using LoopAES if you can mount the encrypted partitions without needing manual intervention at boot time. Dave. -- Dave Ewart [EMAIL PROTECTED] Computing Manager, Epidemiology Unit, Oxford Cancer Research UK PGP: CC70 1883 BD92 E665 B840 118B 6E94 2CFD 694D E370 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Database Encryption (now required by law in Italy)
On Friday, 05.03.2004 at 12:51 +, Silvana Di Martino wrote: > Alle 09:51, venerdì 5 marzo 2004, Dave Ewart ha scritto: > > I guess there may similar legislation in Italy and elsewhere. > > Actually, the Italian law is an implementation of a EU directive > strictly inspired to the existing UK law. It will affect all EU in a > few years. That's interesting to know, certainly. Dave. -- Dave Ewart [EMAIL PROTECTED] Computing Manager, Epidemiology Unit, Oxford Cancer Research UK PGP: CC70 1883 BD92 E665 B840 118B 6E94 2CFD 694D E370 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Database Encryption (now required by law in Italy)
On Friday, 05.03.2004 at 12:52 +, Silvana Di Martino wrote: > Deciding which data are relevant is not easy. The law stipulates that all of > the "personal data" have to be encrypted and that "personal data" are the > data that allow a "spy" to infer any of the following information about a > person: > - identity > - age > - health status > - political orientation > - religious faith > - address > - phone number > - email address > - and a few more... Interesting - of course some of the above, such as age, political orientation and religious faith are NOT personal data if the other identifying information is absent (e.g. you know that someone aged 35 is a Communist and a Christian - not terribly helpful on its own). The exception to that rule is where there are very few people having the characteristics in question. But as you say, encrypting 'the whole database' in some way is probably safest ... If you find any 'automated' front-end to do this at the database-level, rather than something like loopback at the filesystem level or at the field level for specific fields, I think there would be a lot of interest. Dave. -- Dave Ewart [EMAIL PROTECTED] Computing Manager, Epidemiology Unit, Oxford Cancer Research UK PGP: CC70 1883 BD92 E665 B840 118B 6E94 2CFD 694D E370 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] Database Encryption (now required by law in Italy)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday, 05.03.2004 at 14:47 +0100, Grega Bremec wrote: > > > [1] There are ways of avoiding having to enter the info manually, > > > but they're very tricky to implement securely. > > > > Not sure I follow this - there's no point AT ALL in using LoopAES if > > you can mount the encrypted partitions without needing manual > > intervention at boot time. > > Why not, Dave? As far as I understand it, one of the major values of > fs-level data encryption was protection from the abused data being > collected via the means of stolen laptops or even failed hard disks > that've been handed over to various data-rescue labs. Yes, that's exactly my point. Yet it sounds like you're disagreeing with me?! > If a machine is reasonably secure both locally and remotely (i.e. > secure- -enough passwords, rotated on a regular basis and frequently > enough, or even some sort of PKID mechanism such as smartcards etc. > and proper access control for local security, tight and reasonably > reliable encrypted authentication and authorization for remote use), > implementing an extra layer of any level of security is plain > senseless and simply too expensive, both in terms of CPU overhead and > thus performance drop (in the event of application-level encryption, > for example), and time dedicated to research and implementation, as > far as I'm concerned. One is better off by improving techniques in > active use, investigating for the real weaknesses of their information > system or dedicating the time to finding a better general solution > that would cover as many areas as possible in one step. I agree with this too. Yet I still get the feeling you're disagreeing with my original point ... ! > Same goes for interactive booting. In the world where even power > failures are accounted for and automated, what does a user have to do > with booting a machine? Implementing strong authentication in boot > loaders for any kind of configuration other than the default would be > perfectly sufficient, if you ask me. Also, if I may remind you at this > point, human failure has so far remained to be one of the top reasons > for security compromises, social engineering not being an uncommon > one, so eliminating an extra point of failure in the case of an > operator being required to guide the machine through the boot process > doesn't harm in my opinion. > > One of the most important guidelines in implementing and assuring > security for one's data would be the fact that security pays off only > as long as the total cost of its implementation is lower than or equal > to the maximum reasonably expectable harm done in the case of a > security breach. I'm not sure I get your point, Grega - are you suggesting that using LoopAES is a good idea and that a system with LoopAES should also boot up automatically? I don't understand that at all. If it boots up automatically, then the encryption is effectively transparent and pointless. All data on the disk is 'pre-decrypted', very conveniently for whoever has just stolen the server/disk. If you're using LoopAES, it should *require* manual intervention, i.e. passphrase to be entered. If you want the system to boot up automatically, using LoopAES is redundant and so should not be used. Dave. - -- Dave Ewart [EMAIL PROTECTED] Computing Manager, Epidemiology Unit, Oxford Cancer Research UK PGP: CC70 1883 BD92 E665 B840 118B 6E94 2CFD 694D E370 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFASI9VbpQs/WlN43ARAoJdAKDSEGYJ4QpAiavHjQo9faNDK9ihIgCdG/CP X0nuHIF989baKSfaq4oy7c8= =Na0T -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] Transaction logs gone, how to restart?
If it helps in future, I use this script (don't remember where I had found it..) to backup the databases on disk, and then the backup directory can be backed up to the tape -- #!/bin/sh # Backup PostgreSQL TMPDIR=/var/lib/pgsql/backups export TMPDIR echo $TMPDIR # dump each database schema/data separately su -l postgres -c "psql -At -F ' ' -U postgres -d template1 <<__END__ SELECT datname FROM pg_database WHERE datallowconn; __END__ " | while read DB; do echo "PostgreSQL db $DB" mkdir -p $TMPDIR/$DB # schema su -l postgres -c "pg_dump -Cs -F c -Z 9 -S postgresql $DB" \ > $TMPDIR/$DB/schema.pg rm -rf $TMPDIR/$DB/schema.pg.gz gzip $TMPDIR/$DB/schema.pg # data su -l postgres -c "pg_dump -bd -F c -Z 9 -S postgresql $DB" \ > $TMPDIR/$DB/data.pg rm -rf $TMPDIR/$DB/data.pg.gz gzip $TMPDIR/$DB/data.pg # simple su -l postgres -c "pg_dump $DB" \ > $TMPDIR/$DB/simple.sql rm -rf $TMPDIR/$DB/simple.sql.gz gzip $TMPDIR/$DB/simple.sql done # dump all globals (users/groups) su -l postgres -c "pg_dumpall -g" \ > $TMPDIR/globals.sql -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 10:51 AM To: Jeff Boes Cc: [EMAIL PROTECTED] Subject: Re: [ADMIN] Transaction logs gone, how to restart? Jeff Boes <[EMAIL PROTECTED]> writes: > We backed up our test server yesterday (Pg 7.4.1) as the first step in > switching from Red Hat 7 to Whitebox. However, we failed to back up > the transaction and commit logs. Now that the OS and database are back > to where they were, we get this message on attempting to restart: > LOG: could not open file > "/usr/local/pgsql/data/pg_xlog/004100EE" (log file 65, segment > 238): No such file or directory Urgh. If you shut down the postmaster cleanly before backing up, then you don't really need pg_xlog --- running pg_resetxlog will get you out of the above problem. However, if you did not save pg_clog then I fear you are well and truly screwed. How long ago was your last complete (database-wide) VACUUM? If it was just before shutdown then you may be able to achieve some semblance of a consistent database, but if it was awhile ago then you do not have a database but just a pile of bits :-( regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] process list
ps -eaf | grep postgres | grep -v idle (the last grep will get the actual command, and not the idle connection) If you have the stats_command_string enabled, you can also do a $echo "select * from pg_stat_activity" | psql dbname Thanks, Anjan -Original Message- From: Hemapriya [mailto:[EMAIL PROTECTED] Sent: Tue 3/23/2004 4:46 PM To: [EMAIL PROTECTED] Cc: Subject: [ADMIN] process list How can i view the list of processes running in postgresql.. is there any command for that?? Any help is appreciated. Thanks Priya __ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[ADMIN] Use 7.4.1's pg_dump in 7.2.4?
Can I use the pg_dump from 7.4.1 on 7.4.2? I tried copying a couple of libraries that it needed, and I can copy one-by-one that it looks for, but I am not sure how many does it need in all for the new version to work… Purpose is to dump/restore a pretty good-sized db (19GB) on 7.2.4, so I can upgrade it to 7.4.1. Thanks, Anjan
Re: [ADMIN] Use 7.4.1's pg_dump in 7.2.4?
I am having some trouble restoring the data back on 7.4.1 (made from pg_dump on 7.2.4), that's the reason I would like to try dumping using the pg_dump version of 7.4.1. I had read in a post somethere by Tom Lane that the pg_dump of 7.4.1 can be used to dump data on earlier versions, which is what I wanted to try. -anjan -Original Message- From: Peter Eisentraut [mailto:[EMAIL PROTECTED] Sent: Monday, April 05, 2004 1:36 PM To: Anjan Dave; [EMAIL PROTECTED] Subject: Re: [ADMIN] Use 7.4.1's pg_dump in 7.2.4? Anjan Dave wrote: > Purpose is to dump/restore a pretty good-sized db (19GB) on 7.2.4, so > I can upgrade it to 7.4.1. pg_dump should be able to dump databases back to about 7.1, so you should be good to go. As always, if you have problems, we would like to hear about it. By the way, the latest version is 7.4.2 already. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] problem with pg_dump and pg_restore
Corey, In my case, I have tried using the 7.4.1 pg_dump in 7.2.3, and it was missing a lot of libraries...did that happen in your case? I am wondering if it's really possible in the first place. Thanks, Anjan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 12:06 PM To: Grega Bremec Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; Fischer Ulrich Subject: Re: [ADMIN] problem with pg_dump and pg_restore When I tried to use the 7.3.2 dump, and use 7.4.1 import, I had several issues- mostly with data. I actually had to dump with the 7.4.1 dumpall command, upgrade the DB to 7.4.1, and then everything imported correctly. Hope this helps, Corey Grega Bremec <[EMAIL PROTECTED]To: Fischer Ulrich <[EMAIL PROTECTED]> um.si> cc: [EMAIL PROTECTED] Sent by: Subject: Re: [ADMIN] problem with pg_dump and pg_restore [EMAIL PROTECTED] tgresql.org 04/21/2004 08:28 AM ...and on Wed, Apr 21, 2004 at 02:46:54PM +0200, Fischer Ulrich used the keyboard: > Hi > > I try to move my datas from a postresql 7.3.2 to a postgresql 7.4.1. The > database was dumped with: > > pg_dumpall -g GLOBALobjects.sql > pg_dump -s DATABASE > DBschema.sql > pg_dump -Fc DATABASE > DBdata.tar > Why the schema/data separation? One important freedom you get when combining schema and data in the dumps is you can alter it after you imported the data and thus avoid cyclic dependencies where existance of one foreign key mandates the existance of another, which refers to the first one. For now though, try eliminating all constraints from the schema dump and ALTERing the tables after you imported the data. Hope this helped, -- Grega Bremec Senior Administrator Noviforum Ltd., Software & Media http://www.noviforum.si/ (See attached file: att4aa3m.dat) ---(end of broadcast)--- TIP 3: 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] [JDBC] is a good practice to create an index on the oid?
Yes, you can create an index on the oid, but unless you are selecting on it, it is of little use. you would have to do select * from foo where oid=? to get any value out of the index. Dave On Mon, 2004-04-26 at 12:38, Edoardo Ceccarelli wrote: > I am using the oid of the table as the main key and I've found that is > not indexed (maybe because I have declared another primary key in the table) > > it is a good practice to create an index like this on the oid of a table? > CREATE INDEX idoid annuncio400 USING btree (oid); > > > does it work as a normal index? > > Thank you > Edoardo > > ---(end of broadcast)--- > TIP 3: 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 > > > > !DSPAM:408d7c38183971270217895! > > -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] [JDBC] [PERFORM] is a good practice to create an index on the
AFAIK, oids aren't used for anything internally, so duplicates don't really matter. Besides, what would you do about duplicate oid's ? The best suggestion is of course his last, don't use them. On Mon, 2004-04-26 at 22:48, Christopher Kings-Lynne wrote: > > I am using the oid of the table as the main key and I've found that is > > not indexed (maybe because I have declared another primary key in the > > table) > > > > it is a good practice to create an index like this on the oid of a table? > > CREATE INDEX idoid annuncio400 USING btree (oid); > > Yes it is - in fact you really should add a unique index, not just a > normal index, as you want to enforce uniqueness of the oid column. It > is theoretically possible to end up with duplicate oids in wraparound > situations. > > Even better though is to not use oids at all, of course... > > Chris > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > > > > !DSPAM:408dcc51235334924183622! > > -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] [JDBC] [PERFORM] is a good practice to create an index on the
Edoardo, Are you using them for referential integrity? If so you would be wise to use sequences instead. Christopher: yes you are correct, I wasn't sure if that is what he was doing. Dave On Tue, 2004-04-27 at 11:01, Christopher Kings-Lynne wrote: > > AFAIK, oids aren't used for anything internally, so duplicates don't > > really matter. Besides, what would you do about duplicate oid's ? > > If he's using them _externally_, then he does have to worry about > duplicates. > > Chris > > > > !DSPAM:408e75e0137721921318500! > > -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] [PERFORM] Quad processor options
We use XEON Quads (PowerEdge 6650s) and they work nice, provided you configure the postgres properly. Dell is the cheapest quad you can buy i think. You shouldn't be paying 30K unless you are getting high CPU-cache on each processor and tons of memory. I am actually curious, have you researched/attempted any postgresql clustering solutions? I agree, you can't just keep buying bigger machines. They have 5 internal drives (4 in RAID 10, 1 spare) on U320, 128MB cache on the PERC controller, 8GB RAM. Thanks, Anjan -Original Message- From: Bjoern Metzdorf [mailto:[EMAIL PROTECTED] Sent: Tue 5/11/2004 3:06 PM To: [EMAIL PROTECTED] Cc: Pgsql-Admin (E-mail) Subject: [PERFORM] Quad processor options Hi, I am curious if there are any real life production quad processor setups running postgresql out there. Since postgresql lacks a proper replication/cluster solution, we have to buy a bigger machine. Right now we are running on a dual 2.4 Xeon, 3 GB Ram and U160 SCSI hardware-raid 10. Has anyone experiences with quad Xeon or quad Opteron setups? I am looking at the appropriate boards from Tyan, which would be the only option for us to buy such a beast. The 30k+ setups from Dell etc. don't fit our budget. I am thinking of the following: Quad processor (xeon or opteron) 5 x SCSI 15K RPM for Raid 10 + spare drive 2 x IDE for system ICP-Vortex battery backed U320 Hardware Raid 4-8 GB Ram Would be nice to hear from you. Regards, Bjoern ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] [PERFORM] Quad processor options
Did you mean to say the trigger-based clustering solution is loading the dual CPUs 60-70% right now? Performance will not be linear with more processors, but it does help with more processes. We haven't benchmarked it, but we haven't had any problems also so far in terms of performance. Price would vary with your relation/yearly purchase, etc, but a 6650 with 2.0GHz/1MB cache/8GB Memory, RAID card, drives, etc, should definitely cost you less than 20K USD. -anjan -Original Message- From: Bjoern Metzdorf [mailto:[EMAIL PROTECTED] Sent: Tue 5/11/2004 4:28 PM To: Anjan Dave Cc: [EMAIL PROTECTED]; Pgsql-Admin (E-mail) Subject: Re: [PERFORM] Quad processor options Anjan Dave wrote: > We use XEON Quads (PowerEdge 6650s) and they work nice, > provided you configure the postgres properly. > Dell is the cheapest quad you can buy i think. > You shouldn't be paying 30K unless you are getting high CPU-cache > on each processor and tons of memory. good to hear, I tried to online configure a quad xeon here at dell germany, but the 6550 is not available for online configuration. at dell usa it works. I will give them a call tomorrow. > I am actually curious, have you researched/attempted any > postgresql clustering solutions? > I agree, you can't just keep buying bigger machines. There are many asynchronous, trigger based solutions out there (eRserver etc..), but what we need is basically a master <-> master setup, which seems not to be available soon for postgresql. Our current dual Xeon runs at 60-70% average cpu load, which is really much. I cannot afford any trigger overhead here. This machine is responsible for over 30M page impressions per month, 50 page impressums per second at peak times. The autovacuum daemon is a god sent gift :) I'm curious how the recently announced mysql cluster will perform, although it is not an option for us. postgresql has far superior functionality. > They have 5 internal drives (4 in RAID 10, 1 spare) on U320, > 128MB cache on the PERC controller, 8GB RAM. Could you tell me what you paid approximately for this setup? How does it perform? It certainly won't be twice as fast a as dual xeon, but I remember benchmarking a quad P3 xeon some time ago, and it was disappointingly slow... Regards, Bjoern ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] [JDBC] Trouble with locking tables - reg.
Functions can't have transactions inside of them You need to start the transaction then call the function. couple of questions though why not use sequences ? Then you have no concurrency issues. They are guaranteed to be incremented. Also you can use select for update, instead of locking the entire table. Dave On Wed, 2004-06-09 at 23:39, Shanmugasundaram Doraisamy wrote: > Dear Group, > We are using Postgresql 7.3.4 on Redhat 8.0 with > Java 1.4.2. We are developing our applications in Java. We call stored > procedures from the java program. Order numbers are generated by many > departments in the Hospital. We manitain a single table from which to > select the order number. The way this works is that the order numbers > are released for reuse if the order has been completed. We wrote a > procedure in plpgsql with a transaction which locks the table for > concurrency problem. When more than one person tries to generate an > order number (by running the java program) still there arise the > concurrency problem. > > We tried to check how the procedures with transaction that > locks the table works . what we did to check the procedure was as follows > we have one database server. > we took two computer systems. in both system we opened > one terminal (linux). > let the value of the order number be 50. > [1] in one system's terminal we started the transaction > using begin; lock table ; > [2] in another system we run the procedure which fetch > the order number from the locked table ,display it -increment it - store > it in the table again using update statement (not like order number = > order number + 1) but like (x =order number +1), again we fetched the > value of the order number from the table and display it . the procedure > is as follows: > > CREATE OR REPLACE FUNCTION CHECKING() RETURNS TEXT AS' > DECLARE > XVAL INTEGER; > BEGIN > BEGIN > LOCK TABLE CHECKING_LOCK; > SELECT INTO XVAL X FROM CHECKING_LOCK WHERE Y = TRUE; > RAISE NOTICE ''X BEF %'',XVAL; > XVAL := XVAL + 1; > UPDATE CHECKING_LOCK SET X = XVAL WHERE Y = TRUE; > SELECT INTO XVAL X FROM CHECKING_LOCK WHERE Y = TRUE; > RAISE NOTICE ''X AFT %'',XVAL; > END; > RETURN ''OK''; > END; > 'LANGUAGE 'PLPGSQL'; > > Now this procedure waits for the other transaction to complete > [3] in the other system's terminal i update the field value - > increment it by 1 and entered end; to commit the transaction > [4] automatically the procedure runs and displays the result > As per transaction isolation level ( read committed being the > default isolation level) it should be 51 and 52. > as when the transaction in the terminal update it to 51 , the > transaction in the procedure which was waiting should fetch it as 51 and > increment it by 1 (52) and set the field value to 52 and when fetched > after update should return it the value as 52. this is what we want. >but what is the actual is , > The final result the procedure displays is 50 before update and 50 > after update. >when i verified in the database table it shows the field value as > 51. > how to make it to our expectation. > > Your immediate response in this regard is very much appreciate. > Thanking you, > > Yours sincerely, > > Shan. > > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > > !DSPAM:40c88c0d60177625298691! > > -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(end of broadcast)--- TIP 3: 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
[ADMIN] perl(Pg) error on contrib package
Hi, I recently upgraded from 7.2.4 to 7.4.1, but can’t install the contrib rpm. # rpm -ivh postgresql-contrib-7.4.1-1PGDG.i386.rpm error: Failed dependencies: perl(Pg) is needed by postgresql-contrib-7.4.1-1PGDG I installed the ‘postgresql-pl-7.4.1-1PGDG.i386.rpm’ package, but that didn’t help. Here’s what’s installed so far… postgresql-7.4.1-1PGDG postgresql-libs-7.2.4-5.80 postgresql-libs-7.4.1-1PGDG postgresql-server-7.4.1-1PGDG postgresql-pl-7.4.1-1PGDG Some of perl packages – … perl-DBD-Pg-1.13-5 perl-5.8.0-88.3 … Appreciate any pointers, Thanks, Anjan
Re: [ADMIN] [GENERAL] High Availability - Performace Scalability -
Jerome, I'm curious what you expect out of clustering. High Availability, and Load Balancing seem possible, but Performance ? I would think there is a cost associated with clustering, that degrades performance. Anyways, to answer your question, there are a few tools that allow High Availability, and Load Balancing. pgpool ftp://ftp.sra.co.jp/pub/cmd/postgres/pgpool/pgpool-2.0.8.tar.gz and if you prefer java C-JDBC http://c-jdbc.objectweb.org/ Dave On Wed, 2004-08-18 at 06:10, JM wrote: > Hi All, > > I just came off an exibit and I was wondering if there ways to do clustering > in postgres. > I envy mySQL because there's a application for clustering it... > > EMIC networks has this marketing tagline: > High Availability - Performace Scalability - Dyanamic Load Balancing > they are the one who provide this app... ( but its not open source ) > > > Thanks, > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(end of broadcast)--- TIP 3: 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] windows docs?
> -Original Message- > From: Robert Treat [mailto:[EMAIL PROTECTED] > Sent: 21 September 2004 12:27 > To: Magnus Hagander > Cc: Bender, Cheryl; [EMAIL PROTECTED]; Dave Page; > [EMAIL PROTECTED] > Subject: Re: [ADMIN] windows docs? > ? > I'm thinking rename the current faq to faq_win32_cygwin and > then create a new > faq faq_win32_native based the pginstaller one. Does anyone > feel they ought > to be combined? No, absolutely not - that'll just create further confusion. > I guess another alternative is jut remove > faq_mswin from the main sources and then leave it up to > cygwin/native projects to maintain thier own faqs, with > perhaps a single question in the main postgresql pointing to > both of the other faqs... anyone have a preference? The problem with our new FAQ is that much of it is installer specific, so including it in the main distro which doesn't include the installer code just seems wrong somehow. Perhaps the existing faq should have a note in bold at the top directing ppl to the installer FAQ if that's what they really want, and leave the rest of the doc for Cygwin/Mingw specific issues. Regards, Dave. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[ADMIN] moving pg_xlog
All: What’s the best way to separate pg_xlog to another drive? I am using a soft link currently in the data directory (per docs), but is there another way of doing this? Thanks, Anjan
[ADMIN] client authentication problem
Hi, I have a problem connecting to a database when using tcp/ip with psql. Connecting with Unix sockets works fine. TCP/IP is enabled, and the log shows a connection is made but authentication is rejected. From the error messages, it appears that the client and server negotiated to use crypt, or perhaps the client is trying to force using crypt rather than md5. First guess is something like a version mist-match between client and server libraries, but this is a fresh install on Debian, installed using apt so that seems unlikely. More information follows... Details: # uname -a Linux tova 2.6.8-1-686 #1 Thu Nov 25 04:34:30 UTC 2004 i686 GNU/Linux # psql -V psql (PostgreSQL) 7.4.6 contains support for command-line editing Contents of pg_hba.conf: local all postgres ident sameuser local all allmd5 host all all 127.0.0.1 255.255.255.255 md5 host all all 0.0.0.0 0.0.0.0 reject Examining pg_shadow indicates passwords are stored using MD5 encypting. Connecting directly using Unix sockets works fine: # psql testdb -U test Connecting over TCP/IP fails: # psql testdb -U test -h localhost psql: FATAL: Password authentication failed for user "test" The log indicates that a connection was made (so TCP/IP seems to be working ok) but that the "crypt" authentication method is being used rather than "MD5": # tail /var/log/postgresql/postgresql.log LOG: redo record is at 0/6851324; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 61873; next OID: 28747 LOG: database system is ready 2005-01-31 16:03:23 [31418] LOG: connection received: host=127.0.0.1 port=33377 2005-01-31 16:03:23 [31418] LOG: cannot use authentication method "crypt" because password is MD5-encrypted 2005-01-31 16:03:23 [31418] FATAL: Password authentication failed for user "test" My understanding of the connection negotiation is that the client connects, the server suggests the encryption method to use and sends the salt to the client, the client is supposed to make the MD5 hash using Username, Password and Salt, and forward that back for authentication. However it appears that something is failing at the negotiation stage, and the client is trying to use crypt, or perhaps the server is suggesting that crypt be used. Any suggestions on how to correct this problem? thanks, Dave V. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] pg_clog corrupt, can't start postgres
Hi, I need some help in bringing back this db please. The partition ran out of space from an import process. I cleared up the space and attempted to start the postgres service again, but it doesn't start and i get following in the message log. HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. LOG: checkpoint record is at 1B/27F23A6C LOG: redo record is at 1B/27751714; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 45279762; next OID: 43062083 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 1B/27751714 PANIC: could not access status of transaction 45514755 DETAIL: could not read from file "/var/lib/pgsql/data/pg_clog/002B" at offset 106496: Success LOG: startup process (PID 23991) was terminated by signal 6 LOG: aborting startup due to startup process failure Postgres is 7.4.1 on this machine. I saw some previous posts on this subject and so far the solution seems to be initialize and restore databases from the dumps. I can live with some aborted transactions, if it's possible to recover somehow. $ psql psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"? # ls -l pg_xlog/ total 131232 -rw---1 postgres postgres 16777216 Feb 19 13:30 001B0026 -rw---1 postgres postgres 16777216 Feb 19 13:34 001B0027 -rw---1 postgres postgres 16777216 Feb 19 13:44 001B0028 -rw---1 postgres postgres 16777216 Feb 19 13:15 001B0029 -rw---1 postgres postgres 16777216 Feb 19 13:12 001B002A -rw---1 postgres postgres 16777216 Feb 19 13:18 001B002B -rw---1 postgres postgres 16777216 Feb 19 13:26 001B002C -rw---1 postgres postgres 16777216 Feb 19 13:22 001B002D # ls -l pg_clog/ total 628 -rw---1 postgres postgres 262144 Feb 19 04:31 0029 -rw---1 postgres postgres 262144 Feb 19 11:55 002A -rw---1 postgres postgres 106496 Feb 20 22:34 002B Thanks, Anjan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] pg_clog corrupt, can't start postgres
Tom, You're the man! I zeroed out the troubled pg_clog file and the db started up fine! Here's the link to the discussion, and a detailed explanation of the issue by Tom: http://groups-beta.google.com/group/comp.databases.postgresql.hackers/browse_thread/thread/c97c853f640b9ac1/d6bc3c75eed6c2a4?q=could+not+access+status+of+transaction#d6bc3c75eed6c2a4 Tom, is the issue resolved after 7.4.1? Thanks, Anjan -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Mon 2/21/2005 11:42 AM To: Anjan Dave Cc: [email protected] Subject: Re: [ADMIN] pg_clog corrupt, can't start postgres "Anjan Dave" <[EMAIL PROTECTED]> writes: > The partition ran out of space from an import process. I cleared up the space and attempted to start the postgres service again, but it doesn't start and i get following in the message log. > PANIC: could not access status of transaction 45514755 > DETAIL: could not read from file "/var/lib/pgsql/data/pg_clog/002B" at offset 106496: Success > LOG: startup process (PID 23991) was terminated by signal 6 > LOG: aborting startup due to startup process failure > Postgres is 7.4.1 on this machine. > I saw some previous posts on this subject and so far the solution seems to be initialize and restore databases from the dumps. Before that, try updating to 7.4.7 (or at least 7.4.2) --- this looks like the same bug fixed here: 2004-01-26 14:16 tgl * src/backend/access/transam/varsup.c (REL7_4_STABLE): Repair incorrect order of operations in GetNewTransactionId(). We must complete ExtendCLOG() before advancing nextXid, so that if that routine fails, the next incoming transaction will try it again. Per trouble report from Christopher Kings-Lynne. You might also go back to the mail list archives from that time and see what advice was given to Chris about getting out of the problem he found himself in. I *think* that something along the line of forcibly appending a page of zeroes to that clog file might be the best solution, but this was more than a year ago and I don't recall for sure. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[ADMIN] logging time with queries
Hi,
I need some help on a logging situation. I am logging the
stdout to a file (/var/log/pglogs) like this:
In Postgresql.conf:
**
syslog =
0
# range 0-2; 0=stdout; 1=both; 2=syslog
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
log_min_duration_statement = 4000 # Log all statements whose
#
execution time exceeds the value, in
#
milliseconds.
log_timestamp = true
The init.d/postgres is changed like this:
*
PGLOG=/var/log/pglogs
export PGLOG
$SU -l postgres -c "$PGENGINE/initdb --pgdata=$PGDATA" >>
$PGLOG 2>&1 < /dev/null
$SU -l postgres
-c "$PGENGINE/postmaster -p ${PGPORT} -D '${PGDATA}' ${PGOPTS} &"
>> $PGLOG 2>&1 < /dev/null
With this setup, I can capture some queries that take more
than 4 seconds to execute, but I am not seeing any time stamps on any logging, except
database startup/shutdown events, which makes it difficult to track certain
activities.
Is there a way to log time as well?
Thanks,
Anjan
**This e-mail and any files transmitted with it are intended for the use of the addressee(s) only and may be confidential and covered by the attorney/client and other privileges. If you received this e-mail in error, please notify the sender; do not disclose, copy, distribute, or take any action in reliance on the contents of this information; and delete it from your system. Any other use of this e-mail is prohibited.**
[ADMIN] compilation problem with readline
Hi, I need help compiling 7.4.7 please… It’s not able to find readline library, when the package is already installed… checking for readline... no configure: error: readline library not found If you have readline already installed, see config.log for details on the failure. It is possible the compiler isn't looking in the proper directory. Use --without-readline to disable readline support. # # rpm -qa | grep readline readline-4.3-7 # rpm -ql readline-4.3-7 /usr/lib/libhistory.so.4 /usr/lib/libhistory.so.4.3 /usr/lib/libreadline.so.4 /usr/lib/libreadline.so.4.3 /usr/share/info/history.info.gz /usr/share/info/readline.info.gz /usr/share/info/rluserman.info.gz /usr/share/man/man3/history.3.gz /usr/share/man/man3/readline.3.gz I tried to use ‘./configure –with-libraries=/usr/lib’ but that didn’t work either. I read a post where Tom suggested to have termcap, which is also installed on the system. ]# rpm -qa | grep termcap termcap-11.0.1-17 libtermcap-2.0.8-36 Do I need something additional? Thanks, Anjan **This e-mail and any files transmitted with it are intended for the use of the addressee(s) only and may be confidential and covered by the attorney/client and other privileges. If you received this e-mail in error, please notify the sender; do not disclose, copy, distribute, or take any action in reliance on the contents of this information; and delete it from your system. Any other use of this e-mail is prohibited.**
Re: [ADMIN] compilation problem with readline
Thanks guys that did it! Anjan -Original Message- From: Uwe C. Schroeder [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 30, 2005 11:43 AM To: Anjan Dave Cc: [email protected] Subject: Re: [ADMIN] compilation problem with readline you need to install the readline-devel package On Wednesday 30 March 2005 08:30, Anjan Dave wrote: > Hi, > > > > I need help compiling 7.4.7 please... It's not able to find readline > library, when the package is already installed... > > > > checking for readline... no > > configure: error: readline library not found > > If you have readline already installed, see config.log for details on > the > > failure. It is possible the compiler isn't looking in the proper > directory. > > Use --without-readline to disable readline support. > > # > > > > # rpm -qa | grep readline > > readline-4.3-7 > > > > # rpm -ql readline-4.3-7 > > /usr/lib/libhistory.so.4 > > /usr/lib/libhistory.so.4.3 > > /usr/lib/libreadline.so.4 > > /usr/lib/libreadline.so.4.3 > > /usr/share/info/history.info.gz > > /usr/share/info/readline.info.gz > > /usr/share/info/rluserman.info.gz > > /usr/share/man/man3/history.3.gz > > /usr/share/man/man3/readline.3.gz > > > > I tried to use './configure -with-libraries=/usr/lib' but that didn't > work either. > > > > I read a post where Tom suggested to have termcap, which is also > installed on the system. > > > > ]# rpm -qa | grep termcap > > termcap-11.0.1-17 > > libtermcap-2.0.8-36 > > > > Do I need something additional? > > > > Thanks, > Anjan > > > > > > ** > This e-mail and any files transmitted with it are intended for the use > of the > addressee(s) only and may be confidential and covered by the > attorney/client > and other privileges. If you received this e-mail in error, please > notify the > sender; do not disclose, copy, distribute, or take any action in > reliance on > the contents of this information; and delete it from your system. Any > other > use of this e-mail is prohibited. > > ** -- UC -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] [GENERAL] PgAgent logging verbosity
Ezequias Rodrigues da Rocha wrote: Hi list, I would like to know if I set the debug mode to 2 (-l 2) in the pgAgent connection string it could spend much cpu processing. I have other things running in the server and I don't like to create problem to others. Please stop cross posting to lots of lists! -l 2 is extremely unlikely to cause excessive CPU or IO, but it will clutter logs, so you wouldn't want to leave it on permanently. Regards, Dave ---(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
[ADMIN] security question
Hi, I have a quick question about my installation that puzzles me. pgsql 8.2.3on a debian box. Tested it out the database works. pgadmin III connects fine too. I used alter table on the template1 database to give postgres user a password. I changed pg_hba to use md5 on all connections including local. When I use psql -U postgres -d myDataBase to connect to myDataBase I do not get prompted for a password? When I su postgres and use psql myDatabase I get prompted for my password? Is there some reason why I can login using psql to a database when postgres is supposed to use passwords for all access? Probably a mistake I made somewhere along the line... just not sure where, usually I get my pgsql install working great! Thanks Dave
Re: [ADMIN] [INTERFACES] Function's final statement must be a SELECT
On Wed, May 14, 2008 at 9:23 PM, Premil Agarwal <[EMAIL PROTECTED]> wrote: > I am trying to write a function in PgAdmin 1.8.2 for postgres 8.3. The > function should check the input parameter for NULL. If its NULL then > function should return 'Auto' otherwise return the input as such. > > CREATE FUNCTION check_for_null( inp character varying) RETURNS character > varying AS $$ > DECLARE > temp1 character varying := 'Auto'; > temp2 character varying := inp; > BEGIN > > IF temp2 IS NOT NULL THEN > temp1 := temp2; > END IF; > > RETURN temp1; > END; > $$ LANGUAGE SQL; > > When I hit 'OK' in create function window, I get following error :- > > ERROR: return type mismatch in function declared to return character varying > DETAIL: Function's final statement must be a SELECT. > CONTEXT: SQL function "check_for_null" > > Any suggestions why this happens ?? Choose pl/pgsql as the language, not SQL. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] [INTERFACES] Function's final statement must be a SELECT
On Wed, May 14, 2008 at 9:38 PM, Premil Agarwal <[EMAIL PROTECTED]> wrote: > Tried that already, still the same error It (using plpgsql instead of sql) works fine for me using your code. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] displaying enum
Assuming you are are running 8.3, you can do this: SELECT a.typname,b.enumlabel from pg_type a , pg_enum b where a.oid=b.enumtypid and a.typname='NAME_OF_ENUM'; Chirag Dave Afilias On Mon, Sep 22, 2008 at 9:20 AM, Jagadeesh <[EMAIL PROTECTED]> wrote: > Hi admins, > > I had created enum and forgotten what were the values. Is there any > way to see what I created? > > Thanks > > -- > Sent via pgsql-admin mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >
Re: [GENERAL] [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit
2008/9/23 Peter Eisentraut <[EMAIL PROTECTED]>: > Gerd König wrote: >> >> a few months ago we started using Postgres on Opensuse10.3-64bit. >> We installed Postgres 8.3.1 with the (at that time) latest available >> rpm's. >> But now Postgres' current version is 8.3.4 and I'm wondering why there >> are no new rpm's for Opensuse ?!?! > > The answer is quite simply that no one is volunteering to do the work. Oh, I thought you were looking after that build. If it's not being maintained, we'll need to remove it from the download pages unless someone else can volunteer? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [GENERAL] [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit
On Tue, Sep 23, 2008 at 9:49 AM, Simon Riggs <[EMAIL PROTECTED]> wrote: >> Oh, I thought you were looking after that build. If it's not being >> maintained, we'll need to remove it from the download pages unless >> someone else can volunteer? > > I'll look at doing that. We need the SUSE builds also. Great, thanks Simon. If you can commit to ongoing maintenance then we'll get you on the packagers list so you can get advance notice of releases and having input on the scheduling details. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Postgres Stats after Crash Recovery
Testing AutoVac on 8.3 , i came across the problem of loosing stats data, which was discussed in my last post http://archives.postgresql.org/pgsql-general/2008-08/msg00198.php that problem was recognized that doing hard stop, server will throw away the stats while going through crash recovery. Problem i see is after crash recovery , we have to manually analyze database in order for autovac to work but it not working as expected. Here is test case: foo=# SELECT version(); version PostgreSQL 8.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 (Ubuntu 4.1.2-0ubuntu4) (1 row) foo=# SELECT * from pg_stat_user_tables where relname='accounts'; -[ RECORD 1 ]+-- relid| 57350 schemaname | public relname | accounts seq_scan | 1 seq_tup_read | 100 idx_scan | 0 idx_tup_fetch| 0 n_tup_ins| 100 n_tup_upd| 0 n_tup_del| 0 n_tup_hot_upd| 0 n_live_tup | 100 n_dead_tup | 0 last_vacuum | 2008-09-24 15:04:35.384012-04 last_autovacuum | last_analyze | 2008-09-24 15:04:35.384012-04 last_autoanalyze | Next i will stop DB immediate and expect to loose stats as normal behavior. pg_ctl -m immediate -D /opt/rg/foo/pgsql83 stop waiting for server to shut down...LOG: received immediate shutdown request done server stopped After stating the DB, as expected: foo=# SELECT * from pg_stat_user_tables where relname='accounts'; -[ RECORD 1 ]+- relid| 57350 schemaname | public relname | accounts seq_scan | 0 seq_tup_read | 0 idx_scan | 0 idx_tup_fetch| 0 n_tup_ins| 0 n_tup_upd| 0 n_tup_del| 0 n_tup_hot_upd| 0 n_live_tup | 0 n_dead_tup | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | Next step is to manually analyse to collects the stats again: foo=# ANALYZE ; ANALYZE foo=# SELECT * from pg_stat_user_tables where relname='accounts'; -[ RECORD 1 ]+- relid| 57350 schemaname | public relname | accounts seq_scan | 0 seq_tup_read | 0 idx_scan | 0 idx_tup_fetch| 0 n_tup_ins| 0 n_tup_upd| 0 n_tup_del| 0 n_tup_hot_upd| 0 n_live_tup | 0 n_dead_tup | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | note: After ANALYSE, ststs were not updated. Running ANALYSE, second time seems to does the trick. foo=# ANALYZE ; ANALYZE foo=# SELECT * from pg_stat_user_tables where relname='accounts'; -[ RECORD 1 ]+-- relid| 57350 schemaname | public relname | accounts seq_scan | 0 seq_tup_read | 0 idx_scan | 0 idx_tup_fetch| 0 n_tup_ins| 0 n_tup_upd| 0 n_tup_del| 0 n_tup_hot_upd| 0 n_live_tup | 162 n_dead_tup | 0 last_vacuum | last_autovacuum | last_analyze | 2008-09-24 15:13:13.423424-04 last_autoanalyze | So question i have is, is this normal operation,why we need to analyze twice to updates the stats ? if table/tables are candidate for vacuuming after crash recovery will never get auto-vac unless you do 'ANALYZE' twice. Thanks in advance, Chirag Dave DBA Afilias
Re: [ADMIN] checkpoint_timeout
What version of postgres are you running ? and please also provide more information about your suspected slowdown ? On Tue, Oct 7, 2008 at 4:21 PM, Rafael Domiciano <[EMAIL PROTECTED] > wrote: > Hello there, > I'm having some slowndowns in my postgresql server, and I had been reading > something about checkpoints, that could resolve my problem. > Now, I have: > checkpoint_segments = 30# in logfile segments, min 1, 16MB > each > #checkpoint_timeout = 5min > > And I percebed that this slowndown is occuring for 5 min...! So I'm > suposing that the problem is in the checkpoint_timeout. > > What's the impact to chang it to: > checkpoint_timeout = 2min or > checkpoint_timeout = 5min > > Tnhks, > > Rafael Domiciano >
Re: [ADMIN] checkpoint_timeout
Again very little information to work with but I would start with looking at postgres logs. you should enable checkpoint_timeout to a very high number, so you have an entry in the log anytime checkpoint occurs and set your log_min_duration_statement to number which your considered to be long running, to log all slower statement. Chirag Dave DBA, Afilias On Wed, Oct 8, 2008 at 8:31 AM, Rafael Domiciano <[EMAIL PROTECTED] > wrote: > The server is running Postgres 8.2.4When the server is doing too much > operations like I, U or D the postgres stop a while each 5 min...! > I mean "stopping" that I don't receive any response from postgres, and the > process the all people are running stay there, waiting for something. > I read a several names to it like: gap, stall, slowdown... I don't know the > write expression for this situation. > > I hope you can help me. > > Thnks, > > Rafael Domiciano > Postgres DBA > > 2008/10/7 <[EMAIL PROTECTED]> > > What version of postgres are you running ? and please also provide more >> information about your suspected slowdown ? >> >> >> >> >> >> On Tue, Oct 7, 2008 at 4:21 PM, Rafael Domiciano < >> [EMAIL PROTECTED]> wrote: >> >>> Hello there, >>> I'm having some slowndowns in my postgresql server, and I had been >>> reading something about checkpoints, that could resolve my problem. >>> Now, I have: >>> checkpoint_segments = 30# in logfile segments, min 1, >>> 16MB each >>> #checkpoint_timeout = 5min >>> >>> And I percebed that this slowndown is occuring for 5 min...! So I'm >>> suposing that the problem is in the checkpoint_timeout. >>> >>> What's the impact to chang it to: >>> checkpoint_timeout = 2min or >>> checkpoint_timeout = 5min >>> >>> Tnhks, >>> >>> Rafael Domiciano >>> >> >> >
Re: [ADMIN] [GENERAL] Scheduling backup via PgAgent
On Thu, Nov 20, 2008 at 5:41 AM, Abdul Rahman <[EMAIL PROTECTED]> wrote: > Oh! > > I have got the mistake made by me. Actually I didn't start the PostgreSQL > Scheduling Agent - pgAgent service. Now its working fine. > > I also have attached the complete document of my work for the sake of > knowledge share. I think you forgot the attachment. It would be useful if you could add it to the wiki though: http://wiki.postgresql.org/ -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] [GENERAL] Tool to converter plsql in pgplsql
On Fri, Dec 12, 2008 at 7:12 AM, Albe Laurenz wrote: > paulo matadr wrote: >> you knowns a tool for automatic converter plsql in pgplsql? >> this tool exist? > > EnterpriseDB claim that they can do something like this, > but I don't believe that there is any tool which can do > more than assist you. We don't have a converter, though we do have experience in this area of course. Our Advanced Server product has direct support for pl/sql however so you can run your code unmodified. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Slony-I: cache lookup failed for type 267501
Help?
Just configured a Slony-I cluster, two nodes, Centos 5.2 x64, master
logging errors like the excerpt below almost continuously. stopped the
slon daemons, and dropped replication on each node for now.
There were two file sets, one of 7 tables, and another of 21 tables. I
believe it was in the process of copying one of the large tables in the
first set to the slave.
2009-06-11 15:44:10.752 CDTERROR: cache lookup failed for type 267501
2009-06-11 15:44:10.752 CDTCONTEXT: SQL statement "INSERT INTO
_meteorstats.sl_log_1 (log_origin, log_xid, log_tableid, log_actionseq,
log_cmdtype, log_cmddata) VALUES (1, $1, $2,
nextval('_meteorstats.sl_action_seq'), $3, $4);"
SQL function "increment_url_visits" statement 1
2009-06-11 15:44:10.752 CDTSTATEMENT: select increment_url_visits($1,$2)
2009-06-11 15:44:10.756 CDTERROR: cache lookup failed for type 267501
2009-06-11 15:44:10.756 CDTCONTEXT: SQL statement "INSERT INTO
_meteorstats.sl_log_1 (log_origin, log_xid, log_tableid, log_actionseq,
log_cmdtype, log_cmddata) VALUES (1, $1, $2,
nextval('_meteorstats.sl_action_seq'), $3, $4);"
SQL function "increment_url_visits" statement 1
2009-06-11 15:44:10.756 CDTSTATEMENT: select increment_url_visits($1,$2)
2009-06-11 15:44:10.765 CDTERROR: cache lookup failed for type 267501
2009-06-11 15:44:10.765 CDTCONTEXT: SQL statement "INSERT INTO
_meteorstats.sl_log_1 (log_origin, log_xid, log_tableid, log_actionseq,
log_cmdtype, log_cmddata) VALUES (1, $1, $2,
nextval('_meteorstats.sl_action_seq'), $3, $4);"
SQL function "increment_url_visits" statement 1
Master rpms:
compat-postgresql-libs-4-1PGDG.rhel5
postgresql-server-8.3.6-1PGDG.rhel5
postgresql-libs-8.1.11-1.el5_1.1
postgresql-libs-8.3.6-1PGDG.rhel5
postgresql-8.3.6-1PGDG.rhel5
postgresql-devel-8.3.6-1PGDG.rhel5
postgresql-contrib-8.3.6-1PGDG.rhel5
postgresql-docs-8.3.6-1PGDG.rhel5
slony1-docs-1.2.15-3.rhel5
slony1-1.2.15-3.rhel5
Slave rpms:
postgresql-8.3.7-1PGDG.rhel5
postgresql-contrib-8.3.7-1PGDG.rhel5
postgresql-libs-8.1.11-1.el5_1.1
postgresql-server-8.3.6-1PGDG.rhel5
postgresql-docs-8.3.6-1PGDG.rhel5
postgresql-libs-8.3.7-1PGDG.rhel5
postgresql-server-8.3.7-1PGDG.rhel5
postgresql-plpython-8.3.7-1PGDG.rhel5
postgresql-devel-8.3.7-1PGDG.rhel5
compat-postgresql-libs-4-1PGDG.rhel5
postgresql-python-8.1.11-1.el5_1.1
slony1-docs-1.2.15-3.rhel5
slony1-1.2.15-3.rhel5
Noticed that there are updated slony1 rpms, but don't want to start
playing rpm roulette, just yet.
Same two servers had been replicating happily for months, but only 5 tables.
--
Sent via pgsql-admin mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Re: [pgadmin-hackers] [ADMIN] pgAdmin 1.10, OSX 10.6 problem
On Fri, Oct 30, 2009 at 8:00 AM, Guillaume Lelarge wrote: > Le vendredi 30 octobre 2009 à 02:37:04, David Lazar a écrit : >> Hi, >> >> On Linux version of pgAdmin, in an edit grid, with a boolean column, I >> could hammer the space bar key and cycle through True, False and the other >> one... whatever... >> >> With OSX version if I hit spacebar I get True... but nothing cycles.. I >> have to use the Mouse (eeks) to cycle to the false... >> >> Is there something I can do to get spacebar (or another key) to cycle >> through values in the edit grid??? >> > > I don't think so. Maybe a bug. I can't check right now. Checkboxes aren't cycled by the space bar by default on Mac OS X. To change this, try the following (tested on Snow Leopard): - Open the Preferences tool, and go to the Keyboard pane. - Select the 'Keyboard Shortcuts' tab - At the bottom, select the Full keyboard access: All controls option. or, just press Control+F7 from anywhere. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PGDay.EU 2009 Conference: http://2009.pgday.eu/start -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [pgadmin-hackers] [ADMIN] pgAdmin 1.10, OSX 10.6 problem
On Fri, Oct 30, 2009 at 1:20 PM, David Lazar wrote: > Hi, > > Preferences for Snow Leopard for me match your advice Mr. Page. Full > Keyboard Access, All Controls not sure what to try now... sure would be > sweet to get that cycling option with the KB... It works fine for me if I turn on all controls, if I turn it off, I get the behaviour you describe. I'm not sure what else to suggest. (and please, Dave, not Mr Page :-) ) -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PGDay.EU 2009 Conference: http://2009.pgday.eu/start -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Recovering (slowly!) from database corruption
Hi, We've been working with the excellent Second Quadrant to resolve some data corruption issues caused by a database crash. I'm trying to get an explanation of a recurring issue that we're seeing with the database. Basically, some queries that are run against the DB take the following form; CREATE TEMP TABLE 'x' ON COMMIT DROP AS SELECT... >From this, we often get an ERROR and the transaction doesn't complete. The error message is invariably; ERROR: cache lookup failed for relation 5168456 Using ois2name has left me more confused, as the provided OID doesn't seem to existing anywhere in the database. I've read some discussion from Tom Lane that I didn't really follow on the [HACKERS] list and one of our consultants mentioned that this might be related to a bug fix between 8.4.0 and 8.4.1. Can anyone break this down into SysAdmin (NOT DBA!) language for me? If it involves reading manuals or whitepapers, that's fine. If it involves reading source code, I'm game to give it a try. If it involves years of study in database theory, I think it needs to be a little simpler than that!! Many thanks -- Dave Barton Senior Systems Administrator - Comodo smime.p7s Description: S/MIME Cryptographic Signature
[ADMIN] PostgreSQL Active-Active Configuration
Hi everyone, I am looking for some Master-Master replication solutions for PostgreSQL database. Please let me know if you are using one and if you are happy with the performance. Thanks -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] PITR backup & Restore issue
You other option is to use Slony. Create replica and in a short outage you can move master to replica. Chirag Dave 416-673-4102 Database Administrator, Afilias Canada Corp. [email protected] On Wed, May 5, 2010 at 11:27 AM, raghu ram wrote: > Hi Postgres Guru's, > > > Is it possible to use the Hot Backup (PITR Backup) of PostgreSQL on an > Operating system to restore the database on other Operating system. > > > Thanks & Regards > > Raghu >
Re: [ADMIN] Slony-I - Update trigger on a replicated table
On Thu, May 13, 2010 at 5:11 PM, Plugge, Joe R. wrote: > Nevermind ... I RTFM ... > > echo "alter table mytable ENABLE REPLICA TRIGGER check_lockout;" | psql > mydb > This is not good idea. Always run DDL script using Slony execute script. http://www.slony.info/documentation/ddlchanges.html > > -Original Message- > From: [email protected] [mailto: > [email protected]] On Behalf Of Plugge, Joe R. > Sent: Thursday, May 13, 2010 4:02 PM > To: [email protected] > Subject: [ADMIN] Slony-I - Update trigger on a replicated table > > Anyone know if it is possible to create an update trigger on the > destination side of a replicated table in Slony-I and have the replication > itself cause the trigger to fire? I was able to add the trigger but when run > the test transaction through, it does not seem to be firing. It works on > the non-replicated version of the solution. > > Running postgres 8.4.1 and Slony-I 2.0.3 > > > -- > Sent via pgsql-admin mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > > -- > Sent via pgsql-admin mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >
Re: [ADMIN] How to find if a SELECT is reading from buffer or disk ?
On Tue, May 25, 2010 at 1:48 PM, Balkrishna Sharma wrote: > Hi, > > I am increasing the shared_buffer size in postgresql.conf and want to > measure its effect on READ. In essence I want to know if the SELECT queries > I am firing repeatedly is reading from the buffer or going directly to the > disk. > > I am expecting the first SELECT to go to disk and the subsequent call of > the same SELECT to read from buffer . > > Right now I am just looking at execution time of the SELECTs and trying to > conclude. But there should be a direct way to see where the SELECT reads > from. > You can also use pg_stat_database view. you can compute cache reads percentage of the total number of reads (cache and physical) between the two snapshots using pg_stat_database.blks_hit and pg_stat_database.blks_read. Chirag Dave 416-673-4102 Database Administrator, Afilias Canada Corp. [email protected] > How can I accomplish this ? > > Thanks > Bala > > -- > The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with > Hotmail. Get > busy.<http://www.windowslive.com/campaign/thenewbusy?tile=multicalendar&ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5> >
Re: [ADMIN] How to find if a SELECT is reading from buffer or disk ?
On Wed, May 26, 2010 at 5:25 AM, Cédric Villemain < [email protected]> wrote: > 2010/5/25 Chirag Dave : > > > > > > On Tue, May 25, 2010 at 1:48 PM, Balkrishna Sharma > wrote: > >> > >> Hi, > >> I am increasing the shared_buffer size in postgresql.conf and want to > >> measure its effect on READ. In essence I want to know if the SELECT > queries > >> I am firing repeatedly is reading from the buffer or going directly to > the > >> disk. > >> I am expecting the first SELECT to go to disk and the subsequent call of > >> the same SELECT to read from buffer . > >> Right now I am just looking at execution time of the SELECTs and trying > to > >> conclude. But there should be a direct way to see where the SELECT reads > >> from. > > > > You can also use pg_stat_database view. you can compute cache reads > > percentage of the total number of reads (cache and physical) between the > two > > snapshots using pg_stat_database.blks_hit and > pg_stat_database.blks_read. > > views does not reflect this exact behavior : hit and read are relative > to hit shared buffers and request a block (from OS page cache or from > disk). > > Correct, thats where pgFincore will be usefull. > > > > > Chirag Dave 416-673-4102 > > Database Administrator, Afilias Canada Corp. > > [email protected] > > > > > >> > >> How can I accomplish this ? > >> Thanks > >> Bala > >> > >> The New Busy think 9 to 5 is a cute idea. Combine multiple calendars > with > >> Hotmail. Get busy. > > > > > > -- > Cédric Villemain 2ndQuadrant > http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support >
Re: [ADMIN] pginstaller policy?
On Fri, May 28, 2010 at 6:56 PM, Bruce Momjian wrote: > Marc G. Fournier wrote: >> On Fri, 28 May 2010, Bruce Momjian wrote: >> >> > >> > I talked to Dave Page and it seems the pginstaller (non-bitRock) Win32 >> > installers were _not_ moved from their beta holding area to the download >> > section. >> > >> > They have just been moved and the URLs are now: >> > >> > ftp://developer.postgresql.org/pub/binary/v8.3.11/win32/ >> > ftp://developer.postgresql.org/pub/binary/v8.2.17/win32/ >> > >> > They will propogate to the main download pages in 24 hours. >> >> That's odd ... wonder why v8.3 and v8.2 were overlooked ... but v8.4 was >> moved? > > No idea. I talked to Dave Page on IM and he thought it wasn't moved. It wasn't until I did it. There are no 8.4 MSI installers to be moved. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Help compiling --with-ldap on Solaris 11 Express?
TL; DR version: I'm trying to compile 9.0.4 on Solaris 11 Express and having problems with LDAP support; I finally got it to compile and install but when I try to authenticate via LDAP I get "Assertion failed: LBER_VALID( ber ), file sasl.c, line 103, function ldap_sasl_bind" and it kills the process. More detailed version: I started with a clean install of Solaris Express 11 on a VMWare ESXi VM, single processor/2GB RAM. I installed from packages gcc-3, library/openldap and system/library/math/header-math. I downloaded the source tarball, uncompressed it and tried to configure: ./configure \ --enable-nls \ --with-python \ --with-openssl \ --with-libxml \ --enable-thread-safety \ --enable-dtrace \ --with-ldap \ --with-libs=/usr/lib \ --with-includes=/usr/include/openldap Configure failed with an error "checking for ldap_simple_bind in -lldap_r... no configure: error: library 'ldap_r' is required for LDAP" It looked to me that it wasn't finding /usr/lib/libldap_r-2.4.so I added a symlink pointing libldap_r.so to libldap_r-2.4.so -- after that configure worked but make failed: Undefined first referenced symbol in file ldap_start_tls_slibpq/auth.o So I tried setting LDFLAGS in the configure and make commands: LDFLAGS="-lldap_r" ./configure LDFLAGS="-lldap_r" gmake That worked okay, as did "gmake install" -- I could initdb successfully and connect locally with psql. But when I try to connect using LDAP auth it pukes: Assertion failed: LBER_VALID( ber ), file sasl.c, line 103, function ldap_sasl_bind LOG: server process (PID 26751) was terminated by signal 6 LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2011-07-01 01:54:39 CDT LOG: database system was not properly shut down; automatic recovery in progress LOG: consistent recovery state reached at 0/2501DBB4 LOG: record with zero length at 0/2501DBB4 LOG: redo is not required LOG: autovacuum launcher started LOG: database system is ready to accept connections Any pointers would be appreciated; I'm far from an expert on either Solaris or PostgreSQL, so it's very possible that I'm making one or more elementary mistakes. -- Dave Pooser Cat-Herder-in-Chief, Pooserville.com "...Life is not a journey to the grave with the intention of arriving safely in one pretty and well-preserved piece, but to slide across the finish line broadside, thoroughly used up, worn out, leaking oil, and shouting GERONIMO!!!" -- Bill McKenna -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Help compiling --with-ldap on Solaris 11 Express?
On 7/1/11 9:21 AM, "Achilleas Mantzios" wrote: >Looking in openldap-2.4.10/libraries/liblber/lber-int.h:94 i see : >#define LBER_VALID(ber) ((ber)->ber_valid==LBER_VALID_BERELEMENT) >the assertion fails for some reason. > >Could you make a proper reinstallation of the openldap related packages >(openldap-client and possibly -devel), >remove the manual symbolic link, and try to ./configure again? OK, this is Solaris so there isn't an openldap-client or openldap-devel; there's only SUNWopenldap, which I installed. >You might change the --with-libs or --with-include part to match wherever >your ldap client installation is located. OK. >Also, for the moment, drop the rest of --with- arguments, until you have >a working postgresql with ldap, >and you'll go gradually from there. Well, dropping the rest of --with- arguments leaves me with: ./configure \ --with-ldap \ --with-libs=/usr/lib/amd64 \ --with-libs=/usr/sfw/lib/amd64 \ --with-includes=/usr/include/openldap Which just gives a config error: checking for ldap_simple_bind in -lldap_r... no configure: error: library 'ldap_r' is required for LDAP >Unfortunately i do not have a Solaris system to test. I'm pretty sure this is a Solaris-specific set of problems, starting with the fact that instead of libldap_r.so it's got libldap_r-2.4.so and the configure script can't sort that out even though nm shows that ldap_r-2.4.so does in fact have the function it's griping about (ldap_simple_bind) in both the 32-bit and 64-bit versions. -- Dave Pooser Cat-Herder-in-Chief, Pooserville.com "...Life is not a journey to the grave with the intention of arriving safely in one pretty and well-preserved piece, but to slide across the finish line broadside, thoroughly used up, worn out, leaking oil, and shouting GERONIMO!!!" -- Bill McKenna -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Help compiling --with-ldap on Solaris 11 Express?
On 7/2/11 7:16 PM, "Tom Lane" wrote: >> I'm pretty sure this is a Solaris-specific set of problems, starting >>with >> the fact that instead of libldap_r.so it's got libldap_r-2.4.so and the >> configure script can't sort that out even though nm shows that >> ldap_r-2.4.so does in fact have the function it's griping about >> (ldap_simple_bind) in both the 32-bit and 64-bit versions. > >The Oracle documentation avers that "-lldap" ought to be sufficient; >see for example ldap_simple_bind page in >http://download.oracle.com/docs/cd/E19963-01/pdf/821-1466.pdf >and even without that evidence it's hard to believe that they'd expect >calling applications to hardwire a library version number into their >build scripts. They don't; they cheat and symlink: root@testdb:/home/locadmin/postgresql-9.0.4# ls -l /usr/lib/libldap.so lrwxrwxrwx 1 root root 12 Jul 2 14:48 /usr/lib/libldap.so -> libldap.so.5 But there's no such libldap_r.so symlink created by default. >So I'm pretty sure that there *should* be a libldap_r.so someplace. >You could possibly try symlinking that to the version-numbered library. Yep, and I did that to get past the configure problems, which allows me to encounter a whole new problem in make :^) >If all else fails you could --disable-thread-safety. Is that safe to do on a server with a few dozen concurrent users? -- Dave Pooser Cat-Herder-in-Chief, Pooserville.com "...Life is not a journey to the grave with the intention of arriving safely in one pretty and well-preserved piece, but to slide across the finish line broadside, thoroughly used up, worn out, leaking oil, and shouting GERONIMO!!!" -- Bill McKenna -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Help compiling --with-ldap on Solaris 11 Express?
>> >>So I'm pretty sure that there *should* be a libldap_r.so someplace. >>You could possibly try symlinking that to the version-numbered library. > >Yep, and I did that to get past the configure problems, which allows me >to >encounter a whole new problem in make :^) That problem is that when I run gmake it eventually errors out with: Undefined first referenced symbol in file ldap_start_tls_slibpq/auth.o ld: fatal: symbol referencing errors. No output written to postgres Needless to say, checking with nm confirms that ldap_start_tls_s is present in libldap_r.so -- so I try forcing the issue: root@testdb:/home/locadmin/postgresql-9.0.4#LIBS="-lldap_r" gmake At this point 'gmake' works, as does 'gmake install' and when I check with ldd things appear to be linked properly: root@testdb:/home/locadmin/postgresql-9.0.4# ldd /usr/local/pgsql/bin/postmaster libnsl.so.1 => /lib/libnsl.so.1 libsocket.so.1 =>/lib/libsocket.so.1 libm.so.2 => /lib/libm.so.2 libldap_r-2.4.so.2 =>/usr/lib/libldap_r-2.4.so.2 libldap.so.5 => /usr/lib/libldap.so.5 libc.so.1 => /lib/libc.so.1 libmp.so.2 =>/lib/libmp.so.2 libmd.so.1 =>/lib/libmd.so.1 libumem.so.1 => /lib/libumem.so.1 liblber-2.4.so.2 => /usr/lib/liblber-2.4.so.2 libresolv.so.2 =>/lib/libresolv.so.2 libgen.so.1 => /lib/libgen.so.1 libnet.so => /usr/lib/libnet.so libsasl.so.1 => /usr/lib/libsasl.so.1 libssl.so.0.9.8 => /lib/libssl.so.0.9.8 libcrypto.so.0.9.8 =>/lib/libcrypto.so.0.9.8 libnspr4.so => /usr/lib/mps/libnspr4.so libplc4.so =>/usr/lib/mps/libplc4.so libnss3.so =>/usr/lib/mps/libnss3.so libssl3.so =>/usr/lib/mps/libssl3.so libgcc_s.so.1 => /usr/sfw/lib/libgcc_s.so.1 libpthread.so.1 => /lib/libpthread.so.1 librt.so.1 =>/lib/librt.so.1 libdl.so.1 =>/lib/libdl.so.1 libnssutil3.so =>/usr/lib/mps/libnssutil3.so libplds4.so => /usr/lib/mps/libplds4.so libthread.so.1 =>/lib/libthread.so.1 root@testdb:/home/locadmin/postgresql-9.0.4# ldd /usr/local/pgsql/bin/postgres libnsl.so.1 => /lib/libnsl.so.1 libsocket.so.1 =>/lib/libsocket.so.1 libm.so.2 => /lib/libm.so.2 libldap_r-2.4.so.2 =>/usr/lib/libldap_r-2.4.so.2 libldap.so.5 => /usr/lib/libldap.so.5 libc.so.1 => /lib/libc.so.1 libmp.so.2 =>/lib/libmp.so.2 libmd.so.1 =>/lib/libmd.so.1 libumem.so.1 => /lib/libumem.so.1 liblber-2.4.so.2 => /usr/lib/liblber-2.4.so.2 libresolv.so.2 =>/lib/libresolv.so.2 libgen.so.1 => /lib/libgen.so.1 libnet.so => /usr/lib/libnet.so libsasl.so.1 => /usr/lib/libsasl.so.1 libssl.so.0.9.8 => /lib/libssl.so.0.9.8 libcrypto.so.0.9.8 =>/lib/libcrypto.so.0.9.8 libnspr4.so => /usr/lib/mps/libnspr4.so libplc4.so =>/usr/lib/mps/libplc4.so libnss3.so =>/usr/lib/mps/libnss3.so libssl3.so =>/usr/lib/mps/libssl3.so libgcc_s.so.1 => /usr/sfw/lib/libgcc_s.so.1 libpthread.so.1 => /lib/libpthread.so.1 librt.so.1 =>/lib/librt.so.1 libdl.so.1 =>/lib/libdl.so.1 libnssutil3.so =>/usr/lib/mps/libnssutil3.so libplds4.so => /usr/lib/mps/libplds4.so libthread.so.1 =>/lib/libthread.so.1 So I set up my postgresql.conf to allow remote connections and my pg_hba.conf to use LDAP authentication; I load my data; I try to connect using LDAP-- And authentication fails, and I see in the log: Assertion failed: LBER_VALID( ber ), file sasl.c, line 103, function ldap_sasl_bind LOG: server process (PID 7675) was terminated by signal 6 LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process Of course the function is also present in that @#$%^& libldap_r: root@testdb:/home/locadmin# nm /usr/lib/libldap_r.so | grep "ldap_sasl_bind" [1385] |104232| 760|FUNC |GLOB |0|12 |ldap_sasl_bind [1309] |104992| 327|FUNC |GLOB |0|12 |ldap_sasl_bind_s It seems there's some magic incantation to REALLY bind that I'm just not seeing here. Any help gratefully accepted. -- Dave Pooser Cat-Herder-in-Chief, Pooserville.com "...Life is not a journey to the grave with the intention of arriving safely in one pretty and well-preserved piece, but to slide across the finish line broadside, thoroughly used up, worn out, leaking oil, and shouting GERONIMO!!!" -- Bill McKenna -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Help compiling --with-ldap on Solaris 11 Express?
On 7/2/11 8:50 PM, "Tom Lane" wrote: >Most likely what is happening is that one of the other libraries called >by the postmaster is pulling in libldap_r --- a bit of research with ldd >should confirm or deny that. If so, you could probably fix things by >hacking LDAP_LIBS_BE in the configured src/Makefile.global to pull >in libldap_r not libldap. That worked perfectly-- thanks so much for your help! Do you think this is a Solaris bug, or a Postgres bug, or is this a "works as designed but sysadmin is an idiot" situation? If it's the first, I might as well file a bug with Oracle, not that I expect them to be in any hurry to improve compatibility with a rival DB. -- Dave Pooser Cat-Herder-in-Chief, Pooserville.com "...Life is not a journey to the grave with the intention of arriving safely in one pretty and well-preserved piece, but to slide across the finish line broadside, thoroughly used up, worn out, leaking oil, and shouting GERONIMO!!!" -- Bill McKenna -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Help compiling --with-ldap on Solaris 11 Express?
On 7/3/11 10:33 AM, "Tom Lane" wrote: >Did you identify what it was that was linked to libldap_r? Not yet; it was 4AM at that point here in Dallas and I'd been beating my head against it off and on since 11AM. So I was too brain-fried to figure out how to identify the library and too exhausted to put in the Google-time looking for a step-by-step guide. Maybe this evening, unless you can hit me with a step-by-step off the top of your head. -- Dave Pooser Cat-Herder-in-Chief, Pooserville.com "...Life is not a journey to the grave with the intention of arriving safely in one pretty and well-preserved piece, but to slide across the finish line broadside, thoroughly used up, worn out, leaking oil, and shouting GERONIMO!!!" -- Bill McKenna -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] libpqxx for x86 builds of RHEL?
I apologize if this is not the correct list to post this in, but I just noticed that libpqxx is not in the yum repos for the 32-bit builds of RHEL. Would it be possible to add them to the 32-bit repos? Thanks, Dave
