[GENERAL] copy data from one db into another via copy & psql

[Kevin Kempter]

Can I copy from one db (via COPY) and pipe the results to a psql/COPY stmt so 
I can load the data into a table in the second db 'inline' without writing to 
& reading from a flat file?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table space question

[Kevin Kempter]

On Friday 02 April 2010 12:44, akp geek wrote:
> Hi all -
>
>  I have created dump of a database where I have a tablespace
> (data_tblspace ) associated with relations. I have restored to a different
> server. I created the same table space. Now I need to create an other
> database on the same server using the same dump. but I need to have a
> different tablespace for all the relations.
>
>Can you please suggest?   Do I have to do the restore the
> database to the same tablespace first? and then alter the tablespace?
>
>
> Regards

Maybe something like this (assuming the dump file is in text/sql format):

sed "s/data_tblspace/new_tblspace/g" dump_file > new dump file


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to grant a user read-only access to a database?

[Kevin Kempter]

On Wednesday 03 March 2010 08:19:46 am Tom Lane wrote:
> Kevin Kempter  writes:
> > I believe all you have to do is this to create a read only user:
> > create user ro_user with password 'passwd';
> > alter user ro_user set default_transaction_read_only = true;
> 
> You do realize the user can just unset that again?
> 
>   regards, tom lane


I did not. Thanks for the heads up.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to grant a user read-only access to a database?

[Kevin Kempter]

On Wednesday 03 March 2010 07:29:21 am Merlin Moncure wrote:
> On Tue, Mar 2, 2010 at 9:56 AM, Thom Brown  wrote:
> > As far as I'm aware.  It's only in the upcoming version 9.0 that you
> > can do things like:
> > 
> > GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
> > 
> > Other folk on here may have some alternative suggestions though.
> 
> 9.0 will also have the hot standby feature.  setting up a standby is
> pretty much always a good idea and access to the standby is
> automatically read only.  this would be a cheap way to get what you
> want without dealing with privileges which is nice.  you are also
> relatively insulated from problematic queries the user might make like
> accidental unconstrained joins, full table sorts etc..
> 
> merlin


I believe all you have to do is this to create a read only user:

create user ro_user with password 'passwd';

alter user ro_user set default_transaction_read_only = true;




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] playr (or similar tool)?

[Kevin Kempter]

Hi All;

I need a tool that will duplicate ALL messages from one db to another 
(including begin, commit, etc).  I think Playr does (did) this but the 
myyearbook links from the past pg conference talks (the one from PG East 2009 
in particular) no longer work.

Anyone know how to get Playr? 

If it duplicates ALL statements? 

other tools that will do this for me?


Thanks in advance

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Justifying a PG over MySQL approach to a project

[Kevin Kempter]

On Thursday 17 December 2009 09:51:19 Richard Broersma wrote:
> On Thu, Dec 17, 2009 at 8:23 AM, Gauthier, Dave  
wrote:
> > How difficult is it to switch the master's hat from one DB instance to
> > another?  Let's say the master in a master-slave scenario goes down but
> > the slave is fine.  Can I designate the slave as being the new master,
> > use it for read/write, and then just call the broken master the new slave
> > once it comes back to life (something like that)?

This is very easy with SLONY.  If the master is truly 'dead' you can run a 
SLONY  'failover' command like this (note these are slonik commands where node 
1 is the 'dead' master and node 2 is the current slave):

echo 'Preparing to failover (set id = 1, backup node = 2)';
failover (id = 1, backup node = 2);
echo 'Done';
echo 'Preparing to drop node (set id = 1, event node = 2)';
drop node (id = 1, event node = 2);
echo 'Done';
echo 'Failover complete';



at this point the dead master node is no longer part of the replication 
cluster and the slave is the new master

Once the dead node is rebuilt then you simply add it to the replication 
cluster as a new slave node


Also, if you just wanted to "move" the master, changing the existing master 
into a slave in the process you can do this:

lock set (id = 1, origin = 1);
wait for event (origin = 1, confirmed = 2);
echo 'set locked';
move set (id = 1, old origin = 1, new origin = 2);
wait for event (origin = 1, confirmed = 2);
echo 'switchover complete';



> 
> I know someone that uses a revolving Sony Master-Slave setup between
> China and the US.  During the US working hour the US server is the
> master, during the working hours of China it becomes the Master.
> 
> Of course the person how constructed this system mentioned it was
> woefully challenging.  In his case, not only was the slony
> configuration difficult but also finding and keeping stable
> communication path-ways between China and the US.
> 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How do I pipe output from query to a file in PostgreSQL?

[Kevin Kempter]

On Monday 26 October 2009 12:03:13 Penrod, John wrote:
> In oracle:
> 
> SPOOL filename.txt
> Select * from customer;
> SPOOL OFF
> 
> Results are piped to filename.txt
> 
> 
> How do I do this from the psql command line?
> 
> 
> John J. Penrod, OCP
> Oracle/EnterpriseDB Database Administrator
> St. Jude Children's Research
>  Hospital 10VgnVCM100e2015acRCRD&plt=STJGENSEGOOGL009&gclid=CM6Imp6I0Z0CFSMND
> QodNXLerQ> 262 Danny Thomas Place, MS 0574
> Memphis, TN  38105
> Phone: (901) 595-4941
> FAX: (901) 595-2963
> john.pen...@stjude.org
> 
> 
> 
> 
>   
> Email Disclaimer: www.stjude.org/emaildisclaimer
> 


psql
postgres=# \o file.out
postgres=# select * from customer;

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Data file recovery

[Kevin Kempter]

Hi all;

Here's my scenario:

1) we setup a db on a local server and we created an external tablespace on a 
san. We loaded about 400GB in the san tablespace.

2) we had lots of HW issues, subsequently the server was re-purposed in an 
emergency scenario

3) now we want the data back from the san however the original server, which 
is where the postgres cluster lived (on the internal disk) that we used to 
originally create this san tablespace is now gone.


So, based on the above; is it possible to recover the data on the san into a 
tablespace via a new postgres cluster on a new server?

Thanks in advance

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Possible Bug - error creating a tablespace

[Kevin Kempter]

So I think I *may* have found a bug but I want to be sure before I file a bug.  
I did a search on the pgsql-bugs list using the search text:

cannot create tablespace

and got nothing back.


Here's the scenario:

1) we installed CentOS 5.3 x86_64 on a 64bit Dell server

2) I installed the pgdg_centos-8.3-6.noarch rpm in order to enable the 8.3.x 
repo.

