Re: [GENERAL] Changing the data directory Ubuntu
On 15 Aug 2006 11:19:39 -0700 [EMAIL PROTECTED] wrote: Hello, I have done a good deal of investigation and cannot seem to find a straight answer. Is there way to change the default data directory? I am using Ubuntu Dapper LTS. I have a seperate hard drive (and partition) that I want to keep my data on in case of a problem with the OS. Any help would be appreciated. Shaun Shaun, Normally the default data directory is set when starting PostgreSQL with the -D switch. Sometimes it is controled by the PGDATA environmental variable. On my Ubuntu Dapper system PostgreSQL (which was build from source, not the .deb) this is set from SysV startup script located at /etc/init.d/postgresql. To change the default directory run initdb -D New Directory then change the data directory in the SysV init script. Stop and restart PostgreSQL. John Purser ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] server optimisation
On Mon, 07 Aug 2006 11:14:53 +0100 Richard Huxton dev@archonet.com wrote: YannicK wrote: I'm look for documentation about server optimization. the only thing I find is QUERY optimization. there are a lot of parameter in postgresql.conf no site explain which value is better This is a good place to start. http://www.powerpostgresql.com/PerfList -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Richard, EXCELLENT resource. Thank you very much. John Purser -- The abuse of greatness is when it disjoins remorse from power. -- William Shakespeare, Julius Caesar ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Autovacuum help..
On Tue, 1 Aug 2006 08:02:59 -0700 (PDT) Sundar Narayanaswamy [EMAIL PROTECTED] wrote: Hi, I need your help/suggestions with a problem I am facing related to autovacuum. I am using PostgreSQL 8.1.2 through a JDBC connection. The connection is long lived (established when the application starts up and is closed only when the application is shutdown). I have enabled the autovacuum daemon and setup additional parameters (for instance, stats_row_level=on) as specified in the PostgreSQL documentation. In the database, I have a table that has a fairly high rate of inserts and deletes (~10 rows a second). The table is basically a FIFO queue that can have a maximum of 800 entries. As new rows are added to the table, oldest rows are deleted such that the table always about 800 rows. The problem I see is that the database size (disk usage) is continually increasing even though I have the autovacuum daemon enabled and the PostgreSQL log file indicates that the autovacuum daemon is processing the databases every minute as expected. On digging in further, I noticed that the reltuples (in pg_catalog.pg_class) for the relevant table keeps increasing continually. I also noticed a large number of dead unremovable rows when I ran the vacuum analyze command. After shutting down my application, if I watch the reltuples, it continues to stay high until I run the analyze command (analyze verbose table_name) after which the reltuples drops to about 800 immediately. The analyze command output also indicates that the dead rows have been removed and I notice that the space is reused for future inserts when I restart the application. I am pretty sure that I don't have any transaction that is held open forever (the work flow is insert, commit, insert commit etc). My question is, is autovacuum expected to work in situations like this where I have a long lived connection to the database ? After I shutdown my application, why am required to run the analyze command before the dead rows are removed (autovacuum is not able to remove the dead rows until I run the analyze command) ? I'll appreciate your thoughts since I seem to be running out of things to try.. Thanks __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend Sundar, Take a look at the documentation at: http://www.postgresql.org/docs/8.1/static/maintenance.html#AUTOVACUUM There are a lot of configuration options that effect the autovacuum daemon. John Purser -- You will pay for your sins. If you have already paid, please disregard this message. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to access a table from one database to another
On Sat, 15 Jul 2006 10:26:55 +0530 VivekanandaSwamy R. [EMAIL PROTECTED] wrote: Hai all, I have 2 databases namee PAO and CAS. PAO contains 3 schemas named Public,pao,sts CAS contains 4 schemas named Public,cao,sts,reports Now i am in PAO database..now i want access table 'activity' in schema 'cas' in CAS database. How it is posible. 2nd thing is... i have 2 servers access i.e local and mainserver. How access table from one server to another server? please tel me...because we need this one Vivekananda, I hope you just want the first one. I think what you need is to re-think your database design. From the PostgreSQL manual: A PostgreSQL database cluster contains one or more named databases. Users and groups of users are shared across the entire cluster, but no other data is shared across databases. Any given client connection to the server can access only the data in a single database, the one specified in the connection request. http://www.postgresql.org/docs/8.1/static/ddl-schemas.html Now your CLIENT can connect to two different databases with two different connections and ditto for connecting to two different clusters. John Purser -- Always do right. This will gratify some people and astonish the rest. -- Mark Twain ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Database connectivity using ECPG
On Thu, 13 Jul 2006 13:22:49 -0400
Jasbinder Bali [EMAIL PROTECTED] wrote:
Hi,
I'm using ECPG to connect to my postgres database. Doing it for the
very first time in my life.
The name of my database is postgres and uses a trusted connection.
So, i don't think it needs any authentication parameteres like a
user name and password.
I have the follwoing line of code in my .pgc file for the database
connectivity:-
EXEC SQL CONNECT TO postgres;
after compiling my .c file, i get the following error
In function `main':ecpg_test.c:(.text+0x5d): undefined reference to
`ECPGconnect'
Can any1 tell me wot is this all about???
EXEC SQL CONNECT TO postgres; gets converted to
{ ECPGconnect(__LINE__, 0, postgres , NULL,NULL , NULL, 0); }
after ECPG does its internal conversion from .pgc to .c
Regards,
~Jas
It has been my impression that PosgreSQL REALLY likes to know who it's
talking to. I'd include a user name with my connection string at the
least.
John Purser
--
Learn to pause -- or nothing worthwhile can catch up to you.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How do I revoke CREATE TABLE and other privileges?
On 6 Jul 2006 09:46:48 -0700 Karen Hill [EMAIL PROTECTED] wrote: Michael Fuhr wrote: On Wed, Jul 05, 2006 at 02:27:19PM -0700, Karen Hill wrote: I would like for one role to be able to login, and execute a couple of functions and nothing else. I've tried to revoke access to CREATE on the database, schema, and tablespace but when I tested it, the user was still allowed to create tables. From the REVOKE documentation: Note that any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to PUBLIC. If PUBLIC still has privileges on the objects then the role still has privileges, even if you've attempted to revoke them. You'll probably need to alter the privileges that PUBLIC has, which might also require altering other roles' privileges to compensate. Hi, Revoking PUBLIC worked. I can now login to the database and it will not allow me to create new tables. However when I gave (as postgres) the restricted user permission to execute one function it says it cannot find the function when I try to execute it. regards, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Karen, You hopefully just need to edit your search path. See page 56 of the manual for details. Type 'show search_path;' into pgsql and see what the value is currently set to. Then use set to make it include the schema with your function. John Purser -- I must have a prodigious quantity of mind; it takes me as much as a week sometimes to make it up. -- Mark Twain, The Innocents Abroad ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Inheritance and unique constraints
On Wed, 05 Jul 2006 15:51:23 +0200 Christian Rengstl [EMAIL PROTECTED] wrote: Hi everyone, i hope (and i'm sure) somebody can answer my question: if i have a master table and several child tables, do the child tables inherit the unique constraint(s) defined for the master table or do i have to define the same constraints for the same fields in all the child tables? Thanks! -- Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 ---(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 As of 8.1.0 UNIQUE constraints are not inherited. See page 62 of the documentation. John Purser -- Courage is your greatest present need. ---(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: [GENERAL] how to debugg
On Tue, 09 May 2006 10:59:20 +0530 N Srinivasa [EMAIL PROTECTED] wrote: Hi I downloded postgresql source code, and compile it in windows platform, can any body plz tell me that how can i debug the sourcecode in windows platform, what are the steps are i should go through.. Regards Srinivasa _ Spice up your IM conversations. New, colorful and animated emoticons. Get chatting! http://server1.msn.co.in/SP05/emoticons/ ---(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 Good Morning Srinivasa, What do you mean by debug? Speaking purely for myself I'd appreciate it if you fixed windows FIRST. Then turn your skills to fixing PostgreSQL. John Purser ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Exporting postgres query to CSV
On Tue, 09 May 2006 10:58:07 -0400 Ryan Suarez [EMAIL PROTECTED] wrote: Greetings, I am running postgres 7.4.7 on debian sarge. I need to run an SQL query and store the results in a file. The format needs to be comma separated values (CSV), so I can import this later in Excel. Any ideas on how to accomplish this? much appreciated, Ryan ---(end of broadcast)--- TIP 6: explain analyze is your friend Ryan, Two tips. First: psql -U PGSQL USER -o OUPUT FILE NAME --pset format=unaligned --pset fieldsep=',' -c 'SQL COMMAND HERE' -d DATABASE NAME HERE I think that will give you the output you were after assuming you're scripting psql and that you replace the values in with appropriate values. The syntax is slightly different from the psql command line. Second: man psql is your friend. John Purser ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] install postgres on usb drive???
On Tue, 9 May 2006 16:54:37 -0400 Rodrigo Cortés [EMAIL PROTECTED] wrote: It is possible to install postgres on usb driver to run it anywhere??? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org A) Possible is a BIG word. So is anywhere. If you could narrow these two down a bit it might help answer your question. B) Why? Posgrgres is a network aware service so your clients can generally attach TO the server FROM anywhere so why have a portable installation? Not arguing, just trying to understand. John Purser -- Beware the one behind you. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] install postgres on usb drive???
On Tue, 9 May 2006 17:06:53 -0400 Rodrigo Cortés [EMAIL PROTECTED] wrote: On 5/9/06, John Purser [EMAIL PROTECTED] wrote: On Tue, 9 May 2006 16:54:37 -0400 Rodrigo Cortés [EMAIL PROTECTED] wrote: It is possible to install postgres on usb driver to run it anywhere??? Im trying to make a portable development environment for ruby on rails. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Well, I see no problem with INSTALLING postgreSQL on a USB drive. But if you compiled it for Linux I don't think it would run on a windows machine. In fact you might have a lot of trouble just getting to to run on a different version of the same DISTRIBUTION of linux and that's assuming you could maintain mount points and paths. I think I'd go with a cheap laptop and/or Ruby installed on the USB drive and my postgreSQL installation on the network. John Purser -- Q: How do you keep a moron in suspense? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] install postgres on usb drive???
On Tue, 9 May 2006 17:10:21 -0400 Rodrigo Cortés [EMAIL PROTECTED] wrote: A) Possible is a BIG word. So is anywhere. If you could narrow these two down a bit it might help answer your question. possible mean how to do it anywhere mean a pc with a windows os ---(end of broadcast)--- TIP 6: explain analyze is your friend Rodrigo with Windows I'm not sure how much the registry would have to be edited (if at all) to run the same postgrsql installation from windows machines A B and C. I strongly doubt that you could just walk up to a strange machine, plug in your USB drive, and away you go. Some years ago when I was still running Windows at home I recall there was a discussion on the cygwin mailing lists about running cygwin from a cd drive. You might want to check out their archives and see if you could use that information. http://www.cygwin.com/ Good luck. John Purser -- You will never know hunger. ---(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: [GENERAL] pg_dump and grants to PUBLIC
On Mon, 08 May 2006 15:47:13 -0600 Blair Lowe [EMAIL PROTECTED] wrote: Hi, I have had this problem for a while, and have not been able to find anything in the archives or on search engines: If I want to back up a client's database on our shared web server, I would type: pg_dump database_name Since we are running a shared server, and since crappy (only because of this problem) off the shelf database open source software such as oscommerce, or phpBB2 grants access to public rather than the web user www or nobody, when I do a pg_dump for a database, I get all the databases on the system that grant to PUBLIC being dumped with with database that I want. To restore, I need to go in and prune out all the extra junk that was granted to PUBLIC by other users in other databases - very time consuming. How can I use pg_dump to get JUST the database in th argument, and not other tables and databases that have granted to PUBLIC? Altering my client's software to grant to nobody is not practical. Thanks in advance, Blair. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Blair, UNTESTED How about setting up a seperate schema (private), adding your customer's database to it (leaving it in public) and then backing up private.customerdb? /UNTESTED John Purser -- You need more time; and you probably always will. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
