Re: [ADMIN] unable to locate a valid checkpoint
Hi Tom & pg_list, > "Shutdown"? Exactly what happened there? It certainly doesn't look > like you had a clean shutdown. mmm, yes the postmaster was running in foregroud and was interrupted by a CTRL+C What's the correct way to shutdown? kill -15 I think... > At this point I think your only hope is contrib/pg_resetxlog, but that's > a very blunt instrument. It'd be nice to understand what went wrong. pg_resetxlog mmm I've to read something about this command! U think this is the solution? TIA, Auri ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] Time stamp on log file
Is it possible have on log file the time stamp ?
Ciao
Gaetano
--
#exclude
#include
printf("\t\t\b\b\b\b\b\b");.
printf("\t\t\b\b\b\b\b\b");
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [ADMIN] large table support 32,000,000 rows
Hi! There are no problems with large tables. I am running postgresql with a database with over 100,000,000 records. The biggest table is with about 55,000,000 rows there are tables with with 25,000,000 and 19,000,000 rows and few other with less than 10,000,000. It works pretty good. It's online at http://www.astro.bas.bg/stargazer/ . The performance is excellent. The database is about 15G. The only problem was dump and restore while upgrading to 7.2 it took about 5 days. Rumen --- Christopher Smith <[EMAIL PROTECTED]> wrote: > > I have a set of data that will compose a table with 32 million rows. I > currently run postgresql with tables as large as 750,000 rows. > > Does anyone have experience with such large tables data.In addition, I > have been reading information on moving postgresql tables to > > another hard-drive can anyone advise me. > > Thanks > > > > - > Do You Yahoo!? > Yahoo! Movies - coverage of the 74th Academy Awards® __ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Time stamp on log file
"Gaetano Mendola" <[EMAIL PROTECTED]> wrote:
> Is it possible have on log file the time stamp ?
>
I find it !!! Thank you anyway.
It's enough send the SIGHUP to postmaster
for don't loose the existing connections?
Ciao
Gaetano
--
#exclude
#include
printf("\t\t\b\b\b\b\b\b");.
printf("\t\t\b\b\b\b\b\b");
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [ADMIN] unable to locate a valid checkpoint
Always me... ;P I've just recover my installation by using pg_resetxlog. But, what is the operation made by this command? I believe it performs a reset to the previous checkpoint, so some data could be lost.. >From now I'll use the start/stop/status script that I've found in contrib/start_script dir! Auri ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] slow inserts
Jodi Kanter wrote:
> I am currently using a Perl data loader that was set up to load data to
three particular
> tables.
(Snip)
> I have placed some debugging syntax in the code and it seems that the
extra time if
> related to postgres as I had originally thought it may have to do with
the parsing of
> the Excel file.
You don't mention it, but I assume you are using DBI/pg. You are sure you
are setting up you
insert qyery handlers once and then reuse them? >s setting up a query
handler takes a lot of time.
ie
my $dbh=DBI->connect(dbi:Pg ...);
my $insh = $dbh->prepare("Insert into table values (?,?,?)";
foreach ($excelrow){
parse;
$insh->execute($data1,$data2,$data3);
}
I have written a few script of that kind my self, and I was really surprised
how much it mattered when I managed to move a $dbi->prepare out of the
insert loop.
regards
--
Morten Sickel
Norwegian Radiation Protection Authority
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] large table support 32,000,000 rows
are you running that on 32 bit or 64 bit hardware? I have an 18G database which is mostly one table. Dumps and restores in about 2-4 hours but sometimes is slow on certain operations. Running it on 4G Ram, redhat 7.2 enterprise kernel. > Hi! > There are no problems with large tables. > I am running postgresql with a database with over 100,000,000 records. > The biggest table is with about 55,000,000 rows there are tables with with > 25,000,000 and 19,000,000 rows and few other with less than 10,000,000. It > works pretty good. It's online at http://www.astro.bas.bg/ stargazer/ . > The performance is excellent. The database is about 15G. > The only problem was dump and restore while upgrading to 7.2 it took about 5 > days. > > Rumen > > --- Christopher Smith <[EMAIL PROTECTED]> wrote: > > > > I have a set of data that will compose a table with 32 million rows. I > > currently run postgresql with tables as large as 750,000 rows. > > > > Does anyone have experience with such large tables data.In addition, I > > have been reading information on moving postgresql tables to > > > > another hard-drive can anyone advise me. > > > > Thanks > > > > > > > > - > > Do You Yahoo!? > > Yahoo! Movies - coverage of the 74th Academy Awards® > > > __ > Do You Yahoo!? > Yahoo! Movies - coverage of the 74th Academy Awards® > http://movies.yahoo.com/ > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] slow inserts
> my $dbh=DBI->connect(dbi:Pg ...);
>
> my $insh = $dbh->prepare("Insert into table values (?,?,?)";
>
$insh->begin_work;
> foreach ($excelrow){
> parse;
> $insh->execute($data1,$data2,$data3);
> }
$insh->commit;
>
> I have written a few script of that kind my self, and I was really
> surprised how much it mattered when I managed to move a $dbi->prepare out
> of the insert loop.
Try to use transactions, that increates the speed too.
Regards,
Ferdinand
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [ADMIN] to --enable-locale or not to --enable-locale?
Hi, I'm having trouble sorting my data.
I'm using PostgresQL 7.2 compiled with:
--enable-multibyte=LATIN1 --enable-locale
I've also created a test db, with one table:
CREATE DATABASE "testdb" WITH ENCODING = 'LATIN1';
And
CREATE TABLE "sorttest" (
"id" int8 NOT NULL,
"data" varchar(100),
CONSTRAINT "sorttest_pkey" PRIMARY KEY ("id")
) WITH OIDS;
Then I've inserted some test values which seem to be sorted wrongfully
when I issue an
select * from sorttest order by data
This is the output I get:
aa
aå
aä
åa
äa
ab
åb
äb
aö
ba
bå
bä
bb
bö
za
zö
I want it sorted in abcd..zåäö
What am I missing here? Any Ideas?
Regards,
Niclas Gustafsson
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]] On Behalf Of Jean-Michel POURE
Sent: den 17 mars 2002 11:15
To: Peter Eisentraut; Morten Sickel
Cc: Pgsql-Admin (E-post)
Subject: Re: [ADMIN] to --enable-locale or not to --enable-locale?
Le Vendredi 15 Mars 2002 17:19, Peter Eisentraut a écrit :
> --enable-recode is a simplified version of part (2) of multibyte,
which
> only works for single-byte encodings. It's mostly useful for
environments
> where Unix and Windows use different character sets for the same
language.
> (I think Czech was an example.)
As of PostgreSQL 7.2+, --enable--recode provides:
- Unicode <-> Latin1/Latin15 recoding,
- Unicode <-> SJIS (=Japanese Multibyte),
- and much more...
Client and server encodings can be set separately. Examples:
- CREATE DABASE foo WITH ENCODING 'Unicode';
- SET CLIENT_ENCODING = 'Latin9' (=ISO-8859-15) = Latin1 + euro symbol.
In pgAdmin2, we plan to take advantage of these new features to :
- change client encoding on the fly,
- display multi-byte text.
Cheers,
Jean-Michel POURE
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] to --enable-locale or not to --enable-locale?
On Mon, 2002-03-25 at 11:14, Niclas Gustafsson wrote: > Hi, I'm having trouble sorting my data. > > I'm using PostgresQL 7.2 compiled with: > --enable-multibyte=LATIN1 --enable-locale ... > Then I've inserted some test values which seem to be sorted wrongfully > when I issue an > select * from sorttest order by data ... > I want it sorted in abcd..zåäö > What am I missing here? Any Ideas? 1. Does the locale you are using sort as you want? 2. Did you initdb with that locale set? (Use pg_controldata from contrib to see what locale the backend is using.) locale must be set correctly for initdb, to ensure that indexes don't get broken by changes of locale. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Why are you downcast, O my soul? Why so disturbed within me? Put your hope in God, for I will yet praise Him, my Saviour and my God." Psalm 42:11 signature.asc Description: This is a digitally signed message part
Re: [ADMIN] to --enable-locale or not to --enable-locale?
Hi, I am sure that 1) is correct, allthough I think I've overlooked 2)! pg_controldata reports: (last 2 rows) LC_COLLATE: en_US LC_CTYPE: en_US Is it possible to change this after you've run initdb? Regards, Niclas Gustafsson -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Oliver Elphick Sent: den 25 mars 2002 12:26 To: Niclas Gustafsson Cc: [EMAIL PROTECTED] Subject: Re: [ADMIN] to --enable-locale or not to --enable-locale? On Mon, 2002-03-25 at 11:14, Niclas Gustafsson wrote: > Hi, I'm having trouble sorting my data. > > I'm using PostgresQL 7.2 compiled with: > --enable-multibyte=LATIN1 --enable-locale ... > Then I've inserted some test values which seem to be sorted wrongfully > when I issue an > select * from sorttest order by data ... > I want it sorted in abcd..zåäö > What am I missing here? Any Ideas? 1. Does the locale you are using sort as you want? 2. Did you initdb with that locale set? (Use pg_controldata from contrib to see what locale the backend is using.) locale must be set correctly for initdb, to ensure that indexes don't get broken by changes of locale. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Why are you downcast, O my soul? Why so disturbed within me? Put your hope in God, for I will yet praise Him, my Saviour and my God." Psalm 42:11 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] to --enable-locale or not to --enable-locale?
Hrm, a big RTFM to myself. :) >"The sort order used within a particular database cluster is set >by initdb and cannot be changed later, short of dumping all data, >rerunning initdb, and reloading the data." Well I changed LC_ALL to LATIN1 and run initdb, dumped back all the data, but the sorting order seem to wrong still, allthough different, pg_controldata shows: LC_COLLATE: C LC_CTYPE: C Now I get the data sorted in the order below, quite close though: aa ab aä aå aö ba bb bä bå bö za zö äa äb åa åb The å and ä are sorted in the wrong order. Where can I see the order of the charset? Regards, Niclas Gustafsson -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Niclas Gustafsson Sent: den 25 mars 2002 12:47 To: 'Oliver Elphick' Cc: [EMAIL PROTECTED] Subject: Re: [ADMIN] to --enable-locale or not to --enable-locale? Hi, I am sure that 1) is correct, allthough I think I've overlooked 2)! pg_controldata reports: (last 2 rows) LC_COLLATE: en_US LC_CTYPE: en_US Is it possible to change this after you've run initdb? Regards, Niclas Gustafsson -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Oliver Elphick Sent: den 25 mars 2002 12:26 To: Niclas Gustafsson Cc: [EMAIL PROTECTED] Subject: Re: [ADMIN] to --enable-locale or not to --enable-locale? On Mon, 2002-03-25 at 11:14, Niclas Gustafsson wrote: > Hi, I'm having trouble sorting my data. > > I'm using PostgresQL 7.2 compiled with: > --enable-multibyte=LATIN1 --enable-locale ... > Then I've inserted some test values which seem to be sorted wrongfully > when I issue an > select * from sorttest order by data ... > I want it sorted in abcd..zåäö > What am I missing here? Any Ideas? 1. Does the locale you are using sort as you want? 2. Did you initdb with that locale set? (Use pg_controldata from contrib to see what locale the backend is using.) locale must be set correctly for initdb, to ensure that indexes don't get broken by changes of locale. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Why are you downcast, O my soul? Why so disturbed within me? Put your hope in God, for I will yet praise Him, my Saviour and my God." Psalm 42:11 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] to --enable-locale or not to --enable-locale?
On Mon, 2002-03-25 at 11:47, Niclas Gustafsson wrote: > Hi, > > I am sure that 1) is correct, allthough I think I've overlooked 2)! > pg_controldata reports: (last 2 rows) > LC_COLLATE: en_US > LC_CTYPE: en_US > > Is it possible to change this after you've run initdb? No. You have to dump the database, destroy everything, do initdb again (with locale set correctly) and reload from your dump. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Why are you downcast, O my soul? Why so disturbed within me? Put your hope in God, for I will yet praise Him, my Saviour and my God." Psalm 42:11 signature.asc Description: This is a digitally signed message part
Re: [ADMIN] to --enable-locale or not to --enable-locale?
On Mon, 2002-03-25 at 12:41, Niclas Gustafsson wrote: > Hrm, a big RTFM to myself. :) > >"The sort order used within a particular database cluster is set > >by initdb and cannot be changed later, short of dumping all data, > >rerunning initdb, and reloading the data." > > Well I changed LC_ALL to LATIN1 and run initdb, dumped back all the > data, but the sorting order seem to wrong still, allthough different, LATIN1 is an encoding, but I don't think it is a locale. A locale looks like de_DE@euro or en_GB: it consists of a language code followed by a country code and an optional supplement. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Why are you downcast, O my soul? Why so disturbed within me? Put your hope in God, for I will yet praise Him, my Saviour and my God." Psalm 42:11 signature.asc Description: This is a digitally signed message part
Re: [ADMIN] to --enable-locale or not to --enable-locale?
Sorry, I'm a bit overclocked today, the locale is ofcourse sv_SE... -Original Message- From: Oliver Elphick [mailto:[EMAIL PROTECTED]] Sent: den 25 mars 2002 13:57 To: Niclas Gustafsson Cc: [EMAIL PROTECTED] Subject: Re: [ADMIN] to --enable-locale or not to --enable-locale? On Mon, 2002-03-25 at 12:41, Niclas Gustafsson wrote: > Hrm, a big RTFM to myself. :) > >"The sort order used within a particular database cluster is set > >by initdb and cannot be changed later, short of dumping all data, > >rerunning initdb, and reloading the data." > > Well I changed LC_ALL to LATIN1 and run initdb, dumped back all the > data, but the sorting order seem to wrong still, allthough different, LATIN1 is an encoding, but I don't think it is a locale. A locale looks like de_DE@euro or en_GB: it consists of a language code followed by a country code and an optional supplement. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Why are you downcast, O my soul? Why so disturbed within me? Put your hope in God, for I will yet praise Him, my Saviour and my God." Psalm 42:11 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] to --enable-locale or not to --enable-locale?
On Mon, 2002-03-25 at 12:59, Niclas Gustafsson wrote: > Sorry, I'm a bit overclocked today, the locale is ofcourse > sv_SE... Well if that doesn't sort right, it is a problem with the lcoale definition, not PostgreSQL. As I understand it, PostgreSQL merely uses the locale without amendment. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Why are you downcast, O my soul? Why so disturbed within me? Put your hope in God, for I will yet praise Him, my Saviour and my God." Psalm 42:11 signature.asc Description: This is a digitally signed message part
Re: [ADMIN] large table support 32,000,000 rows
I have multi tables with over 10,000, 000 rows, the biggest one is 70, 000, 000 rows. For each table, there are several indexes, almost all columns are varchar2. In my experiences, with many indexes on large table, data insertion will be a pain. In my case, I have 30, 000 rows to be inserted to a table every day, it takes hours for each table, if I drop indexes, insertion speeds up, but recreate such indexes takes 7 hours. Usually querying data is not the problem, but I think you must consider the performance if you have to insert, update data frequently like me. Hope it helps! Anna Zhang -Original Message-From: Christopher Smith [mailto:[EMAIL PROTECTED]]Sent: Wednesday, March 20, 2002 5:26 PMTo: [EMAIL PROTECTED]Subject: [ADMIN] large table support 32,000,000 rows I have a set of data that will compose a table with 32 million rows. I currently run postgresql with tables as large as 750,000 rows. Does anyone have experience with such large tables data. In addition, I have been reading information on moving postgresql tables to another hard-drive can anyone advise me. Thanks Do You Yahoo!?Yahoo! Movies - coverage of the 74th Academy Awards®
Re: [ADMIN] to --enable-locale or not to --enable-locale?
"Niclas Gustafsson" <[EMAIL PROTECTED]> writes: > Well I changed LC_ALL to LATIN1 and run initdb, dumped back all the > data, but the sorting order seem to wrong still, allthough different, > pg_controldata shows: > LC_COLLATE: C > LC_CTYPE: C "C" is certainly not the locale you want... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] broken view?
I had a view set up and then went and had to change one of the tables that was referenced in the view. I needed to drop one of the field in this particular table so I created a temp table (without the field I wanted to eliminate) and then moved the data in. I dropped the original table and thought I was ok. Today I am getting the following error from any query that tries to access the view. ERROR: Relation "al_spots" with OID 1132243 no longer exists I assume that this is because the OIDs were changed when I recreated the table? So I decided to recreate the view using the new table but when I try to do that I get the following error: ERROR: Attempt to insert rule "_RETam_spots_usf_view" failed: already exists I cannot seem to determine what this last error is telling me. Any thoughts? Thanks Jodi Kanter ___Jodi L KanterBioInformatics Database AdministratorUniversity of Virginia(434) 924-2846[EMAIL PROTECTED]
[ADMIN] in addition to last posting
I should also mention that the view no longer states its definition when \d is used. Thanks Jodi Kanter ___Jodi L KanterBioInformatics Database AdministratorUniversity of Virginia(434) 924-2846[EMAIL PROTECTED]
Re: [ADMIN] broken view?
got it workingI was trying to create and rename the view but apparently that breaks some sort of link. It worked when I completely removed the existing view and then recreated with original name from scratch. thanks anyway... Jodi - Original Message - From: Jodi Kanter To: Postgres Admin List Sent: Monday, March 25, 2002 2:47 PM Subject: [ADMIN] broken view? I had a view set up and then went and had to change one of the tables that was referenced in the view. I needed to drop one of the field in this particular table so I created a temp table (without the field I wanted to eliminate) and then moved the data in. I dropped the original table and thought I was ok. Today I am getting the following error from any query that tries to access the view. ERROR: Relation "al_spots" with OID 1132243 no longer exists I assume that this is because the OIDs were changed when I recreated the table? So I decided to recreate the view using the new table but when I try to do that I get the following error: ERROR: Attempt to insert rule "_RETam_spots_usf_view" failed: already exists I cannot seem to determine what this last error is telling me. Any thoughts? Thanks Jodi Kanter ___Jodi L KanterBioInformatics Database AdministratorUniversity of Virginia(434) 924-2846[EMAIL PROTECTED]