3) I used yum to install postgres and friends

4) Here's the weird part:
-- If I start the cluster via "sudo /etc/init.d/postgres start" and then try 
and create a tablespace it fails with :

create tablespace benchmark2 location '/eq5/pwbbench';
ERROR:  could not set permissions on directory "/eq5/pwbbench": Permission 
denied


-- however if I start the db as user postgres with: 

pg_ctl -D /var/lib/pgsql/data start

and then create the tablespace it works.


Likewise if I create the tablespace after starting the cluster via pg_ctl and 
then subsequently restart the db via 'sudo /etc/init.d/postgres start' I get 
errors trying to insert data into the previously created tablespace but if I 
start the cluster with pg_ctl (as user postgres) then I can also add data to 
the tablespace.

I see that in the /etc/init.d/postgres file the start command is running the 
postmaster (not pg_ctl) as user postgres.

So, based on the above I suspect Its a bug but I wanted to be sure.

Thoughts?


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] print/return only the first X chars of a varchar column?

[Kevin Kempter]

Hi all;

I'm selecting from a table that has a varchar(1000) but I only want to display 
the firs 20 characters.  Looked at the string functions in the docs but nothing 
jumped out...

Suggestions?


Thanks in advance


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] indexes on partitioned tables - on the base table, or the partitioned tables?

[Kevin Kempter]

Hi all;

where's the best place for the indexes/constraints on a partitioned table.

I assume it's best to place the FK constraints/triggers on the base/master 
table and the indexes on the individual partition tables.

Thoughts?

Thanks in advance.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] convert epoch to date

[Kevin Kempter]

Hi all;

I know how to convert a date to an epoch:

select extract ('epoch' from timestamp '2009-08-12')


How do I do the opposite, I want to convert epoch values to a date


Thanks in advance

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] locking/waiting queries

[Kevin Kempter]

Hi all ;

were seeing a backlog of queries in pg_stat_activity. The system has slowed 
big time.

I see many many queries where waiting = 't'

I want to find out for each query which query they are waiting on (who's doing 
the blocking).

What's the best way to find this, I looked at pg_locks for rows where granted = 
'f' but its not helping me determine which query is doing the blocking.

Thanks in advance...


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] LDAP Configuration for Postgres authenticating against AD

[Kevin Kempter]

On Tuesday 04 August 2009 19:41:57 Richard Esmonde wrote:
> Hi,
>
>
>
> I'm new to PostGres (so go easy on my naivety).  I am trying to configure
> the postgres host based configuration file to permit users to authenticate
> against our Active Directory.
>
> Needless to say both Ubuntu server and AD are in the same Domain.
>
>
>
> . I am running PostGRESQL v8.3.7 on a 64-Bit Ubuntu Hardy Heron
> Dell server with Apache 2.
>
> . I am not running SSL.
>
> . This work is happening on a LAN.  My AD server=master1 and the
> LAN=belfry.lan
>
> . I installed Postgres as follow:
>
> o   # sudo apt-get install postgresql-8.3 postgresql-client-8.3
> postgresql-client-common postgresql-common
>
>
>
> It runs just fine and I can create databases users and tables with no
> problems.
>
>
>
> Currently, the end of my pg_hba.conf file looks like:
>
> 
>
> # IPv4 local connections:
>
> hostall all 127.0.0.1/32  md5
>
> hostall all 10.5.5.0 255.255.255.0  password
>
>
>
> # IPv6 local connections:
>
> hostall all ::1/128   md5
>
>
>
> # Remote TCP/IP connection
>
> #host   all postgres127.0.0.1/32password
>
> # host  all all 10.5.5.0/16ldap
> "ldap://master1:389/dc=belfry,dc=lan;BELFRY\";
>
> # host  all all 10.5.5.0 255.255.255.0  ldap
> "ldap://master1:389/dc=belfry,dc=lan;BELFRY\";
>
>
>
> host  all all 10.5.5.0 255.255.255.0   ldap
> "ldap://master1. belfry.lan:389/ou=Belfry
> Users,ou=programmers;dc=belfry,dc=lan;cn=*;BELFRY\"
>
>
>
>
>
> =
>
>
>
> Each time I change it I stop and start PostGres.
>
>
>
> I created a testuser and a test database.  The user, testuser exists in my
> Active directory with a different password.  I can connect as testuser to
> the DB via command line or via pgAdmin111 with the postgres password for
> testuser.  When I try to connect using the users LDAP password I always
> get:
>
>
>
> . psql: FATAL:  password authentication failed for user testuser
>
>
>
> Three days into this I am none the wiser - I'm exhausting Google servers.
> Can anyone tell me what I have forgotten to do or have overlooked in
> getting this setup correctly?  To my mind it's behaving as though it's not
> honoring anything I have put in the pg_hba.conf for Remote TCP/IP
> connections.  I have to be missing something super simple...  a
> postgres-ldap add-on for Postgres on Ubuntu perhaps?
>
>
>
> I set connections to debug2 in the logs.  Debug5 was giving me hundreds of
> lines of "blah".  Tail  of logs now looks like:
>
> =
>
>
>
> 2009-08-04 16:49:15 PDT DEBUG:  proc_exit(0)
>
> 2009-08-04 16:49:15 PDT DEBUG:  shmem_exit(0)
>
> 2009-08-04 16:49:15 PDT DEBUG:  exit(0)
>
> 2009-08-04 16:49:15 PDT DEBUG:  server process (PID 8637) exited with exit
> code 0
>
> 2009-08-04 16:49:24 PDT LOG:  incomplete startup packet
>
> 2009-08-04 16:49:24 PDT DEBUG:  proc_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  shmem_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  forked new backend, pid=8646 socket=9
>
> 2009-08-04 16:49:24 PDT DEBUG:  server process (PID 8646) exited with exit
> code 0
>
> 2009-08-04 16:49:24 PDT DEBUG:  postmaster received signal 2
>
> 2009-08-04 16:49:24 PDT LOG:  received fast shutdown request
>
> 2009-08-04 16:49:24 PDT LOG:  aborting any active transactions
>
> 2009-08-04 16:49:24 PDT LOG:  autovacuum launcher shutting down
>
> 2009-08-04 16:49:24 PDT DEBUG:  proc_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  shmem_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  proc_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  shmem_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  exit(0)
>
> 2009-08-04 16:49:24 PDT LOG:  shutting down
>
> 2009-08-04 16:49:24 PDT LOG:  database system is shut down
>
> 2009-08-04 16:49:24 PDT DEBUG:  proc_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  shmem_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  proc_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  shmem_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  exit(0)
>
> 2009-08-04 23:53:23 GMT DEBUG:  postgres: PostmasterMain: initial environ
> dump:
>
> 2009-08-04 23:53:23 GMT DEBUG:  -
>
> 2009-08-04 23:53:23 GMT DEBUG:  LC_CTYPE=en_US.UTF-8
>
> 2009-08-04 23:53:23 GMT DEBUG:  PGSYSCONFDIR=/etc/postgresql-common
>
> 2009-08-04 23:53:23 GMT DEBUG:  PGLOCALEDIR=/usr/share/locale
>
> 2009-08-04 23:53:23 GMT DEBUG:  PWD=/var/lib/postgresql
>
> 2009-08-04 23:53:23 GMT DEBUG:  PGDATA=/var/lib/postgresql/8.3/main
>
> 2009-08-04 23:53:23 GMT DEBUG:  LC_COLLATE=C
>
> 2009-08-04 23:53:23 GMT DEBUG:  LC_MESSAGES=en_US.UTF-8
>
> 2009-08-04 23:53:23 GMT 

