Re: [GENERAL] I/O error on data file, can't run backup

[Leif Biberg Kristensen]

On Thursday 6. October 2011 07.07.11 Craig Ringer wrote:
> On 10/06/2011 03:06 AM, Leif Biberg Kristensen wrote:
> > I seemingly fixed the problem by stopping postgres and doing:
> > 
> > balapapa 612249 # mv 11658 11658.old
> > balapapa 612249 # mv 11658.old 11658
> > 
> > And the backup magically works.
> 
> Woo! That's ... "interesting".
> 
> I'd be inclined to suspect filesystem corruption, a file system bug /
> kernel bug (not very likely if you're on ext3), flakey RAM, etc rather
> than a failing disk ... though a failing disk _could_ still be the culprit.
> 
> Use smartmontools to do a self-test; if 'smartctl -d ata -t long
> /dev/sdx' (where 'x' is the drive node) is reported by 'smartctl -d ata
> -a /dev/sdx' as having passed, there are no pending or uncorrectable
> sectors, and the disk status is reported as 'HEALTHY' your disk is quite
> likely OK. Note that a 'PASSED' or 'HEALTHY' report by its self doesn't
> mean much, disk firmwares often return HEALTHY even when the disk can't
> even read sector 0.
> 
> I strongly recommend making a full backup, both a pg_dump *and* a
> file-system level copy of the datadir. Personally I'd then do a test
> restore of the pg_dump backup on a separate Pg instance and if it looked
> OK I'd re-initdb then reload from the dump.

Craig,
Thank you very much for the tip on smartmontools, which I didn't know about. 
There indeed appears to be some problems with this disk:

8<---

balapapa ~ # smartctl -d ata -a /dev/sdb -s on
smartctl 5.40 2010-10-16 r3189 [x86_64-pc-linux-gnu] (local build)
Copyright (C) 2002-10 by Bruce Allen, http://smartmontools.sourceforge.net

=== START OF INFORMATION SECTION ===
Model Family: Seagate Barracuda 7200.11 family
Device Model: ST31000340AS
Serial Number:9QJ1ZMHY
Firmware Version: SD15
User Capacity:1 000 204 886 016 bytes
Device is:In smartctl database [for details use: -P show]
ATA Version is:   8
ATA Standard is:  ATA-8-ACS revision 4
Local Time is:Thu Oct  6 07:46:19 2011 CEST

==> WARNING: There are known problems with these drives,
AND THIS FIRMWARE VERSION IS AFFECTED,
see the following Seagate web pages:
http://seagate.custkb.com/seagate/crm/selfservice/search.jsp?DocId=207931
http://seagate.custkb.com/seagate/crm/selfservice/search.jsp?DocId=207951

SMART support is: Available - device has SMART capability.
SMART support is: Disabled

=== START OF ENABLE/DISABLE COMMANDS SECTION ===
SMART Enabled.

=== START OF READ SMART DATA SECTION ===
SMART overall-health self-assessment test result: PASSED

General SMART Values:
Offline data collection status:  (0x82) Offline data collection activity
was completed without error.
Auto Offline Data Collection: Enabled.
Self-test execution status:  (  25) The self-test routine was aborted by
the host.
Total time to complete Offline 
data collection: ( 650) seconds.
Offline data collection
capabilities:(0x7b) SMART execute Offline immediate.
Auto Offline data collection on/off 
support.
Suspend Offline collection upon new
command.
Offline surface scan supported.
Self-test supported.
Conveyance Self-test supported.
Selective Self-test supported.
SMART capabilities:(0x0003) Saves SMART data before entering
power-saving mode.
Supports SMART auto save timer.
Error logging capability:(0x01) Error logging supported.
General Purpose Logging supported.
Short self-test routine 
recommended polling time:(   1) minutes.
Extended self-test routine
recommended polling time:( 236) minutes.
Conveyance self-test routine
recommended polling time:(   2) minutes.
SCT capabilities:  (0x103b) SCT Status supported.
SCT Error Recovery Control supported.
SCT Feature Control supported.
SCT Data Table supported.

SMART Attributes Data Structure revision number: 10
Vendor Specific SMART Attributes with Thresholds:
ID# ATTRIBUTE_NAME  FLAG VALUE WORST THRESH TYPE  UPDATED  
WHEN_FAILED RAW_VALUE
  1 Raw_Read_Error_Rate 0x000f   114   099   006Pre-fail  Always   
-   61796058
  3 Spin_Up_Time0x0003   094   092   000Pre-fail  Always   
-   0
  4 Start_Stop_Count0x0032   100   100   020Old_age   Always   
-   46
  5 Reallocated_Sector_Ct   0x0033   100   100   036Pre-fail  Always   
-

Re: [GENERAL] user-interface to upload csv files

[Craig Ringer]

On 10/06/2011 01:47 PM, Robert Buckley wrote:

Hi,

I am having problems getting csv files into postgres. Does anyone know
if there is an opensource user-interface to tackle this?


If it's a well-behaved CSV file, use the COPY command or psql's \copy.

For more complicated work, use an ETL tool like Talend, look into 
pg_bulkload, or write some perl/python script to do it.


--
Craig Ringer


--
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] Fwd: Postgresql-8.2 Replication

[Scott Marlowe]

On Wed, Oct 5, 2011 at 10:41 PM, khizer  wrote:
> ***root***@**mehdi-desktop**:~/slony/slony1-1.2.22# make
>
> make[1]: Entering directory `/home/mehdi/slony/slony1-1.2.22/src'
> make[2]: Entering directory `/home/mehdi/slony/slony1-1.2.22/src/xxid'
> gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -fpic -I../..
> -I/usr/include/postgresql/ -I/usr/include/postgresql/8.2/server/  -c -o
> xxid.o xxid.c
> make[2]: Leaving directory `/home/mehdi/slony/slony1-1.2.22/src/xxid'
> make[1]: Leaving directory `/home/mehdi/slony/slony1-1.2.22/src'

So does the make finish?  Does  sudo make install then work?

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

[GENERAL] user-interface to upload csv files

[Robert Buckley]

Hi,

I am having problems getting csv files into postgres. Does anyone know if there 
is an opensource user-interface to tackle this?

yours,

Rob

[GENERAL] Fwd: Postgresql-8.2 Replication

[khizer]

Hi Experts,


I want to set Master Slave replication of a database on Ubuntu 10.10
 So kindly requesting u to send  the instructions.

Please waiting for your valuable responses

I tried to compile and install slony but i am getting error for 
installation as follows;



***mehdi***@**mehdi-desktop**:~$ pg_config
BINDIR = /usr/lib/postgresql/8.2/bin
DOCDIR = /usr/share/doc/postgresql-doc-8.2
INCLUDEDIR = /usr/include/postgresql
PKGINCLUDEDIR = /usr/include/postgresql
INCLUDEDIR-SERVER = /usr/include/postgresql/8.2/server
LIBDIR = /usr/lib
PKGLIBDIR = /usr/lib/postgresql/8.2/lib
LOCALEDIR = /usr/share/locale
MANDIR = /usr/share/postgresql/8.2/man
SHAREDIR = /usr/share/postgresql/8.2
SYSCONFDIR = /etc/postgresql
PGXS = /usr/lib/postgresql/8.2/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--build=x86_64-linux-gnu' '--prefix=/usr' 
'--includedir=/usr/include' '--mandir=/usr/share/man' 
'--infodir=/usr/share/info' '--sysconfdir=/etc' '--localstatedir=/var' 
'--libexecdir=/usr/lib/postgresql-8.2' '--disable-maintainer-mode' 
'--disable-dependency-tracking' '--srcdir=.' 
'--mandir=/usr/share/postgresql/8.2/man' 
'--with-docdir=/usr/share/doc/postgresql-doc-8.2' 
'--datadir=/usr/share/postgresql/8.2' 
'--bindir=/usr/lib/postgresql/8.2/bin' 
'--includedir=/usr/include/postgresql/' '--enable-nls' 
'--enable-integer-datetimes' '--enable-thread-safety' '--enable-debug' 
'--disable-rpath' '--with-tcl' '--with-perl' '--with-python' 
'--with-pam' '--with-krb5' '--with-openssl' '--with-gnu-ld' 
'--with-tclconfig=/usr/lib/tcl8.4' '--with-tkconfig=/usr/lib/tk8.4' 
'--with-includes=/usr/include/tcl8.4' '--with-pgport=5432' 'CFLAGS=-g 
-O2 -g -Wall -O2 -fPIC' 'LDFLAGS=-Wl,-Bsymbolic-functions 
-Wl,--as-needed' 'CC=cc' 'CPPFLAGS=' 'build_alias=x86_64-linux-gnu'

CC = cc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/tcl8.4
CFLAGS = -g -O2 -g -Wall -O2 -fPIC -Wall -Wmissing-prototypes 
-Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels 
-fno-strict-aliasing -fwrapv -g

CFLAGS_SL = -fpic
LDFLAGS = -Wl,-Bsymbolic-functions -Wl,--as-needed
LDFLAGS_SL =
LIBS = -lpgport -lpam -lssl -lcrypto -lkrb5 -lcom_err -lz -lreadline 
-lcrypt -ldl -lm

VERSION = PostgreSQL 8.2.7




