Re: [ADMIN] odbc
On Tue, Mar 29, 2011 at 10:51 AM, Marc Fromm marc.fr...@wwu.edu wrote: I am running postgres on a red hat linux server. postgresql-python-8.1.23-1.el5_6.1 postgresql-test-8.1.23-1.el5_6.1 postgresql-libs-8.1.23-1.el5_6.1 postgresql-docs-8.1.23-1.el5_6.1 postgresql-contrib-8.1.23-1.el5_6.1 postgresql-8.1.23-1.el5_6.1 postgresql-pl-8.1.23-1.el5_6.1 *postgresql-odbc-08.01.0200-3.1* postgresql-jdbc-8.1.407-1jpp.4 postgresql-server-8.1.23-1.el5_6.1 postgresql-tcl-8.1.23-1.el5_6.1 User on windows computers would like to odbc to the postgres databases. I downloaded the windows postgres odbc drivers from the following site and installed version “psqlodbc-08_01_0200” on the windows computer. http://ftp9.us.postgresql.org/pub/mirrors/postgresql/odbc/versions/msi/ When I configure a “File Data Source” connection in access, I cannot connect to the postgresql database. I get the message, “A connection could not be made using the file data source parameters entered. Save non-verified file DSN?” The parameters entered are: Database: database_name Server: I entered the IP address of the server User Name: I entered postgres Password: SSL Mode: prefer Port: 5432 (which is the port used by postgres) Do I need to configure the /etc/odbc.ini file? It is currently just a blank file. Thanks for any help. You also need to make sure postgresql is listening on a public IP address AND you don't have any intervening filewall -- hostbased or otherwise. Be default, redhad does not allow connections to port 5432 via its standard rule set. I would install something like pgadmin on the windows client and see if that will allow you to connect first. If pgadmin connects, then its likely and odbc config issue. Marc -- David Bear College of Public Programs at ASU 602-496-0424
[ADMIN] rpm failure
While running a yum update on a system, the update fails with the message: Transaction Check Error: package postgresql-libs-8.3.11-1PGDG.el5.x86_64 (which is newer than postgresql-libs-8.1.22-1.el5_5.1.i386) is already installed I've checked, and I don't have postgresql-libs-8.1.22-1.el5_5.1.i386 installed. I've installed postgresql from the pgdg rpm. I've modified all the /etc/yum.repos.d/ files to exclude=postgresql* I have /etc/yum.repos.d/pgdg-83.repo that gets v8.3 . I don't know why rpm thinks I have something else installed. I just need to know how to clear the error. Anyone have any pointers? -- David Bear College of Public Programs at ASU 602-494-0424
[ADMIN] running in a virtual environment
I'm wondering what experience people have had running pg 8.x in a virtualized environment. Are there any best practices to follow that differen when running on a real metal? Are there performance issue? What virtual environment seems most friendly to pg ? Xen, VMWare, other? What about storage; is it best to keep the cluster in a filesystem that is part of the vm-image or on a mounted file system that exists external to the image? (in other words, a /dev/sdxx that is really some NAS type of device, or NFS? CIFS?) -- David Bear College of Public Programs at ASU 602-494-0424
Re: [ADMIN] postgres data permission
this doesn't make sense. why go through the overhead of smb to get to a database cluster over a network connection when pg can already work over tcpip sockets and work much more switfly on a local file system? 2010/7/15 Silvio Brandani silvio.brand...@tech.sdb.it Hi all, I need to start a postgres istance on a samba filesystems /data which have rwxrwxrwx (777) permissions, Ic ould not change such permissions. If I try to start postgres it complain that should be 700 permission . How can I skip this control by postgres startup process and be able to startup istance on this special folder?? --- Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/news/privacy.html Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/codice_penale_616.html L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme. -- -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- David Bear College of Public Programs at ASU 602-494-0424
Re: [ADMIN] PgsqlODBC 8.1 and 8.4 side-by-side installation
On Wed, Jul 7, 2010 at 9:49 AM, Rob Richardson rob.richard...@rad-con.comwrote: I wanted to try using the ODBC driver for PostgreSQL 8.4. But when I installed it, I was surprised and dismayed to see that the old driver no longer appeared in the list of available drivers when I tried to create a new DSN. At first, I thought that the new driver was working, but any update query gave me a multiple-step operation raised errors. See error list error. is this a 64bit machine? if so, you will need to run the 32bit odbc manager in order to find the pgodbc driver. Is it possible to have the 8.1 and 8.4 ODBC drivers available at the same time? If so, how do I install the 8.4 driver so that the 8.1 driver is still available? Thanks very much! RobR -- David Bear College of Public Programs at ASU 602-494-0424
Re: [ADMIN] redhat and postgresql management
Thank you all. This group is amazingly helpful. The combinations of emails and the document linked below did the trick. On Fri, Jun 18, 2010 at 10:40 AM, Greg Smith g...@2ndquadrant.com wrote: David Bear wrote: I have RHEL 5.4 (64bit) that has a postgresql-lib 8.1 installed to satisfy dependancy for a dozen other packages including apache, php and other apache modules, and subversion. I want to install postresql 8.3 but yum informs me of the version conflict of the library. May i safely just over write the postgresql-lib with the 8.3 lib ?? No. I wrote a detailed guide to exactly how to step around this problem by installing the compat-postgresql-libs package provided by the PostgreSQL 8.3 package set you may find useful: http://www.westnet.com/~gsmith/content/postgresql/pgrpm.htmhttp://www.westnet.com/%7Egsmith/content/postgresql/pgrpm.htm -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- David Bear College of Public Programs at ASU 602-494-0424
[ADMIN] redhat and postgresql management
I have RHEL 5.4 (64bit) that has a postgresql-lib 8.1 installed to satisfy dependancy for a dozen other packages including apache, php and other apache modules, and subversion. I want to install postresql 8.3 but yum informs me of the version conflict of the library. May i safely just over write the postgresql-lib with the 8.3 lib ?? I'd rather not have to uninstall all the dependancies, install pg, then reinstall everything I just removed facing more possible dependacy issue. any advice much appreciated. -- David Bear College of Public Programs at ASU 602-494-0424
[ADMIN] failure on system update for lack of gpg key
while running yum update, yum stopped with the following message: warning: rpmts_HdrFromFdno: header V3 DSA signature: NOKEY, key ID e8e345b8 pgdg83/gpgkey public key for postgresql-server is not installed. is there a quick how-to or guide on getting the correct gpg installed? -- David Bear College of Public Programs at ASU 602-494-0424
Re: [ADMIN] failure on system update for lack of gpg key
Thanks for the info, and the pointers. Since I am running pg 8.3.10-2PGDG.el5.x86_64 I wonder if you would have the pgp key for that version? On Thu, Apr 8, 2010 at 12:51 PM, Lacey Powers lacey.pow...@commandprompt.com wrote: David Bear wrote: while running yum update, yum stopped with the following message: warning: rpmts_HdrFromFdno: header V3 DSA signature: NOKEY, key ID e8e345b8 pgdg83/gpgkey public key for postgresql-server is not installed. is there a quick how-to or guide on getting the correct gpg installed? -- David Bear College of Public Programs at ASU 602-494-0424 Hello David, Please see the following article: https://public.commandprompt.com/news/3 This should get you on your way. If you have any more issues, please feel free to contact us. Regards, Lacey -- Lacey Powers The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 104 PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- David Bear College of Public Programs at ASU 602-494-0424
[ADMIN] finding dev rpms
I'm needing to installed psycopg2.. but I've hit a dependancy problem. pscycopg2 needs libpq stuff -- which is the the developer rpms. Trouble is I can only find developer rpms in yum repositories. So I went to install yum and found that since my red had is x86_64, yum croaks on importing cElementTree -- which it does have but won't import. Since yum wasn't really designed for RHEL -- it may not be worth it to try to resolve that issue, which leads me back to finding a downloadable rpm for the postgres developer packages; which I can't seem to locate at postgresql.org. Can anyone point me to postgresql 8.2 development rpms? or source? -- David Bear College of Public Programs at ASU 602-464-0424
[ADMIN] example of insert timestamp
I've been reading about triggers on insert and found the page at http://www.postgresql.org/docs/8.1/interactive/triggers.html with some sample code in the comments. I'm thinking what I want can't really be this involved. I want to have a table with a timestamp field that automatically gets the value of now() on insert. The timestamp will never be updated. I assume I need to create a trigger to do this. If there is an easier way, please advise. Otherwise, is there any sample code that would should be how to do this? The sample on the page above looks like overkill. -- David Bear College of Public Programs/ASU 411 N Central, Phoenix, AZ 85004 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] example of insert timestamp
On Monday 25 February 2008 18:21, Aaron Bono wrote: On Mon, Feb 25, 2008 at 7:21 PM, David Bear [EMAIL PROTECTED] wrote: I've been reading about triggers on insert and found the page at http://www.postgresql.org/docs/8.1/interactive/triggers.html with some sample code in the comments. I'm thinking what I want can't really be this involved. I want to have a table with a timestamp field that automatically gets the value of now() on insert. The timestamp will never be updated. I assume I need to create a trigger to do this. If there is an easier way, please advise. Otherwise, is there any sample code that would should be how to do this? The sample on the page above looks like overkill.http://www.postgresql.org/docs/faq In your create table statement: create_dt TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL Thanks. I knew it must be easier. This is exactly what I want. -- David Bear College of Public Programs/ASU 411 N Central, Phoenix, AZ 85004 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] creating column content from entry values
On Thursday 21 February 2008 19:17, Tom Lane wrote: David Bear [EMAIL PROTECTED] writes: Breifly, I want to do an insert where the values for three of the attributes are combined via a function and make up the value for the fourth attribute. Well, there's more than one way to do it. What you didn't tell us is just how tightly you want to bind column 4 to be func-of-cols-1-2-3. Thanks for your kind response. I guess I didn't give enough information to have anyone give me a complete solution. Your questions below help me a little more and I will attempt to outline my use case. Do you want it to be purely read-only and always equal to the function on the current values of the other columns? Yes, I want it to be purely read only. The purpose of this is to generate a 'canonical string' based upon the input of some values. However, I do not yet know what the cononical form of the string would be. So I wanted to abstract it into a function. Do you want to force it to be that way on initial insertion of the row, but subsequent updates could allow the columns to diverge? I do not ever want it to change. There will never be updates to the other columns in the tuple. Do you merely want it as a default that could be overridden during the insert? Also, is the function expensive enough that you really want to precompute it at insert time and store the output; or maybe it should be just a virtual column where the function is computed on demand during readout? I don' t thing the function will be expensive. It will be mostly string slicing, casing, etc. I would rather have the function that does it associated with the data base rather than code that generates and insert statement. Depending on what you think about these questions, you might choose to not store column 4 physically at all, but just have it be part of a view wherein the function is computed on-the-fly. Or you could use an ON INSERT and/or ON UPDATE trigger, perhaps with different degrees of aggressiveness about whether it overrides a pre-supplied value for column 4. Okay, no I think what I am asking for as an insert trigger. The value must be stored as the table is a lookup table to control entry in other tables. Thus, the field that is generated will be a foreign key for other attributes in other relations. regards, tom lane -- David Bear College of Public Programs/ASU 411 N Central, Phoenix, AZ 85004 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[ADMIN] creating column content from entry values
I am too new to this environment to know what to call what I want to do.
Breifly, I want to do an insert where the values for three of the attributes
are combined via a function and make up the value for the fourth attribute.
The function may be a simple as a concatenation, or I may want to do other
things to manipulate the strings that are entered. I think what I want is a
function that I can call when I do the insert like this
insert into mytable (col1, col2, col3) values ('somestring', 'another
string' )
but have the insert fix col3 to be whatever it the function computes it to be.
does this make any sense?
--
David Bear
College of Public Programs/ASU
411 N Central, Phoenix, AZ 85004
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[ADMIN] PITR with rsync
I've been looking at the pages on PITR and am wondering if anyone has tried using rsync to accomplish this. I'm still uncomfortable with using the file system style backup method in PITR and am very interested in ' more information ' on how others may be doing backups. Specifically, I assume that PITR methods would also be accompanied with some combination of pg-dump. I am curious about how frequently a pg-dump would be done if a PITR method was also used (every 24 hours, every 7 days, not at all ) ?? Also, looking at the archive_command argument in postgresql.conf. Has anyone use rsync there? -- David Bear phone: 602-496-0424 fax:602-496-0955 College of Public Programs/ASU University Center Rm 622 411 N Central Phoenix, AZ 85007-0685 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] PITR with rsync
On Mon, Aug 06, 2007 at 08:27:13PM -0400, Tom Lane wrote: David Bear [EMAIL PROTECTED] writes: I'm still uncomfortable with using the file system style backup method in PITR and am very interested in ' more information ' on how others may be doing backups. Specifically, I assume that PITR methods would also be accompanied with some combination of pg-dump. You assume wrong. Replaying WAL demands starting from a physical not logical dump, because page and tuple numbers and so forth have to be the same as in the master. A pg_dump backup will simply not work. I am curious about how frequently a pg-dump would be done if a PITR method was also used (every 24 hours, every 7 days, not at all ) ?? I'd personally be inclined to take a pg_dump every now and then just to have an independent backup method. Especially if I wasn't comfortable with PITR ;-). How often depends on how much bandwidth you can spare for backup purposes. okay, thanks. the pg-dump is not an augementation, but a completely separate backup method. Also, looking at the archive_command argument in postgresql.conf. Has anyone use rsync there? I suppose you could, but what's the point? Copying a single file that doesn't currently exist on the destination plays to none of rsync's strengths. well, I was using rysnc so I could copy to a different computer over ssh, not using the rsync protocol to catch diffs. I don't have a remote file system mounted so I can do just a cp. Am I still thinking wrong here? regards, tom lane -- David Bear phone: 602-496-0424 fax:602-496-0955 College of Public Programs/ASU University Center Rm 622 411 N Central Phoenix, AZ 85007-0685 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[ADMIN] incrementals with pg-dump
I googled a bit for an incremental or differential way to backup postgresq. I was hoping there would be switch on pg-dumpall that would backup all tupples added since some date. Doesn't look like it exists. I did find a backup method using the WAL files on the file system at http://www.oreillynet.com/pub/a/databases/2006/07/13/lamp-data-protection.html?page=last I also found this: http://www.postgresql.org/docs/8.1/static/backup-online.html I seem to remember around pg 7.x that file system level backups were very bad. Now, it seems the pg 8.x way is to allow the admin to take advantage of file system tools look tar, cpio, dump, etc. I was wondering if there any other recommendations beyond what is mentioned in the docs above? -- David Bear phone: 602-496-0424 fax:602-496-0955 College of Public Programs/ASU University Center Rm 622 411 N Central Phoenix, AZ 85007-0685 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[ADMIN] which distro, or to build
I'm getting really mad at RHEL 4. They do not package a recent postgres in the base distro, and only put 8.1.x in a disk call lamp-beta... So now I wonder what a 'recommended' way of running postgresql is -- meaning, any linux distro will do, just build it -- or is there any particular distro that 'does the right thing' with postgresql... meaning, they package recent versions and keep them updated with security fixes... I was using Suse 9.3 . They did a pretty good job -- at least they included pg 8.0 and kept it updated. RHEL just stinks. I suppose a I've come to like the way yast keeps packages together, helps resolve dependancies, etc. I switch to RHEL for 'enterprise' reasons. There is some central support for it though I have yet to figure out if that extends beyond just buying a site license for it.. Anyway, I'd appreciate any pointers on which system seems to be best suited for making life easier for a pg system admin. btw, I have run pg on FreeBSD as well, and relied on the ports maintainers to keep things updated. cvsupdate seemed to work reasonably well for this. Any advice? use packages.. build.. or ? -- David Bear phone: 602-496-0424 fax:602-496-0955 College of Public Programs/ASU University Center Rm 622 411 N Central Phoenix, AZ 85007-0685 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] which distro, or to build
On Tue, Mar 20, 2007 at 09:06:35PM -0400, Joshua Kramer wrote: David, I'm getting really mad at RHEL 4. They do not package a recent postgres in the base distro, and only put 8.1.x in a disk call lamp-beta... So now I wonder what a 'recommended' way of running postgresql is -- meaning, any linux distro will do, just build it -- Why not just download the RHEL4 packages from the Postgres mirrors? PG runs fine for me in that manner, on CentOS-4 (a RHEL rebuild). Thought of that and then wondered if I would face the same dependancy issues that using the rpms from redhat gave me. Do you know if redhat just redistributed the pg rpms? Or, do these rpms actually include depndancy resolution? For example, trying to install the rpm from redhat I get a failed dependance for libpq.so.4 --- but I have no idea what package redhat would have put libpq in... and so it goes. Where should I spend time? Tracking down dependancies? Or finding a better host operating system -- at least one packaged with things the pg needs. Cheers, -J -- David Bear phone: 602-496-0424 fax:602-496-0955 College of Public Programs/ASU University Center Rm 622 411 N Central Phoenix, AZ 85007-0685 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] kerberized odbc driver
I was hoping there was a kerberized odbc driver for postgresql that works on windows. I know this is a longshot. Anyone? -- David Bear phone: 602-496-0424 fax:602-496-0955 College of Public Programs/ASU University Center Rm 622 411 N Central Phoenix, AZ 85007-0685 Beware the IP portfolio, everyone will be suspect of trespassing ---(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] hba conf ident sameuser not working
On Wed, Feb 15, 2006 at 09:00:41PM -0500, Tom Lane wrote: David Bear [EMAIL PROTECTED] writes: now, back on teancum that has the tunnel on port , I do this: [EMAIL PROTECTED]:~ psql -p -h localhost -U tlhowell psql: FATAL: Ident authentication failed for user tlhowell [EMAIL PROTECTED]:~ psql -p -h localhost -U iddwb psql: FATAL: Ident authentication failed for user iddwb I'm afraid you're kind of stuck on getting that to work. In the cases that work, psql is executing on the server side of the ssh connection. Here, you want it to work on the client side. The problem is that the Postgres server is going to see that TCP connection as originating from a server-side sshd daemon process, and so ident is quite properly going to fail unless the requested database username matches whatever sshd is running as. You could possibly get it to work if you could get sshd to run the daemon subprocess as yourself instead of root ... dunno enough about ssh to know if that's possible. thats the path I was thinking along... Thanks. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend -- David Bear phone: 480-965-8257 fax:480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] hba conf ident sameuser not working
On Wed, Feb 15, 2006 at 11:27:36AM +0100, Peter Eisentraut wrote: David Bear wrote: now, when I connection to postgres from remote machines I ssh into the pg server, but I tunnel postgres traffic. The ident sameuser prevents tunneled connections from working. That is not true. Please give more detail so we can figure out what is really wrong. ok. 2 machine, mine is called teancum. The server running postgresql is called dbsrv1. starting with: == [EMAIL PROTECTED]:~ ssh -L :localhost:5432 [EMAIL PROTECTED] Password: Last login: Tue Feb 14 20:21:08 2006 from teancum Have a lot of fun... [EMAIL PROTECTED]:~ psql Welcome to psql 8.0.3, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit iddwb= \q [EMAIL PROTECTED]:~ psql -U tlhowell psql: FATAL: Ident authentication failed for user tlhowell = This is what I expect. users on dbsrv1 must be who they are. now, back on teancum that has the tunnel on port , I do this: === [EMAIL PROTECTED]:~ psql -p -h localhost -U tlhowell psql: FATAL: Ident authentication failed for user tlhowell [EMAIL PROTECTED]:~ psql -p -h localhost -U iddwb psql: FATAL: Ident authentication failed for user iddwb === That is with the following pg_hba.conf == # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all ident sameuser # IPv4 local connections: hostall all 127.0.0.1/32 ident sameuser # IPv6 local connections: hostall all ::1/128 ident sameuser === Now, if I change the connection for 'host' to use the trust method, I can connect as any user from psql AFTER I have already authenticated and setup the ssh tunnel. -- Peter Eisentraut http://developer.postgresql.org/~petere/ -- David Bear phone: 480-965-8257 fax:480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] hba conf ident sameuser not working
I am using auth method of 'ident sameuser' on a postgresql 8 server. ALL connections to postgres must come from localhost or local unix socket. now, when I connection to postgres from remote machines I ssh into the pg server, but I tunnel postgres traffic. The ident sameuser prevents tunneled connections from working. is there any workaround? -- David Bear phone: 480-965-8257 fax:480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[ADMIN] auth methods
after reading through http://www.postgresql.org/docs/8.0/static/auth-methods.html I'm still left wondering how to solve something that should be simple. Ident method looks like it might work. All I really want to do is prevent user jack, who is logged in and properly authenticated to the system as jack, from connecting to pg as user joe. I have kerb authentication working for all ssh connections to the data base server. I don't want another layer of authentication for postgresql. I'm currently using the trust method, but any user can connect to pg as any other pg user. the trouble is that ident seems to be non existent in suse linux (at least, it doesn't seem to be installed ) any pointers would be appreciated. -- David Bear phone: 480-965-8257 fax:480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[ADMIN] using pgdb and python
I am using python to collect data and insert it into postgresql. I am hoping there will be someone on this list that has used python for this becuase all the books and documentation on the subject seem sparse. I am using pgdb. I know psycpg is also a DBI compliant interface for postgresql. If there is a recommendation as to which one is better, I'd like to hear it. (better for me now means that it has better documentation, sample code, etc.) I am constructing a sql insert statement (parameterized) and would like to catch meaningfull exceptions like, keyviolation (on the insert) or other postgresql exceptions that might happen. So far the only exception I see from pgdb when I do a full traceback and anlyze that. What I would like is something like try: cursor.execute(sql, parameters) except KeyViolation: except DataConversionError: and such like. I would really appreciate any pointers you might have on using python with postgresql. thanks. -- David Bear phone: 480-965-8257 fax:480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[ADMIN] listening on tcp socket instead of unix domain
Hoping to get postgresql to listen on a tcp socket I made the following change to postgresql.conf: #listen_addresses = 'localhost' # what IP interface(s) to listen on; listen_addresses = 'localhost' # what IP interface(s) to listen on; # defaults to localhost, '*' = any #port = 5432 port = 5432 I understand this to mean, listen on localhost, ie 127.0.0.1. However, a netstat -l tells me: netstat -l | grep 5432 unix 2 [ ACC ] STREAM LISTENING 732094 /tmp/.s.PGSQL.5432 netstat -l | grep postg tcp0 0 localhost:postgresql*:* LISTEN tcp0 0 localhost:postgresql*:* LISTEN This doesn't look right. Moreover, when attempting to create an ssh tunnel to connect to postgres from a windows box, the connection fails. Is postgres really listening on a socket that is 'connect-able' through ssh tunneling? If so, then would using ssh command ssh -L 5432:remotehost:5432 [EMAIL PROTECTED] be sufficient to connect to the postgres running on remote host? This fails when running psql on the machine with message: psql -p 5432 psql: could not connect to server: No such file or directory ... So, I don't think something is quite working. Please advise. -- David Bear phone: 480-965-8257 fax:480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] psql copy errors
I'm finding the \copy is very brittle. It seems to stop for everyone little reason. Is there a way to tell it to be more forgiving -- for example, to ignore extra data fields that might exists on a line? Or, to have it just skip that offending record but continue on to the next. I've got a tab delimited file, but if \copy sees any extra tabs in the file it just stops at that record. I want to be able to control what pg does when it hits an exception. I'm curious what others do for bulk data migration. Since copy seems so brittle, there must be a better way... -- David Bear phone: 480-965-8257 fax:480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[ADMIN] stupid insert error
I guess I'm too stupid to see the error, but I don't understand why the following fails. insert into person3 (asuid, fname, lname, addedby, addedon, slopbucket) values (123455, name, name, entered, 12/12/2004, NULL); ERROR: column 123455 does not exist is the double quote byting me? -- David Bear phone: 480-965-8257 fax:480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] possible os recommendations
wanting to avoid religious warfare, I'm curious if there may be some who would have recommendations for what operating system is 'best' to run postgresql 8.x on.. I've used postgresql 7.x on FreeBSD. It was already. But, I'm getting sucked into the linux world and have found Suse 9.3 a pretty good distro. Since I've chosen a packaged distro, I realize that I depend upon 'them' to keep me updated with criticals and other bug fixes wrt postrgresql. That may be consider bad by some, good by others. I'm more concerned though about the complete experience, i.e. postgresql performance on Linux vs Bsd vs Windows... Maintainability.. Stability... os tweeks that were really needed to make pg work well, etc. I would really like to hear from those who have used postgresql on multiple operating systems.. thanks. -- David Bear phone: 480-965-8257 fax:480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] query and stored procedures
I'm so totally new to postgresql that I'm not sure how to word this. I want to create a query, that does two things 1) take input in the form of a string of numbers, and test if a) is ssn b) or internal id if the string is an ssn, make a second query to another data base, asking it to convert the ssn to our internal id otherwise, 2) use the id given to query an existing table I'm not sure what feature/function postgresql has to help me do this. I'd like to stick with using python as my language, and I'm guessing this would be stored procedure, but I'm too new to know. Please point me to some good reading. -- David Bear phone: 480-965-8257 fax:480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 8: explain analyze is your friend
[ADMIN] product feature sets
This may sound like an impossible thing to ask because it may seem like an apples to oranges questions. If there were a book entitled Using Postgresql with MS Access I would suspect answers there. Still I'm going to draw upon the collective experience of this list. 1) are there any comparisons done between the relational features of ms-access vs postgresql? Things like, does access enforce referential integrity... triggers... transactions... etc. 2) Since Access has the upsize tool that helps converting data to an ms-sql server, has anyone made a similar tool that helps with access? I have received from this group an email with some script in it that helps with table migration but I haven't had a chance to really understand what it does -- Many thanks in advance. -- David Bear phone: 480-965-8257 fax:480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 Beware the IP portfolio, everyone will be suspect of trespassing ---(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] using oids as primary keys and foreign keys
I've looked in the archives searching on oid and 'access' (microsoft) to see how others have handled migration from data bases in ms-access to postgresql. MS access has an auto-inc field that is used frequently as a uniq id. Seems the OID has the same function. Yet, I've seem parts of messages that say not to use the OID in that manner, but nothing seems 'definitive'. Can I use an OID as a foreign key in another table? If not, what is the prefered method for replicating the auto-inc field that ms-access users are so fond of... a 'sequence'? -- David Bear phone: 480-965-8257 fax:480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] using psql copy command
I'm getting fairly frustrated here. I'm trying to populate a table using psql copy command. The file lives in the client machine where psql is running, not on the back end. I assume from the readings that I can you the \copy command to get to data in my clients file system. Yet, its not working.. Here are the commands: [EMAIL PROTECTED]:~/data/copp/dev/npcenter ls -l total 231 -rw-r--r--1 3117 iddwb 88979 2004-11-08 16:32 contactName.txt -rw---1 3117 iddwb 145024 2004-11-08 16:09 tblContactNames.txt [EMAIL PROTECTED]:~/data/copp/dev/npcenter psql -p 5432 -h localhost npcenter -U david npcenter= \copy contactName from /home/iddwb/data/copp/dev/npcenter/contact Name.txt /home/iddwb/data/copp/dev/npcenter/contactName.txt: No such file or directory npcenter= \copy contactName from ./contactName.txt ./contactName.txt: No such file or directory npcenter= I must be missing something very obvious.. Help... -- David Bear phone: 480-965-8257 fax:480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 Beware the IP portfolio, everyone will be suspect of trespassing ---(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] pulling my hair out..
I'm using pg 7.3 on freebsd.. I've created a database use sql. now, as the pg superuser I cant seem to grant any permissions on the tables in the db.. npcenter-# \z Access privileges for database npcenter Schema |Table | Access privileges +--+--- public | contactNames | public | pga_diagrams | public | pga_forms| public | pga_graphs | public | pga_images | public | pga_layout | public | pga_queries | public | pga_reports | public | pga_scripts | (9 rows) npcenter=# grant all on table contactNames to group npcenter; ERROR: relation contactnames does not exist npcenter=# grant all on table 'contactNames' to group npcenter; ERROR: syntax error at or near 'contactNames' at character 20 I know I'm missing something simple... but would appreciate any help on this.. clearly, the table 'contactNames' does exist. I am using psql to do this. -- David Bear phone: 480-965-8257 fax:480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] tunneling through ssh
On Wed, Aug 18, 2004 at 09:21:54PM -0400, Tom Lane wrote: David Bear [EMAIL PROTECTED] writes: and on the ssh'd terminal tunnel session: $ channel 3: open failed: administratively prohibited: open failed --- Perhaps a firewalling problem? Look at your kernel packet filtering setup ... it's not uncommon for even local-loopback traffic to be aggressively filtered by default, and none of the people who set these things up think port 5432 is a standard service :-( thanks for all the suggestions. The solution was to tunnel 'localhost' -- the key is knowing which localhost localhost is referring too.. so, here's what I did. ssh -L 4001:localhost:5432 [EMAIL PROTECTED] This allows postgresql to listen on the 'localhost' interface.. thanks for all the replies. -- David Bear phone: 480-965-8257 fax:480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] long term persistent tunnels
Now that I have ssh tunneling working I am wondering what pitfalls away. Assume I have a server called db1 running the postgresql backend. Asume I have another server called web1 that will be connecting to db1 for data base operations. If I setup an ssh tunnel should I be aware of any problems with 1) spontaneously dropped connections...2) long term time out -- 3)other? any advice most appreciated. -- David Bear phone: 480-965-8257 fax:480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 Beware the IP portfolio, everyone will be suspect of trespassing ---(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] tunneling through ssh
I'm attempting to run pgsql through a tunnel. I'm using the default pg_hba.conf file for now which has the relevant information: local all all trust hostall all 127.0.0.1 255.255.255.255 trust I assume this means that the back end will bind to 127.0.0.1:5432 since that seems to be the default port number. Yet, when trying to come through the tunnel I get this in my logs on the 'server' machine - the one running postgres backend. --- Aug 18 16:00:40 dbsrv1 sshd[41006]: error: connect_to ::1 port 5432: Connection refused Aug 18 16:00:40 dbsrv1 sshd[41006]: error: connect_to 127.0.0.1 port 5432: Connection refused Aug 18 16:00:40 dbsrv1 sshd[41006]: error: connect_to localhost port 5432: failed. --- Am I missing something obvious? -- David Bear phone: 480-965-8257 fax:480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 Beware the IP portfolio, everyone will be suspect of trespassing ---(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] tunneling through ssh
On Wed, Aug 18, 2004 at 04:21:24PM -0700, Steve Crawford wrote: On Wednesday 18 August 2004 4:13 pm, David Bear wrote: I'm attempting to run pgsql through a tunnel. I'm using the default pg_hba.conf file for now which has the relevant information: local all all trust hostall all 127.0.0.1 255.255.255.255 trust I assume this means that the back end will bind to 127.0.0.1:5432 since that seems to be the default port number. Yet, when trying to come through the tunnel I get this in my logs on the 'server' machine - the one running postgres backend. --- Aug 18 16:00:40 dbsrv1 sshd[41006]: error: connect_to ::1 port 5432: Connection refused Aug 18 16:00:40 dbsrv1 sshd[41006]: error: connect_to 127.0.0.1 port 5432: Connection refused Aug 18 16:00:40 dbsrv1 sshd[41006]: error: connect_to localhost port 5432: failed. --- Am I missing something obvious? Is PG set to accept tcp/ip connections? Check postgresql.conf for: tcpip_socket=true 127.0.0.1 is connecting through tcp/ip, not local domain sockets. thanks for the info. I've check the postgresql.conf file and have the following lines: -- tcpip_socket = true max_connections = 40 port = 5432 virtual_host = '127.0.0.1' # what interface to listen on; defaults to any -- I assume I am binding to tcp socket 5432 as sockstat reveals: -- USER COMMANDPID FD PROTO LOCAL ADDRESS FOREIGN pgsqlpostgres 412293 tcp4 127.0.0.1:5432*:* -- I still get the error: - $psql -h localhost -p 4001 test1 psql: could not receive server response to SSL negotiation packet: No such file or directory - and on the ssh'd terminal tunnel session: $ channel 3: open failed: administratively prohibited: open failed --- Any other suggestions? Cheers, Steve ---(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 -- David Bear phone: 480-965-8257 fax:480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[ADMIN] new install starting backend
I installed postgres from the freebsd ports collection. Then, after doing the initdb, I tried to run the backend as the postgres user, ie while su'ed as pgsql. I was prompt for a password. Since I had not change and pg_hba.conf settings, nor set a password during the initdb phase, I was surprised by this. When I su'ed back to root, I was able to run the startup script the package had put in /usr/local/etc/rc.d. But, this contradicts: http://www.postgresql.org/docs/7.4/static/postmaster-start.html Different systems have different conventions for starting up daemons at boot time. Many systems have a file /etc/rc.local or /etc/rc.d/rc.local. Others use rc.d directories. Whatever you do, the server must be run by the PostgreSQL user account and not by root or any other user. Therefore you probably should form your commands using su -c '...' postgres. For example: I did check ps ax and found pgsql 39212 0.0 0.4 6292 4376 ?? I 4:15PM 0:00.00 postmaster: stats pgsql 39210 0.0 0.4 7280 4384 ?? I 4:15PM 0:00.00 postmaster: stats pgsql 39206 0.0 0.4 15532 4460 ?? Is4:15PM 0:00.02 /usr/local/bin/pos which indicates the postmaster is running as the postgresql superuser. So far, all seems okay, but I wanted to check to make sure this looked okay. any comments? -- David Bear phone: 480-965-8257 fax:480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 Beware the IP portfolio, everyone will be suspect of trespassing ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[ADMIN] pg_hba.conf on win32
We have a system that someone setup on cygwin. We are trying to locate where the pg_hba.conf file is and found one but it doesn't seem that it is being used. How might we (1) locate which pg_hba.conf file postgres uses on cygwin and (2) where pg is storing the data. I looked into the environment but couldn't find anything there. Also, since postgres starts as a windows service, how does one put commandline parms on the postmaster so that it listens to a socket. Sorry about the newbie questions but all the docs I found point to runing pg on various unicies. -- David Bear College of Public Programs/ASU ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] counting tupples
I couldn't find anything relating to getting the cardinality of a table. I know a select will display number of rows. I was hoping there was a 'cheaper' (less expensive) way than doing a full select. -- David Bear College of Public Programs/ASU ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] windoze accesse to pg backend
I am wondering what 'common' solutions are used to access postgress data on unix back ends. I'd be glad to tabulate results for the list, but don't even know all the options. I am thinking of MS-Access Paradox Excel Brio Other tools ODBC other Note, that I don't know if there is a native lib for accessing postgress from windows -- I'm thinking of something like the sybase netlibs. If anyone else wants to add to the above, please feel free. If I get much of a response, I'll tabulate and repost. Thx. -- David Bear College of Public Programs/ASU 480-965-8257 ...the way is like water, going where nobody wants it to go ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