Re: [GENERAL] where is pg_resetxlog ?

[Kevin Kempter]

On Saturday 25 July 2009 20:05:14 Scott Marlowe wrote:
> On Sat, Jul 25, 2009 at 1:30 PM, Kevin
>
> Kempter wrote:
> > On Saturday 25 July 2009 13:23:54 Scott Marlowe wrote:
> >> On Sat, Jul 25, 2009 at 1:08 PM, Kevin
> >>
> >> Kempter wrote:
> >> > On Saturday 25 July 2009 13:02:52 Scott Marlowe wrote:
> >> >> On Sat, Jul 25, 2009 at 12:55 PM, Kevin
> >> >>
> >> >> Kempter wrote:
> >> >> > Hi all;
> >> >> >
> >> >> > I'm trying to restore from a tar of the filesystem on a debian box
> >> >> > and I get xlog errors.
> >> >> >
> >> >> > I suspect I need to run pg_resetxlog but I cannot find it anywhere,
> >> >> > where would I find pg_resetxlog on a debian box that was installed
> >> >> > via the deb packages ? or how do I get it ?
> >> >>
> >> >> Wait, if you're restoring a backup, to a freshly initted db, then you
> >> >> shouldn't be getting any kind of pg_xlog errors.  If you are, then
> >> >> there's likely something wrong with your server that pg_resetxlog
> >> >> isn't going to fix in the long term.  Are you running on windows with
> >> >> anti-virus software or have some other kind of possible problem that
> >> >> could be causing a problem with the postmaster writing to the hard
> >> >> drives?
> >> >
> >> > we're not restoring from a pg_dump. We were in the process of moving
> >> > the db to a new server. We brought the db down (on host A) and did an
> >> > rsync of all the db dir's (including tablespace dir's) to host B.
> >> >
> >> > Then bad things were done to host A and we want to get back to where
> >> > we started. So, we stopped the db on host A and rsync'ed the files
> >> > back from host B to host A.  Now when I try and start the db I see all
> >> > these tx sement errors in the log.
> >> >
> >> > We're actually ok if we loose anything that was in the pg_xlog dir.
> >>
> >> Did you rsync the pg_xlog and pg_clog (i.e. EVERYTHING under data/) or
> >> just the base directory?  You really need to do an rsync of
> >> everything, not most everything.
> >
> > we rsync'ed EVERYTHING (the entire directory and all sub-dirs)
>
> OK, on my laptop, in the /var/lib/postgresql/8.3/main there's a bunch
> of dirs that look like this:
>
> basepg_clog   pg_subtrans  pg_twophase  pg_xlog
> postmaster.pid  server.crt
> global  pg_multixact  pg_tblspcPG_VERSION   postmaster.opts
> root.crtserver.key
>
> Did you rsync the base dir only, or all of the directories above?
> Cause if you just rsynced base, you need the rest of them, as well.

I rsync'd the /var/lib/postgresql/8.3/main dir. However several of the sub-
dirs were soft links (pg_xlog, server.cert, root.cert and server.key) so I 
unfortunately did not get the data from the links' real location since they 
all pointed to outside of the /var/lib/postgresql/8.3/main tree




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] where is pg_resetxlog ?

[Kevin Kempter]

On Saturday 25 July 2009 13:23:54 Scott Marlowe wrote:
> On Sat, Jul 25, 2009 at 1:08 PM, Kevin
>
> Kempter wrote:
> > On Saturday 25 July 2009 13:02:52 Scott Marlowe wrote:
> >> On Sat, Jul 25, 2009 at 12:55 PM, Kevin
> >>
> >> Kempter wrote:
> >> > Hi all;
> >> >
> >> > I'm trying to restore from a tar of the filesystem on a debian box and
> >> > I get xlog errors.
> >> >
> >> > I suspect I need to run pg_resetxlog but I cannot find it anywhere,
> >> > where would I find pg_resetxlog on a debian box that was installed via
> >> > the deb packages ? or how do I get it ?
> >>
> >> Wait, if you're restoring a backup, to a freshly initted db, then you
> >> shouldn't be getting any kind of pg_xlog errors.  If you are, then
> >> there's likely something wrong with your server that pg_resetxlog
> >> isn't going to fix in the long term.  Are you running on windows with
> >> anti-virus software or have some other kind of possible problem that
> >> could be causing a problem with the postmaster writing to the hard
> >> drives?
> >
> > we're not restoring from a pg_dump. We were in the process of moving the
> > db to a new server. We brought the db down (on host A) and did an rsync
> > of all the db dir's (including tablespace dir's) to host B.
> >
> > Then bad things were done to host A and we want to get back to where we
> > started. So, we stopped the db on host A and rsync'ed the files back from
> > host B to host A.  Now when I try and start the db I see all these tx
> > sement errors in the log.
> >
> > We're actually ok if we loose anything that was in the pg_xlog dir.
>
> Did you rsync the pg_xlog and pg_clog (i.e. EVERYTHING under data/) or
> just the base directory?  You really need to do an rsync of
> everything, not most everything.

we rsync'ed EVERYTHING (the entire directory and all sub-dirs)

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] where is pg_resetxlog ?

[Kevin Kempter]