***root***@**mehdi-desktop**:~/slony/slony1-1.2.22# ./configure 
--prefix=/usr/lib/postgresql/8.2/bin/  
--with-pgconfigdir=/usr/lib/postgresql/8.2/bin/  
--with-perltools=/usr/lib/postgresql/8.2/bin/ 
--with-pgpkglibdir=/usr/lib/postgresql/8.2/lib/  
--with-pgsharedir=/usr/share/postgresql/8.2/



checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking which template to use... linux
configure: using CFLAGS=
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking for ld used by GCC... /usr/bin/ld
checking if the linker (/usr/bin/ld) is GNU ld... yes
checking for perl... /usr/bin/perl
checking for tar... /bin/tar
checking for flex... no
checking for ,... no
checking for lex... no
checking for bison... bison -y
checking for sed... sed
checking for the pthreads library -lpthreads... no
checking whether pthreads work without any flags... no
checking whether pthreads work with -Kthread... no
checking whether pthreads work with -kthread... no
checking for the pthreads library -llthread... no
checking whether pthreads work with -pthread... yes
checking for joinable pthread attribute... PTHREAD_CREATE_JOINABLE
checking if more special flags are required for pthreads... no
checking for cc_r... gcc
checking how to run the C preprocessor... gcc -E
checking for grep that handles long lines and -e... /bin/grep
checking for egrep... /bin/grep -E
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking fcntl.h usability... yes
checking fcntl.h presence... yes
checking for fcntl.h... yes
checking limits.h usability... yes
checking limits.h presence... yes
checking for limits.h... yes
checking stddef.h usability... yes
checking stddef.h presence... yes
checking for stddef.h... yes
checking sys/socket.h usability... yes
checking sys/socket.h presence... yes
checking for sys/socket.h... yes
checking sys/time.h usability... yes
checking sys/time.h presence... yes
checking for sys/time.h... yes
checking for inttypes.h... (cached) yes
checking for gettimeofday... yes
checking for dup2... yes
checking for alarm... yes
checking for memset... yes
checking for select... yes
checking for strdup... yes
checking for 

Re: [GENERAL] I/O error on data file, can't run backup

[Craig Ringer]

On 10/06/2011 03:06 AM, Leif Biberg Kristensen wrote:

I seemingly fixed the problem by stopping postgres and doing:

balapapa 612249 # mv 11658 11658.old
balapapa 612249 # mv 11658.old 11658

And the backup magically works.


Woo! That's ... "interesting".

I'd be inclined to suspect filesystem corruption, a file system bug / 
kernel bug (not very likely if you're on ext3), flakey RAM, etc rather 
than a failing disk ... though a failing disk _could_ still be the culprit.


Use smartmontools to do a self-test; if 'smartctl -d ata -t long 
/dev/sdx' (where 'x' is the drive node) is reported by 'smartctl -d ata 
-a /dev/sdx' as having passed, there are no pending or uncorrectable 
sectors, and the disk status is reported as 'HEALTHY' your disk is quite 
likely OK. Note that a 'PASSED' or 'HEALTHY' report by its self doesn't 
mean much, disk firmwares often return HEALTHY even when the disk can't 
even read sector 0.


I strongly recommend making a full backup, both a pg_dump *and* a 
file-system level copy of the datadir. Personally I'd then do a test 
restore of the pg_dump backup on a separate Pg instance and if it looked 
OK I'd re-initdb then reload from the dump.


--
Craig Ringer

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

[GENERAL] plpgsql: type of array cells

[Amit Dor-Shifer]

I'm trying to use an array of objects in plpgsql (postgresql 8.4):

drop type if exists test_t cascade;

create type test_t AS
(
       i integer,
       s text
);

create or replace function test2()
       RETURNS SETOF test_t AS
$$
DECLARE
arr test_t ARRAY[3];
tmp test_t;
BEGIN
       FOR i in 1 .. 3
       LOOP
               -- ok. Can write to test_t.i
               tmp.i:=i;
               -- ok. Can assign a cell from arr to a test_t object
               arr[i]:=tmp;
               -- error:
               arr[i].i=3;
               RETURN NEXT tmp;
       END LOOP;
END;
$$
LANGUAGE plpgsql;

I'm getting an error when attempting to interpret this function:

NOTICE:  drop cascades to function test2()
ERROR:  syntax error at or near "."
LINE 21:   arr[i].i=3;
                ^

** Error **

ERROR: syntax error at or near "."
SQL state: 42601
Character: 272

Isn't arr[i] of type test_t??

Thanks,
Amit

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

[GENERAL] plpgsql: type of array cells

[Amit Dor-Shifer]

I'm trying to use an array of objects in plpgsql (postgresql 8.4):

drop type if exists test_t cascade;

create type test_t AS
(
i integer,
s text
);

create or replace function test2()
RETURNS SETOF test_t AS
$$
DECLARE
arr test_t ARRAY[3];
tmp test_t;
BEGIN
FOR i in 1 .. 3
LOOP
-- ok. Can write to test_t.i
tmp.i:=i;
-- ok. Can assign a cell from arr to a test_t object
arr[i]:=tmp;
-- error:
arr[i].i=3;
RETURN NEXT tmp;
END LOOP;
END;
$$
LANGUAGE plpgsql;

I'm getting an error when attempting to interpret this function:

NOTICE:  drop cascades to function test2()
ERROR:  syntax error at or near "."
LINE 21:   arr[i].i=3;
 ^

** Error **

ERROR: syntax error at or near "."
SQL state: 42601
Character: 272

Isn't arr[i] of type test_t??

Thanks,
Amit

-- 
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] Create Extension search path

[Tom Lane]

Roger Niederland  writes:
> On my windows install of postgres 9.1.0.
> I have a search_path set in the config file.  This search path has 
> several schemas defined.
> Some of the databases within postgres, do not have the schema specified 
> on the search path defined within the database.

> Trying to add pgcryto via:
> CREATE EXTENSION pgcrypto;
> or
> CREATE EXTENSION pgcrypto SCHEMA public;
> Fail with the error invalid value for parameter "search_path".

> To add the extension required me to change the search_path.

I've committed a fix for this.  Thanks for the report!

(As pointed out in the thread, there are probably better ways to do what
you're doing with the search path, but in any case it's not real
desirable for CREATE EXTENSION to fail if the current setting is wonky.)

regards, tom lane

-- 
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] Selecting All Columns Associated With Maximum Value of One Column

[Chris Curvey]

On Wed, Oct 5, 2011 at 7:34 PM, Rich Shepard wrote:

>  A table (chemistry) has columns named site_id, sample_date, param, quant,
> and str_name (among other columns). I want to find the site_id,
> sample_date,
> and quant for a specific str_name and param. I cannot get the proper syntax
> in the SELECT statement.
>
>  My attempts are variations of,
>
> SELECT max(quant), param, site_id, sample_date, str_name from chemistry
> WHERE param = 'TDS' AND str_name = 'BurrowCrk';
>
> which prompts postgres to tell me,
>
> ERROR:  column "chemistry.param" must appear in the GROUP BY clause or be
> used in an aggregate function
>
>  I suspect that retrieving these data requires nested SELECT statements,
> and I'd appreciate learning how to retrive such data.
>
> Rich
>


Based on your subject line, I'm guessing that you want something like this:

select quant, param, site_id, sample_date, str_name
from chemistry
where param = 'TDS' and str_name = 'BurrowCrk'
and quant = (select max(quant) from chemistry where param = 'TDS' and
str_name = 'BurrowCrk')

Re: [GENERAL] I/O error on data file, can't run backup

[Steve Crawford]

On 10/05/2011 03:43 PM, Leif Biberg Kristensen wrote:

On Thursday 6. October 2011 00.17.38 Steve Crawford wrote:

I'm thinking perhaps a funky memory problem - you are having odd crashes
after all.

I've been thinking about the memory myself, but it passes memtest86plus with
flying colors. Or at least it did the last time I checked which is a few months
ago.
I have had two machines pass extensive memtest86plus but fail on heavy 
pgbench testing and in both cases the cause was ultimately traced to bad 
memory.


Cheers,
Steve


--
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] Problem with pg_upgrade 9.0 -> 9.1

[Bruce Momjian]

Thomas Kellerer wrote:
> Thomas Kellerer, 17.09.2011 12:32:
> > I was trying to upgrade my Postgres 9.0 installation using pg_upgrade. 
> > Running it first with --check revealed no problems.
> >
> > The when I did the actual migration, the following happened:
> >
> >
> > Mismatch of relation id: database "dellstore", old relid 83613, new relid 
> > 16530
> > Failure, exiting
> >
> 
> I now got the same error (alas with a different relation id) while
> migrating a completely different data directory.
> 
> Anything I can do to help find the reason for this problem (or bug?)
> 
> Unfortuantely the data contains some confidential information so I
> cannot make it available.

This bug was fixed just after 9.1.1 was released.  The bug is that
Windows doesn't properly pass the right flags for the oid set functions
to operate.  If you can compile the git 9.1.X current, the fix is in
there;  the fix will be in 9.1.2.


--
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] Selecting All Columns Associated With Maximum Value of One Column

[David Johnston]

On Oct 5, 2011, at 19:34, Rich Shepard  wrote:

