[ADMIN] Advantages of pg_dumpall over tar/gzip?
What's the advantage of using pg_dumpall over tar/gzip for backup?
Re: [ADMIN] Advantages of pg_dumpall over tar/gzip?
Bruce Momjian wrote: > > What's the advantage of using pg_dumpall over tar/gzip for backup? > > pg_dumpall grabs a constent snapshot of the data. tar/gzip is just > backing up the files, so you can get some data in some table that is > committed, but miss data in another table that is part of the same > transaction. Of course, if the database is not being used, this is not > an issue. > > -- > Bruce Momjian| http://www.op.net/~candle > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 Thank you, Bruce. I was using pg_dumpall nightly (with the help of cron) to backup and suddenly my backup fails. The following was what I get from my backup file: \connect template1 select datdba into table tmp_pg_shadow from pg_database where datname = 'template1'; delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba; drop table tmp_pg_shadow; copy pg_shadow from stdin; officeuser 41 t t f t bb \N \. \connect template1 postgres create database cpos; \connect cpos postgres I don't know why it stopped there. The size of the database is about 350MB. I allocated about 6 hours for the backup and it still fails.Is there a limit to the size of the database for pg_dumpall or is it because it takes much longer time than 6 hours for 350MB? Regards, Thomas.
[ADMIN] Web hosting and PostgreSQL?
Anyone knows of any good web hosting that support PostgreSQL? Regards, Thomas.
[ADMIN] ODBC Call fail?
Everytime I try to hit my database from my ODBC driver I get ODBC call failed. I have a pretty good idea why. I do not have an entry for postgres in my /etc/services (I'm using solaris 2.6). Am I correct? If I do need an /etc/services entry for port 5432 for postgres, let me know. Also what is the entry? Thanks, Thomas == |[EMAIL PROTECTED] | Thomas Lester | | http://www.iakom.com | System Administrator | | | Iakom | ==
Re: [ADMIN] ODBC Call fail? (fwd)
That worked for me... I made the change in my hba file for the host that needs to connect and it works. I wish I had enough experience in that matter to help you out, but I don't... Good luck, Thomas == |[EMAIL PROTECTED] | Thomas Lester | | http://www.iakom.com | System Administrator | | | Iakom | == On Mon, 1 Jun 1998, Peter Mount wrote: > > -- Forwarded message -- > Date: Sun, 31 May 1998 18:52:53 +0530 > From: Venugopal <[EMAIL PROTECTED]> > To: Peter Mount <[EMAIL PROTECTED]> > Subject: Re: [ADMIN] ODBC Call fail? > Newsgroups: comp.databases.postgresql.admin > > Peter Mount wrote: > > > On Sat, 30 May 1998, Thomas Lester wrote: > > > > > Everytime I try to hit my database from my ODBC driver I get ODBC call failed. > > > I have a pretty good idea why. I do not have an entry for postgres in my > > > /etc/services (I'm using solaris 2.6). Am I correct? > > > > You don't need an entry. > > > > Are you running the postmaster with the -i argument which enables TCP/IP > > communication? > > > > > > Hi, I am facing the same problem even though i run postmaster with "-i" and -p > 5432 option. > psql connects over the loopback but, > psql -d mydb -h 192.168.0.34 -p 5432 - fails to connect. (192.168.0.34 is my IP) > > I rectified this by editing the /var/lib/pgsql/pg_hba.conf to add > > host all192.168.0.0255.255.255.0 trust > > at the end of the file > I know this is not ideal, with no security, but then , i can connect using ip... > But i am still not able to connect thru ODBC on Win95... any solutions ? > > venu > > > >
Re: [ADMIN] How to transfer data from FoxPro data to Postgresql?
On Tue, 1 Sep 1998, S.Ramaswamy wrote: > We have our data now in FoxPro dbf format under DOS. We want to transfer > this to > Postgresql under Linux. What is the best way to do so? Hi, I don't know the best way ;-) Here is what I do (this is the `clumsy way'): Dump the query output as a dumpfile delimited by tabs. Reformat the output for pgsql... NOTES: FoxPro DOS gave me an EOF (^Z) that had to be stripped. As DOS does a ^J^M dance (CRLF) these embedded chars must also be stripped. The / / bit in the script below was to rm any bad date values - making them proper NULLs. The empty date placeholders must be nulled BEFORE you strip away the double quotes otherwise char strs end up with embedded nulls. ;-) Next the double quote FoxPro nulls ( "" ) are converted to proper NULLs. Lastly, the surviving quotes are stripped. IMPORTANT: the ^Z and ^M are _literals_. I edit them into the script with i, ^V, ^M (in vi...) #!/bin/sh tput clear echo -n "Enter file to be converted: " read file sed -e '/^Z/d' $file > $file.step0 sed -e 's/^M//g' $file.step0 > $file.step1 sed -e 's//\\N/g' $file.step1 > $file.step2 sed -e 's/""/\\N/g' $file.step2 > $file.step3 sed -e 's/"//g' $file.step3 > $file.pg rm $file.step0 rm $file.step1 rm $file.step2 rm $file.step3 After this, add COPY table_name FROM stdin; to the top of your dumpfile. And \. to the end. Then run: psql -e db_name < dumpfile_name BTW, if you have questions, fire away. All of us FoxPro victims must stick together! (I recommend a pint of bitter be kept handy during the process of hacking the foxpro psuedo-sql into postgres ;-) Cheers, Tom --- Sisters of Charity Medical Center -- Department of Psychiatry Thomas Good, System Administrator<[EMAIL PROTECTED]> North Richmond CMHC/Residential Services Phone: 718-354-5528 75 Vanderbilt Ave, Quarters 8Fax: 718-354-5056 Staten Island, NY 10304www.panix.com/~ugd Powered by PostgreSQL 6.3.2 / Perl 5.004 / DBI-0.91::DBD-PG-0.69
[ADMIN] Re: [INTERFACES] where did that date and time come from??
> I have a database that has four fields - > Create table dispnote ( > Autoinv int4 > noteby char(8) not null default 'current_user', > added datetime not null default 'now', > notes varchar(255), > constraint dispnotepk primary key (autoinv, added), > constraint dispnotessms foreign key (auoinv) references ssms > ); > ... But when I insert a record it came up with a date of april 14, 10am! My > server and my client both have the correct date and time (april 19, 3pm) - > where did it get the wrong date and time from?? This is (still) a problem with Postgres optimizing untyped constants by evaluating them at table creation time rather than at run time. Redefine your datetime field as ... added datetime not null default text 'now', ... to force the type of the default value to be a string, which is then converted at run time as you would expect. Fixing this behavior would seem to be a good project for someone (maybe me :) for v6.6... - Tom -- Thomas Lockhart [EMAIL PROTECTED] South Pasadena, California
[ADMIN] Re: [INTERFACES] where did that date and time come from??
> How about this?
> added datetime default datetime(now()) not null,
Yes, this apparently works by converting from timestamp to datetime.
In general datetime has better dynamic range than does timestamp, but
for calculating 'now' both are equivalent in performance.
- Tom
--
Thomas Lockhart [EMAIL PROTECTED]
South Pasadena, California
Re: [ADMIN] Irix 6.2/PostgreSQL 6.4.2: compile-error
On Mon, 3 May 1999, Jelle Ruttenberg wrote:
> Hi,
>
> I'm trying to install PostgreSQL on our SGI-Irix, but when I make it all
> with 'gmake all' it's going wrong. In the mailinglist I read stories of
> people who succeeded in installing PostgreSQL, so I hope those people
> can give me a hint.
>
> Thanks,
>
> Jelle Ruttenberg
> [EMAIL PROTECTED]
Hallo Jelle - Hoe gaat het met u?
I have just finished compiling PG for UnixWare...I cheated!
I don't use ecpg on this box so I was able to get beyound the error
message you appended. It seemed I am missing a library file that
ecpg needs to compile (I searched my library path and didn't find
the undef symbol in any of my libraries).
So - to get PG going - I commented out the call to ecpg in the
../src/interfaces/Makefile
and reran make from the src directory.
This is a cheap trick but it worked. I suppose this may help you
get jumpstarted, provided you don't plan to access the db with ecpg.
(I use perl...) If no-one has a better solution, this *will* get
PG installed.
Tot straks,
Tom
> SYSTEM:
>
> SGI-IRIX 6.2 IP22
>
> POSTGRESQL:
>
> 6.4.2
>
> CONFIGURE:
> --
> configure --with-prefix=/disk3/usr/local/pgsql --with-odbc --without-CXX
>
> template chosen by configure: irix 5
>
> MAKE:
> -
> GNU Make version 3.77
>
> CC:
> -
> SGI's cc
>
> LEX:
> ---
> SGI's lex
>
> LAST LINES FROM GMAKE BEFORE QUITTING:
> -
> gmake -C preproc all
> gmake[3]: Entering directory
> `/disk3/pgsql/postgresql-6.4.2/src/interfaces/ecpg/preproc'
> /usr/bin/yacc -d preproc.y
> Redeclaration of precedence of UNLISTEN, line 576
> cc -I../../../include -I../../../backend -I../include
> -DMAJOR_VERSION=2 -DMINOR_VERSION=4 -DPATCHLEVEL=4
> -DINCLUDE_PATH=\"/disk3/usr/local/pgsql/include\"-c y.tab.c -o
> y.tab.o
> lex pgc.l
> "pgc.l":line 53: Error; missing translation value
> gmake[3]: *** [pgc.c] Error 1
> gmake[3]: Leaving directory
> `/disk3/pgsql/postgresql-6.4.2/src/interfaces/ecpg/preproc'
> gmake[2]: *** [all] Error 2
> gmake[2]: Leaving directory
> `/disk3/pgsql/postgresql-6.4.2/src/interfaces/ecpg'
> gmake[1]: *** [all] Error 2
> gmake[1]: Leaving directory
> `/disk3/pgsql/postgresql-6.4.2/src/interfaces'
> gmake: *** [all] Error 2
>
>
North Richmond Community Mental Health Center
Thomas Good Information Systems Coordinator
E-Mail: tomg@ { admin | q8 } .nrnet.org
Phone:718-354-5528
Fax: 718-354-5056
Empowered by PostgreSQL 6.3.2
[ADMIN] vacuum, blownawayrealtion buffers and stale locks!
> > Shouldn't vacuum analyze be ran when the indices are in place? I think the
> > indices tell vacuum analyze which fields are important for stats gathering?
> >
>
> stats are gathered on all fields.
Bruce,
Forgive the naive question but I just had to do a dump, table drop and
re-create and then a reload to get beyond the following stderr
which killed vacuum and created a stale lock:
NOTICE: BlowawayRelationBuffers(tx_plan, 4): block 4 is referenced (private 0, last
0, global 1)
FATAL 1: VACUUM (vc_rpfheap): BlowawayRelationBuffers returned -2
Can you tell me what this cryptic message means?
Thanks alot,
Tom
--- North Richmond Community Mental Health Center ---
Thomas Good MIS Coordinator, Senior DBA
Vital Signs: tomg@ { admin | q8 } .nrnet.org
Phone: 718-354-5528
Fax: 718-354-5056
/* Member: Computer Professionals For Social Responsibility */
Re: [ADMIN] vacuum, blownawayrealtion buffers and stale locks!
> > NOTICE: BlowawayRelationBuffers(tx_plan, 4): block 4 is referenced (private 0,
>last 0, global 1)
> > FATAL 1: VACUUM (vc_rpfheap): BlowawayRelationBuffers returned -2
> >
> > Can you tell me what this cryptic message means?
>
> No. Maybe Vadim does.
>
Vadim -
This stderr is generated by BlowawayRelationBuffers() in
/.../src/backend.storage/buffer/bufmgr.c
It appears ` -2' means `PINNED'...
What does this all mean and what may have caused it?
Is it a serious error (or simply annoying)? It caused vacuum to
barf and generate a stale lock...forcing a dump and reload.
Ouch (and thanks!),
Tom
--- North Richmond Community Mental Health Center ---
Thomas Good MIS Coordinator
Vital Signs: tomg@ { admin | q8 } .nrnet.org
Phone: 718-354-5528
Fax: 718-354-5056
/* Member: Computer Professionals For Social Responsibility */
Re: [ADMIN] Upgrading from 6.5.0 to 6.5.1
> So it seems the 6.5.1 changes are not listed in the INSTALL file,
> though there was some attempt to add them. Thomas, does this make
> sense?
Oops. Sorry, I must have forgotten a section :(
- Thomas
--
Thomas Lockhart [EMAIL PROTECTED]
South Pasadena, California
[ADMIN] damaged pg_log
Hi, while running a nohup & my disk filled up due to an unexpected amount of output in nohup.out. The job included lots of simple SELECT queries, but no changes in the data. Now the pg_log file seems to be damaged. If I try to vacuum the database I get the error: ERROR: cannot read block 1630 of pg_log. When I try to dump the database with pg_dump I get: BEGIN command failed. When I try to create a table I get: AbortTransaction and not in in-progress state. I can still SELECT data under psql, but only for one SELECT command. If I do a second SELECT I get again: ERROR: cannot read block 1630 of pg_log. Every time I get an error I have to kill the postmaster process or stop & start postgres. Can I - somehow repair pg_log? - dump the database without access to pg_log? - delete pg_log? - restore pg_log from my last backup? Any suggestions? Thanks -- Thomas Metz GSF - National Research Center for Environment and Health
[ADMIN] Data Recovery - 6.4.2 to 6.5.3 disk file translation
I was running version 6.4.2 when I lost all the binaries. I still have all the data/* and data/base/* files. The port for FreeBSD is 6.5.3. I have it installed, now. The disk file formats changed between version 6.4.2 and 6.5.3. Is there a tool or mechanism to translate these files into a format acceptable to version 6.5.3? The only way I see is to download version 6.4.2, install it, dump the databases, install version 6.5.3, and, load the dumped data. There must be a better (simpler) way. tomdean
Re: [ADMIN] pgsql-admin: disallowing CREATE TABLE
If I understand your question correctly, createuser will do this. 'man createuser' ... -D Does not allow the user to create databases.
Re: [ADMIN] Recovering corrupted data.
I just completed a lot of recovery from losing all the bin/* part of
psql version 6.4.2, and had to install 6.5.3. The disk file format
changed, so, I had to translate the disk files into sql commands.`
Most of the tables I had were text, char, etc. So, I used the
standatd unix tools, like string, sed, awk, sort, uniq, etc. to
recover the data.
I did have to write some code. It is below. Build it with
cc -o .c -lm
This code is for a specific file. I only had a few data files to deal
with, so, I did not create a general tool. sorry about the lack of comments, etc.
To adapt this to a given data file, look through the file with
'hd a | more'
Note: A date is an offset from 1/1/2000.
tomdean
/*
* read_db_file.c - read a psql 6.4.2 disk file and output all the data.
*
* books
*/
#include
#include
#include
#define BUF_SIZE 100*1024
#define IN_FILE "../tomdean/books"
void usage(char *name) {
printf("usage: %s\n", name);
return;
}
/*
| JulToCal.c - convert the julian data to calendar date.
| arguments:
| jd - julian date
| yr - int return the year
| mo - int return the month
| da - int return the day
| tm - double return the time
*/
void JulToCalDate(double jd, struct tm *time) {
double a, b, c, d, e, f, g, i, tm, d_tmp;
f = modf(jd+0.5,&i);
if (i > 2299160.0) {
a = floor((i - 1867216.25)/36524.25);
b = floor(i + 1.0 + a - floor(a/4.0));
}
else {
a = i;
b = 0;
}
c = b + 1524.0;
d = floor((c-122.1)/365.25);
e = floor(365.25*d);
g = floor((c-e)/30.6001);
tm = 24.0*modf(c - e + f - floor(30.6001*g), &b); /* b is day */
tm = modf(tm, &d_tmp);
time->tm_hour = (int)d_tmp;
tm *= 60.0;
tm = modf(tm, &d_tmp);
time->tm_min = (int)d_tmp;
time->tm_sec = (int)floor(tm*60.0);
time->tm_mday = (int)b;
if (g < 13.5) {
time->tm_mon = (int)(g - 1);
}
else {
time->tm_mon = (int)(g - 13);
}
if (time->tm_mon > 2) {
time->tm_year = (int) (d - 4716);
}
else {
time->tm_year = (int) (d - 4715);
}
}
void copy_lstring(char *dst, char *src, int len) {
char *p = dst, *q = src;
int idx;
for (idx=0; idx
[ADMIN] [Fwd: inserting integer in Table]
>
> In PostgreSQL table is :
>
> test=> \d test_blob_s;
> Table= test_blob_s
> +--+--+---+
> | Field | Type|
> Length|
> +--+--+---+
> | filenumber | int4
> | 4 |
> | filename | varchar()
> | 100 |
> | filecontent | oid
> | 4 |
> +--+--+---+
> test=>
>
>
> Now I'm willing to insert in that table rows like this one :
> , , .
>
> Here's is the (not-so-running) code :
>
>
> #
> #
> #
> import os, glob
> from pg import DB
>
> conn=DB('test')
> conn.query("BEGIN WORK;")
>
> i = 0
> for name in glob.glob("*.jpg"):
> print "Name is ", name,
> try:
> conn.query("insert into test_blob_s filenumber values (" + i +
> ")")
> print " inserted"
> except TypeError,e:
> print '\nerror ', e, 'undefined'
> #conn.query( "update test_blob_s set filename = '" + name + "')")
> #conn.query( "update test_blob_s set filecontent = '" +
> lo_import(name) + "')")
>
> i = i + 1
>
> conn.query(" COMMIT WORK;");
>
>
>
> of course I can't have it passing the insert of "i"
> Could some one help ?
>
> Thomas,
--
Thomas SMETS Phone : +32 (0)2 742. 05. 94.
Av. de la Brabançonne 133 / 3 e-mail : tsmets @altern.org
1030 Bruxelles
Quote of the day =
You shouldn't wallow in self-pity. But it's OK to put your feet in it
and swish them around a little.
-- Guindon
=== End of Quote =
Re: [ADMIN] Implementing mod_perl, Perl 5.004, and Postgres
On Thu, 9 Nov 2000, Jimi Thompson wrote:
> I keep getting error messages from my CGI scripts regarding a file
> called Pg.pm. Since this appears to be a perl module, I was wondering
> if anyone knows how to go about making Perl and Postgres like each other
> :)
If your use directive in your perl routine states:
use DBI;
And you are getting stderr complaining about missing Pg in @INC
then you are missing DBD::Pg. Grab it from CPAN and do a build.
This presumes you have the prerequisite: DBI.pm
Apache ---> DBI.pm ---> DBD::Pg (Pg.pm) ---> Postgres
|<-- Perl -->|
Obviously I have a great future behind me in cartography but I
think you get the idea: DBI is a generic interface that uses
a specific driver to access Pg. DBI and DBD are perl modules.
Good hunting,
Tom
SVCMC - Center for Behavioral Health
----
Thomas Good tomg@ { admin | q8 } .nrnet.org
IS Coordinator / DBA Phone: 718-354-5528
Fax: 718-354-5056
Powered by: PostgreSQL s l a c k w a r e FreeBSD:
RDBMS |-- linux The Power To Serve
[ADMIN] Lack of Performance
Hi all! I wonder if there is any possibilty to boost the performance of my postgresql machine. I used to have it running on a dual p3-450 + 512mb ram (with apache/php on the same machine). Recently I moved it to a new machine, p3-800 + 512mb ram, and moved the apache to another machine. Now the performance is somewhat BAD. The load of the machine is arround 10.00 and the postmaster processes are taking all of the machines cpu power. I don't have correct stats to my hand but the web server is currently handling something about 220.000 page impressions per day (220.000 page calls which open a php -> db connection running at least 1 query). I didn't went to deep into query optimization yet cause I didn't read enough about that now. So I tried adjusting the command line options What are the best values to use for sort_mem and shmem_buffers? I'm currently starting the service with -B 256 -i -N 48 -o '-F -S 512' This seems NOT to be the right value but I don't know it what way I can experiment with them? If somebody has some tips handy i'd like to hear them. Thanks, Thomas
Re: [ADMIN] Lack of Performance
Hi again! > > -B 256 > > -i > > -N 48 > > -o '-F -S 512' > > > I have a gig of ram and use: > > -B 32768 > -o "-F -S 65534" > Hmmm, during peak time these values have no influence to perfomance at all. :( The values help to decrease the load during "not-so-busy" times, but during peak times the load is still arround 12-20. This is absolutly inacceptable for me. What I dont understand about it, is that the DB is not THAT big. The tables are arround 10.000-30.000 rows and there are only about 6 tables. They all use indexes where needed and everything is vacuumed up to 8 times a day. But the load is not affected by it. I can't seem to find "what" is pressing the db. Does query optimization help a lot or does it only affect the performance in a little manner? Most queries look for rows with a specific primary id and return parts/the whole row. Any optimizations hints? Thanks, Thomas
Re: [ADMIN] Perfomance of postgresql-7.0.3-2
Hi, Maybe use less columns/table if possible. It is possible the client to share the load with the server ? Some processing can still be done by the client. Btw, do you measure the system performance (disk access, cpu load, bla bla bla on the OS). I tried using "hdparm" to increase my disk access speed on my server and it's help. hopefully this suggestion will help.. Regards, Thomas wong Karla Peralta wrote: > > > Hi, > > I have some problems with the perfomance when I run my program > and I'm just testing in two machines. > > I have a diskless system. > > This is what I have: > > System Configuration: > > Server > > Architecture:Pentium II > Memory :64Mb > Operating System:RedHat 6.2 > PostgreSQL version :7.0.3-2 > Compiler used :FlagShip-4.48-7451 > Others :FS2tools-4.48-7451 > SQLkit_PG-1.00 > > > Client - Diskless > > Memory : 16Mb > Architecture: Pentium > Printer : TM930 Paralell Port > > My database is 268 Mb, 60 tables, the largest table has about 181659 > rows and 46 columns. I'm using index in each table. > > When the program is running it stop for about 15 seconds and then > continues. I'd like to know what can I do for a better perfomance > because I need 40 clients machines. > > Tell me if you need more information. > > Regards, > > Karla
[ADMIN] postgres getting bigger and bigger ...
Hi there! I just wondered if it is normal that a running postmaster is getting bigger and bigger the more time it runs. I have configured by postmaster to accept a maximum of 64 sessions. When I start it each session is about 1,5 - 3mb of RAM. After 3-4 days each process is about 140mb of RAM and my machine starts swapping a lot. (1gig RAM). When I shutdown the DB and restart it each process is small again but they are growing ... through that the db gets slower and slower and I wonder how i can get by this high mem usage? Im running on Redhat 6.2 (p3-800 + 1gig ram). thnx, Thomas
[ADMIN] FATAL 1: Memory exhausted in AllocSetAlloc()
Hi there! The last db-crash (kernel panic) caused a little error on my db-files i think. After the crash i had to recreate some indexes but no data were lost. Everything is running quite ok now, except for one BIG error. The postgres process crashes every now and then without giving any information on any reasons. :( AND I can't use pg_dumpall, vacuumdb -z. All the time I run a program that is supposed to dump my database or analyze it. I get the following error from the program: FATAL 1: Memory exhausted in AllocSetAlloc() pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. connection to server was lost vacuumdb: vacuum failed pg_dumpall: pg_dumpall failed I'm running a postgres-7.0.3 on a p3-800 with 768mb ram. postgres is the only thing running on that machine. The Database Directory is 500mb big. I have several tables (some arround 5k entries and about 5 tables are >100k entries). The DB is heavy loaded (linux load of 6-20 at main time) and I plan to move it to a bigger machine. The only problem is that i can't dump my db. :( Somebody got any advices how I can dump or vacuum my db again? thanks in advance, thomas
[ADMIN] find bad record
Hi there, i have a little problem with one of my tables. can someone explain me how to find that record? vacuum says: NOTICE: Rel mxaccounts: TID 226/18: OID IS INVALID. TUPGONE 0. VACUUM mxaccounts is my table, but how do i find the record causing this notice? thanks in advance, thomas
[ADMIN] postgres & smp
i hope this list is somewhat the right one for this ... i did some small tests with postgresql-71.beta4 on various filesystems (ext2, reiserfs, xfs) on two machines: 1 single cpu and one 2 cpu smp and was a bit surprised to see the tps results of the smp case to be lower than the one of the up case (also the running times of the used pgbench said the same) ... i was thinking that the load should spread over the cpu's and thus expected better results for smp - is there anything to take care of for smp with postgresql? did i anything wrong here? a lot of thanks in advance t p.s.: btw. i in all cases see about 15% better results with the database running on an xfs filesystem compared to ext2 ... reiserfs is about the same as ext2 but some- times even worse ... just if anyone is interested ... -- [EMAIL PROTECTED] innominate AG the linux architects tel: +49-30-308806-13 fax: -77 http://www.innominate.com
Re: [ADMIN] postgres & smp
just to avoid confusion - some more things: * smp and up case compared here were done on the same machine (linux 2.4.1-XFS, smp kernel booted with or without "nosmp") * the used hardware is a 2 pII333 128mb ide disk * the numbers were generated using pgbench from postgres-7.1beta4 with -c 32 -s 20 -t 100 * the results are ranging from ~17tps (reiserfs/smp) ... ~20-23tps (xfs/smp-up) one idea i have so far is that the pgbench queries are that simple that the whole benchmark is heavily disk bound and the second cpu only results in locking overhead resulting in a bit lower numbers) may this be the reason for the missing improvement in the numbers in the smp case? again - a lot of thanks in advance t Thomas Graichen <[EMAIL PROTECTED]> wrote: > i hope this list is somewhat the right one for this ... > i did some small tests with postgresql-71.beta4 on various > filesystems (ext2, reiserfs, xfs) on two machines: 1 single > cpu and one 2 cpu smp and was a bit surprised to see the tps > results of the smp case to be lower than the one of the up case > (also the running times of the used pgbench said the same) > ... i was thinking that the load should spread over the cpu's > and thus expected better results for smp - is there anything > to take care of for smp with postgresql? did i anything > wrong here? > a lot of thanks in advance > t > p.s.: btw. i in all cases see about 15% better results with > the database running on an xfs filesystem compared to > ext2 ... reiserfs is about the same as ext2 but some- > times even worse ... just if anyone is interested ... > -- > [EMAIL PROTECTED] > innominate AG > the linux architects > tel: +49-30-308806-13 fax: -77 http://www.innominate.com -- [EMAIL PROTECTED] innominate AG the linux architects tel: +49-30-308806-13 fax: -77 http://www.innominate.com
Re: [ADMIN] postgres & smp
Tom Lane <[EMAIL PROTECTED]> wrote: > Thomas Graichen <[EMAIL PROTECTED]> writes: >> i hope this list is somewhat the right one for this ... >> i did some small tests with postgresql-71.beta4 on various >> filesystems (ext2, reiserfs, xfs) on two machines: 1 single >> cpu and one 2 cpu smp and was a bit surprised to see the tps >> results of the smp case to be lower than the one of the up case > Hm, did you set commit_delay to zero? What are the other postmaster > parameters (especially -B) ? i used 32 clients -N set to 128 and -B to 256 - commit_delay was set to 5 (default) - do you expect much better smp results with commit_delay=0? what exaclty does commit_delay=0 mean or where can i find docs about this (and some of the other new parameters)? again - a lot of thanks in advance t -- [EMAIL PROTECTED] innominate AG the linux architects tel: +49-30-308806-13 fax: -77 http://www.innominate.com
Re: [ADMIN] v7.1b4 bad performance
> ... See included png file. What kind of machine was this run on? - Thomas
[ADMIN] {ADMIN} Re: pg_dump potential bug
> Thanks for the help, in fact that is Exactly what is wrong. The pg_shadow > table has duplicated entries in it, I think I'm going to create a unique > index on it. Forgive me if I'm wrong about this, but doesn't the SQL Standard prohibit duplicate rows in a table, unique index or not? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] Re: [PATCHES] Error in the date field (with NULL value...).Thanks!
> How can I "binds" PostgreSQL to consider '' as null ?
You can modify src/backend/utils/{datetime,date,timestamp}.c to accept
an empty string as a null value. But imho it is better to fix this in
your input file, perhaps using sed:
sed "s/''/NULL/g" < informix.dump > pg.dump
- Thomas
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[ADMIN] Debian packages for 7.1 ???
Where can I find debian packages for PostgreSQL 7.1 ?? Thomas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] 7.1 performance
Hi there, i just upgraded my database system from 7.0.3 to 7.1 ... i did a dumpall and psql -f dump.sql to the new db. the data is in place correctly and its running fine. but as soon as i put the connections back on to the db the load raised to 6. prior to the upgrade the system load was an 0.3 ... now it is at 6. is this normal for 7.1 to need so much more hardware? i use the same settings for sort_mem, buffers, etc. just no fsync. I thought 7.1 wouldn't need that anymore. Any suggestions that i should do? the performance is really bad now :( btw: the db is a dedicated p3-800 + 1gig ram + uw scsi drives running redhat6.2 thanks cu, thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Re: 7.1 performance
Hi, > > i use the same > > settings for sort_mem, buffers, etc. just no fsync. I thought 7.1 wouldn't > > need that anymore. > > Does the behavior change if you turn off fsync again? No. The Database is loaded with up to 25req/s ... most of them are just simple SELECTS (no ORDER BY, no JOINS) ... i use indexes on all important fields. Some queries just return 1 row ... some even 1.000 ... when i run these queries in psql they seem fast and not problematic. (i did no query optimizing yet) Note: its a backend database to a webpage (php). There are arround 8 postgres processes running on the machine. Through the mass of Queries i can't really say WHO is causing the load to the machine. As the load raised to about 17 after 1 hour online, i switched back to 7.0.3 ... now the machine is at load 0,3 again and everything is fine. Here are some setting from my postgresql.conf log_connections = off fsync = on (also off tested) tcpip_socket = true max_connections = 32 sort_mem = 4096 shared_buffers = 128 wal_buffers = 8 wal_files = 8 wal_sync_method = fsync wal_debug = 0 commit_delay = 0 commit_siblings = 5 checkpoint_segments = 3 checkpoint_timeout = 300 i would really be interested in using 7.1 cause i set it up for a new project (running a dual-p3-1ghz + 2 gig ram) and this machine really needs good performance and i use (binary &|) which wheren't available in <7.1 afaik. thanks, thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Re: Re: 7.1 performance
Hi, > Two things you should check. > > 1) Are you using persistent connections in your PHP scripts? not that good for 250+ apache httpd sessions running (only serving php ... images/static content is balanced to another server). ;) definitly no. > 2) Are you certain your queries are utilizing the indices you have set up > for them. they use indices .. i'm pretty sure with it cause i didn't change any indices as i pg_dumpall'd the db and restored it to 7.1. As said .. the 7.0.3 is laughing about the traffic (load 0.3) and the 7.1 is about to break (load 16). No change in php conf ... the only change is the upgrade to 7.1. hmpf (i hope the next dual p3-1ghz won't break with 7.1, i need it there ...) cu thomas ---(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
[ADMIN] Re: 7.1 ... wtf?
Hi, hmm I don't know if I'm right but I think when I run the postmaster with default values configured it should run at least in a acceptable manner. But it does not. Also it does not respond to any minor tweaks like increasing wal-buffers, wal-files, sort-mem, etc. ... Its just doing his weird actions and performance is quite bad. I upgraded to 7.1.1 yesterday and i hope it will survive today without any major problems. Looks good yet. Also I would greatly appreciate any help concerning configuration options etc. ... cu mx "August Zajonc" <[EMAIL PROTECTED]> wrote in message 9dc883$1h9g$[EMAIL PROTECTED]">news:9dc883$1h9g$[EMAIL PROTECTED]... > I'm assuming you've read the manual carefully. > > Please make doubley sure you configured PostrgreSQL and your system > properly. Server runtime enviroment comes to mind. > > Check the error logs, its hard to beleive you are getting such blow ups with > absolutly nothing in any of the logs. > > Have you turned on the most verbose loging so you can see all the > transactions on your system? Be suprising if you are still getting nothing. > > If you think performance is bad as hell, and it's clearly not a > configuration or user issue (ie, pgsql is at fault), consider writing up a > proper bug report. The fact that a good number of folks successfully use > pgsql for larger installations argues I think for a configuration issue, but > it could certainly be a bug somewhere. > > AZ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] 7.1 ... wtf?
Hi again, sometime ago i had a post on 7.1 performance .. this is still up-to-date cause the performance is bad like hell but i have a little bit bigger problem now. I have a dual p3-1ghz + 1gig ram. The hardware is new (asus dual board + registered ecc sdram) and is quite nice but postgres7.1 seems to mess up with it. I have no idea why, but the server kills himself every now and then. the load goes up to 50 and then some minutes later its gone (doesn't respond to ANY cmds, no ssh, no shell). i always have to reset it. the only thing i found pointing me towards postgres are these error messages when surfing the pages which use the server as backend. I get these errors in a random manner: The System is in maintenance mode (something like that) The System is in recovery state The System is shutting down. The System is going up. Sorry, to many clients already (2 processes running / 64 max connections configured) Note: I do not restart the postmaster nor does any script. the system is started and running .. why does he say "shutting down"?!! also the performance seems to be really crap. (the database is currenly 80mb nothing THIS server should worry about). any help? thank, thomas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] Cost limit.
Hey all, I can't seem to find it in the docs, so I'll ask here. Is there a way in postgres to impose a limit on query costs? I'd noticed some horribly disfigured queries hitting my poor database from one of our developers, and corrected him. But many of our users are not so easily contacted. What I want to know is if there's a configuration parameter or source patch that will allow me to disallow a query execution if the pre-execution cost estimation is too high. The reason I'm asking? Our developer accidentally created a cross product that resulted in an explain-plan cost of approximately 1.3 trillion. Before we tracked the problem down, the application causing the query would halt for an answer indefinitely. Even after stopping the application, postgres would grind on. The load on the machine was hovering around 20, with 5 postgres threads sharing the blame. I'd hate for any user to have the power to cripple our database server by simply writing a bad query. Should I contact the developers list on this and request a patch? Could someone direct me to the correct piece of source code to... er... enhance? Thanks in advance. -- +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ | Shaun M. ThomasINN Database Programmer | | Phone: (309) 743-0812 Fax : (309) 743-0830| | Email: [EMAIL PROTECTED]AIM : trifthen | | Web : hamster.lee.net | | | | "Most of our lives are about proving something, either to | | ourselves or to someone else." | | -- Anonymous | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] Killing Processes
Oops. That's right. I was relying on memory, which is less reliable than man. Fortunately, I always consult man before killing... :P -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Apr 23, 2005, at 11:16 AM, Michael Fuhr wrote: On Sat, Apr 23, 2005 at 10:58:46AM -0500, Thomas F.O'Connell wrote: It is generally not recommended that you kill processes with anything stronger than HUP, which is (I believe) what kill sends by default. kill usually sends TERM by default, not HUP. (I say "usually" only because I haven't personally examined every implementation of kill in existence. Every one I've used sends TERM by default and I haven't heard of any that work otherwise.) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] [ANNOUNCE] IMPORTANT: two new PostgreSQL security problems found
Considering that this is a security-related system catalog update, is
there any way of providing some sort of signature for a message like
this such that the community can feel that issuing some arcane commands
as a superuser won't open a hole rather than close one?
This is the first time I've seen an announcement of this sort regarding
PostgreSQL, and I'm just curious about the release mechanism for it.
I doubt if anyone is spoofing Tom, but in an era of phishing and
spoofing, one can't be too sure, especially if one is concerned about
security...
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On May 2, 2005, at 3:06 PM, Tom Lane wrote:
Two serious security errors have been found in PostgreSQL 7.3 and newer
releases. These errors at least allow an unprivileged database user to
crash the backend process, and may make it possible for an unprivileged
user to gain the privileges of a database superuser.
We are currently preparing new releases that will correct these
problems
in freshly initdb'd installations. However, because these problems are
really incorrect system catalog entries, updating to a new release will
NOT by itself solve the problems in an existing installation. Instead,
it is necessary for the database administrator to fix the catalog
entries
manually, as described below. We are releasing this advisory to
encourage
administrators of PostgreSQL installations to perform these fixes as
soon
as possible.
Character conversion vulnerability
--
The more severe of the two errors is that the functions that support
client-to-server character set conversion can be called from SQL
commands
by unprivileged users, but these functions are not designed to be safe
against malicious choices of argument values. This problem exists in
PostgreSQL 7.3.* through 8.0.*. The recommended fix is to disable
public
EXECUTE access for these functions. This does not affect normal usage
of
the functions for character set conversion, but it will prevent misuse.
To accomplish this change, execute the following SQL command as a
superuser:
UPDATE pg_proc SET proacl = '{=}'
WHERE pronamespace = 11 AND pronargs = 5
AND proargtypes[2] = 'cstring'::regtype;
In 7.3.* through 8.0.*, this should report having updated 90 rows.
7.4 and later will report a "WARNING: defaulting grantor to user ID 1"
which can be ignored.
The above command must be carried out in *each* database of an
installation, including template1, and ideally including template0 as
well. If you do not fix the template databases then any subsequently
created databases will contain the same vulnerability. template1 can
be fixed in the same way as any other database, but fixing template0
requires additional steps. First, from any database issue
UPDATE pg_database SET datallowconn = true WHERE datname = 'template0';
Next connect to template0 and perform the pg_proc update. Finally, do
-- re-freeze template0:
VACUUM FREEZE;
-- and protect it against future alterations:
UPDATE pg_database SET datallowconn = false WHERE datname =
'template0';
tsearch2 vulnerability
--
The other error is that the contrib/tsearch2 module misdeclares several
functions as returning type "internal" when they do not have any
"internal" argument. This breaks the type safety of "internal" by
allowing users to construct SQL commands that invoke other functions
accepting "internal" arguments. The consequences of this have not been
investigated in detail, but it is certainly at least possible to crash
the backend.
This error affects PostgreSQL 7.4 and later, but only if you have
installed the contrib/tsearch2 module. The recommended fix is to
change the misdeclared functions so that they accept an "internal"
argument and therefore cannot be called directly from SQL commands.
To do this, execute the following command as a superuser:
UPDATE pg_proc SET proargtypes[0] = 'internal'::regtype
WHERE oid IN (
'dex_init(text)'::regprocedure,
'snb_en_init(text)'::regprocedure,
'snb_ru_init(text)'::regprocedure,
'spell_init(text)'::regprocedure,
'syn_init(text)'::regprocedure
);
This should report 5 rows updated. (If it fails with a message
like "function "dex_init(text)" does not exist", then either tsearch2
is not installed in this database, or you already did the update.)
You will need to do this in *each* database in which you have installed
tsearch2, including template1. You need not worry about template0,
however, since it will cer
Re: [ADMIN] IMPORTANT: two new PostgreSQL security problems found
I put together a little Perl script (which assumes proper installation of both DBI and DBD::Pg and that template1 exists) that takes care of the character conversion vulnerability: http://www.sitening.com/postgresql-update-2005-1 I've run this on my development servers, and it seems to have had the anticipated effect, but, as always, more eyeballs help. If anyone notes any potential showstoppers, I'll gladly update the script. I don't have tsearch2 installed anywhere, so I didn't bother with that, but this script could probably be easily modified to address that vulnerability. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] wal logging
Database recovery from write ahead logs is only supported in PostgreSQL 8.0.0 and later. --- hi I have to setup some backup policies for our postgres server. I had a few queries regarding corruption of database. what are the steps to be followed in postgres regarding logical corruption, instance failures, block corruption, log corruption? I am using postgres 7.3.9 I was checking out WAL logging. I enabled WAL logging in postgres.conf I found the logs in the pg_xlog directory but don't know how to recover it thanks, kailash :. CONFIDENTIALITY : This e-mail and any attachments are confidential and may be privileged. If you are not a named recipient, please notify the sender immediately and do not disclose the contents to another person, use it for any purpose or store or copy the information in any medium. ---(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] Import Oracle to Postgres
On Monday 11 July 2005 22:05, [EMAIL PROTECTED] wrote: | Ok, I am new to this backend arena for Postgres. I have been reading to | find out how to take a oracle database and put it into a postgres | database. Can someone refer me to a note, article, etc, that explains | the process in detail. there's a Perl script in the PostgreSQL contrib/oracle directory, that does a good job after some tweaking. Ciao, Thomas Pundt -- Dr. Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] plperl
Did you build from source? Did you explicitly enable PL/Perl during configuration?You need to specify --with-perl to configure to get it to build:http://www.postgresql.org/docs/8.0/static/install-procedure.html --Thomas F. O'ConnellCo-Founder, Information ArchitectSitening, LLCStrategic Open Source: Open Your i™http://www.sitening.com/110 30th Avenue North, Suite 6Nashville, TN 37203-6320615-469-5150615-469-5151 (fax) On Sep 12, 2005, at 1:57 AM, Nirav Parikh wrote: Hi there, I am trying to install plperl language with command "createlang plperl template1" I am getting error message "language installation failed: ERROR: could not access file "$libdir/plperl": No such file or directory" Does anybody know how to fix this? Dont have much experience with linux. Help would be really appreciated. Regards, Neo -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.21/96 - Release Date: 10/09/2005
Re: [ADMIN] Problems when initdb on WinXP with SP2.
Johnson Zhao wrote: Hi, I use Postgresql 8.03/ 8.04 on windows xp with sp2, all have same problems. run the windows cmd by: runas /user:postgres cmd and run the initdb program like: initdb --locale=C --username=postgres -W -A md5 -E UNICODE -D ../data then it says: creating directory ../data/ ... ok creating directory ../data/global ... ok creating directory ../data/pg_xlog ... ok creating directory ../data/pg_xlog/archive_status ... ok creating directory ../data/pg_clog ... ok creating directory ../data/pg_subtrans ... ok creating directory ../data/base ... ok creating directory ../data/base/1 ... ok creating directory ../data/pg_tblspc ... ok selecting default max_connections and the command window stayed there! I have no idea whether this matters or not, but I got bit during an 8.1 install by permissions issues. The postgres user requires read/write permissions for the data folder and below, but it also requires read permissions all the way back to the root of the drive (including the root directory). The specific situation for us was that we were trying to install to D:\PostgreSQL\Data, but the postgres user didn't have read permissions on the root of D:\ (or D:\PostgreSQL). ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[ADMIN] 8.1: import of 8.0 dump fails with UTF-8 error
Hello, I did a pg_dumpall of all my Pg 8.0.3 databases, removed 8.0, installed 8.1 and tried to import the dump. One table in one database failed with: ERROR: invalid UTF-8 byte sequence detected near byte 0x83 CONTEXT: COPY pwd_name, line 22428, column name: "t.tonnement" So I exported that database with 8.0 as Inserts to a text file and tried to fix it using iconv, but that fails as well: # iconv -f UTF-8 -t UTF-8 dump.sql > dump-fixed.sql iconv: illegal input sequence at position 252 How can I fix the sql script to import it? I have Debian Linux 3.1. Thanks a lot, Thomas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] testing pgpool
Is postgres actually running? And is your pg_hba.conf file configured to allow local UNIX socket connections? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 16, 2005, at 10:50 AM, ESPARZA JUAREZ EDUARDO wrote: Hi list!! I tested pgpool with pgpool.conf as follows: listen_addresses = 'localhost' port = socket_dir = '/tmp' backend_host_name = '' backend_port = 5432 backend_socket_dir = '/tmp' secondary_backend_host_name = '' secondary_backend_port = 0 num_init_children = 15 max_pool = 10 child_life_time = 300 connection_life_time = 0 logdir = '/tmp' replication_mode = false replication_strict = true replication_timeout = 5000 load_balance_mode = false weight_master = 0.5 weight_secondary = 0.5 replication_stop_on_mismatch = false reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT' print_timestamp = true master_slave_mode = false connection_cache = true health_check_timeout = 20 health_check_period = 0 health_check_user = 'nobody' but when I try get internal status of pgpool (psql -p -c 'show pool_status' template1) I get: psql: could not connect to server: [EMAIL PROTECTED]@[EMAIL PROTECTED] Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL."? Thank’s Eej- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Pg_autovacuum script.
A while back, I wrote this:
http://www.sitening.com/auto_pg_autovacuum
While not exactly an init script, it sanely avoids multiply starting
pg_autovacuum.
Then I did this:
http://www.sitening.com/pgautovacuum
which is a proper init script.
Thanks for prompting me to post it! Now there are some options!
But I'm hopeful that autovacuum will be integrated into 8.1, thereby
laying this issue to rest.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Apr 10, 2005, at 10:19 PM, Theo Galanakis wrote:
Since I could not locate anything similar on the net, I have written a
script to automatically start and stop the autovacuum daemon. This
should be added to the existing postgresql script.
Please let me know if you can see any shortcomings. I have taken into
consideration that more than one postmaster may be running on the same
machine, so I have stored the pg_autovacuum pid into a file, which is
referenced when bringing down the postmaster instance. I have not gone
to the effort of including all the pg_autovacuum parameters, you can
add them at will.
One issue I can foresee is maintainability, where you will have to
update the postgresql script file with the snippets below, every
postgres upgrade.
Here are the snippets :
This goes where all the other init scripts are located.
# Initialize pg_autovacuum defaults. Theo Galanakis 11/04/2005
PGAUTO_LOGFILE=/var/lib/pg_autovacuum.log
PGAUTO_SLEEP=60
Added to the end of the Start function.
# Start pg_autovacuum. Theo Galanakis 11/05/2005
PAUTO_START=$"Starting pg_autovacuum service: "
echo -n "$PAUTO_START"
pg_autovacuum -D -s ${PGAUTO_SLEEP} -p ${PGPORT} -U postgres
-L ${PGAUTO_LOGFILE}
pg_autovacuum_pid=`pidof -s $PGENGINE/pg_autovacuum`
if [ $pg_autovacuum_pid ]
then
success "$PAUTO_START"
echo $pg_autovacuum_pid >
/var/run/pg_autovacuum.${PGPORT}.pid
echo
else
failure "$PAUTO_START"
echo
fi
Added to the end of the Stop function.
# Stop pg_autovaccum. Theo Galanakis 11/04/2005
echo -n $"Stopping pg_autovacuum service: "
pg_autovacuum_pid=`head -n 1
/var/run/pg_autovacuum.${PGPORT}.pid`
kill -TERM $pg_autovacuum_pid
ret=`ps --no-heading ${pg_autovacuum_pid}`
if [ -z "$ret" ]
then
echo_success
else
echo_failure
fi
echo
rm -f /var/run/pg_autovacuum.${PGPORT}.pid
__
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
---(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 6: explain analyze is your friend
Re: [ADMIN] Table Partitioning
Ken, Table partitioning doesn't really exist, to the best of my knowledge, but tablespaces, which are new to 8.0, allow for partitioning of data (individual tables, indexes) across multiple locations on disk. See http://www.postgresql.org/docs/8.0/static/sql-createtablespace.html -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 3, 2005, at 3:56 PM, Ken Reid wrote: I am new to postgres and was wondering if table partitioning is supported in Postgres. And if so what version and where can I find documentation on it. - Thank You - Ken Reid ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] Log4...
Dear, I was wondering if they were a set of stored procs like the Log4PLSQL available for PostgreSQL ... ? Tx, \T, -- Thomas Lionel SMETS rue J. Wytsmanstraat 62 1050 Brussels T : +32 (0)2 742 05 94 M : +32 (0)497 44 68 12 @ : [EMAIL PROTECTED] Do you skype (callto://tsmets) ? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[ADMIN] remove
remove _ Because e-mail on your cell phone should be easy: Try Windows Live Mail for Mobile beta http://www2.imagine-msn.com/minisites/mail/Default.aspx?locale=en-us ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[ADMIN] delete
delete _ Because e-mail on your cell phone should be easy: Try Windows Live Mail for Mobile beta http://www2.imagine-msn.com/minisites/mail/Default.aspx?locale=en-us ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] remove
remove > Subject: Re: [ADMIN] Database wont start anyway > From: [EMAIL PROTECTED] > To: [EMAIL PROTECTED] > CC: [email protected] > Date: Thu, 8 Jun 2006 18:12:55 +0200 > > Am Donnerstag, den 08.06.2006, 11:44 +0200 schrieb Fourat Zouari: > > Hello all, > > My database wont start, here's the log : > > > > tux2:~# /etc/init.d/postgresql-8.1 start > > Starting PostgreSQL 8.1 database server: main* perl: warning: Setting > > locale failed. > > perl: warning: Please check that your locale settings: > > LANGUAGE = "fr_TN:fr_FR:fr:en_GB:en", > > LC_ALL = (unset), > > LANG = "[EMAIL PROTECTED]" > > are supported and installed on your system. > > perl: warning: Falling back to the standard locale ("C"). > > Error: The server must be started under the locale [EMAIL PROTECTED] which > > does not exist any more. > > failed! > > It looks like a linux system. Or which OS do you use? > > Do you tried to start PG as root (because of the #)? Are you sure root > is allowed to start it? Is root superuser? > > Try to start it as user postgres. > > all installed locales will listen by: > $ locale -a > > I have no french locale installed on my debian system, but you will get > a list like this: > > C > [EMAIL PROTECTED] > de_DE.iso885915 > [EMAIL PROTECTED] > > When I run locale, that list will diplayed: > > $ locale > > [EMAIL PROTECTED] > LANGUAGE=de_DE:de:en_GB:en > > It is the same, as your list. Only fr_FR (France) instead de_DE > (Germany). > > So I am sure, the problem is, that you tried to start postgresql as root > and not as superuser. The standard superuser is postgres. > > Try this: > tux2:~# su postgres > [EMAIL PROTECTED]:~$ /etc/init.d/postgresql-8.1 start > > Susanne _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] Aborting transactions
Hi, we use postgres for student's education and others. Now we had an issue with some SQL statement in connection with some triggers, that probably caused an infinite loop in processing for at least 10 hours or so on some two to ten line tables. Unfortunately, the students used the phpPgAdmin web interface, so they could not abort the running SQL statement. They could not even continue to work with the database from phpPgAdmin. So the question arose, how to abort running statements at least via the postgres account. I remember some methods from Ingres "some" years ago, but I did not find anything similar in postgresql. Is there any chance to monitor the current transactions and abort them, in case of need? Thanks, Thomas Mack TU Braunschweig, Institut für Informationssysteme ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Aborting transactions
Am Montag, 26. Juni 2006 22:15 schrieb Tom Lane: > [EMAIL PROTECTED] writes: > > I don't have a pg_stat_activity on this 7.4.6, so using ps gives me: > > > > [EMAIL PROTECTED] ~ 4 > ps -ef | grep postmaster > > postgres 4499 4498 0 08:59:18 ? 0:04 > > /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e > > What platform is that? If it's Solaris, see our doc/FAQ_Solaris about > getting more useful info from "ps". > Yes, it's Solaris 10. Looking at http://www.postgresql.org/docs/faqs.FAQ_Solaris.html , I did not find anything related. Same with the doc/FAQ_Solaris from the postgresql-8.1.4 sources. ... Ok, I tried on a Linux server with 8.0.0, where I could get some useful informations out of ps. I tried /usr/ucb/ps on Solaris, which revealed more information, but I did not see a test query from a remote client for some magic reason (select * from t1, t2 with 250,000 and 512,000 rows). I think, I can live with it for now, as I will upgrade later this year. Thanks, Thomas Mack TU Braunschweig, Institut für Informationssysteme ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Aborting transactions
Am Dienstag, 27. Juni 2006 15:47 schrieb Tom Lane: > Thomas Mack <[EMAIL PROTECTED]> writes: > > Am Montag, 26. Juni 2006 22:15 schrieb Tom Lane: > >> What platform is that? If it's Solaris, see our doc/FAQ_Solaris > >> about getting more useful info from "ps". > > > > Yes, it's Solaris 10. Looking at > > http://www.postgresql.org/docs/faqs.FAQ_Solaris.html , I did not find > > anything related. Same with the doc/FAQ_Solaris from the > > postgresql-8.1.4 sources. > > Oh, sorry, I assumed it was in the Solaris FAQ, but actually the info > is at the bottom of this page: > Ok, it might be reasonable to duplicate it in the Solaris FAQ though... > http://www.postgresql.org/docs/8.1/static/monitoring.html > > There are several different conditions that can render "ps" useless on > Solaris :-(, and one of them is sprinkling the postmaster start command > with a lot of switches. Put that stuff in postgresql.conf, instead, > so you can invoke the postmaster as just "postmaster". > Oh yes. This explains, why I was missing quite some connections even with /usr/ucb/ps. Might be good to tell in the documentation to move the switches to postgresql.conf . Might otherwise produce additional questions... Oh, well, the 7.4 documentation states these things already as it does in 8.1. Sorry, don't know why I missed it. Maybe I had some other ideas in mind. Not monitoring, but administering or something like this. Thanks for your help, Thomas Mack TU Braunschweig, Institut für Informationssysteme ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] *** How can I install two postgresqls in the same machine? *****
Am Freitag, 14. Juli 2006 08:36 schrieb kitaeda: > I want to install two postgresqls in the same machine. > How can I do so? > What do I have to specific configure options? > You have to have them in different locations obviously, so you might use a different --prefix=... for both on configure. And you have to run them on a different port. This works for us back since a 6.5 version of postgres. Thomas Mack TU Braunschweig, Institut für Informationssysteme ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] DBA tasks
Fine but in the long run, we'd need something like Donald Burleson's web site / books with extended internals explainaintions, no ... ? \T, Michael Fuhr wrote: >On Mon, Jul 24, 2006 at 08:43:15AM +1000, Mark Matthews wrote: > > >>Can anyone recommend good reference text, web or other, for general >>PostgreSQL DBA tasks. >> >> > >The PostgreSQL documentation is a good place to start. See "Server >Administration," especially the chapters concerning routine maintenance, >backup and restore, and monitoring. > >http://www.postgresql.org/docs/8.1/interactive/admin.html > >(Adjust the link to match the version you're running.) > > > >>Stuff like: >>* Things to do to clean up periodically (CLUSTER, VACUUM FULL, etc) >> >> > >See above. > > > >>* How to analyse queries to optimise data retrieval (Use of EXPLAIN, >>etc) >> >> > >See "Performance Tips" in the documentation and the tuning documents >at powerpostgresql.com. You could also peruse the list archives, >especially pgsql-performance, to see how real performance problems >have been investigated and addressed. For example, search for >threads that contain the words "EXPLAIN ANALYZE". > >http://www.postgresql.org/docs/8.1/interactive/performance-tips.html >http://www.powerpostgresql.com/Docs/ >http://archives.postgresql.org/pgsql-performance/ > >The General Bits newsletter has interesting articles; see the >Archives and General Tidbits links. > >http://www.varlena.com/varlena/GeneralBits/ > > > >>We are running Debian stable (sarge) and therefore 7.4. Moving to 8.1 >>soon (hopefully) >> >> > >Before upgrading read the Release Notes for all newer versions to >see what changes you'll be getting. See also "If You Are Upgrading" >in the Installation Instructions and read the Backup and Restore >chapter, which has a "Migration Between Releases" section. > >http://www.postgresql.org/docs/8.1/interactive/release.html >http://www.postgresql.org/docs/8.1/interactive/install-upgrading.html >http://www.postgresql.org/docs/8.1/interactive/migration.html > > > -- Thomas SMETS rue J. Wytsmanstraat 62 1050 Brussels T. : +32 (0)2 742 05 94 M. : +32 (0)497 44 68 12 @ : [EMAIL PROTECTED] begin:vcard fn:Thomas SMETS n:SMETS;Thomas email;internet:[EMAIL PROTECTED] tel;home:+32 (0)2 742 05 94 tel;cell:+32 (0)497 44 68 12 version:2.1 end:vcard ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] Controlling CPU Usage in PostgreSQL
On Monday 31 July 2006 10:06, Aaron Bono wrote: | Is there a way I can tell PostgreSQL to give session a low priority so even | if it does take the full CPU, it only does so if it would otherwise be | idle? the "nice" command might do what you want... Ciao, Thomas -- Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[ADMIN] Cascading replication
Hello, my configuration exists of 2 db-nodes, 2 replication nodes and 1 loadbalancer. Is it able to replicate only some tables (not all) to a third db node. Has someone experiences with cascaded reolication. Can anyone post a sample conf for cascading? Best regards Tom :)
Re: [ADMIN] Start-up script for Solaris
Am Dienstag, 8. August 2006 13:27 schrieb mcelroy, tim:
> Good morning,
>
> Curious if anyone out there has a start-up script for Solaris? A
> version of the Linux /etc/init.d/postgresql one. I recently installed
> postgres on a Solaris 9 box and although I can start up postgres it
> fails to log to the log file as directed and just "doesn't look right",
> like it does on Linux.
>
I have a rather old one, which originally started and stopped a 6.5.2
postgres on Solaris 5.6 . Now it does its job on 7.4 and Solaris 10.
Thomas Mack
=
#!/bin/sh
killproc() {# kill the named process(es)
pid=`/usr/bin/ps -e -o pid,args | \
/usr/bin/grep "$1\>" | \
/usr/bin/grep -v "$0\>" | \
/usr/bin/grep -v grep | \
/usr/bin/awk '{print $1}'`
[ "$pid" != "" ] && kill -s INT $pid
[ "$pid" != "" ] && sleep 2
[ "$pid" != "" ] && kill -s QUIT $pid
}
#
# Start/stop postgres
#
case "$1" in
'start')
echo "Starting postgres74..."
killproc postmaster
/usr/bin/rm -f /usr/local/pgsql/data/postmaster.pid
su - postgres -c "source /usr/local/pgsql/.tcshrc; limit descriptors
512; cd /usr/local/pgsql/bin; ./postmaster -i -p 6007 -o -e >>& server.log&"
;;
'stop')
killproc postmaster # kill postmaster process
/usr/bin/rm -f /tmp/.s.PGSQL.6007
;;
'restart')
$0 stop
sleep 1
$0 start
;;
*)
echo "Usage: /etc/init.d/postgres74 { start | stop | restart }"
;;
esac
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[ADMIN] Silent installation and user accounts
When I try to install silently on windows xp the error message is Internal account lookup failure. No mapping between account names and security IDs was done. I have deleted existing accounts and rebooted the os before. I have read a workaround would be creating the users manually. This is a problem because I have to make an installer the integrates postgres installation by just clicking a button. -- Mit freundlichen Grüßen / Regards Vatter Network Inventory Software Sun Microsystems Principal Partner www.network-inventory.de ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] service account
I have tried creating the service account manually before starting the installer. Nevertheless the installer says "User account .潰瑳牧獥瀀偧獡睳牯d\潰瑳 牧獥瀀偧獡睳牯d does not exist". The batch file is msiexec /i postgresql-8.1-int.msi /qn INTERNALLAUNCH=1 DOSERVICE=1 DOINITDB=1 CREATESERVICEUSER=0 SERVICEACCOUNT="postgres" SERVICEPASSWORD="pgPassword" SUPERUSER="postgres" SUPERPASSWORD="pgPassword" PERMITREMOTE=1
Re: [ADMIN] Programmatically changing passwords
On Thursday 10 August 2006 07:12, David Leangen wrote: | > ALTER USER foo with encrypted password 'bar'; | > CREATE USER foo; | > CREATE DATABASE bar owner foo; | | That makes perfect sense, but how can I do this from the shell? Is | there an easy way to wrap these so I can send them to postgres from | the shell? yes; the following should work: #!/bin/bash psql <<_EOT_ ALTER USER foo with encrypted password 'bar'; CREATE USER foo; CREATE DATABASE bar owner foo; _EOT_ Ciao, Thomas -- Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[ADMIN] Help about types please
Hello everyone,Actually i'm working in a java project which attemps to storage oid data types in a postgres7.4.Anyway, when my application tries to store an oid, it throws a sqlexception that says:ERROR: the column «doc_cidt» is of type oid but yhe _expression_ is of type bytea i'll give you my thanks if someone can helpme with this problem.PD: I also update my postgres to the 8.1 version, but it still don't works. A!, and it occurs on linux plattform (on windows it works properly)
Re: [ADMIN] psql command
Hi, On Thursday 07 September 2006 08:00, Iuri Sampaio wrote: | [EMAIL PROTECTED]:~$ env | grep PATH | PATH=/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games | | Funny thing, speaking about full paths... there is no psql command in | "/usr/bin/" that's because it's presumably installed under /usr/local/pgsql/bin -- look at the PATH variable for user postgres or check with "which psql" as user postgres. | [EMAIL PROTECTED]:~$ ls -l /usr/bin/psql | | ls: /usr/bin/psql: No such file or directory [...] | The postgres commands work fine to postgres user but it doesn't work to a | random user account | and set on .bashrc | | LD_LIBRARY_PATH=:/usr/local/pgsql/lib:/usr/local/pgsql/lib | | PATH=/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games:/usr/local/pgsql/ |b in:/usr/local/pgsql/bin | you want to repeat this step for other users required to run the "psql" command. Ciao, Thomas -- Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[ADMIN]
-- Med venlig hilsen/Kind regards Thomas Damgaard Nielsen http://thomasdamgaard.dk ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] The server lacks instrumentation functions
When connection to the server with pgadmin3 there is a warning: "The server lacks instrumentation functions ..." There may be something wrong with my installation. The server is version 8.1.4, it is the version for sles9 downloaded as rpms from suse. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[ADMIN] Hard time compiling from source
Hi, I have a hard time compiling pgAdmin3 from source on my Linux box. Attached is a logfile from running make, showing the error. Right before trying to compile pgAdmin3 I compiled and installed wxWidgets-2.7.2. My box is a SuSE-9.3 Linux, compiler used is a "gcc version 3.3.5 20050117 (prerelease) (SUSE Linux)" Any idea what's going wrong? Did the wxWidgets include "structure" change recently? Ciao, Thomas -- Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de/ make all-recursive make[1]: Entering directory `/home/pundt/projects/pgadmin3' Making all in i18n /home/pundt/projects/pgadmin3/i18n make[2]: Entering directory `/home/pundt/projects/pgadmin3/i18n' make[2]: Für das Ziel »all« ist nichts zu tun. make[2]: Leaving directory `/home/pundt/projects/pgadmin3/i18n' Making all in src /home/pundt/projects/pgadmin3/src make[2]: Entering directory `/home/pundt/projects/pgadmin3/src' test -f ./include/svnversion.cpp || touch ./include/svnversion.cpp test -d ./.svn && ( V=`svnversion ./..` && OV=`cat ./include/svnversion.h | cut -d' ' -f3|tr -d '"'` ; if [ "$V" != "$OV" ]; then echo "#define VERSION_SVN \"$V\"" > ./include/svnversion.h ; fi ) || true if g++ -DHAVE_CONFIG_H -I. -I. -I.. -DDATA_DIR=\"/usr/local/pgadmin3/share/pgadmin3/\" -Wall -Wno-non-virtual-dtor -I../src/include -I../src/agent/include -I../src/slony/include -I/usr/local/pgsql/include -DSSL -I/usr/lib/wx/include/gtk2-unicode-release-2.7 -I/usr/include/wx-2.7 -D_FILE_OFFSET_BITS=64 -D_LARGE_FILES -D__WXGTK__ -O2 -I/usr/include/libxml2 -I/usr/include/libxml2 -DDATA_DIR=\"/usr/local/pgadmin3/share/pgadmin3/\" -Wall -Wno-non-virtual-dtor -I../src/include -I../src/agent/include -I../src/slony/include -MT svnversion.o -MD -MP -MF ".deps/svnversion.Tpo" -c -o svnversion.o `test -f './include/svnversion.cpp' || echo './'`./include/svnversion.cpp; \ then mv -f ".deps/svnversion.Tpo" ".deps/svnversion.Po"; else rm -f ".deps/svnversion.Tpo"; exit 1; fi if g++ -DHAVE_CONFIG_H -I. -I. -I.. -DDATA_DIR=\"/usr/local/pgadmin3/share/pgadmin3/\" -Wall -Wno-non-virtual-dtor -I../src/include -I../src/agent/include -I../src/slony/include -I/usr/local/pgsql/include -DSSL -I/usr/lib/wx/include/gtk2-unicode-release-2.7 -I/usr/include/wx-2.7 -D_FILE_OFFSET_BITS=64 -D_LARGE_FILES -D__WXGTK__ -O2 -I/usr/include/libxml2 -I/usr/include/libxml2 -DDATA_DIR=\"/usr/local/pgadmin3/share/pgadmin3/\" -Wall -Wno-non-virtual-dtor -I../src/include -I../src/agent/include -I../src/slony/include -MT pgAdmin3.o -MD -MP -MF ".deps/pgAdmin3.Tpo" -c -o pgAdmin3.o `test -f './pgAdmin3.cpp' || echo './'`./pgAdmin3.cpp; \ then mv -f ".deps/pgAdmin3.Tpo" ".deps/pgAdmin3.Po"; else rm -f ".deps/pgAdmin3.Tpo"; exit 1; fi In file included from pgAdmin3.cpp:50: ../src/include/frmMain.h:87: error: 'wxFrameManager' is used as a type, but is not defined as a type. ../src/include/frmMain.h:124: error: `wxFrameManagerEvent' was not declared in this scope ../src/include/frmMain.h:124: error: `event' was not declared in this scope ../src/include/frmMain.h:124: error: invalid data member initialization ../src/include/frmMain.h:124: error: (use `=' to initialize static data members) ../src/include/frmMain.h:124: error: variable or field `OnAuiUpdate' declared void In file included from pgAdmin3.cpp:52: ../src/include/frmQuery.h:46: error: 'wxFrameManager' is used as a type, but is not defined as a type. ../src/include/frmQuery.h:102: error: `wxFrameManagerEvent' was not declared in this scope ../src/include/frmQuery.h:102: error: `event' was not declared in this scope ../src/include/frmQuery.h:102: error: invalid data member initialization ../src/include/frmQuery.h:102: error: variable or field `OnAuiUpdate' declared void make[2]: *** [pgAdmin3.o] Fehler 1 make[2]: Leaving directory `/home/pundt/projects/pgadmin3/src' make[1]: *** [all-recursive] Fehler 1 make[1]: Leaving directory `/home/pundt/projects/pgadmin3' make: *** [all] Fehler 2 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Hard time compiling from source
sorry, wrong list! Ciao, Thomas -- Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[ADMIN] out of memory in backup and restore
Hi, i'm running pg 8.1.0 on a debian linux (64bit) box (dual xeon 8gb ram) pg_dump creates an error when exporting a large table with blobs (largest blob is 180mb) error is: pg_dump: ERROR: out of memory DETAIL: Failed on request of size 1073741823. pg_dump: SQL command to dump the contents of table "downloads" failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: out of memory DETAIL: Failed on request of size 1073741823. pg_dump: The command was: COPY public.downloads ... TO stdout; if i try pg_dump with -d dump runs with all types (c,t,p), but i cant restore (out of memory error or corrupt tar header at ...) how can i backup (and restore) such a db? kr Thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] out of memory in backup and restore
Hi, logfile content see http://www.rafb.net/paste/results/cvD7uk33.html - cat /proc/sys/kernel/shmmax is 2013265920 - ulimit is unlimited kernel is 2.6.15-1-em64t-p4-smp, pg version is 8.1.0 32bit postmaster process usage is 1.8gb ram atm thx Thomas Shoaib Mir schrieb: Can you please show the dbserver logs and syslog at the same time when it goes out of memory... Also how much is available RAM you have and the SHMMAX set? Shoaib Mir EnterpriseDB ( www.enterprisedb.com <http://www.enterprisedb.com>) On 12/15/06, *Thomas Markus* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> wrote: Hi, i'm running pg 8.1.0 on a debian linux (64bit) box (dual xeon 8gb ram) pg_dump creates an error when exporting a large table with blobs (largest blob is 180mb) error is: pg_dump: ERROR: out of memory DETAIL: Failed on request of size 1073741823. pg_dump: SQL command to dump the contents of table "downloads" failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: out of memory DETAIL: Failed on request of size 1073741823. pg_dump: The command was: COPY public.downloads ... TO stdout; if i try pg_dump with -d dump runs with all types (c,t,p), but i cant restore (out of memory error or corrupt tar header at ...) how can i backup (and restore) such a db? kr Thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Thomas Markus Tel:+49 30 29 36 399 - 22 Fax:+49 30 29 36 399 - 50 Mail: [EMAIL PROTECTED] Web:www.proventis.net Web:www.blue-ant.de proventis GmbH Zimmerstraße 79-80 10117 Berlin "proventis: Wir bewegen Organisationen." begin:vcard fn:Thomas Markus n:Markus;Thomas org:proventis GmbH adr;dom:;;Zimmerstr. 79-80;Berlin;Berlin;10117 email;internet:[EMAIL PROTECTED] tel;work:+49 30 29 36 399 22 x-mozilla-html:FALSE url:http://www.proventis.net version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] out of memory in backup and restore
Hi, free diskspace is 34gb (underlying xfs) (complete db dump is 9gb). free -tm says 6gb free ram and 6gb unused swap space. can i decrease shared buffers without pg restart? thx Thomas Shoaib Mir schrieb: Looks like with 1.8 GB usage not much left for dump to get the required chunk from memory. Not sure if that will help but try increasing the swap space... - Shoaib Mir EnterpriseDB ( www.enterprisedb.com <http://www.enterprisedb.com>) On 12/15/06, *Thomas Markus* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> wrote: Hi, logfile content see http://www.rafb.net/paste/results/cvD7uk33.html - cat /proc/sys/kernel/shmmax is 2013265920 - ulimit is unlimited kernel is 2.6.15-1-em64t-p4-smp, pg version is 8.1.0 32bit postmaster process usage is 1.8gb ram atm thx Thomas Shoaib Mir schrieb: > Can you please show the dbserver logs and syslog at the same time when > it goes out of memory... > > Also how much is available RAM you have and the SHMMAX set? > > > Shoaib Mir > EnterpriseDB ( www.enterprisedb.com <http://www.enterprisedb.com> <http://www.enterprisedb.com>) > > On 12/15/06, *Thomas Markus* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > mailto:[EMAIL PROTECTED]>>> wrote: > > Hi, > > i'm running pg 8.1.0 on a debian linux (64bit) box (dual xeon 8gb ram) > pg_dump creates an error when exporting a large table with blobs > (largest blob is 180mb) > > error is: > pg_dump: ERROR: out of memory > DETAIL: Failed on request of size 1073741823. > pg_dump: SQL command to dump the contents of table "downloads" failed: > PQendcopy() failed. > pg_dump: Error message from server: ERROR: out of memory > DETAIL: Failed on request of size 1073741823. > pg_dump: The command was: COPY public.downloads ... TO stdout; > > if i try pg_dump with -d dump runs with all types (c,t,p), but i cant > restore (out of memory error or corrupt tar header at ...) > > how can i backup (and restore) such a db? > > kr > Thomas > > ---(end of > broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] out of memory in backup and restore
df -h FilesystemSize Used Avail Use% Mounted on /dev/sda5 132G 99G 34G 75% / tmpfs 4.0G 0 4.0G 0% /dev/shm /dev/sda1 74M 16M 54M 23% /boot is there another dump tool that dumps blobs (or all) as binary content (not as insert statements, maybe directly dbblocks)? Marcelo Costa schrieb: To decrease shared buffers you need restart your pgsql. If do you make on df -h command what is the result, please send. 2006/12/15, Thomas Markus < [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>: Hi, free diskspace is 34gb (underlying xfs) (complete db dump is 9gb). free -tm says 6gb free ram and 6gb unused swap space. can i decrease shared buffers without pg restart? thx Thomas Shoaib Mir schrieb: > Looks like with 1.8 GB usage not much left for dump to get the > required chunk from memory. Not sure if that will help but try > increasing the swap space... > > - > Shoaib Mir > EnterpriseDB ( www.enterprisedb.com <http://www.enterprisedb.com> <http://www.enterprisedb.com>) > > On 12/15/06, *Thomas Markus* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > mailto:[EMAIL PROTECTED]>>> wrote: > > Hi, > > logfile content see http://www.rafb.net/paste/results/cvD7uk33.html > - cat /proc/sys/kernel/shmmax is 2013265920 > - ulimit is unlimited > kernel is 2.6.15-1-em64t-p4-smp, pg version is 8.1.0 32bit > postmaster process usage is 1.8gb ram atm > > thx > Thomas > > > Shoaib Mir schrieb: > > Can you please show the dbserver logs and syslog at the same > time when > > it goes out of memory... > > > > Also how much is available RAM you have and the SHMMAX set? > > > > > > Shoaib Mir > > EnterpriseDB ( www.enterprisedb.com <http://www.enterprisedb.com> > < http://www.enterprisedb.com> <http://www.enterprisedb.com>) > > > > On 12/15/06, *Thomas Markus* < [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > <mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> > > mailto:[EMAIL PROTECTED]> > mailto:[EMAIL PROTECTED]>>>> wrote: > > > > Hi, > > > > i'm running pg 8.1.0 on a debian linux (64bit) box (dual > xeon 8gb ram) > > pg_dump creates an error when exporting a large table with > blobs > > (largest blob is 180mb) > > > > error is: > > pg_dump: ERROR: out of memory > > DETAIL: Failed on request of size 1073741823. > > pg_dump: SQL command to dump the contents of table > "downloads" failed: > > PQendcopy() failed. > > pg_dump: Error message from server: ERROR: out of memory > > DETAIL: Failed on request of size 1073741823. > > pg_dump: The command was: COPY public.downloads ... TO stdout; > > > > if i try pg_dump with -d dump runs with all types (c,t,p), > but i cant > > restore (out of memory error or corrupt tar header at ...) > > > > how can i backup (and restore) such a db? > > > > kr > > Thomas > > > > ---(end of > > broadcast)--- > > TIP 4: Have you searched our list archives? > > > > http://archives.postgresql.org <http://archives.postgresql.org> > > > > > > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Marcelo Costa ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[ADMIN] eRserver
I would like to hear about any issues related to erserver. I was a little concerned about its use of Java. Java is a great tool for creating application frameworks for the payroll department, but using it for back-end system-level application programming is a bit unnerving. Java is generally slow, memory and CPU intensive and doesn't provide for tight integration like C/C++ applications. Thanks ---(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
[ADMIN] PGSQL and DNCL
Has anyone any experience with PGSQL 7.x and implenting the FTC do-not-call list - which is about 50 million 10 digit N. American phone numbers? If so what structures have you used and what have you done interms of performance tweaks? Is there an equivalent to Oracle's IOT (index organized tables) in PGSQL? Thanks ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Problem with too short column
Hi, I actually try to insert varchar which length is higher than 32 in a column which type is varchar(32). I can't change the type of the column, and I want to trunc the data. I know it's possible ! I have seen this ! Example : "j'aime les saucisses avec du beurre dedans" will be "j'aime les saucisses avec du beu". For the moment it produces a SQL Error ! Is it an option in the config files ? I'm using Postgresql 7.2.2. Thanks in advance. TOm ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[ADMIN] create function problem
I want to index my table using the year of the date. So I create the following function. create function to_year(date) returns varchar as ' declare str varchar; begin str := to_char($1, ""); return str; end; ' language 'plpgsql' with (iscachable, isstrict); But I face the problem as follow select to_year(current_date); WARNING: Error occurred while executing PL/pgSQL functions to_year WARNING: line 4 at assignment ERROR: Attribute "" not found Anybody can help? Thanks. _ Linguaphone : Learning English? Get Japanese lessons for FREE http://go.msnserver.com/HK/30476.asp ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[ADMIN] download problems
Hi, I'm having a really stupid problem -- it seems to be impossible to download postgre. All of the US mirrors timeout. Is this normal? There are no apparent problems with my connection. Thomas E. Burns Founder, jGuru and knowspam.net http://www.knowspam.net -- Enjoy Email with knowspam.net http://www.jguru.com -- FAQs, Forums and News for Java developers [EMAIL PROTECTED] 415.255.7285 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[ADMIN] Compiling problems with 7.4.2
I ran into a problem compiling 7.4.2 on Sun Solaris. The details are as follows. Thanks SunOS 5.8 Generic_108528-29 sun4u sparc SUNW,Ultra-1 Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.8/3.3.2/specs Configured with: ../configure --with-as=/usr/ccs/bin/as --with-ld=/usr/ccs/bin/ld --dis able-nls Thread model: posix gcc version 3.3.2 gcc -O3 -pthreads -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declaration s -fPIC -I. -I../../../src/include -I/usr/local/include -I/usr/local/ssl/include -DFRO NTEND -DSYSCONFDIR='"/usr/local/pgsql/etc"' -c -o thread.o thread.c thread.c: In function `pqGetpwuid': thread.c:116: error: too many arguments to function `getpwuid_r' gmake[3]: *** [thread.o] Error 1 gmake[3]: Leaving directory `/opt/sft/postgresql-7.4.2/src/interfaces/libpq' gmake[2]: *** [all] Error 2 gmake[2]: Leaving directory `/opt/sft/postgresql-7.4.2/src/interfaces' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/opt/sft/postgresql-7.4.2/src' gmake: *** [all] Error 2 ---(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
[ADMIN] E-R Tool
Can someone suggest an E-R tool that will read a _PG_ database and create an E-R diagram - crows feet and all? Are there any that are available for free? Thanks ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] problem compiling 7.4.3
While compiling 7.4.3 on Solaris 8 (sparc) I got: rm -f thread.c && ln -s ../../../src/port/thread.c . gcc -pthreads -O3 -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declar ations -fPIC -I. -I../../../src/include -I/usr/local/include -I/usr/local/ssl/incl ude -D_REENTRANT -D_POSIX_PTHREAD_SEMANTICS -DFRONTEND -DSYSCONFDIR='"/usr/local/p gsql/etc"' -c -o thread.o thread.c thread.c: In function `pqGetpwuid': thread.c:119: error: too few arguments to function `getpwuid_r' thread.c:119: warning: assignment makes pointer from integer without a cast gmake[3]: *** [thread.o] Error 1 gmake[3]: Leaving directory `/opt/sft/postgresql-7.4.3/src/interfaces/libpq' gmake[2]: *** [all] Error 2 gmake[2]: Leaving directory `/opt/sft/postgresql-7.4.3/src/interfaces' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/opt/sft/postgresql-7.4.3/src' GCC: Reading specs from /usr/local/lib/gcc/sparc-sun-solaris2.8/3.4.0/specs Configured with: ../configure --with-as=/usr/ccs/bin/as --with-ld=/usr/ccs/bin/ld --disable-nls Thread model: posix gcc version 3.4.0 SunOS 5.8 Generic_117350-02 sun4u sparc SUNW,Ultra-1 Any patch files for this? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] pg_class corrupted ?
Hi all, After a severe HD crash and the impossibility to read old DAT with our new DAT drive, I've been able to copy $PGDATA/base/mabase on a new HD. I've found all the tables data, and even the java intranet based on it is working fine. My problem is that when I tried to make a dump of the copied DB for a new backup I've had the following error : pg_dump: query to obtain list of tables failed: ERROR: Relation pg_class does not have attribute 1 I can run psql but \dt causes this error : ERROR: Relation pg_class does not have attribute 3 I can make a select * from pg_class but when I select a column from it (i.e. 'select relname from pg_class'), I have the error again : ERROR: Relation pg_class does not have attribute 1. As I'm not a specialist, I can't find a solution or workaround for this problem, I've not been able to find a solution on the net or forums. Anybody has an idea ? Thanks. Bruno Thomas PS: postresql version is 7.2.1 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] shutdown postgres under windows
Hello, I have written a windows tray application which starts postgres under windows. Now I want to shutdown postgres, when windows shutdowns my tray-application. In this case I cannot use the pg_ctl or other programs. If windows is in shutdown mode, it does not allow to start a new program (like pg_ctl) to shutdown postgres. Sending a kill signal does not work under windows. Can I shutdown postgres via a command per TCP/IP or direct with a sql-builtin command? The postgres docu describes only the shutdown functions via pg_ctl. Thanks a lot for any hints, Regards, Thomas Wabner SIgnature Thomas Wabner wissenschaftlicher Mitarbeiter Karl-Liebknechtstrasse 145 04277 Leipzig HTWK Leipzig --- Thomas Wabner CIO Ancoso Development GMBH --- PGP Key: http://search.keyserver.net:11371/pks/lookup?op=get&search=0x48681715&template=netenextract,netennomatch,netenerror ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] Win32 and Tablespaces
Why are tablespaces not supported under Win32? - Thomas Wegner Cabrio Meter - The Weather Plugin for Trillian http://trillian.wegner24.de/cabriometer ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Win32 and Tablespaces
I use the version fro pgInstaller project. Ok, i must wait for a new release. - Thomas Wegner Cabrio Meter - The Weather Plugin for Trillian http://trillian.wegner24.de/cabriometer "Gaetano Mendola" <[EMAIL PROTECTED]> schrieb im Newsbeitrag news:[EMAIL PROTECTED] > Thomas Wegner wrote: > > Why are tablespaces not supported under Win32? > > Who sayd that ? In the last days Andreas Pflug commited a patch > that will permit Win32 version to have tablespace. > > > > Regards > Gaetano Mendola > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] TOAST tables keeps growing!
Title: Message Hi. I'm running a production server with PostgreSQL version 7.2.1, that does email scans. That means up to tens of thousands of entries in the database (log and quarantine) each day, and an equal amount removed every night. The problem is that TOAST tables keeps springing forth and consume disk space. We do a VACUUM every night, but it does not reduce or stop the TOAST table growth. I searched the mailing list for answers to this problem, but found only other people describing the same problem. Namely, that their max_fsm_pages were too low and that postgresql now has lost track of the surplus TOAST data pages. In our postgresql.conf we ourselves had the default value of 1 for max_fsm_pages, which I have now raised to around a million. I restarted psql and did a vacuum to see if that would reduce the disk usage, but no change was seen. So the question(s) is: What can I do to reclaim the wasted TOAST diskspace? Can I find and eliminate the lost TOAST tuples somehow? Thanks! Best regards, Thomas M. Madsen.
Re: [ADMIN] TOAST tables keeps growing!
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane > Sent: 13. august 2004 21:02 > To: Thomas Madsen > Cc: [EMAIL PROTECTED] > Subject: Re: [ADMIN] TOAST tables keeps growing! > > > "Thomas Madsen" <[EMAIL PROTECTED]> writes: > > What can I do to reclaim the wasted TOAST diskspace? > > vacuum full might help. Yes, vacuum full did the trick! > > Can I find and eliminate the lost TOAST tuples somehow? > > Well, one thing to ask is whether it's the toast *table* > that's bloating, or the *index* on the toast table. (VACUUM > VERBOSE would help you find out.) > > If it's the table, then more frequent plain vacuums and/or > increasing FSM should fix it. If the index is growing while > the table stays about the same, you could try periodic > REINDEXes, but the only real solution is to move up to PG 7.4. The bloating was primarily caused by the TOAST tables (several gigs surplus), but the TOAST indexes are also slowly bloating as we have indexes on a UNIX timestamp column. I will have to choose between reindexing and upgrading... For now I will keep an eye on the TOAST tables to see if the new FSM value (1,000,000) keeps the TOAST tables at a reasonable size. Thank you for the input! Cheers, Thomas. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] TOAST tables keeps growing!
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Stephan Szabo > Sent: 13. august 2004 19:36 > To: Thomas Madsen > Cc: [EMAIL PROTECTED] > Subject: Re: [ADMIN] TOAST tables keeps growing! > > > On Thu, 12 Aug 2004, Thomas Madsen wrote: > > > I'm running a production server with PostgreSQL version 7.2.1, that > > does > > You really should upgrade. If not to 7.4.x, at least to the > last 7.2 release. Yes I know, I'm painfully aware of the need to upgrade :)) > > The problem is that TOAST tables keeps springing forth and consume > > disk space. We do a VACUUM every night, but it does not > reduce or stop > > the TOAST table growth. > > > > I searched the mailing list for answers to this problem, but found > > only other people describing the same problem. Namely, that their > > max_fsm_pages were too low and that postgresql now has lost > track of > > the surplus TOAST data pages. > > > > In our postgresql.conf we ourselves had the default value > of 1 for > > max_fsm_pages, which I have now raised to around a million. I > > restarted psql and did a vacuum to see if that would reduce > the disk > > usage, but no change was seen. > > Generally, raising fsm and revacuuming (without full) will > mean that more of the space will get re-used so that it > should stop growing from day to day, but it's not really > going to remove space already taken (excepting blank pages at > the end if it can get appropriate locks I think). > > Vacuum Full should remove the blank space at the cost of an > exclusive lock on the table. Thanks, vacuum full on the table itself worked just perfect! ;) Cheers, Thomas. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[ADMIN] PostgreSQL Logs in Win32 Version from pgInstaller
Hello where can i found the Log files from this Version? - Thomas Wegner Cabrio Meter - The Weather Plugin for Trillian http://trillian.wegner24.de/cabriometer ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[ADMIN] PostgreSQL Migration of databases 7.2 to 7.4
Hello All, We have a server in our datacentre running PostgreSQL 7.2 with about 50 databases set up (for different customers). We have just commissioned a much better specified server which is running PostgreSQL 7.4. We need to migrate the databases from 7.2 to 7.4. The smallest database contains about 200K of data whilst the largest database weighs in at about 100Mb. What's the best way to migrate the data? Would you suggest using the pgdump tools? We've found them to be quite problematic in use and are not entirely sure of the best command line switches to use. We need to make sure the data and permissions get migrated, as the data is being used for live web sites. So obviously speed of migration is important as we will have a period of downtime to complete the migrations. Any suggestions/product ideas would be greatly appreciated. Thanks in advance, Mario ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] [GENERAL] Dump and Restore
On 24/08/2004 14:40 Eduardo S. Fontanetti wrote: How can I do a test if my dumping is really working, I can't apply the dump in my database, because it will overwrite a lot of data. I was thinking about restore in another database name, but I can't, it always restore on the original database. Somebody have a cooking recipe about to test if my backup is working fine, and will help if I need sometime? Are you using pg_dump or pg_dumpall? I just use pg_dump on individual databases (pg_dumpall works on the whole cluster) and can restore that to any database. HTH -- Paul Thomas +--+-----+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[ADMIN] .pgpass and win32
Hello, where i must place the .pgpass file under win32??? - Thomas Wegner Cabrio Meter - The Weather Plugin for Trillian http://trillian.wegner24.de/cabriometer ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] Looking for tool for Graphic Database Design
I use EMS PostgreSQL Manager. They has a graphical Design Tool and many more. - Thomas Wegner Cabrio Meter - The Weather Plugin for Trillian http://trillian.wegner24.de/cabriometer "Sanjay Arora" <[EMAIL PROTECTED]> schrieb im Newsbeitrag news:[EMAIL PROTECTED] > Hi All > > I am looking for a Graphic Database Design Tool, somewhat like MS Access > that can allow me to define tables, fields and relationships. Also > things like triggers etc, if possible. Something that can interface with > Dia or other similar open source diagramming/ ER tools, though I dont > know of any other. > > The tools should preferably able to genearte ddl for postgreSQL. I run > pgSQL 7.4 on RH9. > > With regards. > Sanjay. > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Vacuum full - disk space eaten by WAL logfiles
Title: Message Hi! I ran in to a serious problem with a server running Postgresql 7.2.1. I know that an upgrade would be in its place, but this is not an option at the moment. The problem started with the server running out of space (toast table galore) due to a too low max_fsm_pages number (1,000,000 was too low on this particular server). I raised it to 3,000,000 and tried a "VACUUM FULL" on the table. Out popped an error when it reached the toast table index: "Number of tuples (xxx) not same at number in heap (XXX), recreate index". The numbers (XXX) differed by just 2. It then started recreating the index, but stopped short when another error popped up: "insertion of duplicate keys"-failure. I have subsequently attempted to reindex the tables/indexes, but it didn't help a bit. I still got the: "Number of tuples (xxx) not same at number in heap (XXX), recreate index"- error. I then tried to drop the indexes on the table in question, and then run another "VACUUM FULL". The result is this: SNIP -- backend> vacuum full infected;DEBUG: --Relation infected--DEBUG: Pages 71795: Changed 0, reaped 16560, Empty 0, New 0; Tup 748830: Vac 0, Keep/VTL 0/0, UnUsed 81433, MinLen 142, MaxLen 2032; Re-using: Free/Avail. Space 13279172/10747696; EndEmpty/Avail. Pages 0/21243. CPU 2.05s/0.24u sec elapsed 69.52 sec.DEBUG: XLogWrite: new log file created - consider increasing WAL_FILESDEBUG: Rel infected: Pages: 71795 --> 71795; Tuple(s) moved: 0. CPU 1.37s/1.23u sec elapsed 112.46 sec.DEBUG: --Relation pg_toast_16581--DEBUG: Pages 1517406: Changed 48938, reaped 1134683, Empty 217179, New 0; Tup 1598162: Vac 201754, Keep/VTL 0/0, UnUsed 4053916, MinLen 45, MaxLen 2034; Re-using: Free/Avail. Space 9420709796/9419556500; EndEmpty/Avail. Pages 0/1354671. CPU 38.72s/5.26u sec elapsed 850.95 sec.DEBUG: XLogWrite: new log file created - consider increasing WAL_FILESDEBUG: XLogWrite: new log file created - consider increasing WAL_FILESDEBUG: XLogWrite: new log file created - consider increasing WAL_FILESDEBUG: XLogWrite: new log file created - consider increasing WAL_FILESDEBUG: XLogWrite: new log file created - consider increasing WAL_FILESDEBUG: XLogWrite: new log file created - consider increasing WAL_FILESDEBUG: XLogWrite: new log file created - consider increasing WAL_FILES ... ... DEBUG: XLogWrite: new log file created - consider increasing WAL_FILESFATAL 2: ZeroFill failed to write /var/lib/pgsql/data//pg_xlog/xlogtemp.25146: No space left on deviceDEBUG: shutting downFATAL 2: open of /var/lib/pgsql/data//pg_xlog/002300FE (log file 35, segment 254) failed: No such file or directory-/SNIP The last lines keep coming until no more diskspace is left. I searched this forum and got some hints to this problem. I raised the "checkpoint" default values in postgres.conf to these values, which should have decreased the need for diskspace: - checkpoint_segments = 12 # in logfile segments (16MB each), min 1checkpoint_timeout = 3600 # in seconds, range 30-3600- But the log files keep coming. And my original problem of vacuuming full to reclaim space is unsolved I then moved pg_xlog to a different partition with 2 gigs of diskspace, and raised wal_files further from 24 to 46. Ran "VACUUM FULL" once more. Those 2 gigs was also used up pretty quickly... So this is where i am now... desperate! Any suggestions will be greatly appriciated!!! What can i do to fix this mess?? Regards, Thomas.
Re: [ADMIN] About System Catalogs
Tom Lane wrote: [EMAIL PROTECTED] writes: 2) As i had a very large pg_largeogject, i deleted rows e now i have a clean, small table. The table is empty but its index pg_largeogject_loid_pn_index lasts to retain a lot of bytes. REINDEX should fix this. Is REINDEX still going to be a necessity in the 8.0 release? I remembered at there was a discussion on the mailing list about a fix or need to fix VACUUM so that manually reindexing would not be necessary...
[ADMIN] Index creation failure in postgresql 7.2
smime.p7m Description: S/MIME encrypted message
[ADMIN] FW: Weird problem, cant delete table !
I have a postgresql ( psql (PostgreSQL) 7.2.1 ) database where i have a
problem deleting 1 off the tables
Im logged in locally and access it with root
In the database there is among more, 2 tables with similar names:
infected
infected_
When i try to delete the "infected_" ( drop table "infected_";) it says
ERROR: table "infected_" does not exist.
When i try to recreate it
( create table infected_ as select filename from infected limit 10; ) it
says : type named infected_ already exists
When i check the access rights ( \z ) it looks like i have no rights
more tables/rights
infected {=,postgres=arwdRxt,mailadmin=arwdRxt,root=arwdRxt}
infected_
more tables/rights
When i try to give me rights on
the "infected_" ( grant all on infected_ to mailadmin; )
it says ERROR: relation "infected_" not found
when i do a query to pg_class like this it finds a record select * from
pg_class where relname like 'infected_%'; it does find some records
when i try to make a dump off the database it also complains !
pg_dump: Attempt to lock table "infected_" failed. ERROR: Relation
"infected_" does not exist
Im at a lost here !
Please if anyone could guide me in the right direction :-)
i would be very thankfull !
Best Regards
Nikolaj Steensgaard
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [ADMIN] FW: Weird problem, cant delete table !]
smime.p7m Description: S/MIME encrypted message
[ADMIN] postgresql 7.4.6 and pam_ldap
Hi, I know that i'm not the 1st one who want's to use pam_ldap to authenticate users (55 posts with keywords ldap and pam...). But it also didn't work for me. Please, what's wrong with the following: % pg_config --configure '--host=i386-redhat-linux' '--build=i386-redhat-linux' [...] '--with-openssl' '--with-pam' '--with-krb5=/usr' '--enable-nls' [...] % cat < pg_hba.conf local all postgresident sameuser local all all pam postgresql hostall all 127.0.0.1/32pam postgresql hostall all 192.168.10.0/24 pam postgresql EOF % cat < /etc/pam.d/postgresql--tage auth required pam_stack.so service=system-auth EOF % pg_ctl reload % createuser --adduser --createdb leduc % createdb --owner=leduc --echo leduc % id postgres uid=26(postgres) gid=26(postgres) groupes=26(postgres) $ psql --quiet leduc leduc=# SELECT 1+1; ... IT WORKS !!! % id uid=252(leduc) gid=100(users) groupes=100(users) % psql Mot de passe : psql: FATAL: PAM authentication échouée pour l'utilisateur "leduc" ... IT DOESN'T WORK !!! % tail -f /var/log/messages Jan 5 17:41:17 tage postgresql(pam_unix)[12625]: auth could not identify password for [leduc] Jan 5 17:41:21 tage postgresql(pam_unix)[12627]: authentication failure; logname= uid=26 euid=26 tty= ruser= rhost= user=leduc % psql -U leduc -W Mot de passe : psql: FATAL: PAM authentication échouée pour l'utilisateur "leduc" ... IT DOESN'T WORK !!! % tail -f /var/log/messages Jan 5 17:42:11 tage postgresql(pam_unix)[12635]: authentication failure; logname= uid=26 euid=26 tty= ruser= rhost= user=leduc -- Thomas LEDUC CNRS UMR 1563 - Laboratoire CERMA - Ecole d'Architecture de Nantes Tel: +33 (0) 2 40 59 04 59 -- Fax : +33 (0) 2 40 59 11 77 EAN - Rue Massenet - BP 81931 - 44319 NANTES cedex 3 GPG KeyID: B2342343 Fingerprint: D62CF1A9D4BEDE671602504C46514CC8B2342343 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] postgresql 7.4.6 and pam_ldap
Le 5 janvier 2005 à 22:20, Gémes Géza a écrit : > > I would suggest to retry it with a postgres user readable > /etc/pam.d/postgresql > an with a pg_hba.conf without postgres specified on the last field after > pam. Also if you want ldap authentication, take care, that in > /etc/pam.d/postgresql you don't reference any other non pam_ldap module, > and your /etc/ldap.conf is readable by postgres user. thank you for your help, That's all i have done : $ id uid=26(postgres) gid=26(postgres) groupes=26(postgres) $ cat /etc/pam.d/postgresql authrequired/lib/security//pam_ldap.so $ cat pg_hba.conf local all postgresident sameuser local all all pam hostall all 127.0.0.1/32pam hostall all 192.168.10.0/24 pam $ sed '/^#/d;/^$/d' /etc/ldap.conf host 192.168.10.2 base ou=Users,dc=cerma,dc=archi,dc=fr ssl no pam_password md5 $ psql -U leduc leduc Mot de passe : psql: FATAL: PAM authentication échouée pour l'utilisateur "leduc" => DID NOT WORK !!! # tail -f /var/log/postgresql Jan 6 10:14:06 tage postgres[21450]: [1-1] TRACE: 0: pam_authenticate a échoué : Conversation error Jan 6 10:14:06 tage postgres[21450]: [1-2] EMPLACEMENT : CheckPAMAuth, auth.c:712 Jan 6 10:14:06 tage postgres[21450]: [2-1] FATAL: 28000: PAM authentication échouée pour l'utilisateur "leduc" Jan 6 10:14:06 tage postgres[21450]: [2-2] EMPLACEMENT : auth_failed, auth.c:395 Jan 6 10:14:06 tage postgres[21450]: [3-1] ERREUR: 08006: Impossible d'envoyer les données du client : Relais brisé (pipe) Jan 6 10:14:06 tage postgres[21450]: [3-2] EMPLACEMENT : internal_flush, pqcomm.c:1000 Jan 6 10:14:09 tage postgres[21451]: [1-1] TRACE: 0: pam_acct_mgmt a échoué : User account has expired Jan 6 10:14:09 tage postgres[21451]: [1-2] EMPLACEMENT : CheckPAMAuth, auth.c:723 Jan 6 10:14:09 tage postgres[21451]: [2-1] FATAL: 28000: PAM authentication échouée pour l'utilisateur "leduc" Jan 6 10:14:09 tage postgres[21451]: [2-2] EMPLACEMENT : auth_failed, auth.c:395 => "User account has expired" seems not to be true ! -- Thomas LEDUC CNRS UMR 1563 - Laboratoire CERMA - Ecole d'Architecture de Nantes Tel: +33 (0) 2 40 59 04 59 -- Fax : +33 (0) 2 40 59 11 77 EAN - Rue Massenet - BP 81931 - 44319 NANTES cedex 3 GPG KeyID: B2342343 Fingerprint: D62CF1A9D4BEDE671602504C46514CC8B2342343 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