On Saturday 25 July 2009 13:02:52 Scott Marlowe wrote:
> On Sat, Jul 25, 2009 at 12:55 PM, Kevin
>
> Kempter wrote:
> > Hi all;
> >
> > I'm trying to restore from a tar of the filesystem on a debian box and I
> > get xlog errors.
> >
> > I suspect I need to run pg_resetxlog but I cannot find it anywhere, where
> > would I find pg_resetxlog on a debian box that was installed via the deb
> > packages ? or how do I get it ?
>
> Wait, if you're restoring a backup, to a freshly initted db, then you
> shouldn't be getting any kind of pg_xlog errors.  If you are, then
> there's likely something wrong with your server that pg_resetxlog
> isn't going to fix in the long term.  Are you running on windows with
> anti-virus software or have some other kind of possible problem that
> could be causing a problem with the postmaster writing to the hard
> drives?

we're not restoring from a pg_dump. We were in the process of moving the db to 
a new server. We brought the db down (on host A) and did an rsync of all the 
db dir's (including tablespace dir's) to host B.

Then bad things were done to host A and we want to get back to where we 
started. So, we stopped the db on host A and rsync'ed the files back from host 
B to host A.  Now when I try and start the db I see all these tx sement errors 
in the log.

We're actually ok if we loose anything that was in the pg_xlog dir.

Thoughts?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] where is pg_resetxlog ?

[Kevin Kempter]

Hi all;

I'm trying to restore from a tar of the filesystem on a debian box and I get 
xlog errors.

I suspect I need to run pg_resetxlog but I cannot find it anywhere, where would 
I find pg_resetxlog on a debian box that was installed via the deb packages ? 
or how do I get it ?




Thanks in advance.. here's my log startup messages in case it helps:


Jul 25 18:40:17 ux-dbs01-atl2 postgres[16042]: [1-1] time:2009-07-25 
18:40:17.446 UTC database: LOG:  could not load root certificate file 
"root.crt": no SSL error reported
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16042]: [1-2] time:2009-07-25 
18:40:17.446 UTC database: DETAIL:  Will not verify client certificates.
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [2-1] time:2009-07-25 
18:40:17.653 UTC database: LOG:  database system was shut down at 2009-07-23 
01:18:04 UTC
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [3-1] time:2009-07-25 
18:40:17.653 UTC database: LOG:  could not open file 
"pg_xlog/000104B600C7" (log file 1206, segment
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [3-2]  199): No such file or 
directory
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [4-1] time:2009-07-25 
18:40:17.653 UTC database: LOG:  invalid primary checkpoint record
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [5-1] time:2009-07-25 
18:40:17.653 UTC database: LOG:  could not open file 
"pg_xlog/000104B600C7" (log file 1206, segment
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [5-2]  199): No such file or 
directory
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [6-1] time:2009-07-25 
18:40:17.653 UTC database: LOG:  invalid secondary checkpoint record
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [7-1] time:2009-07-25 
18:40:17.653 UTC database: PANIC:  could not locate a valid checkpoint record
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16042]: [2-1] time:2009-07-25 
18:40:17.653 UTC database: LOG:  startup process (PID 16043) was terminated by 
signal 6: Aborted
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16042]: [3-1] time:2009-07-25 
18:40:17.653 UTC database: LOG:  aborting startup due to startup process 
failure


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [Q] single image Table across multiple PG servers

[Kevin Kempter]

On Tuesday 14 July 2009 14:16:33 you wrote:
> This is great,
> thank you
>
> I have to say, if this is a free-of-charge
> add on to postgres that works and utilizes the hardware on each server
> to perform
> the join,
>
> -- why would anybody need to use mapreduce/hadoop/etc?
> for database-like selects  ?

It's new - the grid project just moved out of beta i believe. However I know 
of at least one commercial company using it in production with no issues


>
>
>
>
>
>
>
> On Tue, 14 Jul 2009 09:45 -0600, "Kevin Kempter"
>
>  wrote:
> > > But do you know if supports viewes that combines tables
> > > from different servers (this way
> > > I can prefix a table on each server with server_id and then
> > > just combine them in the view in a single Image table)
> >
> > no need for this, the system presents the clients (via the controller) a
> > single table - even though its actually partitioned across nodes
> >
> >
> >
> > It's quite similar to the Informix XPS product if that helps
>
> --
> Vlad P
> author of C++  ORM  http://github.com/vladp/CppOrm/tree/master


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [Q] single image Table across multiple PG servers

[Kevin Kempter]

On Tuesday 14 July 2009 13:35:23 you wrote:
>  yes  -- thank you
>
> that's in the direction of what I am looking for
>
>
> ODBC connectivity and joins across databases!
>
>
> It looks like it cannot support
> a single image table across databases

It's effectively a single image across nodes - in that the grid allows you to 
partition a table across nodes (i.e. physical servers) so any sql see's it as 
a single table so long as you send the sql to the grid controller
>
> But do you know if supports viewes that combines tables
> from different servers (this way
> I can prefix a table on each server with server_id and then
> just combine them in the view in a single Image table)
>
no need for this, the system presents the clients (via the controller) a 
single table - even though its actually partitioned across nodes



It's quite similar to the Informix XPS product if that helps

>
> I am downloading it now and will try out on my windows dev
> machine.
>
>
> thanks
>
> > Maybe this is what you're looking for?
> >
> > http://www.enterprisedb.com/community/projects/gridsql.do
> >
> >
> >
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Vlad P
> author of C++  ORM  http://github.com/vladp/CppOrm/tree/master


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [Q] single image Table across multiple PG servers

[Kevin Kempter]