>  A table (chemistry) has columns named site_id, sample_date, param, quant,
> and str_name (among other columns). I want to find the site_id, sample_date,
> and quant for a specific str_name and param. I cannot get the proper syntax
> in the SELECT statement.
> 
>  My attempts are variations of,
> 
> SELECT max(quant), param, site_id, sample_date, str_name from chemistry
> WHERE param = 'TDS' AND str_name = 'BurrowCrk';
> 
> which prompts postgres to tell me,
> 
> ERROR:  column "chemistry.param" must appear in the GROUP BY clause or be
> used in an aggregate function
> 
>  I suspect that retrieving these data requires nested SELECT statements,
> and I'd appreciate learning how to retrive such data.
> 
> Rich

"Max" is an aggregate function and thus requires one of:
1) GROUP BY
2) "Window" -  max(quant) OVER (PARTITION BY ...)

To be present in the query.

A correlated sub-select would work but you would still need to use group by and 
you would not gain anything in this particular scenario.

They each have their own usage scenarios and your description is not sufficient 
to determine which one you need; but likely adding an appropriate GROUP BY 
clause will get you what you want.

Also, while the warning only specifies the param field all the other field will 
give you the same error if you use the GROUP BY claus.  The Window syntax 
affects just the aggregate function and so only that single "field" would need 
to be modified BUT the window clause results in all records remaining in the 
final query whereas the GROUP BY clause effectively removes duplicates.

David J.




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

[GENERAL] Selecting All Columns Associated With Maximum Value of One Column

[Rich Shepard]

  A table (chemistry) has columns named site_id, sample_date, param, quant,
and str_name (among other columns). I want to find the site_id, sample_date,
and quant for a specific str_name and param. I cannot get the proper syntax
in the SELECT statement.

  My attempts are variations of,

SELECT max(quant), param, site_id, sample_date, str_name from chemistry
WHERE param = 'TDS' AND str_name = 'BurrowCrk';

which prompts postgres to tell me,

ERROR:  column "chemistry.param" must appear in the GROUP BY clause or be
used in an aggregate function

  I suspect that retrieving these data requires nested SELECT statements,
and I'd appreciate learning how to retrive such data.

Rich


--
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] I/O error on data file, can't run backup

[Leif Biberg Kristensen]

On Thursday 6. October 2011 00.17.38 Steve Crawford wrote:
> I'm thinking perhaps a funky memory problem - you are having odd crashes
> after all.

I've been thinking about the memory myself, but it passes memtest86plus with 
flying colors. Or at least it did the last time I checked which is a few months 
ago.

The problems got a lot better after I replaced a monster Radeon XFX video card 
with a very basic fanless NVidia card (with the added bonus that I can now 
actually watch Flash videos in full screen), which may point to overheating 
issues.

In other news: I discovered that injecting `date +%u` into the backup file name 
at an appropriate place will number it by weekday, which is great for keeping 
daily backups for a week.

regards, Leif.

-- 
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] gaps/overlaps in a time table : current and previous row question

[Phil Couling]

I think you need to get the full list of change dates first. Assuming
you're searching over a time period between "period_from" and
"period_to":

SELECT change_time, sum(diff) as total_diff FROM (
SELECT starttime as change_time, 1 AS diff FROM t WHERE starttime >
period_from AND endtime < period_to
UNION ALL
SELECT endtime as change_time, -1 AS diff FROM t WHERE endtime >
period_from AND endtime < period_to
) a
GROUP BY change_time
HAVING sum(diff) <> 0
ORDER BY change_time asc

I used this in a pgplsql function to produce a very simular result to
what you were looking for.  You need to start by finding how many time
periods overlapped period_from, then accumulatively add on
"total_diff" for each row you process.

Hope this helps.

2011/10/5 Filip Rembiałkowski :
>
>
> 2011/10/5 thomas veymont 
>>
>> hello,
>>
>> let's say that each rows in a table contains a start time and a end
>> time ("timeinterval" type),
>
> there is no such type ( no result for select * from pg_type where typname ~
> 'timeinterval' ).
> can you show exact table structure (output of psql "\d" or better, CREATE
> TABLE command)?
>
>
>> but the index are not ordered nor consecutive, e.g :
>>
>> $ select * from T order by starttime
>>
>> index  | starttime    |   endtime
>> -+-+-
>> 3        |   t1             |  t2
>> 1        |   t3             |  t4
>> 18      |   t5             |  t6
>> 12      |   t7             |  t8
>>
>> I want a result that shows time gaps and overlaps in this table, that is :
>>
>> delta
>> -+
>> t3 - t2 |
>> t5 - t4 |
>> t7 - t6 |
>>
>> how would I do that ? I guess this could be done with window function and
>> lag()
>> function but I don't know exactly how. Any suggestion ?
>>
>
>
>  -- assuming that you actually want lag compared to previous starttime - try
> this:
> select index, starttime, endtime, starttime - lag(endtime) over(order by
> starttime asc) as delta from test;
>
>
> PS. this question should probably go to "pgslq-sql mailing list more than
> "pgsql-general".  also please give more details next time. Thanks.
>
>

-- 
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] I/O error on data file, can't run backup

[Steve Crawford]

On 10/05/2011 02:48 PM, Leif Biberg Kristensen wrote:


I had a hang on the machine a few hours earlier that required a power-off
reboot. That has been a problem with this rig since I built it about a year
ago, it's probably a funky connection somewhere. This may be the direct cause
of the I/O error, which also may mean that the disk is not to blame.

I'm so used to postgres and everything else coming up without a hiccup after a
power-off that I don't usually pay much attention to it

PostgreSQL is great, but it can't overcome defective hardware.

I'm thinking perhaps a funky memory problem - you are having odd crashes 
after all.


If memory is failing you could have a file that is corrupted not on disk 
but in the cache. Perhaps in the process of stopping and starting 
PostgreSQL, the data that was causing the trouble got flushed from cache 
then reread from disk. You may find this story interesting:

http://blogs.oracle.com/ksplice/entry/attack_of_the_cosmic_rays1

Cheers,
Steve


--
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] I/O error on data file, can't run backup

[Leif Biberg Kristensen]

On Wednesday 5. October 2011 22.41.49 Tom Lane wrote:
> Leif Biberg Kristensen  writes:

> > I'm gonna move the data to another disk right now.
> 
> Good plan.

Couple of things I forgot to mention, in case it matters:

The disk is a 1 TB Seagate Barracuda S-ATA, and it has been in use for about a 
year. I've been using this brand since way back around 1998 without any 
problems, but have never used any disk more than 3 years. The file system is 
ext3.

I had a hang on the machine a few hours earlier that required a power-off 
reboot. That has been a problem with this rig since I built it about a year 
ago, it's probably a funky connection somewhere. This may be the direct cause 
of the I/O error, which also may mean that the disk is not to blame.

I'm so used to postgres and everything else coming up without a hiccup after a 
power-off that I don't usually pay much attention to it. But I'm certainly 
going to rework my backup strategy, and keep several generations.

regards, Leif

-- 
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] I/O error on data file, can't run backup

[Tom Lane]

Leif Biberg Kristensen  writes:
> I seemingly fixed the problem by stopping postgres and doing:
> balapapa 612249 # mv 11658 11658.old
> balapapa 612249 # mv 11658.old 11658

> And the backup magically works.

Wow, that is magic.  I was going to suggest copying pg_opfamily from
template0, which would probably work (maybe requiring reindexing) as
long as you didn't have any non-core data types in use.  But you
got lucky.

> I'm gonna move the data to another disk right now.

Good plan.

regards, tom lane

-- 
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] Analytic type functionality, matching patters in a column then increment an integer

[Henry Drexler]

that was spot on Richard.  Thank you for your time and the solution.

On Wed, Oct 5, 2011 at 3:22 PM, Richard Huxton  wrote:

> On 05/10/11 19:29, Henry Drexler wrote:
>
>>
>> and would like to have a column indicate like this:
>>
>> 'evaluation' 'indicator'
>> tf 1
>> tt 1
>> ft  1
>> ff
>> ff
>> tf  2
>> ft  2
>> tf  3
>> tt  3
>> ft  3
>> ff
>>
>
> SELECT id,evaluation,sum(case when evaluation='tf' then 1 else 0 end) over
> (order by id) FROM tfcount ORDER BY id;
>
> id | evaluation | sum
> ++-
>  1 | tf |   1
>  2 | tt |   1
>  3 | ft |   1
>  4 | ff |   1
>  5 | ff |   1
>  6 | tf |   2
>  7 | ft |   2
>  8 | tf |   3
>  9 | tt |   3
>  10 | ft |   3
>  11 | ff |   3
> (11 rows)
>
> OK, so that's almost it, but you'd like "ff" to be null. You probably can
> do it with a suitably nested CASE, but it's probably clearer as a sub-query.
>
> SELECT
>  id,
>  evaluation,
>  CASE WHEN evaluation='ff' THEN null::int
>  ELSE sum::int END AS section_num
> FROM (
>  SELECT
>id,
>evaluation,
>sum(case when evaluation='tf' then 1 else 0 end) over (order by id)
>  FROM tfcount
> ) AS rows
> ORDER BY id;
>
> HTH
>
> P.S. - I always find the windowing function syntax confusing, but it's as
> the standards define I believe.
>
> --
>  Richard Huxton
>  Archonet Ltd
>

Re: [GENERAL] Analytic type functionality, matching patters in a column then increment an integer

[Richard Huxton]

On 05/10/11 19:29, Henry Drexler wrote:


and would like to have a column indicate like this:

'evaluation' 'indicator'
tf 1
tt 1
ft  1
ff
ff
tf  2
ft  2
tf  3
tt  3
ft  3
ff


