Re: [GENERAL] evaluating expressions stored in table

Douglas Little wrote: > I need to evaluate an expression that I have stored in a table, and not sure > how to force evaluation > of a column value. [...] > The pass/fail query looks something like this > > Update testscore > Set metricstatus = case when table_a.col_a = table_b.col_bthen 'P

[GENERAL] Vacuum analyze verbose output

Hi all, I ran following command on 8.2.2 postgresql:  psql -p port dbname -c "vacuum analyze verbose" last few lines from "vacuum analyze verbose" output: DETAIL:  A total of 2336 page slots are in use (including overhead).2336 page slots are required to track all free space.Current limits are:  1

Re: [GENERAL] Vacuum analyze verbose output

Anjali Arora wrote: > I ran following command on 8.2.2 postgresql: > psql -p port dbname -c "vacuum analyze verbose" > last few lines from "vacuum analyze verbose" output: > > DETAIL: A total of 2336 page slots are in use (including overhead). > 2336 page slots are required to track all free spa

Re: [GENERAL] Vacuum analyze verbose output

> From: Anjali Arora >To: pgsql-general@postgresql.org >Sent: Wednesday, 19 December 2012, 9:14 >Subject: [GENERAL] Vacuum analyze verbose output > > >Hi all, > > >I ran following command on 8.2.2 postgresql: > > > psql -p port dbname -c "vacuum analyze verbose" > > >last few lines from "vacuum

Re: [GENERAL] Corrupt indexes on slave when using pg_bulkload on master

Actually, scratch that.  The difference in behaviour seems to be on the optimiser which now table scans the 10 row table (which I guess it should always have done really) rather than use the index as it was in 9.1.6.   The same index corruption occurs, so the same reproduction case stands, it jus

Re: [GENERAL] Any experience with Drobo SAN and PG?

On Mon, Dec 17, 2012 at 1:27 PM, Michael Nolan wrote: > I'm looking to spec a new production server for a small client and > have been looking at the Drobo SAN units. > One of my engineers and I did a lot of investigation into SAN units. The one that came up to the top for us was the Synology. T

Re: [GENERAL] Join several tables (to fetch user info), but one of them is optional (user avatar)

Alexander Farber wrote: > given a user name in a Drupal 7.17 database > using PostgreSQL 8.4.13 I am trying to fetch user info > (uid, city, gender, avatar) distributed over several tables. > > The avatar is however optional - some users don't have it. > > For users, that do have avatars my query

Re: [GENERAL] Join several tables (to fetch user info), but one of them is optional (user avatar)

Thank you, I've ended up with: # select u.uid, /* u.pass, */ f.filename as avatar, (g.field_gender_value='Female') as female, c.field_city_value as city from drupal_users u LEFT OUTER JOIN drupal_file_managed f on (u.picture=f.fid), drupal_fi

[GENERAL] How to startup the database server?

Hi all, I am a freshman of PostgreSQL, My env: PG 8.4 on CentOS,I follow the document in the offical site to startup the db server. Here's what I do below, the database cluster is ready, but I can't start the database server. [root@testbox ~]# service postgresql initdb Data directory is not emp

Re: [GENERAL] How to startup the database server?

On 12/19/2012 06:06 AM, Jason Ma wrote: Hi all, I am a freshman of PostgreSQL, My env: PG 8.4 on CentOS,I follow the document in the offical site to startup the db server. Here's what I do below, the database cluster is ready, but I can't start the database server. [root@testbox ~]# service

Re: [GENERAL] How to startup the database server?

Thanks, adrian, but I have check the pgstartup.log, I got the following messages: . creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok Success. You can now start the database server using: /usr

Re: [GENERAL] How to startup the database server?

On 12/19/2012 07:07 AM, Jason Ma wrote: Thanks, adrian, but I have check the pgstartup.log, I got the following messages: . creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok Success. You can now

Re: [GENERAL] How to startup the database server?

Hi, The ps output is after the server start, I don't know why I can't see any process after start the server. And of course I use root to initial db, 'cause we have to run this command in CentOS which you need the privilege of root. service postgresql start Regards, Jason 2012/12/19 Adrian

[GENERAL] copy from questions

I am using version 9.1 and have a large number of files to insert. I am trying to use a simple COPY FROM command but have a couple questions. 1. There are a small number of instances where there are duplicate records that are being caught by the primary key (as it is supposed to do). However, th

Re: [GENERAL] copy from questions

On 12/19/2012 08:13 AM, Kirk Wythers wrote: I am using version 9.1 and have a large number of files to insert. I am trying to use a simple COPY FROM command but have a couple questions. 1. There are a small number of instances where there are duplicate records that are being caught by the prim