On Tuesday 14 July 2009 12:38:27 V S P wrote:
> Hi,
> thank you for the links
>
>
> I read through the presentation
>
> and they did not solve the issue for me -- which presenting a e
> table from multiple
> shards as one single table (at least for reads) for ODBC clients.
>
>
> I also do not think that skypetools do that
>
> they have implemented essentially an API on top of their shards
> (separate db servers)
> that does the table querying.  That means that I have to write a
> separate API for every time
> a user decides on a new query.
>
>
> May be I misunderstood the approaches, but none of them actually
> figures out how to
> utilize the computing power/memory of multiple servers to satisfy
> requests that spawn across
> servers.
>
> I think Oracle supports the ability to at least reference a table
> in another server, I do not think
> PG does that
>
> It is possible that I have to look into the free DB2 server
> offering (as the free version is exactly meant
> to run on underpowered computers)
>
> I just wanted to ask the list first.
>
>
> thank you
>
>
> On Tue, 14 Jul 2009 13:04 +0530, "Ransika de Silva"
>  wrote:
>
>   Hello,
>
>
>
> We were also in search of having a table split across multiple
> databases but then found out about skypetools and at the same
> time the following
> article; [1]http://www.jurriaanpersyn.com/archives/2009/02/12/dat
> abase-sharding-at-netlog-with-mysql-and-php/, true that it's not
> done with PG, but the same thing can be done with PG as well.
>
>
>
> Assume this will be helpful for you.
>
>
>
> Regards,
>
> Ransika
>
> On Tue, Jul 14, 2009 at 4:20 PM, Scott Marlowe
> <[2]scott.marl...@gmail.com> wrote:
>
> On Mon, Jul 13, 2009 at 11:16 PM, V S P<[3]torea...@fastmail.fm>
>
> wrote:
> > Hello
> >
> > I am researching how to store the data for easy 'user-driven'
> > reporting (where I do not need to develop application for
> > every user request).
> >
> > The data will typically be number ranges and text strings with
> > unique Id for each row
> >
> > I hope there will be a lot of data :-).
> >
> > So in that anticipation I am looking for a way
> > to allow
> > SQL/ODBC access to the data
> >
> > but in a way that each table resides on more than one
> > PG server
> >
> > for example:
> >
> > table 1 lives in 3 PG instances (I can partition the data by
>
> date range)
>
> > table 2 lives in the same 3 instances plus another one (because
>
> it's
>
> > bigger)
> >
> >
> > and I would like users to be able to issue SQL from within ODBC
>
> that
>
> > joins them.
>
>   I think that skype's skytools could be used to create such a
>   solution,
>   in particular pl/proxy.
>
>
> --
> Sent via pgsql-general mailing list
> ([4]pgsql-gene...@postgresql.org)
> To make changes to your subscription:
> [5]http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Ransika De Silva
> SCMAD 1.0, SCJP 1.4,
> BSc.(Hons) Information Systems
>
> References
>
> 1.
> http://www.jurriaanpersyn.com/archives/2009/02/12/database-sharding-at-netl
>og-with-mysql-and-php/ 2. mailto:scott.marl...@gmail.com
> 3. mailto:torea...@fastmail.fm
> 4. mailto:pgsql-general@postgresql.org
> 5. http://www.postgresql.org/mailpref/pgsql-general
> --
> Vlad P
> author of C++  ORM  http://github.com/vladp/CppOrm/tree/master


Maybe this is what you're looking for?

http://www.enterprisedb.com/community/projects/gridsql.do





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Favorite/Recommended ERD tools

[Kevin Kempter]

Hi All;

I'm looking for suggestions per good ERD tools (Linux based preferred).

Thoughts?


Thanks in advance

Re: [GENERAL] [PERFORM] Postgres Clustering

[Kevin Kempter]

On Wednesday 27 May 2009 13:33:55 Alan McKay wrote:
> > Continuent works (AFAIK) like pgpool clustering, it sends the same
> > statements to both/all servers in the cluster but it has no insight to
> > the servers beyond this, so if via a direct connection server A becomes
> > out of sync with server B then continuent is oblivious.
>
> So can the same be said for pgpool then?

Yes


>
>
> thanks,
> -Alan
>
> --
> “Mother Nature doesn’t do bailouts.”
>  - Glenn Prickett

Re: [GENERAL] [PERFORM] Postgres Clustering

[Kevin Kempter]

On Wednesday 27 May 2009 12:55:51 Eddy Ernesto Baños Fernández wrote:
> Try Cybercluster
>
> -Mensaje original-
> De: pgsql-performance-ow...@postgresql.org
> [mailto:pgsql-performance-ow...@postgresql.org] En nombre de Alan McKay
> Enviado el: miércoles, 27 de mayo de 2009 13:57
> Para: pgsql-performa...@postgresql.org; pgsql-general@postgresql.org
> Asunto: [PERFORM] Postgres Clustering
>
> Hey folks,
>
> I have done some googling and found a few things on the matter.  But
> am looking for some suggestions from the experts out there.
>
> Got any good pointers for reading material to help me get up to speed
> on PostgreSQL clustering?   What options are available?  What are the
> issues?  Terminology.  I'm pretty new to the whole data-warehouse
> thing.   And once I do all the reading, I'll even be open to product
> recommendations :-)
>
> And in particular since I already have heard of this particular
> product - are there any opinions on Continuent?

Continuent works (AFAIK) like pgpool clustering, it sends the same statements 
to both/all servers in the cluster but it has no insight to the servers beyond 
this, so if via a direct connection server A becomes out of sync with server B 
then continuent is oblivious.


Other tools to look at:
- EnterpriseDB's GridSQL
- SLONY
- Command Prompt's PG Replicator