SELECT id,evaluation,sum(case when evaluation='tf' then 1 else 0 end) 
over (order by id) FROM tfcount ORDER BY id;


id | evaluation | sum
++-
  1 | tf |   1
  2 | tt |   1
  3 | ft |   1
  4 | ff |   1
  5 | ff |   1
  6 | tf |   2
  7 | ft |   2
  8 | tf |   3
  9 | tt |   3
 10 | ft |   3
 11 | ff |   3
(11 rows)

OK, so that's almost it, but you'd like "ff" to be null. You probably 
can do it with a suitably nested CASE, but it's probably clearer as a 
sub-query.


SELECT
  id,
  evaluation,
  CASE WHEN evaluation='ff' THEN null::int
  ELSE sum::int END AS section_num
FROM (
  SELECT
id,
evaluation,
sum(case when evaluation='tf' then 1 else 0 end) over (order by id)
  FROM tfcount
) AS rows
ORDER BY id;

HTH

P.S. - I always find the windowing function syntax confusing, but it's 
as the standards define I believe.


--
  Richard Huxton
  Archonet Ltd

--
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] I/O error on data file, can't run backup

[Leif Biberg Kristensen]

I seemingly fixed the problem by stopping postgres and doing:

balapapa 612249 # mv 11658 11658.old
balapapa 612249 # mv 11658.old 11658

And the backup magically works.

I'm gonna move the data to another disk right now.

regards, Leif

-- 
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] I/O error on data file, can't run backup

[Leif Biberg Kristensen]

On Wednesday 5. October 2011 20.42.00 Tom Lane wrote:
> Postgres can't magically resurrect data that your drive lost, if that's
> what you were hoping for.  However, you might be in luck, because that
> file is probably just an index and not original data.  Try this:
> 
>   select relname from pg_class where relfilenode = 11658;
> 
> On my 9.0 installation I get "pg_opclass_am_name_nsp_index".  If you get
> the same (or any other index for that matter) just reindex that index
> and you'll be all right ... or at least, you will be if that's the only
> file your drive has lost.

Tom,
this is what I get:

postgres@balapapa ~ $ psql pgslekt
psql (9.0.5)
Type "help" for help.

pgslekt=# select relname from pg_class where relfilenode = 11658;
   relname   
-
 pg_opfamily
(1 row)

regards, Leif

-- 
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] I/O error on data file, can't run backup

[Tom Lane]

Leif Biberg Kristensen  writes:
> Running postgresql 9.0.5 on 
> balapapa ~ # uname -a
> Linux balapapa 2.6.39-gentoo-r3 #1 SMP Sun Jul 17 11:22:15 CEST 2011 x86_64 
> Intel(R) Core(TM) i7 CPU 930 @ 2.80GHz GenuineIntel GNU/Linux

> I'm trying to run pg_dump on my database, and get an error:

> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR:  could not read block 1 in file 
> "base/612249/11658": Inn/ut-feil
> pg_dump: The command was: SELECT tableoid, oid, opfname, opfnamespace, 
> (SELECT 
> rolname FROM pg_catalog.pg_roles WHERE oid = opfowner) AS rolname FROM 
> pg_opfamily

> I have tried to stop postgresql and take a filesystem backup of the data 
> directory with a cp -ax, but it crashes on the same file.

You have a disk failure on some sector of that file, apparently.  I'd be
thinking about replacing that disk drive if I were you.  Once it starts
showing uncorrectable errors the MTTF is going to be short.

> The backup script overwrote my previous backup with a 40 byte file (yes silly
> me I know that's bloody stupid - I'm gonna fix that) and now I haven't got a 
> recent backup anymore.

> Is this fixable?

Postgres can't magically resurrect data that your drive lost, if that's
what you were hoping for.  However, you might be in luck, because that
file is probably just an index and not original data.  Try this:

select relname from pg_class where relfilenode = 11658;

On my 9.0 installation I get "pg_opclass_am_name_nsp_index".  If you get
the same (or any other index for that matter) just reindex that index
and you'll be all right ... or at least, you will be if that's the only
file your drive has lost.

regards, tom lane

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

[GENERAL] Analytic type functionality, matching patters in a column then increment an integer

[Henry Drexler]

I can do this in excel with vba, though due to the volume of data that is
now impracticable and I am trying to move most of my logic into the query
and db for analysis.

Looking at the analytic functions I see no way to carry values over the way
they need to be.

Example column:

I have a column that evaluates to either tf,tt,ft,ff (true false matches).
tf means the start of a section,
tt mans intermediary but in the good section
ft means the end of the section that needs to be marked.
ff is just ignored

so I can have
'evaluation'
tf
tt
ft
ff
ff
tf
ft

and would like to have a column indicate like this:

'evaluation''indicator'
tf 1
tt 1
ft  1
ff
ff
tf  2
ft  2
tf  3
tt  3
ft  3
ff


I have tried rank() and some case statements though I can quite seem to get
the sql to be aware across rows as shown in the desired indicator column
noted above.

It seems like I am missing something that would we aware like that.  Any
ideas?  Thanks in advance.

[GENERAL] I/O error on data file, can't run backup

[Leif Biberg Kristensen]

Running postgresql 9.0.5 on 

balapapa ~ # uname -a
Linux balapapa 2.6.39-gentoo-r3 #1 SMP Sun Jul 17 11:22:15 CEST 2011 x86_64 
Intel(R) Core(TM) i7 CPU 930 @ 2.80GHz GenuineIntel GNU/Linux

I'm trying to run pg_dump on my database, and get an error:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  could not read block 1 in file 
"base/612249/11658": Inn/ut-feil
pg_dump: The command was: SELECT tableoid, oid, opfname, opfnamespace, (SELECT 
rolname FROM pg_catalog.pg_roles WHERE oid = opfowner) AS rolname FROM 
pg_opfamily

I have tried to stop postgresql and take a filesystem backup of the data 
directory with a cp -ax, but it crashes on the same file. I've looked at the 
directory with ls -l, and the file looks pretty normal to me. I've also 
rebooted from a live CD and run fsck on my /var partition, and it doesn't find 
any problem.

The database is still working perfectly.

The backup script overwrote my previous backup with a 40 byte file (yes silly 
me I know that's bloody stupid - I'm gonna fix that) and now I haven't got a 
recent backup anymore.

Is this fixable?

regards, Leif

-- 
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] null values in a view

[Lauri Kajan]

This worked.
Thank you all!

I know the casting is quite basic operation but could this be added to
the CREATE VIEW documentation? Now there is only an example:
CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
The same syntax won't work with other type as Tom wrote.


-Lauri

On Wed, Oct 5, 2011 at 5:39 PM, Tom Lane  wrote:
> Lauri Kajan  writes:
>> This works with other values but not with nulls:
>
>> CREATE VIEW view1 AS
>> SELECT
>>   attribute1 as a1,
>>   text null as a2,
>>   text 'test' as a3
>> FROM
>>   table;
>
> FYI, the syntax  typename 'literal'  works *only* with string literals,
> not anything else.  For any other target you have to write
> CAST(value AS typename)  or equivalently  value::typename.
> CAST is SQL-standard, :: is a Postgres-ism.
>
>                        regards, tom lane
>

-- 
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 create database link and synonym in postgresql 9.0

[Filip Rembiałkowski]

There is no CREATE SYNONYM in PostgreSQL and it's not planned for
implementation.
There is also no direct support for foreign data wrapper. But it's planned
for 9.2.

Nearest  that you can get with PostgreSQL 9.0 is cautious use of dblink and
views and rules.

here's a sample script to show what I mean:



create database db1;
create database db2;

\c db2;
create table emp ( id integer primary key, name text);
insert into emp values (1,'Joe'), (2,'Mary');

\c db1
\i /usr/share/postgresql/9.0/contrib/dblink.sql

SELECT dblink_connect( 'db2', 'dbname=db2' );

