[GENERAL] autovacuum and reindex
Hello all, I'd like to know if the autovacuum feature also deals with automatically reindexing my indexes. I know Pg8 know comes with a more eficient management of indexes, but I also read in the manuals that it's still good practice to routine reindex de most critical (in terms of speed) indexes. Could someone please tell me if autovacuum does or doesnt take care of reindexing, or if reindexing is or isn't important in pg8. thx++; Joao ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_dump problem
On Wed, 2007-11-14 at 10:32 -0800, SHARMILA JOTHIRAJAH wrote: Hi I try to use pg_dump to dump my database. pg_dump smrs and it gives me an error pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite entry OID 670741 not found check out the --oids option in the manuals (man pg_dump)... could help ?! and try this: pg_dump --oids smrs Cheers jmf What causes this problem? Thanks sharmila __ Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] strange message from pg_dumpall
Hello all, I got surprised by this message: - The program pg_dump is needed by pg_dumpall but was not found in the same directory as /usr/bin/pg_dumpall. Check your installation. -- It's quite strange because I'm quite the pg_* binaries are well installed: $ ls -la /usr/bin/ | grep pg_ -rwxr-xr-x1 adminroot16632 Oct 16 17:39 pg_config -rwxr-xr-x1 adminroot16636 Oct 16 17:39 pg_controldata -rwxr-xr-x1 adminroot24880 Oct 16 17:39 pg_ctl -rwxr-xr-x1 adminroot 187996 Oct 16 17:39 pg_dump -rwxr-xr-x1 adminroot43444 Oct 16 17:39 pg_dumpall -rwxr-xr-x1 adminroot23020 Oct 16 17:39 pg_resetxlog -rwxr-xr-x1 adminroot84460 Oct 16 17:39 pg_restore $ This is what I do: system(su postgres -c \pg_dumpall --clean some_file\); this is run from within a Perl module called from within a Perl script. I've tried the same command directlly on the shell and it works fine. But from the perl script it complaints about pg_dump !!!??? I've even tested both commands directlly on the cmd line and they work properlly ! any ideas ? thx joao ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] strange message from pg_dumpall
| system(su postgres -c \pg_dumpall --clean some_file\); I'd try su - postgres ...; this will use postgres' environment here. That was a good one but still didn't work | this is run from within a Perl module called from within a Perl script. | | I've tried the same command directlly on the shell and it works fine. | But from the perl script it complaints about pg_dump !!!??? What does type -a pg_dump output? Maybe it's a leftover from an it returns /usr/bin/pg_dump, just like 'which pd_dump' does !!! well, never mind I found a diferent solution... I run the command from within a shell script and just have the Perl call that script... it's working... thx joao incomplete uninstall of a PostgreSQL installation? Ciao, Thomas ---(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] Index Usage
On Tue, 2007-10-16 at 15:51 -0700, Ben wrote: You could take a look at pg_statio_user_indexes and/or pg_stat_user_indexes, if you have stats enabled On Tue, 16 Oct 2007, Bryan Murphy wrote: If your intention is to eliminate the unused indexes rows you should run 'vaccum' and/or 'vacuum full' and/or 'reindex'. This also has the consequence of freing filesystem space and returning it back to the OS. Check it out here: http://www.postgresql.org/docs/8.1/static/maintenance.html chapters 22.1, 22.2 and 22.3 I use: VACUUM FULL ANALYZE; REINDEX INDEX yourIndex; REINDEX TABLE yourTable it works just great for me. Cheers joao Is there a way I can track index usage over a long period of time? Specifically, I'd like to identify indexes that aren't being regularly used and drop them. Bryan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] replicating to a stopped server
On Fri, 2007-10-12 at 14:09 -0500, Erik Jones wrote: On Oct 12, 2007, at 1:59 PM, Richard Huxton wrote: Joao Miguel Ferrei Are you restricted to keep that second server in that special run- level? If not, I'd consider using pg_standby with WAL archiving to keep your failover server at most a handful of minutes behind. Well, I can consider having Pg running in that special boot mode... I'll check your suggestion. THX to all. jmf Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] replicating to a stopped server
Hello, I have a 'strange' situation: I need to make a replica copy of my database to a reduntant spare computer. The reduntant computer is not running postgres, but postgres is installed. The redundant computer is running in a special run-level (I'm talking Linux here) in which Pg is _not_ running. When the primary computer crashes the redundant one will be rebooted in 'normal' mode and Postgres must be started with the databases from the replica. a) So... how do I replicate a database to a stopped postgres ? b) Is it safe just to copy the /var/lib/pg/* directories to the right place and let Pg boot on that ? c) I know the right tool for this should be 'pg_dump' but it needs a live postgres daemon running, in order to install the replica. Is this correct ? d) Is it viable to start postgres directlly from the dump ? by specifying the dump-file in the cmd line ? thx a lot joao ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] replicating to a stopped server
Hello, I have a 'strange' situation: I need to make a replica copy of my database to a reduntant spare computer. The reduntant computer is not running postgres, but postgres is installed. The redundant computer is running in a special run-level (I'm talking Linux here) in which Pg is _not_ running. When the primary computer crashes the redundant one will be rebooted in 'normal' mode and Postgres must be started with the databases from the replica. a) So... how do I replicate a database to a stopped postgres ? b) Is it safe just to copy the /var/lib/pg/* directories to the right place and let Pg boot on that ? c) I know the right tool for this should be 'pg_dump' but it needs a live postgres daemon running, in order to install the replica. Is this correct ? d) Is it viable to start postgres directlly from the dump ? by specifying the dump-file in the cmd line ? thx a lot joao ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] granting SELECT on _all_ database objects (even non existing objects)
Hello All, my database contains a big table. on this table I create VIEWs. The problem is this: the VIEWs are created dinamically by an external program (depending on some configurations). Nevertheless I would like to GRANT SELECT priviliges to my readOnlyUser, in a simple way...!!! Summary: is it possible to allow Read-Onlky access to some user, in some database, even if new database objects (VIEWs in my case) are created dinamically ??? something like GRANT SELECT ON * TO readOnlyUser; I'm confused.. thx jmf DISCLAIMER: This message may contain confidential information or privileged material and is intended only for the individual(s) named. If you are not a named addressee and mistakenly received this message you should not copy or otherwise disseminate it: please delete this e-mail from your system and notify the sender immediately. E-mail transmissions are not guaranteed to be secure or without errors as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete or contain viruses. Therefore, the sender does not accept liability for any errors or omissions in the contents of this message that arise as a result of e-mail transmissions. Please request a hard copy version if verification is required. Critical Software, SA. ---(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] database size grows (even after vacuum (full and analyze))....
On Mon, 2006-05-08 at 18:07, Bruno Wolff III wrote: Please keep replies copied to the list so that others can learn from and contribute to the discussion. I don't remember where this was, but it looks like general is probably reasonable. Sorry. I didn't notice. I'll keep that in mind. Yes, my tables contains ever grwoing values afected by a UNIQUE constraint. What would I do next ? jmf (Pg is 7.2, rpm install, Fedora Core 3) 7.2 is subject to index bloat for indexes where the column increase monotonicly and old values are deleted. In the short run you will want to schedule regular reindexes. Well... that seems to answer my questions. Thanks. In the long run, you should upgrade. 7.2 is essentially without support. I beleive there is still a RHEL version using it that is in support, so a critical fix might get back ported. I'll do that. thanks jmf ---(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