>
> thanks,
> -Alan
>
> --
> “Mother Nature doesn’t do bailouts.”
>  - Glenn Prickett
>
> --
> Sent via pgsql-performance mailing list (pgsql-performa...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

Re: [GENERAL] PGS Tuning Wizard destroys my login

[Kevin Kempter]

Jenifer;

Can you send  a copy of both your before and after config files to the list?



On Wednesday 08 April 2009 05:16:35 Jennifer Trey wrote:
> Look, I am telling now what I did.
>
> I don't want to hear claims about how its not the tuning wizards fault. It
> is!
>
> And it does more than the postgresql.conf because thats not true.
> Just until today I have been able to login and logout, using pgAdmin to my
> DB.
>
> Now, after considering what you said, I tried once more! And I am getting
> tired of this whole process.
>
> I installed the tuning wizard. I got myself a new postgresql.conf and it
> renamed the old one which I noted.
>
> I opened my pgAdmin, logged in, worked fine (I guess the changes require
> some kind of restart). I restarted my computer and we are back with the old
> pattern where I cannot login, not with pgAdmin, not with my java-app and
> not with sql shell :
> Username [postgres]:
> psql: could not connect to server: Connection refused (0x274D/10061)
>   Is the server running on host "localhost" and accepting
>   TCP/IP connections on port 5432?
>
> Press any key to continue . . .
>
> looking at the pgadmin.log i see this :
> 2009-04-08 02:03:01 ERROR : server closed the connection unexpectedly
>  This probably means the server terminated abnormally
>  before or while processing the request.
>
> I replaced the new postgresql.conf with the old one. Restarted my computer
> again. And still the problems are there. Last time I had to uninstall
> postgre (the time before I reformatted the computer) .. so I would like
> this to work this time around.
>
> About taking ownership, its not working for me either, (I am trying to
> delete the data folder that was left from the previous installation) but it
> refuses to let me delete two folders (base, pg_log) but I guess thats more
> a windows server issue.
> Lets concentrate on the first problem.
>
> How can I fix it ?
> / Jennifer

[GENERAL] covering indexes?

[Kevin Kempter]

Hi all;

I saw in the 8.1 release notes that a bug fix for covering index logic was 
included. Can anyone point me to documentation on how to get PostgreSQL to 
take advantage of a covering index (i.e. the query will return data only from 
the index with no need to hit the table) ?


Thanks in advance...

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Date math question

[Kevin Kempter]

Hi All;

I have a table that contains 2 columns ts (a timestamp) and dursec (a float - 
number of seconds)

I want to insert the following into another table:

the ts (timestamp column) and a second date which is ts + dursec

I tried these select variations with no luck:

select ts, ts + interval dursec seconds from tmp2 limit 1;

select ts, ts + 'seconds' dursec from tmp2 limit 1;



Anyone know the correct syntax for this ?


Thanks in advance

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Order by question

[Kevin Kempter]

Hi All;

I'm selecting 3 columns.  I want to order the results ascending by col1 and 
col2 and then descending by col3

Whats the syntax for this?


Thanks in advance

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Date math question

[Kevin Kempter]

Hi All;

I have a table that contains 2 columns ts (a timestamp) and dursec (a float - 
number of seconds)

I want to insert the following into another table:

the ts (timestamp column) and a second date which is ts + dursec

I tried these select variations with no luck:

select ts, ts + interval dursec seconds from tmp2 limit 1;

select ts, ts + 'seconds' dursec from tmp2 limit 1;



Anyone know the correct syntax for this ?


Thanks in advance

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgres user account on OSX

[kevin kempter]

On Linux if you install postgres via RPM's and the postgres user  
account does not exist then the RPM install creates it for you and  
sets the home dir to the root for the postgres binaries (i.e. /var/lib/ 
pgsql)


Maybe the same thing happens on a Mac install ?


On Sep 10, 2008, at 5:14 PM, Darren Weber wrote:


There is a postgres user account on my OSX system.  I'm not clear
about how it was created.  I've installed a binary version of 8.3 in
/Library/PostgreSQL/8.3/ and built another version from source into
/usr/local/pgsql/.  When I login as root and then 'su - postgres' it
takes me to the postgres account and the user directory is at
/opt/local/var/db/postgresql83/.

Can someone explain how this user account was created?

I'm trying to start the server that I built from source but it will
not create a logfile, ie:

elegans:~ postgres$ /usr/local/pgsql/bin/pg_ctl -D
/usr/local/pgsql/data -l logfile start
server starting
sh: logfile: Permission denied
elegans:~ postgres$
elegans:~ postgres$ nohup /usr/local/pgsql/bin/postgres -D
/usr/local/pgsql/data >server.log 2>&1 http://www.postgresql.org/mailpref/pgsql-general




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Trigger for insert/update of BLOB's ?

[kevin kempter]

Hi List;

Can I create an insert/update trigger based on a table that contains  
lo_* style BLOB's ?


Thanks in advance


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] function question

[kevin kempter]

Hi LIst;
Is there a way to print all the lines being executed for a function,  
like the equivelant of a psql -ef  for an sql file ?



Thanks in advance

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] String concat issues in 8.3.1 (8.3.1 bug?)

[kevin kempter]

I have a table as follows:

\d test_dim
 Table "public.test_dim"
 Column  |Type |   Modifiers
-+-+
 customer_srcid  | bigint  | not null
 segment_srcid  | bigint  | not null
 show_name   | character varying(500)  | not null
 create_dt   | timestamp without time zone | not null default now()


I have this table in a db on an 8.2.6 cluster and in another db on an  
8.3.1 cluster.


In both clusters I can do the following:


# select customer_srcid,  segment_srcid from test_dim;
 customer_srcid | segment_srcid
+---
  1 | 1
  1 | 2
  1 | 3
  1 |
  1 |
  1 |
  1 | 1
  1 | 2
  1 | 3
  1 |
  1 |
  1 |
  1 | 1
  1 | 2
  1 | 3
  1 |
  1 |
  1 |
  1 | 1
  1 | 2
  1 | 3
  1 |
  1 |
  1 |
(24 rows)



In version 8.2.6 I can do this:

# select customer_srcid || segment_srcid from test_dim;
 ?column?
--
 11
 12
 13



 11
 12
 13



 11
 12
 13



 11
 12
 13



(24 rows)




However in the 8.3.1 cluster I get this (Both clusters have the same  
data set - via a pg_dump):


# select customer_srcid || segment_srcid from test_dim;
ERROR:  operator does not exist: bigint || bigint
LINE 1: select customer_srcid || segment_srcid from sl_segment_dim;
  ^
HINT:  No operator matches the given name and argument type(s). You  
might need to add explicit type casts.





Thoughts ?



Thanks in advance


/Kevin




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] COPY to STDOUT and pipes

[kevin kempter]

Hi List;


I want to run a copy (based on a select) to STDOUT and pipe it to a  
psql copy from STDIN on a different host.


here's what I have:

1) a .sql file that looks like this:

copy (
select
  cust_id,
  cust_name,
  last_update_dt
from sl_cust
)
to STDOUT
with delimiter '|'


This works.

However I want to pipe the resulting data into a psql statement that  
does a copy IN to a table on another host. I can't seem to get it  
right. I tried this:


psql -f file1.sql | psql -h newhost -f file2.sql

where file1.sql is the copy statement above and file2.sql does a copy  
table from STDIN with delimiter '|'


Any thoughts on what I'm doing wrong?

Thanks in advance...

/Kevin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Date / interval question

[kevin kempter]

Hi List;

I'm populating a time dimension. I need to get the number of days  
since the start of the fiscal year and also the number of months since  
the start of the fiscal year based on the current 'date' being  
processed.


Example:

my current process date is 01/01/2007
start date of fiscal year is 09/01/2006

I can get the number of days since the start of the fiscal year like  
this:


# select date '01/01/2007' -  date '09/01/2006' as interval;
 interval
--
  122
(1 row)

However I'm stumped [er how to get the number of months from  
09/01/2007 thru 01/01/2007


Thoughts ?

Thanks in advance...


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Update Join ?

[kevin kempter]

Hi List;

Does Postgres allow updates based on the context of a sub-query,  
something like the sample below ?




1) Insert data (real_tab.keyID and real_tab.data_desc)  into a temp  
table (temp_tab)


2) update real_tab
   set real_tab.data_desc = temp_tab.data_desc
  join real_tab on real_tab.keyID = temp_tab.keyID ;