-- remote SELECT;
CREATE VIEW emp AS SELECT id,name FROM dblink('db2','SELECT id,name FROM
emp') AS emp(id integer, name text);

SELECT * FROM emp ORDER BY name;

-- remote INSERT:
CREATE RULE emp_ins AS ON INSERT TO emp DO INSTEAD
SELECT dblink_exec( 'db2',
'INSERT INTO emp(id,name) VALUES(' || quote_nullable(NEW.id) || ',' ||
quote_nullable(NEW.name) || ')', true );

INSERT INTO emp VALUES(3,'Phil');

-- remote UPDATE:
CREATE RULE emp_upd AS ON UPDATE TO emp DO INSTEAD
SELECT dblink_exec( 'db2',
'UPDATE emp SET id=' || quote_nullable(NEW.id) || ', name=' ||
quote_nullable(NEW.name)
|| ' WHERE id=' || quote_nullable(OLD.id), true );

UPDATE emp SET name = 'Philip' WHERE id = 3;




2011/10/5 tushar nehete 

> Hi,
> In one of the migration porject  want to access and update some tables from
>
> other database on same postgresql server.
>
> The question is how to access tables from other database on the same
> postgres server.
> If it is by database link like Oracle then what is the syntax.
> And how to create synonym?
>
> Say i have a postgres server on RHEL5 and it has 2 databases db1 and db2.
> db2 has table emp which i want to access from db1.
> so i create a dblink in oracle named dblnk_emp and access the table in db1
> by
> select * from emp@dblnk_emp;
> so i create synonym for emp@dblnk_emp as emp in db1.
>
>
> In postgres I can access table from other database by dblink but cannot
> update it. Also there is
> support for synonym.
>
> Please help..
>
>
>

Re: [GENERAL] Restoring 2 Tables From All Databases Backup

[Raghavendra]

Hi Adarsh,

Filip workaround is right approach, since its plain text format you need to
play with SED/AWK to pull those two tables.

Following link will help you:-

http://blog.endpoint.com/2010/04/restoring-individual-table-data-from.html

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/



2011/10/5 pasman pasmański 

> You should to create new database with two empty tables, set access
> rights for all schemas readonly and pipe backup to this database.
>
> 2011/10/5, Dickson S. Guedes :
> > 2011/10/5 Adarsh Sharma :
> >> About 1 month ago, I take a complete databases backup of my Database
> >> server
> >> through pg_dumpall command.
> >> Today I need to extract or restore only 2 tables in a database.
> >>
> >> Is it possible or I have to restore complete Databases again. Size of
> >> backup
> >> is 10 GB in .sql.gz format.
> >
> > If your dump was created using custom format [1] (pg_dump
> > --format=custom or -Fc)  you can do a pg_restore using --use-list and
> > --list [2].
> >
> > [1]
> >
> http://www.postgresql.org/docs/current/interactive/app-pgdump.html#PG-DUMP-OPTIONS
> > [2]
> >
> http://www.postgresql.org/docs/current/interactive/app-pgrestore.html#APP-PGRESTORE-OPTIONS
> >
> > --
> > Dickson S. Guedes
> > mail/xmpp: gue...@guedesoft.net - skype: guediz
> > http://guedesoft.net - http://www.postgresql.org.br
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
>
>
> --
> 
> pasman
>
> --
> 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] function "XXX" already exists with same argument types

[Alexander Farber]

Thank you Michael, but no -

On Wed, Oct 5, 2011 at 5:24 PM, Michael Glaesemann  wrote:
>> psql:pref-2011-10-05-a.sql:339: ERROR:  function "pref_update_match"
>> already exists with same argument types
>> ALTER FUNCTION
>
> Likely someone mistakenly added the functions to template1 of the machine 
> you're restoring onto and they're getting added to the new database when it's 
> created.

# psql -U postgres -W template1
Password for user postgres:
psql (8.4.7)
Type "help" for help.

template1=# \df
   List of functions
 Schema | Name | Result data type | Argument data types | Type
+--+--+-+--
(0 rows)

-- 
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] function "XXX" already exists with same argument types

[Michael Glaesemann]

On Oct 5, 2011, at 11:21, Alexander Farber wrote:

> Hello,
> 
> I use PostgreSQL 8.4 under CentOS 5.7:
> 
> #  rpm -qa | grep post
> compat-postgresql-libs-4-1PGDG.rhel5
> postgresql-8.4.9-1PGDG.rhel5
> postgresql-server-8.4.9-1PGDG.rhel5
> compat-postgresql-libs-4-1PGDG.rhel5
> postgresql-libs-8.4.9-1PGDG.rhel5
> postgresql-devel-8.4.9-1PGDG.rhel5
> 
> And perform nightly backups with this cronjob:
> 
> 1   1*   *   *pg_dump $PGDATABASE | gzip -c >
> $HOME/backups/pref-`date +\%F`.sql.gz
> 
> I also restored them on the same and another
> machines (dev. VMs) often and w/o problems.
> 
> Now I'm trying to migrate to another machine
> and CentOS 6 and suddenly emits the warnings:
> 
> # rpm -qa|grep post
> postgresql-libs-8.4.7-2.el6.x86_64
> postgresql-devel-8.4.7-2.el6.x86_64
> postgresql-8.4.7-2.el6.x86_64
> postgresql-docs-8.4.7-2.el6.x86_64
> postgresql-server-8.4.7-2.el6.x86_64
> 
> pref# \i pref-2011-10-05-a.sql
> SET
> SET
> SET
> SET
> SET
> SET
> psql:pref-2011-10-05-a.sql:16: ERROR:  language "plpgsql" already exists
> ALTER LANGUAGE
> SET
> CREATE DOMAIN
> ALTER DOMAIN
> 
> CREATE FUNCTION
> ALTER FUNCTION
> CREATE FUNCTION
> ALTER FUNCTION
> psql:pref-2011-10-05-a.sql:195: ERROR:  function "pref_update_catch"
> already exists with same argument types
> ALTER FUNCTION
> psql:pref-2011-10-05-a.sql:236: ERROR:  function "pref_update_game"
> already exists with same argument types
> ALTER FUNCTION
> psql:pref-2011-10-05-a.sql:256: ERROR:  function "pref_update_hand"
> already exists with same argument types
> ALTER FUNCTION
> psql:pref-2011-10-05-a.sql:288: ERROR:  function "pref_update_luck"
> already exists with same argument types
> ALTER FUNCTION
> CREATE FUNCTION
> ALTER FUNCTION
> psql:pref-2011-10-05-a.sql:339: ERROR:  function "pref_update_match"
> already exists with same argument types
> ALTER FUNCTION
> 
> The 1st waring is ok, as I've run "create language plpgsql" before.
> 
> But why do I get the function warings?

Likely someone mistakenly added the functions to template1 of the machine 
you're restoring onto and they're getting added to the new database when it's 
created.

Michael Glaesemann
grzm seespotcode net




-- 
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] Restoring 2 Tables From All Databases Backup

[pasman pasmański]

You should to create new database with two empty tables, set access
rights for all schemas readonly and pipe backup to this database.

2011/10/5, Dickson S. Guedes :
> 2011/10/5 Adarsh Sharma :
>> About 1 month ago, I take a complete databases backup of my Database
>> server
>> through pg_dumpall command.
>> Today I need to extract or restore only 2 tables in a database.
>>
>> Is it possible or I have to restore complete Databases again. Size of
>> backup
>> is 10 GB in .sql.gz format.
>
> If your dump was created using custom format [1] (pg_dump
> --format=custom or -Fc)  you can do a pg_restore using --use-list and
> --list [2].
>
> [1]
> http://www.postgresql.org/docs/current/interactive/app-pgdump.html#PG-DUMP-OPTIONS
> [2]
> http://www.postgresql.org/docs/current/interactive/app-pgrestore.html#APP-PGRESTORE-OPTIONS
>
> --
> Dickson S. Guedes
> mail/xmpp: gue...@guedesoft.net - skype: guediz
> http://guedesoft.net - http://www.postgresql.org.br
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


-- 

pasman

-- 
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 "XXX" already exists with same argument types

[Alexander Farber]

Hello,

I use PostgreSQL 8.4 under CentOS 5.7:

#  rpm -qa | grep post
compat-postgresql-libs-4-1PGDG.rhel5
postgresql-8.4.9-1PGDG.rhel5
postgresql-server-8.4.9-1PGDG.rhel5
compat-postgresql-libs-4-1PGDG.rhel5
postgresql-libs-8.4.9-1PGDG.rhel5
postgresql-devel-8.4.9-1PGDG.rhel5

And perform nightly backups with this cronjob:

1   1*   *   *pg_dump $PGDATABASE | gzip -c >
$HOME/backups/pref-`date +\%F`.sql.gz

I also restored them on the same and another
machines (dev. VMs) often and w/o problems.

Now I'm trying to migrate to another machine
and CentOS 6 and suddenly emits the warnings:

# rpm -qa|grep post
postgresql-libs-8.4.7-2.el6.x86_64
postgresql-devel-8.4.7-2.el6.x86_64
postgresql-8.4.7-2.el6.x86_64
postgresql-docs-8.4.7-2.el6.x86_64
postgresql-server-8.4.7-2.el6.x86_64

pref# \i pref-2011-10-05-a.sql
SET
SET
SET
SET
SET
SET
psql:pref-2011-10-05-a.sql:16: ERROR:  language "plpgsql" already exists
ALTER LANGUAGE
SET
CREATE DOMAIN
ALTER DOMAIN

CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
psql:pref-2011-10-05-a.sql:195: ERROR:  function "pref_update_catch"
already exists with same argument types
ALTER FUNCTION
psql:pref-2011-10-05-a.sql:236: ERROR:  function "pref_update_game"
already exists with same argument types
ALTER FUNCTION
psql:pref-2011-10-05-a.sql:256: ERROR:  function "pref_update_hand"
already exists with same argument types
ALTER FUNCTION
psql:pref-2011-10-05-a.sql:288: ERROR:  function "pref_update_luck"
already exists with same argument types
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
psql:pref-2011-10-05-a.sql:339: ERROR:  function "pref_update_match"
already exists with same argument types
ALTER FUNCTION

The 1st waring is ok, as I've run "create language plpgsql" before.

But why do I get the function warings?

In my original database I don't see any duplicates with \df or \df+

# psql
psql (8.4.9)
Type "help" for help.

pref=> \df pref_update_catch

List of functions
 Schema |   Name| Result data type |
  Argument data
types
|  Type
+---+--+


+
 public | pref_update_catch | void | _id character varying, _trix0 i
nteger, _trix1 integer, _trix2 integer, _trix3 integer, _trix4 integer, _trix5 i
nteger, _trix6 integer, _trix7 integer, _trix8 integer, _trix9 integer, _trix10
integer, _trust integer | normal
(1 row)

