Re: [ADMIN] Porting from MS SQL Server to PostGres

while you weren't looking, Goulet, Dick wrote: > I'd precreate all of the tables/indexes and then move the data... If you have a lot of data, it's probably better to punt on creating the indices until after you've moved the data. Bulk populating indexed tables, particularly if the table has mor

[ADMIN] indices and casts

I have a number of timestamp columns on various tables I'd like also to be able to query against as dates. I can easily enough say "SELECT * FROM foo WHERE bar::date = '2004-10-11'" and the like, but such queries are inevitably sequential scans, and the tables are rather large for that to be as pe

Re: [ADMIN] indices and casts

while you weren't looking, Tom Lane wrote: > CREATE INDEX name ON table USING btree((column::date)) Nested parens. D'oh. It actually occurred to me to wonder if that might be the fix. But I thought, nah, too easy. Thanks once again, Tom. /rls -- :wq ---(end of broa

Re: [ADMIN] killing a hung postgres process brings down the Postgres database server on MAC OS X!

while you weren't looking, Qing Zhao wrote: [...] > Is there a way to kill the dead processes/thread without really affect > the PG server? See: http://www.postgresql.org/docs/7.3/static/postmaster-shutdown.html I have occasionally had to kill -9 a backend, but it's never been pleasant. Avoid

Re: [ADMIN] PLEASE GOD HELP US!

while you weren't looking, Shane | SkinnyCorp wrote: > Hey, my name is Shane Witschen and I'm the Systems Administrator (mainly a > developer) for a small web development company. We recently switched over > to PostgreSQL after going over some of the powerful features that it holds > over MySQL.

Re: [ADMIN] PLEASE GOD HELP US!

while you weren't looking, Shane | SkinnyCorp wrote: > How else do you suggest I grab the 25 most recent > threads posted?!? select * from thread_listing t where t.status = 5 order by lastreply desc limit 25 offset 0 The WHERE clause is there to limit the number of tuples you're looking a

Re: [ADMIN] pgsql database .1 .2 .3 file names

while you weren't looking, Bryan Biggers wrote: > Can anyone tell me what the data file names with the .1 .2 .3 etc. > extensions are? Are these undo versions of my tables or something? I > need to recover some disk space and I'm wondering if I need them. Delete them at your peril. Really quick

Re: [ADMIN] Any tools to compare db's

while you weren't looking, Chris Hoover wrote: > Does anyone know of any good tools that will compare 2 db's and generate a > complete listing of any object differences? Perhaps something like: $ pg_dump --schema-only db1 > db1.sql $ pg_dump --schema-only db2 > db2.sql $ diff db1.sql db2.sql

Re: [ADMIN] Any tools to compare db's

while you weren't looking, Chris Hoover wrote: > That really does not work. It will tell you line by line differences, > but not what object it is. Also, it does not help to generate the sql > to sync the db's. There are graphical diff tools that will show the relationships between the various

Re: [ADMIN] A real puzzler: ANY way to recover?

while you weren't looking, David F. Skoll wrote: > Is there any way to recover short of nuking everything and > restoring from a backup dump? :-( I don't have any ability to test this and see if it actually works, but a priori, I'd suggest trying, as your postgres user: $ createdb oopswow $ psql

Re: [ADMIN] PITR Based replication ...

On 4/5/06, Robin Iddon <[EMAIL PROTECTED]> wrote: Andy Shellam wrote:>I have, however, recently developed an interest in rsync but I'm unsure as >to how PG on the standby server would handle a complete rsync'd data>directory. There has just recently been a fairly extensive discussion on this listab

Re: [ADMIN] PITR Based replication ...

On 4/5/06, Robin Iddon <[EMAIL PROTECTED]> wrote:[-l $LOGFILE] Hope this helps,It did, thanks./rls-- :wq

Re: [ADMIN] [admin] is there app to catalog email oe6

On Tue, 6 Jul 2004 23:30:46 -0400, Paul Gimpelj <[EMAIL PROTECTED]> wrote: > I am hoping posgresql will get more widely used. Aren't we all? > I have an idea for an application... I'm not sure this is the most appropriate list for such a discussion. pgsql-admin is for discussions related to ad

Re: [ADMIN] [PERFORM] finding a max value

On Fri, 02 Jul 2004 20:50:26 +0200, Edoardo Ceccarelli <[EMAIL PROTECTED]> wrote: > This is the query: > select max(KA) from annuncio > wasn't supposed to do an index scan? it takes about 1sec to get the result. > TIP 5: Have you checked our extensive FAQ? I believe this is a FAQ. See: http://

Re: [ADMIN] adding defaults

Jodi Kanter wrote: >Can field specific defaults easily be added to a table that is already >in existence and has data in it? I cannot seem to locate the correct >ALTER command in any of my books. $ psql -c "\h alter table" | grep -iC 1 default ALTER TABLE [ ONLY ] name [ * ] ALTER [ COLUMN

Re: [ADMIN] Is it possible to have psql ignore the line I am typing?

On Wed, Oct 21, 2009 at 6:39 PM, Tena Sakai wrote: > Hi everybody, > > Is it possible to have psql ignore the line I am typing? > That is, similar to '#' with unix shell. > I believe the standard "--" remainder of line SQL comment glyph will work, as should the C-style "/* ... */" block comment

Re: [ADMIN] Best Replication Tool

On Sun, Feb 7, 2010 at 8:22 PM, Kiswono Prayogo wrote: > Hi, i'm really new to postgresql replication, was there any > replication tool for postgresql that can do real-time replication from > 1 database to ~3-10 database (on the other machines) with little > overhead for load balancing purpose? I

Re: [ADMIN] Migrate postgres to newer hardware

On Tue, Mar 30, 2010 at 8:51 AM, Renato Oliveira wrote: > If I use postgres 32 bit will it benefit from the extra memory on the system? Indirectly, yes. No individual PG process will be able to address more than 4 gbytes of memory. Assuming you have a 64-bit OS living underneath, however, that

Re: [ADMIN] amazon ec2

On Wed, Apr 14, 2010 at 2:16 PM, Vitaly Burshteyn wrote: > Just curious to find out if anybody is running pdsql on amazon aws and what > kind of backup features you use. For one group's experience, consider the following:

Re: [ADMIN] troubled by pg_dump and pg_restore

On Tue, May 18, 2010 at 5:14 PM, Tena Sakai wrote: > > Can someone please tell me (1) if there is anything wrong with my > invocation > of pd_dump? (I want to generate a file pd_restore can use to feed to > musket database.) (2) Why pg_restore wouldn't give me listing? And (3) > why the second

Re: [ADMIN] Asynchronous commit | Transaction loss at server crash

On Thu, May 20, 2010 at 4:04 PM, Greg Smith wrote: > Also, it's questionable whether a SSD is even going to be faster than > standard disks for the sequential WAL writes anyway, once a non-volatile > write cache is available.  Sequential writes to SSD are the area where the > gap in performance b

Re: [ADMIN] High-water Mark for number of sessions/connections reached in Postgres

On Mon, Aug 9, 2010 at 5:33 PM, Tomeh, Husam wrote: > This is useful from a capacity perspective where that can be monitored to > alert DBAs when a threshold is reached. If alerting based on connection counts is your concern, you might get some benefit from the Bucardo project's check_postgres.p

Re: [ADMIN] replication solution

On Tue, Aug 24, 2010 at 10:38 AM, Joshua D. Drake wrote: > On Tue, 2010-08-24 at 15:46 +0200, Silvio Brandani wrote: >> The Slony solution could be a possibility but the production database is >> 80 Gb of data with  around 1 transaction each hour. > Slony (or Londiste) can handle that withou

Re: [ADMIN] Use hardware snapshots to restore postgres

On Sat, Aug 28, 2010 at 7:15 PM, jigar shah wrote: > so instead of tar or cpio, i am looking for the procedure where you can use > hardware level snapshots of the postgres filesystem, which you can take on > san (3par) or nas (netapp) or any other vendor hardware, and then mount the > postgres fil

Re: [ADMIN] Postgres Replication Options

On Tue, Feb 8, 2011 at 5:34 PM, Rangi, Jai wrote: > I am looking for a replication solution for PG 9.x. Idea is to have one > master replication server and multiple (around 20) slave servers read only. I know (anecdotally) of at least one organization that's using Bucardo [1] to synchronize many

Re: [ADMIN] Off topic - Japanese PostgreSQL community

On Sat, Mar 12, 2011 at 11:46 AM, Bruce Momjian wrote: > Benjamin Krajmalnik wrote: >> Has anyone heard from the Japanese members of the community? > Yes, I posted a list of Japan members who have reported their safety to > the Postgres IRC channel yesterday: >        Atsuni Mitani >        Fuji

Re: [ADMIN] PSQLException: ERROR: could not open relation with OID xxxx

On Sun, Mar 13, 2011 at 11:59 PM, Gnanakumar wrote: [...] Although it's unrelated to your query about an exception being raised, a word of advice on temporary tables. (This may not be relevant to your situation, but it's no small source of pain in one of the systems I'm responsible for.) Signi

Re: [ADMIN] PSQLException: ERROR: could not open relation with OID xxxx

(Apologies for thread-jacking; I saw something similar in the OP's description of his setup that I thought warranted a word of advice.) On Mon, Mar 14, 2011 at 1:50 AM, Gnanakumar wrote: > Do you think that this "significantly bloated system catalogs" is caused > because of the old version 8.2.3

Re: [ADMIN] can not create a tablespace from psql

On Fri, Mar 25, 2011 at 7:10 PM, hyelluas wrote: > [root@Prof20_52_91 data]# ls -ltr psql > postgres=# create tablespace my_test1 OWNER postgres location > '/data/pgsql/my_test1'; > ERROR:  could not set permissions on directory "/data/pgsql/my_test1": No > such file or directory When you say '

Re: [ADMIN] clustering with shared storage

On Tue, Mar 20, 2012 at 1:33 PM, amador alvarez wrote: > I wonder why are you considering this solution, as if something wrong comes > within the data (logical corruption, user error) it will be spread on both > locations, Would not be better a delayed standby database. That's a risk, to be sure.

Re: [ADMIN] Large historical tables and autovacuum

On Mon, Sep 10, 2012 at 9:04 PM, David Morton wrote: > Is there any way of making the table 'read only' so its nice and tidy / > immutable ? Once a table actually *is* read-only, you can VACUUM FREEZE it. rls -- :wq -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make c

[ADMIN] Pg 8.3.x on RHEL 6.3?

Fellow Pg Admins, At $work, we're preparing to move one of our Pg instances to new hardware, and considering an OS upgrade at the same time. The instance in question is running an older version of 8.3, which we'd like to upgrade to 8.3.latest (presently .21; unf., we don't have the time or testin

Re: [ADMIN] diskspace

Additionally, postgres has a number of "metadata" columns (e.g., xmin, xmax, cmin, cmax, &c). Those can add up, particularly when their net size is greater than the user data size of a row. rls On Tue, Feb 5, 2013 at 11:00 AM, Scott Mead wrote: > On Tue, Feb 5, 2013 at 9:51 AM, Albe Laurenz w

Re: [ADMIN] diskspace

On Tue, Feb 5, 2013 at 12:33 PM, Geoff Winkless wrote: > Indexes are stored separately. There should be no free space (why would > there be? FILLFACTOR is 100!) and I've vacuumed already. > Did you just VACUUM, or did you VACUUM FULL/CLUSTER? Vanilla vacuuming merely marks space consumed by "de

Re: [ADMIN] Table DDL Causing All Tables To Be Hit During Query

> We have tried dropping the constrainst and re-creating casting the > check to timestamp rather than date but no change. What is your constraint_exclusion setting? If it's not enabled, try enabling it temporarily with "SET constraint_exclusion = on" and re-run your query. rls -- :wq