Thanks in advance..

[GENERAL] SQL question

[kevin kempter]

Hi List;

I have a table that has 3 date columns :

create table xyz (
xyz_id integer,
date1   timestamp,
date2   timestamp,
date3   timestamp
)


I want to select in a query the xyz_id and the max date column for  
each row

something like :
create table temp2 as select xyz_id (max date?) where ...

Is this - the (max date?) part a case scenario or is there a better,  
more efficient method ?


Thanks in advance


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PostgreSQL and MOLAP ?

[Kevin Kempter]

Anyone know of any MOLAP/MDDB/MDX Business Intelligence reporting solutions 
tahat work on top of PostgreSQL ?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] ER Diagram design tools (Linux)

[Kevin Kempter]

On Wednesday 05 March 2008 20:33:43 Conor McTernan wrote:
> I was wondering if anyone knows of any good ER Diagram tools for
> Postgres that run on Linux.
>
> I have been using DBDesigner by FabForce for a couple of years, but
> development has stopped while MySQL workbench is being built (for
> windows only). Neither of these applications will talk to Postgres and
> I've found DBDesigner to be a bit buggy at the best of times (it's
> still quite good and better than nothing I suppose).
>
> I've been using PgAdmin3 which is great for updating/managing
> tables/view etc, but I would really like something for modelling ER
> diagrams which will talk directly to Postgres.
>
> Does anyone know of any commercial or open source software that will do
> this?
>
> Cheers,
>
> Conor
>
> ---(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

Have a look at Data Architect (www.theKompany.com) 
or Open System Architect (www.codebydesign.com)


/Kevin

---(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 view temp tables

[Kevin Kempter]

On Thursday 21 February 2008 00:17:56 Chris wrote:
> pc wrote:
> > My php code is creating temporary table named mytemp,but when I run a
> > selec * from mytemp I cannot see the table.How can I see the table
> > from postgresql command prompt?
>
> temp tables are automatically deleted when the connection is closed.
>
> make it a non-temp table :)

Temp tables are also limited in scope to the current session, so if your PHP 
process created the temp table, then ONLY your PHP process can see the temp 
table (and even then only until a dis-connect occurs)



---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

[GENERAL] temp table question

[Kevin Kempter]

Hi list;

If I create a temp table (i.e. create temp table xyz as select from ...) is 
the scope of this table limited to a session. Meaning,  can several sessions 
all run the above create temp table statement all referencing the same temp 
table name at the same time?


Thanks in advance

---(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] How to query for a user-table column name?

[Kevin Kempter]

Hi List;

I'm researching a db and I want to find samples of some of the data. I know 
based on the documentation for the proposed "new" schema that the db I have 
access to (the "old" schema) probably has a column in one or more of the user 
tables called 'region'. 

I'm looking for a way to query the system tables to find all user tables with 
a column named region.  Any thoughts, help?


Thanks in advance..

/Kevin

---(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] thank you

[Kevin Kempter]

On Tuesday 18 December 2007 19:26:40 Kevin Hunter wrote:
> Hullo List,
>
> This is aimed at everyone in this community who contributes to the
> Postgres project, but especially at the core folks who continually make
> this community great through energy, time, money, responses, and
> what-have-you.
>
> I see lots of "Thank you"s go by for this problem or that conundrum
> solved, but I don't think I've seen a general thank you for all that all
> y'all do.  (If I've missed them in the blur of emails I get, I do
> apologize.)  Thank you for all that you do.
>
> Thank you for the countless times you've saved my butt from my own
> stupidity.  Thank you for the absolute awesome help you've been while
> tracking down bugs.  Thank you for signing the NDA without fuss and
> searching my DB for a bug that I may have caused.  Thank you for
> answering my frustrated emails at some ungodly hour of the morning (do
> you sleep?!).  Thank you for engaging others in discussion, sometimes
> beyond the point of annoyance ... your enthusiasm and love for the
> project is duly noted. Thank you for constantly preaching "the right
> way".  Thank you . . . well you get the drift.  I'll stop before this
> turns into some chain-letter type ordeal.
>
> The point is that I hope you realize just how much you all mean to the
> community.
>
> Kevin


I'll second that. Plus thank you for a database system that's a joy to work 
with.

/Kevin (Although not the same Kevin as above)

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] is the default of lowercase enforced on all OS platforms?

[Kevin Kempter]

Hi List;