Same picture in my target database on CentOS 6.0
(it has 8.4.7 and not 8.4.9 though)

Any ideas what is happening please?

Regards
Alex

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

[GENERAL] How to create database link and synonym in postgresql 9.0

[tushar nehete]

Hi,
In one of the migration porject  want to access and update some tables from
other database on same postgresql server.

The question is how to access tables from other database on the same
postgres server.
If it is by database link like Oracle then what is the syntax.
And how to create synonym?

Say i have a postgres server on RHEL5 and it has 2 databases db1 and db2.
db2 has table emp which i want to access from db1.
so i create a dblink in oracle named dblnk_emp and access the table in db1
by
select * from emp@dblnk_emp;
so i create synonym for emp@dblnk_emp as emp in db1.


In postgres I can access table from other database by dblink but cannot
update it. Also there is
support for synonym.

Please help..

Re: [GENERAL] Restoring 2 Tables From All Databases Backup

[Filip Rembiałkowski]

2011/10/5 Adarsh Sharma 

> Dear all,
>
> About 1 month ago, I take a complete databases backup of my Database server
> through pg_dumpall command.
> Today I need to extract or restore only 2 tables in a database.
>
> Is it possible or I have to restore complete Databases again. Size of
> backup is 10 GB in .sql.gz format.
>
> Please let me know how to extract the tables from this 10Gb backup file
>
>

since this is a plaintext file, not a custom format backup,
you unfortunately need to extract portions of text using some editor or
program...

for this kind of work I would recommend Perl or Awk.

below is my "first shot" - thats incomplete (no restoration of
indexes/sequences):

gunzip -cd all.sql.gz | awk '/^CREATE TABLE mytable /,/^$/ { print }; /^COPY
mytable /,/^$/ { print };'

which does print all lines from CREATE TABLE mytable to next empty line, and
all lines from COPY mytable to next empty line.

Re: [GENERAL] how to disable all pkey/fkey constraints globally

[salah jubeh]

Create a temp table that will store all the foreign kez constraints 
then create a function that add an entry to this table 
then write a plpgsql function that reads the constraints and disable them 
write another function to read the constraints from the temporary tables and 
create them again 


have a look on pg_constraints , pg_tables 
 
regards




From: Joe Abbate 
To: pgsql-general@postgresql.org
Sent: Wednesday, October 5, 2011 11:27 AM
Subject: Re: [GENERAL] how to disable all pkey/fkey constraints globally

On 10/05/2011 04:49 AM, depst...@alliedtesting.com wrote:
>> -Original Message-
>> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
>> ow...@postgresql.org] On Behalf Of J.V.
>> Sent: Tuesday, October 04, 2011 10:00 PM
>> To: pgsql-general
>> Subject: [GENERAL] how to disable all pkey/fkey constraints globally
>>
>> Is there a generic way to drop just all primary key and foreign key 
>> constraints
>> on a given table?
>>
>> I know how to do given the specific name of the constraint.
>>
>> same question but one statement that would just disable all primary key and
>> foreign key constraints on a given database?
>>
>> and am assuming the reverse could not be done because would have to re-
>> create each one individually?
>>
>> Maybe I do not want to drop, so is there a way to simply disable all globally
>> (not drop) & then enable all globally?
>>
> 
> You can find all foreign key constraints for a given table, save 
> constraint definitions, drop constraints, and later re-enable them.
> Look into table pg_constraint and function pg_get_constraintdef.

If you'll allow me to toot my horn, here's an alternative:

- Use dbtoyaml [1] to output your tables to a file, say, yaml1
- Edit the yaml1 file, searching for primary_key and foreign_keys and
remove those you want to drop, save the result to a different file, say,
yaml2
- Use yamltodb [2] with yaml2 to generate SQL to drop the primary keys
and foreign keys, in the correct dependency order (at least that's what
it's supposed to do, make sure you use the -1 option), to a file, say, sql1
- Run sql1 through psql to drop the constraints
- Use yamltodb with yaml1 to generate SQL to recreate the primary keys
and foreign keys to, say sql2
- Run sql2 through psql to recreate the constraints

Regards,


Joe

[1] http://www.pyrseas.org/docs/dbtoyaml.html
[2] http://www.pyrseas.org/docs/yamltodb.html

-- 
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] null values in a view

[Tom Lane]

Lauri Kajan  writes:
> This works with other values but not with nulls:

> CREATE VIEW view1 AS
> SELECT
>   attribute1 as a1,
>   text null as a2,
>   text 'test' as a3
> FROM
>   table;

FYI, the syntax  typename 'literal'  works *only* with string literals,
not anything else.  For any other target you have to write
CAST(value AS typename)  or equivalently  value::typename.
CAST is SQL-standard, :: is a Postgres-ism.

regards, tom lane

-- 
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] Restoring 2 Tables From All Databases Backup

[Dickson S. Guedes]

2011/10/5 Adarsh Sharma :
> About 1 month ago, I take a complete databases backup of my Database server
> through pg_dumpall command.
> Today I need to extract or restore only 2 tables in a database.
>
> Is it possible or I have to restore complete Databases again. Size of backup
> is 10 GB in .sql.gz format.

If your dump was created using custom format [1] (pg_dump
--format=custom or -Fc)  you can do a pg_restore using --use-list and
--list [2].

[1] 
http://www.postgresql.org/docs/current/interactive/app-pgdump.html#PG-DUMP-OPTIONS
[2] 
http://www.postgresql.org/docs/current/interactive/app-pgrestore.html#APP-PGRESTORE-OPTIONS

-- 
Dickson S. Guedes
mail/xmpp: gue...@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br

-- 
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] null values in a view

[hubert depesz lubaczewski]

On Wed, Oct 05, 2011 at 05:22:21PM +0300, Lauri Kajan wrote:
> Hi all,
> 
> How could I create a view that returns null values among all other values.
> Here is a sample that i want to achieve:
> 
> CREATE VIEW view1 AS
> SELECT
>   attribute1 as a1,
>   null as a2
> FROM
>   table;
> 
> Now the problem is that I got an warning:  column "a2" has type "unknown"
> I know that I should define a data type for a field a2. But how?

using normal cast:
null::text as a2

or cast(null as text);

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.com/

-- 
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] null values in a view

[Achilleas Mantzios]

try:

CREATE OR REPLACE VIEW view1 AS
SELECT
  name as a1,
  null::text as a2,
  'test'::text as a3
FROM
  some_table;

Στις Wednesday 05 October 2011 17:22:21 ο/η Lauri Kajan έγραψε:
> Hi all,
> 
> How could I create a view that returns null values among all other values.
> Here is a sample that i want to achieve:
> 
> CREATE VIEW view1 AS
> SELECT
>   attribute1 as a1,
>   null as a2
> FROM
>   table;
> 
> Now the problem is that I got an warning:  column "a2" has type "unknown"
> I know that I should define a data type for a field a2. But how?
> This works with other values but not with nulls:
> 
> CREATE VIEW view1 AS
> SELECT
>   attribute1 as a1,
>   text null as a2,
>   text 'test' as a3
> FROM
>   table;
> 
> 
> 
> Thanks
> 
> -Lauri Kajan
> 



-- 
Achilleas Mantzios

-- 
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] gaps/overlaps in a time table : current and previous row question

[Filip Rembiałkowski]

2011/10/5 thomas veymont 

> hello,
>
> let's say that each rows in a table contains a start time and a end
> time ("timeinterval" type),
>

there is no such type ( no result for select * from pg_type where typname ~
'timeinterval' ).
can you show exact table structure (output of psql "\d" or better, CREATE
TABLE command)?


but the index are not ordered nor consecutive, e.g :
>
> $ select * from T order by starttime
>
> index  | starttime|   endtime
> -+-+-
> 3|   t1 |  t2
> 1|   t3 |  t4
> 18  |   t5 |  t6
> 12  |   t7 |  t8
>
> I want a result that shows time gaps and overlaps in this table, that is :
>
> delta
> -+
> t3 - t2 |
> t5 - t4 |
> t7 - t6 |
>
> how would I do that ? I guess this could be done with window function and
> lag()
> function but I don't know exactly how. Any suggestion ?
>
>

 -- assuming that you actually want lag compared to previous starttime - try
this:
select index, starttime, endtime, starttime - lag(endtime) over(order by
starttime asc) as delta from test;


PS. this question should probably go to "pgslq-sql mailing list more than
"pgsql-general".  also please give more details next time. Thanks.

[GENERAL] null values in a view

[Lauri Kajan]

Hi all,

How could I create a view that returns null values among all other values.
Here is a sample that i want to achieve:

CREATE VIEW view1 AS
SELECT
  attribute1 as a1,
  null as a2
FROM
  table;

Now the problem is that I got an warning:  column "a2" has type "unknown"
I know that I should define a data type for a field a2. But how?
This works with other values but not with nulls:

CREATE VIEW view1 AS
SELECT
  attribute1 as a1,
  text null as a2,
  text 'test' as a3
FROM
  table;



Thanks

-Lauri Kajan

-- 
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 select one column into another in same table?

[Phil Couling]

I don't this this is possible as postgres.

There is something simular with:
alter table table_name alter column column_foo using column_bar
But I don't think there's any performance advantage over a simple
update and the using clause doesn't appear to have an equivalent in an
add column statement.

You could.
alter table table_name rename column_foo to column_bar;
alter table table_name add column_foo foo_data_type default =
nextval('new_foo_sequence');