Re: [GENERAL] How to startup the database server?

On Wed, Dec 19, 2012 at 7:34 AM, Jason Ma wrote: > Hi, >The ps output is after the server start, I don't know why I can't see > any process after start the server. And of course I use root to initial db, > 'cause we have to run this command in CentOS which you need the privilege > of root. >

[GENERAL] cannot load server.crt

9.1.3 on Linux . . . We use our own CA implementation inside Java to generate a PEM-encoded certificate chain (server.crt) and key (server.key). The certificates are, as they should be, base-64 encoded and surrounded by the appropriate delimiters such as -BEGIN CERTIFICATE- -END CER

Re: [GENERAL] Join several tables (to fetch user info), but one of them is optional (user avatar)

On 12/19/2012 5:54 AM, Alexander Farber wrote: I wonder though what is the syntax if I wanted the other 2 tables (drupal_field_data_field_gender and drupal_field_data_field_city) to be joined as a "left outer join" as well? you were using an implied join instead of an explicit one. select

[GENERAL] cannot load server.crt

Continuation . . . When this happens, Postgres rejects the certificate. FATAL: could not load server certificate file "server.crt": no SSL error reported -dvs-

Re: [GENERAL] Any experience with Drobo SAN and PG?

On 12/19/2012 5:29 AM, Vick Khera wrote: The Drobo uses some proprietary technology for their RAID. its not really proprietary, and its not really raid. its file replication. totally doesn't work for random write type applications like databases. for my HOME storage, I picked up a HP Micro

Re: [GENERAL] cannot load server.crt

David http://docs.oracle.com/cd/E19159-01/819-3671/ablrg/index.html cd FolderThatContainsNameOfCertificate.crt certutil -d NameOfCertificate.crt a few things to check: 1)make sure current date of usage is after "Not Before Date" 2)make sure current date of usage is before "Not After Date" 3)mak

[GENERAL] pg_xlog is getting bigger

Hi, I am working on a Postgresql 9.0 server. I have no replication and archive mode setup. But I found that the pg_xlog is getting bigger and bigger. Right now it is 20 GB. How should I recover these spaces? Please let me know. Thanks.

Re: [GENERAL] pg_xlog is getting bigger

AI Rumman wrote: > I am working on a Postgresql 9.0 server. I have no replication and archive > mode setup. But I found that the pg_xlog is getting bigger and bigger. > Right now it is 20 GB. > > How should I recover these spaces? Do you have archiving turned on? Are you getting errors in the se

Re: [GENERAL] copy from questions

Thanks for the reply Steve. These suggestions are new to me, so I'd like to rephrase them back to you in order to make sure I understand the bits and details. On Dec 19, 2012, at 10:48 AM, Steve Crawford wrote: >> > I suppose you could use a trigger to check each record before inserting bu

Re: [GENERAL] pg_xlog is getting bigger

AI Rumman wrote: > Kevin Grittner wrote: >> AI Rumman wrote: >>> Kevin Grittner wrote: AI Rumman wrote: > I am working on a Postgresql 9.0 server. I have no replication and > archive mode setup. But I found that the pg_xlog is getting bigger > and bigger. Right now it is 20

Re: [GENERAL] pg_xlog is getting bigger

> > I don't see anything obvious. Putting this back on the list, where > it should have stayed all along. Maybe someone else has an idea; > I've only seen such behavior when there were archiving problems > which were showing up in the server log. > > -Kevin > > > -- Perhaps it cannot delete old log

Re: [GENERAL] pg_xlog is getting bigger

On 12/19/2012 01:16 PM, Kevin Grittner wrote: AI Rumman wrote: Kevin Grittner wrote: AI Rumman wrote: Kevin Grittner wrote: AI Rumman wrote: I am working on a Postgresql 9.0 server. I have no replication and archive mode setup. But I found that the pg_xlog is getting bigger and bigger. Ri

Re: [GENERAL] copy from questions

On 12/19/2012 01:06 PM, Kirk Wythers wrote: Thanks for the reply Steve. These suggestions are new to me, so I'd like to rephrase them back to you in order to make sure I understand the bits and details. On Dec 19, 2012, at 10:48 AM, Steve Crawford > wr

Re: [GENERAL] pg_xlog is getting bigger

On 12/19/2012 01:39 PM, AI Rumman wrote: Well just for grins and to go to the source, look in the postgresql.conf file itself. In particular what is the settings for: wal_keep_segments Also what does querying pg_stat_activity as the postgres user show? Looking for qu