I know that the default case for postgres (psql) on Linux/*ix platforms is 
lowercase. I wonder is this also true on Windows platforms?


Thanks in advance..

/Kevin


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] A few basic troubleshooting questions

[Kevin Kempter]

Hi List;

I have a few basic troubleshooting questions...

1)  If I have autovacuum turned on, how do I know which table is being vacuumed 
when in pg_stat_activity I only see VACUUM?

I've been using this query but it doesn't always work... is there a better way?

CREATE Temp table tmp_p as
SELECT
procpid from pg_stat_activity where current_query = 'VACUUM'
;

SELECT
relname as current_vacuum_activity
from pg_class where oid in
( select relation from pg_locks where pid = any (select procpid from tmp_p) 
)
;

2) if I see a 'ROLLBACK' in pg_stat_activity, how can I determine what 
query/update/etc is being rolled back?

3) How do I know for sure what processes are are waiting on a specific lock ? 
for example I have a process that has an ungranted lock on table X. Is there an 
easy way via pg_locks to determine which processes are waiting on the ungranted 
lock on table X?

4) How do I determine in general if the db has a memory bottleneck vs CPU 
bottleneck vs I/O bottleneck? I know about pg_statio, just not sure how to 
guage where the db is the most constrained.

Thanks in advance

/Kevin

Re: [GENERAL] One database vs. hundreds?

[Kevin Kempter]

On Tuesday 28 August 2007 06:32:32 A. Kretschmer wrote:
> am  Tue, dem 28.08.2007, um 14:23:00 +0200 mailte Kamil Srot folgendes:
> > Kynn Jones wrote:
> > >I'm hoping to get some advice on a design question I'm grappling with.
> > > I have a database now that in many respects may be regarded as an
> > >collection of a few hundred much smaller "parallel databases", all
> > >having the same schema.  What I mean by this is that, as far as the
> > >intended use of this particular system there are no meaningful queries
> > >whose results would include information from more than one of these
> >
> > I don't have experience in this type of application, but we use pgsql
> > partitioning for other reasons
> > and it has some of the features you want (data separation, query
> > performance, ...).
> > It can be worth reading:
> > http://www.postgresql.org/docs/8.2/interactive/ddl-partitioning.html
>
> He don't need table partitioning, this is a different thing.
>
>
> Andreas



I find that creating multiple schema's is often better than creating multiple 
db's since in the remote chance you might want to query across the schema's 
it's a no-brainer where queries across db's are a bit more difficult.

That sais AFAIK db's vs. schema's outside of the query Issue I mentioned above 
are merely an organizational mechanism. Unless you're talking about multiple 
clusters it's kinda the old 6 vs. half a dozen arguement - meaning it doesn't 
really matter per performance or functionality from strictly a postgres 
perspective. The deciscion then becomes an application architecture 
question(s) such as how important is it to isolate these db's/schema's from 
each other and is there a forseeable need to query across them in the future, 
etc...

Hope this helps.. My vote would be for schema's unless there's a requirement 
on the table for isolation - it leaves your options open & more flexible for 
the future.

/Kevin


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] SQL Diff ?

[Kevin Kempter]

On Saturday 25 August 2007 23:49:39 Ron Johnson wrote:
> On 08/25/07 22:21, Kevin Kempter wrote:
> > On Saturday 25 August 2007 21:10:19 Ron Johnson wrote:
> >> On 08/25/07 21:51, Kevin Kempter wrote:
> >>> Hi List;
> >>>
> >>> I have a very large table (52million rows) - I'm creating a copy of it
> >>> to rid it of 35G worth of dead space, then I'll do a sync, drop the
> >>> original table and rename table2.
> >>
> >> What is your definition of "dead space"?
> >>
> >> Bad rows, duplicate rows, old rows?  Something else?
> >
> > deleted rows that should have been cleaned up with vacuum, problem is the
> > client let it go so long that now I cant get a vacuum to finish cause it
> > impacts the day2day operations too much.  Long story, see my recent
> > questions on the performance list for more info.
>
> OK.
>
> >>> Once I have the table2 as a copy of table1 what's the best way to
> >>> select all rows that have been changed, modified in table1  since the
> >>> initial laod from table1 into table2?
>
> Is this a 24x7 database?

Yes. with little room for extra overhead


>
> >>> Also I'll need to delete any rows in table2 that no longer remain in
> >>> table1.
> >>>
> >>> There is no change date column
> >>> I could do something like select * from table1 where col1 || col2 ||
> >>> col3 etc not in (select col1 || col2 || col3 etc from table2)
> >>>
> >>> but this would be ineffecient & slow.
> >>>
> >>> Anyone have a suggestion to do this in an efficient manner?
> >>>
> >>> Thanks in advance



---(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] SQL Diff ?

[Kevin Kempter]

On Saturday 25 August 2007 21:10:19 Ron Johnson wrote:
> On 08/25/07 21:51, Kevin Kempter wrote:
> > Hi List;
> >
> > I have a very large table (52million rows) - I'm creating a copy of it to
> > rid it of 35G worth of dead space, then I'll do a sync, drop the original
> > table and rename table2.
>
> What is your definition of "dead space"?
>
> Bad rows, duplicate rows, old rows?  Something else?

deleted rows that should have been cleaned up with vacuum, problem is the 
client let it go so long that now I cant get a vacuum to finish cause it 
impacts the day2day operations too much.  Long story, see my recent questions 
on the performance list for more info.


>
> > Once I have the table2 as a copy of table1 what's the best way to select
> > all rows that have been changed, modified in table1  since the initial
> > laod from table1 into table2?
> >
> > Also I'll need to delete any rows in table2 that no longer remain in
> > table1.
> >
> > There is no change date column
> > I could do something like select * from table1 where col1 || col2 || col3
> > etc not in (select col1 || col2 || col3 etc from table2)
> >
> > but this would be ineffecient & slow.
> >
> > Anyone have a suggestion to do this in an efficient manner?
> >
> > Thanks in advance



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] SQL Diff ?

[Kevin Kempter]

Hi List;

I have a very large table (52million rows) - I'm creating a copy of it to rid 
it of 35G worth of dead space, then I'll do a sync, drop the original table 
and rename table2.

Once I have the table2 as a copy of table1 what's the best way to select all 
rows that have been changed, modified in table1  since the initial laod from 
table1 into table2?

Also I'll need to delete any rows in table2 that no longer remain in table1.

There is no change date column
I could do something like select * from table1 where col1 || col2 || col3 etc 
not in (select col1 || col2 || col3 etc from table2)

but this would be ineffecient & slow.

Anyone have a suggestion to do this in an efficient manner?

Thanks in advance

/Kevin


---(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

[GENERAL] DRDB risk factors?

[Kevin Kempter]

Hi List ;

per considering DRDB as a replication solution in a failed master node 
scenario, is there a risk of loosing not only in-flight transactions but alos 
un-sync'd buffer-pool dirty pages?

If so, how might I minimize this risk ?

Thanks in advance...

---(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

[GENERAL] pgpool redundancy question

[Kevin Kempter]

Hi List;

It seems that in a replication scenario pgpool becomes the single point of 
failure, i.e. if we loose the pgpool box were dead. Any thoughts, 
suggestions, best practices, etc per creating redundancy in the pgpool layer?

I've posted to the pgpool list as well but I wanted to get some thoughts on 
how the postgresql community has dealt with this...


Thanks in advance

---(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

[GENERAL] STDERR vs. SYSLOG logging

[Kevin Kempter]

Hi List;

Anyone have any thoughts per which logging method (SYSLOG vs STDERR) is the 
better approach ?

Thanks in advance...

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Or selection on index versus union

[kevin . kempter]

Just a guess but have you tried using an in clause?

select something from table where fase in  ('1' , '2');




On Tuesday 04 October 2005 13:32, [EMAIL PROTECTED] wrote:
> Hello
>
> I've got a table with an index, let's call it fase.
>
> The following query is fine: 'select something from table where fase = '1';
>
> However, this is disastrously slow:
> select something from table where fase = '1' or fase = '2';
>
> The reason is that the query planner decides to ignore the index, and goes
> for a sequential scan of the table (with a couple of million records).
>
> If I do:
> select something from table where fase = '1'
> union
> select something from table where fase = '2';
>
> it's fine again, but it's a lot of typing, and the first formulation has a
> more natural feel to it.
>
> Is there a way to convince the planner to use the fase index for this type
> of query, or is there a hook somewhere that I missed that allows me to
> rewrite a query like the above with a server-side function ?
>
> Thanks in advance,
>
> Han Holl
>
> ---(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