This has your best chance of success since renaming a column should
not have to touch every row of the table.

Regards

On 4 October 2011 20:21, J.V.  wrote:
> What I need to do is to save the id column for future use and then modify
> the id column resetting all values from another sequence.
>
> So I need to select the id column or somehow get the data into another
> column in the same table.
>
> And then I can update the id column (after dropping the constraint).
>
> J.V.
>
> On 10/4/2011 1:09 PM, Scott Marlowe wrote:
>>
>> On Tue, Oct 4, 2011 at 12:24 PM, J.V.  wrote:
>>>
>>> Currently I can select one column into another with two statements:
>>>
>>>    alter table  add column id_old int;
>>>    update  set id_old = id;
>>>
>>> Is there a way to do this in one statement with a select into?  I have
>>> tried
>>> various select statements but want the new column (with the same data) to
>>> be
>>> in the same table and to have it execute much more quickly that the two
>>> statements currently do.
>>
>> Do you need another column or do you just want to alter a column that
>> already exists?  If so you can alter a column from one type to another
>> and throw a using clause at it to convert the data in some way.  I
>> think we need to know a bit better what you're trying to do.,
>>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> 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] gaps/overlaps in a time table : current and previous row question

[thomas veymont]

hello,

let's say that each rows in a table contains a start time and a end
time ("timeinterval" type),
but the index are not ordered nor consecutive, e.g :

$ select * from T order by starttime

index  | starttime|   endtime
-+-+-
3|   t1 |  t2
1|   t3 |  t4
18  |   t5 |  t6
12  |   t7 |  t8

I want a result that shows time gaps and overlaps in this table, that is :

delta
-+
t3 - t2 |
t5 - t4 |
t7 - t6 |

how would I do that ? I guess this could be done with window function and lag()
function but I don't know exactly how. Any suggestion ?

thanks





I guess my question is more about

-- 
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] Postgresql-8.2 Replication

[Raymond O'Donnell]

On 05/10/2011 12:24, khizer wrote:
> ok   Thank u
> 
>  but as i am new bie i am not aware abour mailing list of postgresql

:-)

But that's what you emailed to originally - the general mailing list for
PostgreSQL. See here:

  http://www.postgresql.org/community/lists

Make sure you do "Reply-all", so that your replies go to the list as
well as to the person who replied to you; that way everyone subscribed
to the list sees them, and is in a position to help you.

Ray.


-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

-- 
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] Postgresql Data directory Issue

[Achilleas Mantzios]

Sorry to be rude, but you are in urgent need of a PostgreSQL DBA, (if you feel 
you somehow value your data).
Anyways, 8.3 catalog is not compatible with 8.4. You need to 
dump->upgrade->restore
if you just start 8.3 server with
/usr/local/pgsql/bin/postgres -D 
what happens?

Στις Wednesday 05 October 2011 15:05:26 ο/η Adarsh Sharma έγραψε:
> Dear all,
> 
> I have a database server ( 10 databases near about 110 GB) running 
> Postgresql-.8.3 )
> Today I need to format that system but I an facing the below issues :-
> 
> 1. I am trying to use the previous data directory 
> (/opt/PostgresPlus/8.3/data) in new Postgresql-8.4 installation but it 
> results in below error while installing :-
> 
> Data Directory 
> [/media/a519e535-62a8-43a2-af93-2323690a3177/opt/PostgresPlus/8.3/data]:
> Error: The existing data directory (catalog version: 200711281) is not
> compatible with this server (catalog version: 200904091).
> Press [Enter] to continue :
> 
> So i think I need to install Postgresql-8.3 to use that data.
> 
> 2. I installed Postgresql-8.3 in new server and trying to use the data 
> directory but it faces the below error :-
> 
> Existing data directory is not empty and it cannot able to use it .
> 
> 
> Please note that I am  not able to take the complete databases backup 
> because the database server is down and restarts when we start backups.
> 
> Any help will be appreciated.
> 
> 
> 
> Thanks
> 
> 
> 



-- 
Achilleas Mantzios

-- 
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] Postgresql Data directory Issue

[Christian Ullrich]

* Adarsh Sharma wrote:


2. I installed Postgresql-8.3 in new server and trying to use the data
directory but it faces the below error :-

Existing data directory is not empty and it cannot able to use it .


First, be very, very careful. initdb already saved your data from 
destruction once, but you should never have allowed it anywhere near 
your live data in the first place.


Also, do not tempt fate: Make sure that your new installation is as 
close to identical to the old one as possible (architecture, 
endianness, word size). Starting PostgreSQL on an incompatible data 
directory should fail reliably, but it sounds as if you do not have 
current backups.


If your data is on some sort of shared storage, maybe you can take a 
snapshot before you do any more experiments?


Install with a different data directory (or make sure it is not 
mounted while you are installing), then reconfigure before starting 
the database.


--
Christian




--
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 Data directory Issue

[Adarsh Sharma]

Dear all,

I have a database server ( 10 databases near about 110 GB) running 
Postgresql-.8.3 )

Today I need to format that system but I an facing the below issues :-

1. I am trying to use the previous data directory 
(/opt/PostgresPlus/8.3/data) in new Postgresql-8.4 installation but it 
results in below error while installing :-


Data Directory 
[/media/a519e535-62a8-43a2-af93-2323690a3177/opt/PostgresPlus/8.3/data]:

Error: The existing data directory (catalog version: 200711281) is not
compatible with this server (catalog version: 200904091).
Press [Enter] to continue :

So i think I need to install Postgresql-8.3 to use that data.

2. I installed Postgresql-8.3 in new server and trying to use the data 
directory but it faces the below error :-


Existing data directory is not empty and it cannot able to use it .


Please note that I am  not able to take the complete databases backup 
because the database server is down and restarts when we start backups.


Any help will be appreciated.



Thanks



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

[GENERAL] Fwd: [BUGS] BUG #6240: About - postgreswdinit.sql

[Chethan HB]

Hi,

In a freshly commissioned h/w, when watchdog is created for the first time,
I faced some problem.

I never faced this problem earlier.

Currenlty we are using the following version:
export PGODBC_VERSION=psqlodbc-09.00.0300
Postgres version=9.0.4

Logs are attached here:

Jan  1 02:05:09.492736 info CLA-0 FSPostgresWD: COMMUTIL INFO connection to
db server succeeded, Postgres is up
Jan  1 02:05:09.520838 info CLA-0 FSPostgresWD: COMMUTIL INFO create WD DB
postgreswd
Jan  1 02:05:09.521774 info CLA-0 FSPostgresWD: COMMUTIL INFO port = 5432
Jan  1 02:05:09.526093 info CLA-0 FSPostgresWD: COMMUTIL INFO => executing:
/opt/nokiasiemens/SS_Postgres/bin/psql -d template1 -v ON_ERROR_STOP=on -f
/opt/nokiasiemens/SS_DBHAforPostgres/etc/postgreswdinit.sql -q -h 127.0.0.4
-p 5432  as user: _qnrnwdbman
Jan  1 02:05:10.531239 warn CLA-0 postgres[9919]: [2-1] ERROR:  could not
create file "base/16384/11500": File exists[This error is
from postgres
server]
Jan  1 02:05:10.531317 warn CLA-0 postgres[9919]: [2-2] STATEMENT:  create
database postgreswd;
Jan  1 02:05:10.536788 err CLA-0 FSPostgresWD: COMMUTIL ERROR psql:
/opt/nokiasiemens/SS_DBHAforPostgres/etc/postgreswdinit.sql returned with
error: 3
Jan  1 02:05:10.536851 err CLA-0 FSPostgresWD: COMMUTIL ERROR failed to
execute /opt/nokiasiemens/SS_DBHAforPostgres/etc/postgreswdinit.sql got
error 3
Jan  1 02:05:10.536895 err CLA-0 FSPostgresWD: COMMUTIL ERROR failed to
initialize PersDBProxy object
Jan  1 02:05:10.536943 info CLA-0 FSPostgresWD: COMMUTIL INFO DB postgreswd
does not exist
Jan  1 02:05:10.546575 err CLA-0 postgres[9922]: [2-1] FATAL:  database
"postgreswd" does not exist
Jan  1 02:05:10.547846 warn CLA-0 FSPostgresWD: COMMUTIL WARNING ===> Sql
response :  connection to SQL server  rc code :  -1
Jan  1 02:05:10.547903 info CLA-0 FSPostgresWD: COMMUTIL INFO
[unixODBC]FATAL: database "postgreswd" does not exist (210)
Jan  1 02:05:10.547947 warn CLA-0 FSPostgresWD: COMMUTIL WARNING ###>
failed to connect to the DB
Jan  1 02:05:10.547994 info CLA-0 FSPostgresWD: COMMUTIL INFO initializing
DB
Jan  1 02:05:10.548920 info CLA-0 FSPostgresWD: COMMUTIL INFO postgreswd
starting with redundancy model 1 current role 0
Jan  1 02:05:10.572467 info CLA-0 FSPostgresWD: COMMUTIL INFO connection to
db server succeeded, Postgres is up
Jan  1 02:05:10.598632 info CLA-0 FSPostgresWD: COMMUTIL INFO create WD DB
postgreswd
………..
……….

Kindly let me know as early as possible. Thanks in advance

Regards
Chethan HB

"Life is just a moment between a breath and none. Hope I do enough to add
meaning to it."