[GENERAL] Moving a database to a new TABLESPACE in Postgres 8.3

In Postgres 8.3, how can I move a database to a different TABLESPACE? My goal is to move rarely used databases off of the SSD and onto the HDD. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-gen

Re: [GENERAL] Moving a database to a new TABLESPACE in Postgres 8.3

Robert James wrote: > In Postgres 8.3, how can I move a database to a different TABLESPACE? > My goal is to move rarely used databases off of the SSD and onto the > HDD. http://www.postgresql.org/docs/8.3/interactive/sql-createtablespace.html http://www.postgresql.org/docs/8.3/interactive/sql-al

Re: [GENERAL] pg_xlog is getting bigger

On 12/19/2012 02:00 PM, AI Rumman wrote: Please reply to the list also. Well the question is how long have those idle transactions been around? For a good blog on the subject see: http://www.depesz.com/2008/08/__28/hunting-idle-in-__transactions/

Re: [GENERAL] pg_xlog is getting bigger

On 12/19/2012 01:16 PM, Kevin Grittner wrote: AI Rumman wrote: Kevin Grittner wrote: AI Rumman wrote: Kevin Grittner wrote: AI Rumman wrote: I am working on a Postgresql 9.0 server. I have no replication and archive mode setup. But I found that the pg_xlog is getting bigger and bigger. Ri

Re: [GENERAL] cannot load server.crt

"Sahagian, David" writes: > We use our own CA implementation inside Java to generate a PEM-encoded > certificate chain (server.crt) and key (server.key). > The certificates are, as they should be, base-64 encoded and surrounded by > the appropriate delimiters such as > -BEGIN CERTIFICATE---

Re: [GENERAL] pg_xlog is getting bigger

Adrian Klaver writes: > Well the question is how long have those idle transactions been around? Idle transactions shouldn't have anything to do with pg_xlog bloat. What causes xlog bloat is inability to release old WAL because either (a) we're not able to complete checkpoints, or (b) WAL archivin

Re: [GENERAL] pg_xlog is getting bigger

On 12/19/2012 04:12 PM, Tom Lane wrote: Adrian Klaver writes: Well the question is how long have those idle transactions been around? Idle transactions shouldn't have anything to do with pg_xlog bloat. What causes xlog bloat is inability to release old WAL because either (a) we're not able to

Re: [GENERAL] pg_xlog is getting bigger

On Wed, Dec 19, 2012 at 7:52 PM, Adrian Klaver wrote: > On 12/19/2012 04:12 PM, Tom Lane wrote: > >> Adrian Klaver writes: >> >>> Well the question is how long have those idle transactions been around? >>> >> >> Idle transactions shouldn't have anything to do with pg_xlog bloat. >> What causes xl

Re: [GENERAL] pg_xlog is getting bigger

Adrian Klaver writes: > On 12/19/2012 04:12 PM, Tom Lane wrote: >> Idle transactions shouldn't have anything to do with pg_xlog bloat. >> What causes xlog bloat is inability to release old WAL because either >> (a) we're not able to complete checkpoints, or (b) WAL archiving is >> enabled but malf

Re: [GENERAL] pg_xlog is getting bigger

Adrian Klaver writes: > On 12/19/2012 01:16 PM, Kevin Grittner wrote: >> AI Rumman wrote: >>> checkpoint_segments | 300 > Aah, just noticed the above. I saw that too, but it doesn't seem to explain 20GB worth of pg_xlog. The fine manual mentions somewhere that we can keep up to twice the specifi

Re: [GENERAL] pg_xlog is getting bigger

On 12/19/2012 04:55 PM, Tom Lane wrote: Adrian Klaver writes: On 12/19/2012 04:12 PM, Tom Lane wrote: Idle transactions shouldn't have anything to do with pg_xlog bloat. What causes xlog bloat is inability to release old WAL because either (a) we're not able to complete checkpoints, or (b) WAL

Re: [GENERAL] pg_xlog is getting bigger

On 12/19/2012 04:54 PM, AI Rumman wrote: I modified checkpoint_segment to 100 form 300 and then forced some CHECKPOINT and pg_switch_xlog() and now found that the pg_xlog file got almost 1 gb of space back. Per Toms post this is a start but probably not the answer. Have you looked in the pg_x

Re: [GENERAL] How to startup the database server?

Hi Jason, sounds a bit weird. Can you check which user the database files belong to? A 'ls -lah /var/lib/pgsql' should do the job. Then switch from root user to the postgresql user and try to start the cluster manually with the command line from below (put from your message): /usr/bin/postgres -D