Re: [GENERAL] Postgresql-8.2 Replication

[Raymond O'Donnell]

On 05/10/2011 12:04, khizer wrote:
> Raymond i have attached the error file as above.
> 

You really should keep your replies on the list - some of the many
experts on the list may be able to help you.

I'm out of my depth on this one, but the error message looks to me as if
you're missing the PostgreSQL header files, as  suggested in my last
email. If you post the output to the list, someone else will be able to
say for sure.

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

-- 
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] Postgresql-8.2 Replication

[Raymond O'Donnell]

On 05/10/2011 11:19, khizer wrote:
> Thanks for reply
> 
> 
> Can we use slony on ubuntu OS
> as it was giving error while compiling and installing slony

Please keep your replies on the list.

What errors were you getting? If I recall correctly, you need to have
the headers for PostgreSQL on the machine where you're building Slony.
Anyway, if you post your errors here others may be able to help you.

> one more thing there is no package for slony8.2 isn't it

I don't know - 8.2 is getting old now, so maybe. It probably depends on
what version of Ubuntu you're using.

Ray.


> So any other steps to Replicate postgresql8.2 databse on ubuntu
> 
> On 10/05/2011 03:44 PM, Raymond O'Donnell wrote:
>> On 05/10/2011 09:46, khizer wrote:
>>> Hi,
>>>
>>> Kindly requesting u to send the steps to set up the Postgresql-8.2
>>> database replication.
>> 8.2 doesn't have built-in replication - you'll need to use Slony or one
>> of the other tools. See the docs for suggestions:
>>
>>http://www.postgresql.org/docs/8.2/static/high-availability.html
>>
>> Ray.
>>
>>
> 
> 
> 


-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

-- 
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] Postgresql-8.2 Replication

[Raymond O'Donnell]

On 05/10/2011 09:46, khizer wrote:
> Hi,
> 
>Kindly requesting u to send the steps to set up the Postgresql-8.2
> database replication.

8.2 doesn't have built-in replication - you'll need to use Slony or one
of the other tools. See the docs for suggestions:

  http://www.postgresql.org/docs/8.2/static/high-availability.html

Ray.


-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

-- 
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 disable all pkey/fkey constraints globally

[Joe Abbate]

On 10/05/2011 04:49 AM, depst...@alliedtesting.com wrote:
>> -Original Message-
>> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
>> ow...@postgresql.org] On Behalf Of J.V.
>> Sent: Tuesday, October 04, 2011 10:00 PM
>> To: pgsql-general
>> Subject: [GENERAL] how to disable all pkey/fkey constraints globally
>>
>> Is there a generic way to drop just all primary key and foreign key 
>> constraints
>> on a given table?
>>
>> I know how to do given the specific name of the constraint.
>>
>> same question but one statement that would just disable all primary key and
>> foreign key constraints on a given database?
>>
>> and am assuming the reverse could not be done because would have to re-
>> create each one individually?
>>
>> Maybe I do not want to drop, so is there a way to simply disable all globally
>> (not drop) & then enable all globally?
>>
> 
> You can find all foreign key constraints for a given table, save 
> constraint definitions, drop constraints, and later re-enable them.
> Look into table pg_constraint and function pg_get_constraintdef.

If you'll allow me to toot my horn, here's an alternative:

- Use dbtoyaml [1] to output your tables to a file, say, yaml1
- Edit the yaml1 file, searching for primary_key and foreign_keys and
remove those you want to drop, save the result to a different file, say,
yaml2
- Use yamltodb [2] with yaml2 to generate SQL to drop the primary keys
and foreign keys, in the correct dependency order (at least that's what
it's supposed to do, make sure you use the -1 option), to a file, say, sql1
- Run sql1 through psql to drop the constraints
- Use yamltodb with yaml1 to generate SQL to recreate the primary keys
and foreign keys to, say sql2
- Run sql2 through psql to recreate the constraints

Regards,


Joe

[1] http://www.pyrseas.org/docs/dbtoyaml.html
[2] http://www.pyrseas.org/docs/yamltodb.html

-- 
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-8.2 Replication

[khizer]

Hi,

   Kindly requesting u to send the steps to set up the Postgresql-8.2 
database replication.


Mehdi

--
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 disable all pkey/fkey constraints globally

[depstein]

> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of J.V.
> Sent: Tuesday, October 04, 2011 10:00 PM
> To: pgsql-general
> Subject: [GENERAL] how to disable all pkey/fkey constraints globally
> 
> Is there a generic way to drop just all primary key and foreign key 
> constraints
> on a given table?
> 
> I know how to do given the specific name of the constraint.
> 
> same question but one statement that would just disable all primary key and
> foreign key constraints on a given database?
> 
> and am assuming the reverse could not be done because would have to re-
> create each one individually?
> 
> Maybe I do not want to drop, so is there a way to simply disable all globally
> (not drop) & then enable all globally?
> 

You can find all foreign key constraints for a given table, save constraint 
definitions, drop constraints, and later re-enable them. Look into table 
pg_constraint and function pg_get_constraintdef.

Dmitry Epstein | Developer
 
Allied Testing

www.alliedtesting.com
We Deliver Quality.

-- 
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] Hash index not being updated

[pasman pasmański]

Hi.

Right direction is to use btree index.

Hash indexes are sensitive to power failures.

2011/10/5, Justin Naifeh :
> In Postgres 8.4, I have a table called java_types with two columns,
> package_name and class_name. There is another table called java_objects
> that defines a column called type whose value matches the concatenation
> of package_name and class_name. A typical join and result looks like this:
>
> SELECT package_name, class_name, type FROM java_objects o INNER JOIN
> java_types t ON (t.package_name || '.' || t.class_name) = o.type;
>
> package_name, class_name, type
> "java.lang" , "String"  , "java.lang.String"
>
> The above works, although it is slow for large data sets so I defined
> the following index:
>
> CREATE INDEX java_type_hash
>ON java_types
>USING hash
>(((package_name::text || '.'::text) || class_name::text));
>
> I confirmed that my new index is being used by inspecting the query plan
> and finding the following:
>
> ->  Index Scan using java_type_hash on java_types (cost=0.00..1.22
> rows=1 width=49)
> Index Cond: java_types.package_name)::text || '.'::text) ||
> (java_types.class_name)::text) = (java_objects.type)::text)
>
> This gave me a speed boost and worked initially. A day after defining
> the index, however, I inserted a few rows into java_types and then many
> rows into java_objects. When I ran the aforementioned query I got 0
> results. A month later (without restarting postgres), I ran the same
> query and still got 0 results. I suspected the index had either been
> corrupted or not updated automatically. I confirmed this by running the
> following:
>
> REINDEX INDEX java_type_hash;
>
> And then when I re-ran the same query the correct results came back! I
> thought that maybe my system wasn't set to autovacuum but a query for my
> current settings confirmed that it was:
>
> autovacuum = on
> autovacuum_analyze_scale_factor = 0.1
> autovacuum_analyze_threshold = 256
> autovacuum_freeze_max_age = 2
> autovacuum_max_workers = 3
> autovacuum_naptime = 60
> autovacuum_vacuum_cost_delay = 20
> autovacuum_vacuum_cost_limit = -1
> autovacuum_vacuum_scale_factor = 0.2
> autovacuum_vacuum_threshold = 512
> track_counts = on
>
> Finally, this query gave me some extra information about when my system
> had last been vacuumed/analyzed:
>
> SELECT relname, last_vacuum, last_autovacuum, last_analyze,
> last_autoanalyze FROM pg_stat_all_tables WHERE relname = 'java_types'
>
> rename  , last_vacuum , last_autovacuum ,
> last_analyze, last_autoanalyze
> "java_types", "2011-10-04 13:37:03.867-07", ""  ,
> "2011-10-04 13:21:22.342-07", "2011-08-30 10:15:13.018-07"
>
> The index was created on 8/30/2011, inserts were done on java_types and
> java_objects on 8/31/2011, and then 10/4/2011 is when I manually ran
> REINDEX (and later VACUUM ANALYZE).
>
> So after all that, the questions is: If I have autovacuum set to true
> then I shouldn't have to worry about calling VACUUM/ANALYZE/REINDEX
> manually to update my java_types_hash index, right? Maybe my settings
> are wrong or I'm making an incorrect assumption about Postgres behavior.
>
> Any pointer in the right direction helps. Thanks in advance.
>
> --
>
>
>   Justin Naifeh Software Developer
>
> Voice:
> 303-460-7111 x1   Toll Free:
> 877-444-3074 x1   Cell:
> 720-363-8874  AIM, Yahoo
> justinnaifeh 
>
> www.terraframe.com/products/runwaysdk
> 
> Makers of TerraFrame RUNWAY SDK^(TM), the next-generation
> model-driven engineering (MDE) application toolkit for software developers
>
>


-- 

pasman

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

Re: [BUGS] [GENERAL] One-click installer, Windows 7 32-bit, and icacls.exe

[Dave Page]

On Wednesday, October 5, 2011, Thomas Kellerer  wrote:
> Dave Page, 04.10.2011 21:46:
>>
>> We updated our build system to use BitRock 7 today (for unrelated
>> reasons) which has new features for ACL management. We're going to
>> investigate replacing cacls/icacls with those features tomorrow and
>> will create some test builds ASAP.
>
> If you can provide the test builds publicly, I will be happy to test them
and see if that behaves differently on my system.

Thanks, we will.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company